My Blog

May 9, 2007

Redo Logs and Recovery

Filed under: Oracle — by enotebook @ 5:57 pm

After I read John Hibbard’s article which is named ‘Redo Logs and Recovery’, try to record my understanding as following:

Redo log operation:
1: Towards the SQL statement of UPDATE emp SET sal = 10 WHERE id = 1234, firstly, search in data buffer cache to determine whether the block of the record has already been there. If not, then access the data file and retrieve it into data buffer cache.

2. Retrieve the rollback block and create the image in data buffer cache. Then log an entry in the log buffer cache:
Entry = TransID + File# + block# + Row# + Column# + value

3. Change the value of sal in data buffer cache, and place an entry with new value into log buffer cache.

4. When the user commits the transaction, then place an entry into log buffer cache:
Entry = TransID + Commit + SCN + Timestamp

For the log buffer cache, it invokes LGWR process to write the log into redo log file when the following events occurred:
1) Commit;
2) One third full of log buffer cache
3) 1Migabytes log
4) DBWR process writes the data into data file
5) 3 seconds
6) Checkpoint

Database recovery:
When the database startup after a crash event, it does following steps to recover the database:
1. Roll toward
Redo all the committed entries exist in log file, called‘re-committed’ .

2. Open database
Database begins to work.

3. Rollback
Rollback the changes which have been already recorded into data file according to the redo log. Just need to find the fist log entry of each transaction, and then use its values to rollback.

Notice that after the version of 9i, DBWR events have been recorded into log file, it enables the recovery process much faster (it’s just used for Rollback process, the roll toward process will skip all the entries with its File# + block# = File_block# ).
DBWR entry = DBWR + File_block# + File_block# + File_block# + File_block#
(File_block# = File# + block#)

Blog at