Oracle Database, Back to Basics – Redo Log!

This is part 2 of the Back to Basics series! We will be talking about Oracle Online Redo Logs in this post. Redo Logs are perhaps the most important files in Oracle database. They are the files that help to enforce the ACID properties of the Oracle database.

In a multiple instance setup such as Oracle RAC, we refer to them as Redo threads instead of Redo logs. Each instance will have their own Redo threads for better performance. The Redo Log files will contain all the changes to the database as it occurs. They are filled with redo records that contain the delta changes to the database.

When you update a row in the employee table, you will generate 3 things

  1. A redo record that contains the information about the change to the data block for the table
  2. A redo record for the undo segment data block that contains the information to rollback the transaction
  3. A record in the transaction table of the Undo Segments

Redo records are data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

The Redo logs are written by the Log Writer (LGRW) background process in a circular manner. When are transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

Redo logs will be in either of the 3 different states

  1. Active (Current redo log that is being written to by LGWR, required for instance recovery)
  2. InActive (Not used by LGWR, NOT required for instance recovery)
  3. Archived (If the database is running in ARCHIVELOG mode, Active => Archived => Inactive. A active redo log cannot be reused/inactive until it is archived! )

A database requires a minimum of 2 Redo log groups to function properly! A log switch will occur automatically when it the redo log file is full. You can also automate the frequency by setting FAST_START_MTTR_TARGET.

You can do a manual switch by either of the 2 commands.

  1. alter system switch logfile => LGWR runs in background and return control to user
  2. alter system archive log current => LGWR runs in foreground and return to control only after redo log is archived successfully.

It’s kind of obvious which command you should use for switch redo log files manually, isn’t it? =).

This past few weeks has been pretty crazy for me as I am working on my OCE RAC 11g & my part time degree. Will be posting about DBWR and database buffer next week!


Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s