Checkpoint and SCN

Checkpoint

A checkpoint is the act of writing dirty (modified blocks from the buffer cache to disk.)

Checkpoint is responsible for

->Signaling DBWn

->Updating datafile headers with checkpoint info. (i.e. SCN)

->Updating Control file with checkpoint info. (i.e. scn)

Checkpoint has two purposes:

(1) To establish data consistency

(2) Enable faster database recovery. 

How is recovery faster?  Because all database changes up to the checkpoint have been recorded in the datafiles,

The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).

Whenever we say checkpoint happened, we mean that the writing of modified database buffers in the database buffer cache to disk. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles and SCNs recorded in the file headers guarantee that all changes made to database blocks prior to that SCN are already written to disk. As a result, only those changes made after the checkpoint need to be applied during recovery.

Checkpoints triggered on following conditions:

Every 3 seconds (Incremental Checkpoint)

When Log switch happened

When instance shutdown normal/transactional/immediate

Whenever Alter Tablespace [Offline Normal| Read Only| Begin Backup]

Controlled by internal checkpoint forced by recovery related parameters i.e. Fast_Start_MTTR_Target etc.

Purpose of Checkpoints

Oracle Database uses checkpoints to achieve the following goals:

Reduce the time required for recovery in case of an instance or media failure

Ensure that dirty buffers in the buffer cache are written to disk regularly

Ensure that all committed data is written to disk during a consistent shutdown

During a checkpoint the following occurs:

-  The database writer (DBWR) writes all modified database blocks in the buffer cache back to datafiles,

-  Checkpoint process (ckpt) updates the headers of all the datafiles to indicate when the last checkpoint occurred (SCN)

Tuning checkpoints involves four key initialization parameters

-  FAST_START_MTTR_TARGET

-  LOG_CHECKPOINT_INTERVAL

-  LOG_CHECKPOINT_TIMEOUT

-  LOG_CHECKPOINTS_TO_ALERT

Note: Log file switches will always override checkpoints caused by following parameters.

- FAST_START_MTTR_TARGET  parameter is the preferred method of tuning incremental checkpoint target.

- FAST_START_MTTR_TARGET enables you  to specify the number of seconds the database takes to perform crash recovery

of a single instance.

Based on internal statistics, incremental checkpoint  automatically adjusts the checkpoint target to meet the requirement of FAST_START_MTTR_TARGET.

SCN

The system change number (SCN) is Oracle's clock - every time we commit, the clock

increments. The SCN just marks a consistent point in time in the database.

It’s used to determine the consistency of database.

It’s stored at various places like control file, data files, redo log files.

If it doesn’t match, that means inconsistency

Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.