Advancing the Checkpoint: How Oracle Reduces Recovery Time

Checkpointing is Oracle’s way to bound crash-recovery work. A checkpoint records a target SCN and ensures that all data blocks dirtied up to that SCN are written from the buffer cache to datafiles. During instance recovery, Oracle can then start redoing from that checkpoint position instead of scanning the entire redo stream.

What a checkpoint really is

• Data structures: The checkpoint SCN is stored in the control file and each datafile header (checkpoint_change#).
• Who does what: CKPT advances the checkpoint and updates headers; DBWn writes the dirty buffers needed to satisfy that target; LGWR is independent and makes COMMITs durable by flushing redo.

Why checkpointing writes from the buffer cache

Hot blocks tend to stick around in memory. Without checkpoints, frequently updated blocks could remain dirty for a long time, widening the gap between datafiles and the end of redo. Checkpoints “pull” the datafiles forward so recovery only needs to apply recent redo.

Events that advance the checkpoint

• Incremental checkpointing (MTTR policy) - With FAST_START_MTTR_TARGET set, Oracle continuously advances the checkpoint to meet your recovery-time objective (RTO).
• Redo log switch - A switch typically triggers checkpoint activity so the database won’t fall too far behind the redo tail.
• Manual - ALTER SYSTEM CHECKPOINT advances it on demand (use sparingly; let MTTR policy handle it).
• Clean shutdown - Brings files and control file to a consistent point.

What gets written

• DBWn writes the dirty buffers whose block SCNs are ≤ the target checkpoint SCN.
• CKPT updates file headers and control file with the new checkpoint SCN once DBWn has made the writes safe.

Monitoring and diagnostics

-- Target MTTR (seconds) and current estimate
SHOW PARAMETER fast_start_mttr_target;
SELECT estimated_mttr FROM v$instance_recovery;

-- Current database SCN vs. checkpoint SCN in headers
SELECT d.current_scn,
       dh.file#, dh.checkpoint_change#
FROM   v$database d
JOIN   v$datafile_header dh ON 1=1
ORDER  BY dh.file#;

-- Checkpoint and write activity since startup
SELECT name, value
FROM   v$sysstat
WHERE  name IN ('background checkpoints completed',
                'DBWR checkpoints', 'physical writes');

-- Log switch cadence (sizing/frequency)
SELECT sequence#, first_time, next_time
FROM   v$log_history
ORDER  BY first_time DESC
FETCH FIRST 20 ROWS ONLY;

Tuning checklist (23c-friendly)

1. Set MTTR by policy: Choose a realistic FAST_START_MTTR_TARGET; let incremental checkpointing do the work.
2. Size redo logs: Avoid thrashing (very frequent switches) and very large logs that delay checkpoints. Aim for a steady, workload-appropriate switch interval.
3. Ensure async I/O: DBWn benefits from asynchronous writes; confirm at the OS/storage layer.
4. Add DBWn only if needed: Increase DB_WRITER_PROCESSES after confirming sustained dirty-buffer pressure and storage headroom.

Common misconceptions

• "DBWn writes on COMMIT." No-LGWR ensures commit durability by flushing redo. DBWn writes are decoupled.
• "Only log switches cause checkpoints." Modern systems advance checkpoints continuously to meet MTTR goals.
• "More frequent checkpoints are always better." Excessive checkpointing increases write I/O; tune to your RTO.

Usefulness and modernization notes

• The original focus on “minimizing the gap” between redo and datafiles is correct; this rewrite clarifies that MTTR policy drives continuous advancement, not just log switches.
• Roles of CKPT, DBWn, and LGWR are disentangled to avoid the common commit/DBWn confusion.