Oracle Checkpoint ( CKPT ) Background Process

I will explain Oracle Checkpoint ( CKPT ) Background Process in this post.

Oracle Checkpoint ( CKPT ) Process

CKPT (Checkpoint Process): When this process is triggered, the Database Writer (DBW) process writes dirty blocks in the database buffer cache to datafiles. It also updates the Datafiles header ( latest checkpoint SCN ) and controlfiles with checkpoint information.

If this process is triggered at very frequent intervals, the database will slow down because disk I / O will increase.

If it is rarely triggered, it will take some time to recover instance when the Oracle database instance crashes suddenly. Because changing blocks are not written to datafiles and the number of changing blocks is accumulated. Therefore, during the recovery process, the number of blocks to be written from the online redo log to the data files will be more.

Determining the frequency of triggering of this process is important for the above reasons.

Checkpoint ( CKPT ) Process

Oracle instance has 3 parameters related with the Checkpoint, you can list them as follows.

SQL> show parameter Checkpoint

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
SQL>

If you set parameter LOG_CHECKPOINTS_TO_ALERT=TRUE , you can see checkpoint start and end times in the database alert log.

Checkpoint automatically occurs at the following situations:

1. Log switch
2. If the datafile is offline
3. Shutdown immediate command
4. ALTER DATABASE BEGIN BACKUP statement.
5. fast_start_mttr_target parameter
6. Manual DBA command as follows.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL>

You can list the Checkpoint process on the server as follows.

[MSDB1]/home/oracle $ ps -ef | grep -i ckpt
grid 104640 1 0 Apr21 ? 00:19:55 asm_ckpt_+ASM1
oracle 121685 182163 0 17:07 pts/3 00:00:00 grep --color=auto -i ckpt
oracle 185010 1 0 Jun21 ? 00:23:42 ora_ckpt_MSDB1
[MSDB1]/home/oracle $

You can read the following post to learn more details about Oracle Background process and Oracle Architecture.

Oracle Database Architecture -3 SMON, PMON, DBWR,CKPT and LGWR Processes

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )