Oracle Checkpoint

A checkpoint performs the following three operations:

1. Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
   It's the DBWR that writes all modified databaseblocks back to the datafiles.
2. The latest SCN is written (updated) into the datafile header.
3. The latest SCN is also written to the controlfiles.

The update of the datafile headers and the control files is done by the LGWR(CKPT if CKPT is enabled). As of version 8.0, CKPT is enabled by default.

The following events trigger a checkpoint.

• Redo log switch
• LOG_CHECKPOINT_TIMEOUT has expired
• LOG_CHECKPOINT_INTERVAL has been reached
• DBA requires so (alter system checkpoint)

Additionally, if a tablespace is hot backuped, a checkpoint for the tablespace in question is taking place.

While redo log switches cause a checkpoint, checkpoints don't cause a log switch.

The date and time of the last checkpoint can be retrieved through checkpoint_time in v$datafile_header

The SCN of the last checkpoint can be found in v$database.checkpoint_change#.

If the size of the redo log is to small, the performance of the checkpoint will not be optimal. This is the case if the alert.log contains messages like Thread .. cannot allocate new log....