Difference between switch logfile and archive log current, Difference between SWITCH LOGFILE and ARCHIVE LOG ALL Options

[cagataycebi]

Hi,

Can anyone tell me the differences of;

1. ALTER SYSTEM SWITCH LOGFILE;
2. ALTER SYSTEM ARCHIVE LOG ALL;
3. ALTER SYSTEM ARCHIVE LOG CURRENT;

I read Oracle's document but still didn't understand the difference in real life-use.

If you were writing a hotbackup script, which would you use at the end of datafile copy?

Thanks

[burleson]

ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT both force a log switch.

From Oracle backup documentation:

After an online backup or inconsistent closed backup, always ensure that you have the redo necessary to recover the backup by archiving the unarchived redo logs. When the database is open, run the following SQL statement to force Oracle to switch out of the current log and archive it as well as all other unarchived logs:

ALTER SYSTEM ARCHIVE LOG CURRENT;

See example of hot baqckup script here:

http://www.dba-oracle.com/concepts/hot_backup_script.htm

[cagataycebi]

Hi Mr. Burleson

When I use "ALTER SYSTEM SWITCH LOGFILE", it makes a log switch and archives the current log. That's crystal clear. And when I try "ALTER SYSTEM ARCHIVE LOG CURRENT", I see that a log switch is happening by looking at v$logfile and v$log. And also see a new archivelog file is generated at my archivelog folder. They look like they do exact same job. This is what makes me confuse.

Is there a specific reason for choosing switch logfile after file copy?

[burleson]

>> Is there a specific reason for choosing switch logfile after file copy?

Yes.

It makes a "quiesce point", a firm place whereby that last redo log is a part of the hot backup, and the whole next redo is not.

Make sense?

[cagataycebi]

I know we have to get current log. But I have been wondering which command (switch log or archive log current) would be suitable for a hot backup script.

I found a mail list at http://www.mail-archive.com/oracle-l@fatci...m/msg40028.html . Do you think the information is true in the mail? If that's true, I will replace 'all switch log statements' to 'archive log current' in my hot backup scripts.

[edwardstoever]

[quote name='cagataycebi' date='Feb 21 2009, 08:00 PM' post='48687']
I know we have to get current log. But I have been wondering which command (switch log or archive log current) would be suitable for a hot backup script.

If you have a single instance database, using either command will work fine. If you are on RAC, using "ALTER SYSTEM ARCHIVE LOG CURRENT" will switch the logs on all instances, whereas "ALTER SYSTEM SWITCH LOGFILE" will switch on the instance you are connected to.

Please test to verify.

[burleson]

Hi Edward,

Welcome back!

>> I have been wondering which command (switch log or archive log current) would be suitable for a hot backup script.

It's always safer to wait for the redo log to complete writing to the OS directory:

- “alter system switch logfile” is asynchronous: This command is fast to return to the invoking program because the writing of the redo log to the OS filesystem is done in the background. There is a very small risk in cases where the ARCH process cannot complete writing the redo log, such as cases where the OS archivelog file directory is out of space. It is also risky because the calling script may move on to a subsequent step, assuming that the redo has been written. Some scripts will place a “sleep 60” command in their backup script to allow time for the redo to complete writing, but this is not a best practice.

- “alter system archivelog current” is synchronous: This is fast because this command waits until the online redo log has completed the writing of the redo log file to the filesystem. This command is safer because it waits for the OS to acknowledge (ACK) that the redo log has been successfully written. Hence, “alter system archivelog current” is the best practice for production backup scripts with RMAN.

[RHR]

For easy reference, information on the difference betweent ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVELOG CURRENT is here:

http://dba-oracle.com/t_alter_system_switc...log_current.htm

RHR