What is a Checkpoint ?

A checkpoint is the act of flushing modified, cached database blocks from SGA Buffer Cache to disk (DB Datafiles) using DBWR (Database Writer) process.

Normally, when you make a change to a block the modifications of that block are made to a memory copy of the block. When you commit the block, it is not written to the datafile but it is written to the REDO LOG, so we can “replay”  a transaction in the event of an instance failure. Eventually, the system will checkpoint your modified blocks to the datafiles on disk.

A checkpoint number is the SCN number at which all the dirty buffers are written to disk. There can be a checkpoint at object/tablespace/datafile/database level.

A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during instance recovery.

The goal of a checkpoint is to get dirty buffers from the SGA onto disk safely.

Events that make checkpoint to occur:

  • When all dirty buffers in the Oracle SGA are written to datafiles by DBWR.
  • When a redo log switch occurs.
  • Whenever the time set by the LOG_CHECKPOINT_TIMEOUT parameter is reached.
  • By issuing the command ‘alter system switch logfile’ or ‘alter system checkpoint’.

The checkpoint process updates the control file when a checkpoint is performed. Remember that a checkpoint operation writes all changed (dirty) blocks to disk. By recording this fact in the control file, Oracle knows what redo records need to be applied in the event of an instance failure. To recover from an instance failure, Oracle needs to apply all redo generated after the last checkpoint recorded in the control file.

DBWR writes dirty blocks from the buffer cache to disk — that does not happen when you “commit” — LGWR is involved during a commit.

When the log buffer is 1 MB full, 1/3 full, every 3 seconds or every commit – whichever comes first – those all trigger redo writes
DBWR <> checkpoint
LGWR <> commit

NOTE:

  • Log switches cause checkpoints. Checkpoints do not cause log switches.
  • When the checkpoint is complete, the redo logs that protected the now checkpointed data are not needed for instance recovery anymore.

Parameters related to Check Point:

  • LOG_CHECKPOINTS_TO_ALERT
  • LOG_CHECKPOINT_TIMEOUT
  • LOG_CHECKPOINT_INTERVAL
  • FAST_START_MTTR_TARGET

LOG_CHECKPOINTS_TO_ALERT=TRUE to observe checkpoint start and end times in the database alert log.

LOG_CHECKPOINT_TIMEOUT  specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.

LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.

FAST_START_MTTR_TARGET   enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,  FAST_START_MTTR_TARGET  is overridden by LOG_CHECKPOINT_INTERVAL.

 

Leave a Reply