Different Types of Checkpoints in Oracle

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 get written
• Brings administration up to date
• Several types of checkpoint exist

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
• Statistics updated:
– DBWR checkpoints
– DBWR checkpoint buffers written
– DBWR thread checkpoint buffers written
• Caused by:
– Alter system checkpoint [global]
– Alter database close
– Shutdown
• Controlfile and datafile headers are updated
– CHECKPOINT_CHANGE#

Thread Checkpoint

Writes block images to the database for all dirty buffers from one instance
• Statistics updated:
– DBWR checkpoints
– DBWR checkpoint buffers written
– DBWR thread checkpoint buffers written
• Caused by:
– Alter system checkpoint local
• 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
• Statistics updated:
– DBWR tablespace checkpoint buffers written
– DBWR checkpoint buffers written
– DBWR checkpoints
• Caused by:
– Alter tablespace XXX offline
– Alter tablespace XXX begin backup
– Alter tablespace XXX read only
• 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
• Statistics updated:
– DBWR checkpoint buffers written
– DBWR checkpoints
• Caused by:
– Parallel Query
– Parallel Query component of PDML or PDDL
– Mandatory for consistency

Object “Checkpoint”

Writes block images to the database for all dirty buffers belonging to an object from all instances
• Statistics updated:
– DBWR object drop buffers written
– DBWR checkpoints
• Caused by:
– Drop table XXX
– Drop table XXX purge
– Truncate table XXX
• 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
• Statistics updated:
– DBWR checkpoints
– DBWR checkpoint buffers written
– background checkpoints started
– background checkpoints completed
• Controlfile and datafile headers are updated
– CHECKPOINT_CHANGE#

What is “some” above?

Every 3 seconds CKPT calculates the checkpoint target RBA based on:
The most current RBA
-  log_checkpoint_timeout
– log_checkpoint_interval
– fast_start_mttr_target
– fast_start_io_target
– 90% of the size of the smallest online redo log file
• All buffers dirtied prior to the time corresponding to the target RBA are written to the database

Useful views:-

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