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.
• 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
– V$INSTANCE_RECOVERY
– V$SYSSTAT
– V$DATABASE
– V$INSTANCE_LOG_GROUP
– V$THREAD
– V$DATAFILE
– V$DATAFILE_HEADER
