Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 170,656
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Interested in Database Performance Tuning ?

    Learn Oracle Performance Tuning from experts

  • Prashant Dixit is the FatDBA

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

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

  • 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.

Redo Logs

Posted by FatDBA on December 7, 2012

Redo Log Files are used in a circular fashion.

  • One log file is written in sequential fashion until it is filled, and then the second redo log begins to fill.  This is known as a Log Switch.
  • When the last redo log is written, the database begins overwriting the first redo log again.



  • The Redo Log file to which LGWR is actively writing is called the current log file.
  • Log files required for instance recovery are categorized as active log files.
  • Log files no longer needed for instance recovery are categorized as inactive log files.

·        Active log files cannot be overwritten by LGWR until ARCn has archived the data when archiving is enabled.


Log Writer Failure

What if LGWR cannot write to a Redo Log File or Group?  Possible failures and the results are:

  1. At least one Redo Log File in a Group can be written – Unavailable Redo Log Group members are marked as Invalid, a LGWR trace file is generated, and an entry is written to the alert file – processing of the database proceeds normally while ignoring the invalid Redo Log Group members.
  2. LGWR cannot write to a Redo Log Group because it is pending archiving – Database operation halts until the Redo Log Group becomes available (could be through turning off archiving) or is archived.
  3. A Redo Log Group is unavailable due to media failure – Oracle generates an error message and the database instance shuts down.  During media recovery, if the database did not archive the bad Redo Log, use this command to disable archiving so the bad Redo Log can be dropped:


  1. A Redo Log Group fails while LGWR is writing to the members – Oracle generates an error message and the database instance shuts down.  Check to see if the disk drive needs to be turned back on or if media recovery is required.  In this situation, just turn on the disk drive and Oracle will perform automatic instance recovery.

Sometimes a Redo Log File in a Group becomes corrupted while a database instance is in operation.

  • Database activity halts because archiving cannot continue.
  • Clear the Redo Log Files in a Group (here Group #2) with the statement:



Minimum size for an On-line Redo Log File is 4MB.



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: