Friday, February 22, 2008

Instance Recovery in Single Instance Database

When a database starts oracle performs two consistency checks (among others).
  1. Check if the start SCN value of each datafile header matches the corresponding stop SCN value in the controlfile.
  2. Check if the checkpoint counter values matches.
If these two checks are successful then no instance recovery is needed.

If datafile header SCNs are out of sync then at least an instance recovery is needed.

If checkpoint counter value check fails then Oracle knows datafile was replaced with a backup copy while it was down and requires a media recovery.

Instance recovery is completed when Oracle has performed
  1. Cache recovery : replays the contents of the online redologs of the failed  instance.
  2. Transaction recovery : rollback the uncommitted transactions of the failed instance.

In a nutshell instance recovery is nothing more than using the contents in the online redo logs to rebuild the database buffer cache to the state it was in before the crash.

At startup Database goes through nomount and mount phases. During the checks if it is found that instance recovery is needed then SMON automatically starts the instance recovery process. This phase is know as the roll forward phase and it reinstates all changes to both data blocks as well as undo blocks, for both committed and uncommitted transactions.

Each redo record has bare minimum information to reconstruct a change (block address, new value). In the roll forward phase blocks are brought into the database buffer cache, changes are applied and then written back to the disk.

At the end of the roll forward phase cache recovery is completed and it as  crash never happened.
Once the roll forward phase is over database is opened for users to connect. 

But there are still uncommitted transactions in the database. 
To complete the transaction recovery these uncommitted transactions needs to be rolled back
Oracle does this automatically during rollback phase. If  a user connects and hit a datablock that 
needs to be
rolled back then this is done automatically as roll forward phase has populated the undo segments that was protecting the uncommitted transaction. This ensures the read consistency.