Oracle Checkpoint Process

A checkpoint is an event that ensures that all modified database buffers are written to the database. During the checkpoint processing the CKPT process also updates the header files of the data files, control files, and the redo log files. Let’s review this process in more detail.

During a checkpoint, DBWR writes all dirty buffers in the database buffer cache to disk. Checkpoints are considered to be either fast or slow. A fast checkpoint means that the DBWR process scans for dirty buffers in the data buffer cache, buffers with data that have been modified and not written to disk, without interruption. On the other hand, a slow checkpoint means that the DBWR process can be interrupted, allowing the CPU to do some foreground work, such as executing user processes. I would recommend reviewing the Oracle Server Concepts manual for a more complete discussion of checkpoints. Checkpoint processing is complicated because there can be multiple active checkpoint requests outstanding at any one moment. There is a new queue in Oracle8 to help manage these requests, and it’s called the checkpoint queue. The checkpoint queue contains pointers to dirty, logged buffers ordered by their redo value.
Ordering the dirty database cache buffers in this way, if a data buffer is modified at a later time, it is not reordered in the queue. DBWR knows which buffers must be written out to satisfy a particular checkpoint request. This also means that every block that is written to disk will satisfy the earliest outstanding checkpoint request. Prior to Oracle8, this was not the case.
Each checkpoint has a number assigned to it, the checkpoint SCN (System Control Number, a unique number assigned to each transaction). This number is used to synchronize the redo, control, and data files. The database is considered to be in a consistent state if they all have the same number. The control file is used to confirm the correct SCN number. If any of the file headers contain a different number, the database is inconsistent and a failure occurs. If this happens, the database must be recovered.

Checkpoints help you in the instance recovery process. Once the dirty buffers are written to disk, the redo entries prior to the checkpoint SCN can be ignored during the roll-forward phase of instance recovery. You can review LOG_CHECKPOINTS_TO_ALERT to determine if checkpoints are occurring often enough. I usually recommend setting the checkpoint interval to 20-30 minutes for On-Line Transaction Processing applications.
The next lesson is about placing your file on disk.

Click the Quiz link below to answer some questions about the archive log file.
Archive Log File - Quiz