Popular Posts

Checkpoint in Oracle Database

Here we are discussing about checkpoint in Oracle database


Image result for checkpoint in oracle

Checkpoint is the mechanism that defines the SCN in the redo thread of database and it is recorded in the control file and each data file header. This very much required for the recovery. CKPT is the process responsible for checkpoint operation and when a checkpoint occurs,oracle must update the header of all data files to record the details of checkpoint. CKPT will not write any block to disk and it is done by DBWn(DB Writer Process).Checkpoint information includes the checkpoint position, SCN, location in online redo log to begin recovery, and so on.

Checkpoint reduces the time required for recovery in case of an instance or media failure. It ensures the dirty buffers in the buffer cache are written to disk regularly and all committed data is written to disk during a consistent shutdown.

Checkpoint occurs at each switch of logfiles, when the dealy for LOG_CHECKPOINT_TIMEOUT is reached,when the size of bytes corresponding to (LOG_CHECKPOINT_INTERBAL*size of IO OS block) is written on the current redo log file and when ALTER SYSTEM SWITCH LOGFILE command is issued.

Thread Checkpoint 


When we say checkpoint usually we are referring Thread checkpoint, it is the event guarantees that all checkpoint-SCN details of all online datafilee has been written to disk.  The database writes to disk all buffers modified by redo in a specific thread before Thread Checkpoint SCN. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations: 
  • Consistent database shutdown 
  • ALTER SYSTEM CHECKPOINT statement 
  • Online redo log switch 
  • ALTER DATABASE BEGIN BACKUP statement. 


Tablespace and data file checkpoints


The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

Incremental checkpoints


An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

No comments