A checkpoint is an operation that Oracle performs to ensure data file consistency. When a checkpoint occurs, Oracle ensures all modified buffers are written from the data buffer to disk files. Frequent checkpoints decrease the time necessary for recovery should the database crash, but may decrease overall database performance.
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 checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:
1.) Thread checkpoints : The database writes to disk all buffers modified by redo in a specific thread before a certain target. 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
2.) 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.
3.) 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.
Importance of Checkpoints for Instance Recovery :
Instance recovery uses checkpoints to determine which changes must be applied to the data files. The checkpoint position guarantees that every committed change with an SCN lower than the checkpoint SCN is saved to the data files.
Checkpoint Position in Online Redo Log File
During instance recovery, the database must apply the changes that occur between the checkpoint position and the end of the redo thread. As shown in Figure, some changes may already have been written to the data files. However, only changes with SCNs lower than the checkpoint position are guaranteed to be on disk.
Time and SCN of last checkpoint :
The date and time of the last checkpoint can be retrieved through checkpoint_time in v$datafile_header view
The SCN of the last checkpoint can be found in v$database.
Enjoy J J J
