What Is Oracle Instance Recovery?
Oracle instance recovery occurs automatically when you try to startup and open a database that crashed or was shutdown abort. An instance recovery is needed in this case, because the database is in an inconsistent state, meaning there are committed and uncommitted transactions present. Some committed transactions might not be written yet to the data files (these are only present in the online redo logs, and must be reapplied), and some data files might contain uncommitted changes (these must be rolled back).
Why are committed and uncommitted changes present in datafiles? This is due to the way db writer or DBW background process writes the dirty buffers to data files. The dirty buffers in the buffer cache, could contain committed and uncommitted changes.
How does Oracle know it needs to perform instance recovery? Do we need to explicitly specify it?
When the database is open in read-write mode, a redo thread is also marked open in the control file. When the database is closed “nicely” (with shutdown or shutdown immediate) the redo thread is marked closed in the control file. If the instance crashes, or is shutdown abort, the redo thread is not closed in the control file, so at the next startup, the redo thread shows as open, however, no instance is holding the thread enqueues on the redo thread. This is how Oracle knows it needs to execute instance recovery. You do not need to specify anything, instance recovery is automatic at the next startup statement … no DBA intervention is required. Remember that only when the database is startup open, instance recovery occurs. No instance recovery is executed at startup nomount, or startup mount.
Do you know which background process is in charge of the instance recovery? You guessed it: it is SMON. The SMON background process performs instance recovery, and applies the changes from the redo logs (rolls forward all the changes).
The instance recovery has two phases:
1) cache recovery or roll forward
2) transaction recovery or roll back
In the first phase of the instance recovery, all the changes from the redo logs are applied by SMON. After the roll forward phase completes, the data files contain committed and uncommitted changes. In order to bring the database back to a consistent state, the uncommitted transactions must be rolled back in the second phase. Multiple transactions can be rolled back at the same time.
There are the key things to remember about instance recovery:
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !
