What is checkpointing and why is it important?
If the Redo Log Buffer is the high-speed stenographer and the Log Writer (LGWR) is the court reporter, then a Checkpoint is the official "Notary Seal" on the database's timeline.
In a system where data is constantly being changed in memory (RAM) but only occasionally written to disk, a Checkpoint is the mechanism that ensures the physical files on your hard drive stay "in sync" with what’s happening in the Instance's brain.
A Checkpoint is an event where the Checkpoint Process (CKPT) and the Database Writer (DBWn) work together to update the database's physical files.
During a checkpoint, Oracle does three main things:
Flush Data: It triggers DBWn to write "dirty buffers" (modified data blocks) from the SGA to the physical Data Files.
Update Headers: It updates the headers of all Data Files with the latest System Change Number (SCN).
Update Control Files: It records the checkpoint information in the Control Files.
Without Checkpointing, your database would be fast, but it would be incredibly fragile. It serves two vital purposes:
Imagine your database has been running for 24 hours. If it crashes, and you haven't performed a checkpoint, Oracle would have to read every single Redo Log from the last 24 hours to reconstruct the data.
By checkpointing every few minutes, you "set a save point." Upon a crash, Oracle only needs to replay the logs from the last checkpoint to the time of the crash.
A checkpoint is the "guarantee" that all committed changes up to a specific SCN have been physically written to the disk. It keeps the "gap" between the memory and the disk manageable.
Checkpointing is a background task, but several specific events can trigger it:
Log Switch: When one Online Redo Log file fills up and Oracle moves to the next one.
Shutdown: When you perform a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL.
Manual Trigger: When a DBA runs ALTER SYSTEM CHECKPOINT;.
Time/Thresholds: Based on the FAST_START_MTTR_TARGET parameter, which tells Oracle how many seconds you’re willing to wait for a crash recovery.
It’s a common misconception that the CKPT process writes the data. It doesn't!
DBWn (Database Writer): Does the heavy lifting. It writes the actual data blocks to the data files.
CKPT (Checkpoint Process): Acts as the project manager. It tells DBWn to write, and then it updates the "metadata" (the file headers and control files) once the write is successful.
| Frequency | Pros | Cons |
| Frequent Checkpoints | Very fast recovery after a crash. | Higher disk I/O; can slightly slow down transaction performance. |
| Infrequent Checkpoints | Lower disk overhead; better peak performance. | Longer recovery time; "stale" data files. |
When a database is running, the data files are marked as "Fuzzy." This means the SCN in the file header is older than the latest change in the database. A checkpoint is the only thing that clears that "fuzziness" by syncing the file headers with the actual state of the data.