What is Checkpoint

What is a Checkpoint?

•      A synchronization event at a specific point in time

•      Causes some or all dirty block images to be written to the database thereby guaranteeing that blocks dirtied prior to that point in time gaet written

•      Brings administration up to date

•      Several types of checkpoint exist

What happens when Checkpoint occurs:

A checkpoint performs the following three operations:

•      DBWR gets the signal from CKPT.

•      DBWRn checks redo log entries are written to online redolog files and writes dirty block in the buffer cache is written to the Data files. That is, it synchronizes.

•      The latest SCN is written (updated) into the Datafile header and control files.

When checkpoint occurs:

The following events trigger a checkpoint.

•      While clean shutdown.
•      While tablespace is offline normally.
•      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.

Time and SCN of last checkpoint
The date and time of the last checkpoint can be found in
checkpoint_time in v$datafile_header

The SCN of the last checkpoint can be found in
checkpoint_change#  in v$database.

Types of Checkpoints?

•      Full Checkpoint

•      Thread Checkpoint

•      File Checkpoint

•      Object “Checkpoint”

•      Parallel Query Checkpoint

•      Incremental Checkpoint

•      Log Switch Checkpoint

Full Checkpoint

 Writes block images to the database for all dirty buffers from all instances

• Caused by:

– Alter system checkpoint [global]

– Alter database close

– Shutdown

• Statistics updated:

– DBWR checkpoints

– DBWR checkpoint buffers written

– DBWR thread checkpoint buffers written

• Controlfile and datafile headers are updated

– CHECKPOINT_CHANGE#

Thread Checkpoint

Writes block images to the database for all dirty buffers from one instance

• Caused by:

– Alter system checkpoint local

• Statistics updated:

– DBWR checkpoints

– DBWR checkpoint buffers written

– DBWR thread checkpoint buffers written

• Controlfile and datafile headers are updated

– CHECKPOINT_CHANGE#

File Checkpoint

Writes block images to the database for all dirty buffers for all files of a tablespace from all instances

• Caused by:

– Alter tablespace XXX offline

– Alter tablespace XXX begin backup

– Alter tablespace XXX read only

• Statistics updated:

– DBWR tablespace checkpoint buffers written

– DBWR checkpoint buffers written

– DBWR checkpoints

• Controlfile and datafile headers are updated

– CHECKPOINT_CHANGE#

Parallel Query Checkpoint

Writes block images to the database for all dirty buffers belonging to objects accessed by the query from all instances

• Caused by:

– Parallel Query

– Parallel Query component of PDML or PDDL

– Mandatory for consistency

• Statistics updated:

– DBWR checkpoint buffers written

– DBWR checkpoints

Object Checkpoint

Writes block images to the database for all dirty buffers belonging to an object from all instances

• Caused by:

– Drop table XXX

– Drop table XXX purge

– Truncate table XXX

• Statistics updated:

– DBWR object drop buffers written

– DBWR checkpoints

• Mandatory for media recovery purposes

Incremental Checkpoint

Writes the contents of “some” dirty buffers to the database from CKPT-Q

• Block images written in SCN order

• Checkpoint RBA updated in SGA

• Statistics updated:

– DBWR checkpoint buffers written

• Controlfile is updated every 3 seconds by CKPT

– Checkpoint progress record

Log Switch Checkpoint

Writes the contents of “some” dirty buffers to the database

• Caused by:

– alter system log switch.

• Statistics updated:

– DBWR checkpoints

– DBWR checkpoint buffers written

– background checkpoints started

– background checkpoints completed

• Controlfile and datafile headers are updated

– CHECKPOINT_CHANGE#

Useful views:-

Useful checkpoint administration views:
– V$INSTANCE_RECOVERY
– V$SYSSTAT
– V$DATABASE
– V$INSTANCE_LOG_GROUP
– V$THREAD
– V$DATAFILE
– V$DATAFILE_HEADER