Oracle CheckPoint usage

A checkpoint is the writing by the DBWR (database writer) process of all modified buffers in the SGA buffer cache to the database data files. Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks, as well as the control files.

Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters.

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

Checkpoints can be forced with the ALTER SYSTEM CHECKPOINT; command.

 

• A number of dirty database buffers covered by the log being checkpointed are written to the data files by DBWn. The number of buffers being written by DBWn is determined by the FAST_START_MTTR_TARGET parameter, if specified.  (Oracle official doc) .

**AskTom***:  a checkpoint is the act of flushing modified, cached database blocks to disk. 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 is not written (but the REDO LOG is -- that makes it so we can "replay" your transaction in the event of a failure). Eventually, the system will checkpoint your modified blocks to disk. there is no relationship between "checkpoint" and sid and instance recovery does not imply "checkpoint". a checkpoint reduces the amount of time it takes to perform instance recovery.......

 

 

	Oracle Checkpoint
		A checkpoint performs the following three operations: 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. The latest SCN is written (updated) into the datafile header. 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. 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. 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....