Tales From A Lazy Fat DBA

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

  • Likes

    • 148,962
  • Archives

  • Categories

  • Subscribe

  • Advertisements

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.

image008

 

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

ALTER DATABASE CLEAR UNARCHIVED LOG

  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:

ALTER DATABASE CLEAR LOGFILE GROUP 2;

 

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

 

Advertisements

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: