About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Wednesday, November 26, 2014

Alter System Switch Logfile VS Alter System Archive Log Current

Whenever I did a logswitch in past, one thought came in my mind that what's the difference between ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT.

Both commands force a log switch when fired. But the way of doing is different. Please find below details :-

When we fire an ALTER SYSTEM SWITCH LOGFILE, Command completes in less than a second and we get the control back. Reason for this is, it does not wait for ARCH process to write Current Online redo log file to Achive destiation File System. The writing happens in background. So Commad completes in less than a second.
Once this command fired and completed database immediately starts writing to next redo log file . 

But when we fire ALTER SYSTEM ARCHIVE LOG CURRENT, it waits for the ARCH process to write current online Redo Log to File system, Once it gets the acknowledge that redo log has been archived, it gives the control back. Till then it pauses, once writing completed it gives back the control.

In a RAC database both commands differs slightly. PFB :-

ALTER SYSTEM SWITCH LOGFILE when fired from an instance, it switched the current redo log of that particular instance where as ALTER SYSTEM ARCHIVE LOG CURRENT will do a redo log switch for all available instances. In RAC , ALTER SYSTEM ARCHIVE LOG CURRENT is the best practice to use.

For RAC database Production backup scripts ,  ALTER SYSTEM ARCHIVE LOG CURRENT is best pactice to use with , as its waits for an acknowledgement from OS that whether last redo has been written to archive destination and we can be pretty sure that our backup contains the last redo from all theads.

Using ALTER SYSTEM ARCHIVE LOG CURRENT we can spcify which thread to archive, if we leave the thead parameter in the commad, it will so a redo log switch for all threads. This facility is not available with ALTER SYSTEM SWITCH LOGFILE.

PFB detailed analysis :-

SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.02
SQL> alter system archive log current;

System altered.

Elapsed: 00:00:10.40

From above we can see there is much time difference between both commands.

Thread parameter in ALTER SYSTEM ARCHIVE LOG CURRENT command :-

SQL> alter system archive log thread 2 current;

System altered.

I hope this article helped you.

Amit Rath