Checkpoint & SCN

Checkpoint is a data structure that indicates the “checkpoint position“, determined by the oldest dirty buffer in the database buffer cache. In terms of Oracle’s clock this position is actually the SCN in the redo stream where instance recovery must begin. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header. 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 Logswitch 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

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Implementing full checkpoint every time would be a costly operation and a major bottleneck for concurrency, so Oracle using different types of checkpoints based on different purposes:

  • Full checkpoint: Writes block images to the database for all dirty buffers from all instances. Controlfile and datafile headers are updated during this checkpoint. Until Oracle 8 log switch was also causing full check point which is changed since 8i onwards for performance reasons. Occurred in following situations
    • Alter system checkpoint global
    • Alter database begin backup
    • Alter database close
    • Shutdown Immediate/Transactional
  • Thread checkpoints: The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Controlfile and datafile headers are updated during this checkpoint. Occures in the following situations
    • Consistent database shutdown
    • Alter system checkpoint local
    • Online redo log switch
  • Tablespace and Datafile Checkpoint: Writes block images to the database for all dirty buffers for all files of a tablespace from all instances. Controlfile and datafile headers are updated during this checkpoint. Occurs in following situations
    • Alter tablespace … offline
    • Alter tablespace … begin backup
    • Alter tablespace … read only
    • Alter database datafile resize ( while shrinking a data file)
  • Parallel Query Checkpoint: Writes block images to the database for all dirty buffers belonging to objects accessed by the query from all instances. It’s mandatory to maintain consistency. Occurs in following situations
    • Parallel Query
    • Parallel Query component of PDML or PDDL.
  • Incremental checkpoints: An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every 3 seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.
  • Object Checkpoint: Writes block images to the database for all dirty buffers belonging to an object from all instances. Occurs in following situations
    • Drop table
    • Drop table … purge
    • Truncate table
    • Drop Index
  • Log Switch Checkpoint: Writes the contents of “some” dirty buffers to the database. Controlfile and datafile headers are updated with checkpoint_change#.
  • Instance Recovery Checkpoint: Writes recovered block back to datafiles. Trigger as soon as SMON is done with instance recovery.
  • RBR Checkpoint: It’s actually Reuse Block Range checkpoint, usually appears post index rebuild operations.
  • Multiple Object Checkpoint: Triggered whenever a single operation causes checkpoints on multiple objects i.e. dropping partitioned table or index.

Whenever anything happened in database, Oracle has a SCN number which has to update into various places. We can classify SCN into following major categories:

  • System (checkpoint) SCN: After a checkpoint completes, Oracle stores the system checkpoint SCN in the control file. We can check that in checkpoint_change# of v$database view.

    SQL> select checkpoint_change# from v$database;

    CHECKPOINT_CHANGE#
    ------------------
    1677903

    SQL> alter system checkpoint;

    System altered.

    SQL> select checkpoint_change# from v$database;

    CHECKPOINT_CHANGE#
    ------------------
    1679716

  • DataFile (checkpoint) SCN: After a checkpoint completes, Oracle stores the SCN individually in the control file for each datafile. The following SQL shows the datafile checkpoint SCN for a datafile in the control file:

    SQL> select name,checkpoint_change# from v$datafile where name like '%system01%';

    NAME                                                 CHECKPOINT_CHANGE#
    ---------------------------------------------------- ------------------
    /u02/app/oracle/oradata/mask11g/system01.dbf                1679716

  • Partial (Checkpoint) SCN: Operational non-full checkpoints for sub set of system i.e. tablespace or a datafile etc, would set checkpoint for affected entities only

    SQL> select name,checkpoint_change# from v$datafile_header where name like '%01.dbf';

    NAME                                                 CHECKPOINT_CHANGE#
    ---------------------------------------------------- ------------------
    /u02/app/oracle/oradata/mask11g/system01.dbf                 1685610
    /u02/app/oracle/oradata/mask11g/sysaux01.dbf                 1685610
    /u02/app/oracle/oradata/mask11g/undotbs01.dbf                1685610
    /u02/app/oracle/oradata/mask11g/users01.dbf                  1685610

    SQL> alter tablespace users read only;

    Tablespace altered.

    SQL> select name,checkpoint_change# from v$datafile_header where name like '%01.dbf';

    NAME                                                 CHECKPOINT_CHANGE#
    ---------------------------------------------------- ------------------
    /u02/app/oracle/oradata/mask11g/system01.dbf                  1685610
    /u02/app/oracle/oradata/mask11g/sysaux01.dbf                  1685610
    /u02/app/oracle/oradata/mask11g/undotbs01.dbf                 1685610
    /u02/app/oracle/oradata/mask11g/users01.dbf                   1685618

    SQL> alter tablespace users read write;

    Tablespace altered.

    SQL> select name,checkpoint_change# from v$datafile_header where name like '%01.dbf';

    NAME                                                 CHECKPOINT_CHANGE#
    ---------------------------------------------------- ------------------
    /u02/app/oracle/oradata/mask11g/system01.dbf                  1685610
    /u02/app/oracle/oradata/mask11g/sysaux01.dbf                  1685610
    /u02/app/oracle/oradata/mask11g/undotbs01.dbf                 1685610
    /u02/app/oracle/oradata/mask11g/users01.dbf                   1685642

  • Start (Checkpoint) SCN: Oracle stores the checkpoint SCN value in the header of each datafile. This is referred to as the start SCN because it is used at instance startup time to check if recovery is required. The following SQL shows the checkpoint SCN in the datafile header for a single datafile.

    SQL> select name,checkpoint_change# from v$datafile_header where name like '%system01%';

    NAME                                                 CHECKPOINT_CHANGE#
    ---------------------------------------------------- ------------------
    /u02/app/oracle/oradata/mask11g/system01.dbf                  1657172

  • End (checkpoint) SCN: The stop SCN or Termination is held in the control file for each datafile. The following SQL shows the stop SCN for a single datafile when the database is open for normal use.

    SQL> select distinct LAST_CHANGE# from v$datafile;

    LAST_CHANGE#
    ------------

    SQL> alter database close;

    Database altered.

    SQL> select distinct LAST_CHANGE# from v$datafile;

    LAST_CHANGE#
    ------------
    2125206

    SQL> select distinct CHECKPOINT_CHANGE# from v$datafile_header ;

    CHECKPOINT_CHANGE#
    ------------------
    2125206

During normal database operation, the stop SCN is NULL for all datafiles that are online in read-write mode. SCN Values while the Database Is Up Following a checkpoint while the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match. The stop SCN for each datafile in the control file is NULL. SCN after a Clean Shutdown After a clean database shutdown resulting from a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL of the database, followed by STARTUP MOUNT, the previous queries on v$database and v$datafile return the following:

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

Here are links to our other posts in SCN series

System Change Number (SCN)
Role of SCN in Instance Recovery & Media Recovery
SCN Structure & Generation Schemes
SCN on Scale of Time & SCN of a ROW
Read Consistency & SCN
Block Sequence Number & SCN

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of either the companies I have worked for or Oracle Corporation and its affiliates. The comments, views and opinions expressed by visitors on this blog are theirs alone and may not reflect mine. Whatever scenarios suggested under this blog were simulated only on demo environment, so it’s advisable to test those in test systems before pushing to your production environment.

Advertisements

10 Responses to Checkpoint & SCN

  1. Pingback: System Change Number (SCN) | Amit Saraswat

  2. Pingback: Role of SCN in Instance Recovery & Media Recovery | Amit Saraswat

  3. Pingback: SCN Structure & Generation Schemes | Amit Saraswat

  4. Pingback: SCN on Scale of Time & SCN of a ROW | Amit Saraswat

  5. Pingback: Read Consistency & SCN | Amit Saraswat

  6. Pingback: Block Sequence Number & SCN | Amit Saraswat

  7. Pingback: ORACLE CHECKPOINTS - ORACLE IN ACTION

  8. Pingback: CHECKPOINTS IN ORACLE | Rajat DBA'S Blog

  9. Good going Amit, keep it up … 🙂

  10. Hand Green says:

    If checkpoint occurs at log switch, why can we still see “active” status of a redo log? I think there will be only two states of a redo log: current and inactive.

Share Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: