René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

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.

Events that trigger a checkpoint

The following events trigger a 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.

Time and SCN of last checkpoint

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#.

Size of redo log

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....