In this lesson, you will learn how to distinguish between failures that a database engine can resolve automatically and those that necessitate manual log intervention.
Automatic Restart Recovery
Most crashes—such as sudden power loss or an OS panic—are handled entirely by the database's internal recovery process. When the system restarts, it performs a Crash Recovery sequence using the Write-Ahead Log (WAL).
The engine scans the log to identify:
- Redo operations: Transactions that committed but were only in memory; these are "replayed" to ensure data integrity.
- Undo operations: Transactions that were active (uncommitted) at the time of the crash; these are rolled back to maintain atomicity.
This is a "self-healing" loop. Because the engine knows the state of the log and the data files via the last successful Checkpoint, it manages this transition without human help.
When Manual Log Intervention is Required
Manual intervention becomes necessary when the recovery process itself fails or is unable to reconcile the state of the database. Common scenarios include:
- Log Corruption: If the physical log file itself is corrupted due to hardware failure, the engine cannot determine the "Redo" or "Undo" status. You may need to restore from a backup and perform Point-in-Time Recovery.
- Media Failure: If a disk sector holding the data files or the log files becomes unreadable, the internal recovery mechanism stops because it cannot complete the necessary reads.
- Inconsistent Log State: Sometimes, a failed recovery leaves the database in an "In-Recovery" loop due to conflicting information between the log and the data files. In these cases, you might use low-level tools to force a reset or extract logs manually for troubleshooting.
Consider a scenario where the database logs an error: CRITICAL: Log sequence number mismatch. This signals that the automatic process is stuck. While the engine handles "expected" crashes (like power loss), manual intervention is the final layer of defense when the system's "memory" (the logs) is damaged or logically inconsistent.
Key Takeaway: Automatic recovery relies on the integrity of the Write-Ahead Log; if the log itself is corrupted or missing, you must transition from automated replay to manual restoration and log-based recovery.