Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,422
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

ALTER SYSTEM SWITCH LOGFILE vs ALTER SYSTEM ARCHIVELOG CURRENT vs ALTER SYSTEM ARCHIVE LOG ALL

Posted by FatDBA on November 18, 2012

Both ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVELOG CURRENT will force a log switch, but they do it in different ways.

ARCHIVELOG CURRENT waits for the writing to complete.  This can take several minutes for multi-gigabyte redo logs.  Conversely, the ALTER SYSTEM SWITCH LOGFILE command is very fast and returns control to the caller in less than a second while ALTER SYSTEM ARCHIVELOG CURRENT pauses.

  • 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 faster to return 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.
  • ALTER SYSTEM ARCHIVE LOG ALL triggers an archive process if your DB (System) has archive logs that needs to be apply if still you try using the command you’ll receive below provided error message.

*
ERROR at line 1:
ORA-00271: there are no logs that need archiving

Advertisements

One Response to “ALTER SYSTEM SWITCH LOGFILE vs ALTER SYSTEM ARCHIVELOG CURRENT vs ALTER SYSTEM ARCHIVE LOG ALL”

  1. Franchesca said

    Please let me know if you’re looking for a writer for your blog. You have some really great posts and I feel I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write
    some articles for your blog in exchange for a link back to mine.
    Please send me an e-mail if interested. Regards!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: