Crash recovery is the automatic process Oracle performs when an instance fails abruptly. Its goal is: - To roll forward all committed changes that were not yet written to datafiles. To roll forward all committed changes that were not yet written to datafiles. - To roll back any uncommitted transactions left in the system. To roll back any uncommitted transactions left in the system. It ensures the database is brought back to a consistent state , even if the crash happened mid-transaction. Crash recovery runs automatically during instance startup (Mount phase → Open). During normal operation: - Transactions generate redo records . Transactions generate redo records . - Redo is written to redo logs immediately (LGWR). Redo is written to redo logs immediately (LGWR). - Dirty buffers in memory are written to datafiles later (DBWn). Dirty buffers in memory are written to datafiles later (DBWn). Since LGWR works faster than DBWn, some committed changes might still be only in the buffer cache when a crash occurs. So after a crash, the datafiles may not have the latest committed data → this requires REDO (roll forward) . Also, uncommitted changes may exist in the datafiles → this requires UNDO (rollback) . Crash recovery depends on three major structures:

Contain a sequential record of all database changes.

Help reverse changes of uncommitted transactions.

Stores dirty blocks not yet written to datafiles. Oracle uses these to rebuild the database to a consistent state. Crash recovery involves two phases : Oracle applies all changes recorded in redo logs after the last checkpoint .

- Identify the checkpoint SCN (System Change Number). Identify the checkpoint SCN (System Change Number). - Read redo log files (online redo + possibly archived redo). Read redo log files (online redo + possibly archived redo). - Apply all changes from redo to data blocks: Even committed changes not written to datafiles Even some uncommitted updates—these will be rolled back later Apply all changes from redo to data blocks: - Even committed changes not written to datafiles Even committed changes not written to datafiles - Even some uncommitted updates—these will be rolled back later Even some uncommitted updates—these will be rolled back later - Update datafiles to reflect the latest consistent state. Update datafiles to reflect the latest consistent state. Goal: Bring datafiles “forward in time” to the moment just before the crash. After roll-forward, some blocks belong to uncommitted transactions (because Oracle replays redo of all transactions).

- Identify all transactions that were active and uncommitted during the crash. Identify all transactions that were active and uncommitted during the crash. - Read undo records for those transactions. Read undo records for those transactions. - Reverse their changes (using undo blocks). Reverse their changes (using undo blocks). - Finalize rollback and mark transactions as aborted. Finalize rollback and mark transactions as aborted. Goal: Restore transactional consistency. Crash recovery never replays uncommitted transactions as committed. Oracle uses transaction tables in undo segments to determine commit status. Crash recovery time depends on: - Fast-start MTTR Target (primary tunable parameter) Fast-start MTTR Target (primary tunable parameter) - Volume of redo generated after the last checkpoint Volume of redo generated after the last checkpoint - Buffer cache size Buffer cache size - I/O performance I/O performance Setting: Helps guarantee predictable crash recovery time. Suppose: - A user commits data at 12:01 A user commits data at 12:01 - LGWR writes redo at 12:01:01 LGWR writes redo at 12:01:01 - DBWn has not yet written blocks to datafiles DBWn has not yet written blocks to datafiles - Crash happens at 12:01:05 Crash happens at 12:01:05 During recovery:

Redo log says: > Block X was updated and transaction committed Oracle applies redo → block updated Block X was updated and transaction committed Oracle applies redo → block updated

If another transaction was uncommitted: Oracle uses undo to reverse its partial changes. This restores ACID compliance . Crash recovery = uses redo logs only (no backups required) Media recovery = requires datafile restore + archived redo logs Crash recovery happens automatically during STARTUP. During startup, the alert log shows: - Roll forward start/end Roll forward start/end - Roll back start/end Roll back start/end - Recovery SCN Recovery SCN - Number of transactions processed Number of transactions processed - Estimated MTTR Estimated MTTR Sample alert log snippet: Oracle crash recovery is powerful, fast, and completely automatic. It guarantees: - No loss of committed data No loss of committed data - Removal of any uncommitted changes Removal of any uncommitted changes - Quick restore of database consistency Quick restore of database consistency This robust mechanism is one of the biggest reasons Oracle is trusted in mission-critical environments. Explore more with Learnomate Technologies! Want to see how we teach? Head over to our YouTube channel for insights, tutorials, and tech breakdowns: www.youtube.com/@learnomate To know more about our courses, offerings, and team: Visit our official website: www.learnomate.org Interested in mastering Oracle Database Administration? Check out our comprehensive Oracle DBA Training program here: https://learnomate.org/oracle-dba-training/ Want to explore more tech topics? Check out our detailed blog posts here: https://learnomate.org/blogs/ And hey, I’d love to stay connected with you personally! Let’s connect on LinkedIn: Ankush Thavali Happy learning! Ankush😎