Checkpoint & SCN

What is System Change Number (SCN)?

The SCN is a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN. The value of a SCN is the logical point in time at which changes are made to a database. This number is utilized by Oracle to log the changes made to the database.

SCN is a 6 Byte (48 bit) number whose value is 281,474,976,710,656 and represented as 2 parts - SCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bit) number and SCN_WRAP is a 2 Byte (16 bit) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296 ), SCN_WRAP is increased by one and SCN_BASE will be reset to 0. This continues until SCN_WRAP reaches its maximum, i.e. 2 power 16 = 65536.

SCN = (SCN_WRAP * 4294967296) + SCN_BASE

Even if the SCN value does reach its maximum, then SCN will be reset to 0, thus causing a new incarnated database. So, all your old backups and archived logs becomes useless and you need to take fresh backups.

SCN is incremented with the completion of each and every transaction. A commit does not write to datafiles at all. It does not update control files at all.

The SCN is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".

SCN's are written to redo logs continuously - when you commit they are emitted into the redo stream, and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED.

The current SCN can be obtained by either of the following queries:

select dbms_flashback.get_system_change_number scn from dual;

select current_scn from v$database;

What is a Checkpoint?

A checkpoint is the act of flushing modified, cached database blocks to disk. Normally, when you make a change to a block the modifications of that block are made to a memory copy of the block. When you commit the block, it is not written to the datafile but it is written to the REDO LOG, so we can "replay"  a transaction in the event of an instance failure. Eventually, the system will checkpoint your modified blocks to the datafiles on disk.

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

A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during instance recovery.

The goal of a checkpoint is to get dirty buffers from the SGA onto disk safely.

Events that make checkpoint to occur:

• When a redo log switch occurs.
• Whenever the time set by the LOG_CHECKPOINT_TIMEOUT parameter is reached.
• By issuing the command 'alter system switch logfile' or 'alter system checkpoint'.

The checkpoint process updates the control file when a checkpoint is performed. Remember that a checkpoint operation writes all changed (dirty) blocks to disk. By recording this fact in the control file, Oracle knows what redo records need to be applied in the event of an instance failure. To recover from an instance failure, Oracle needs to apply all redo generated after the last checkpoint recorded in the control file.

DBWR writes dirty blocks from the buffer cache to disk -- that does not happen when you "commit" -- LGWR is involved during a commit.

When the log buffer is 1 MB full, 1/3 full, every 3 seconds or every commit - whichever comes first - those all trigger redo writes
DBWR <--- checkpoint
LGWR <--- commit

NOTE:

• Log switches cause checkpoints. Checkpoints do not cause log switches.
• When the checkpoint is complete, the redo logs that protected the now checkpointed data are not needed for instance recovery anymore.