SCN and CheckPoint

Sytem Change Number (SCN)

System Change Number(SCN) is an Oracle Clock, that keeps on ticking. This number is utilized by Oracle to log the changes made to the database. This number is quite useful while recovering the database from any failure.

SCN is 8Byte (48bit) number represented as 2 parts – SCN_BASE and SCN_WRAP. An SCN_BASE is a 4Byte(32 bits) number and SCN_WRAP is a 2Byte(16 bits) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296 ) , SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This continues till SCN_WRAP reaches it’s maximum(i.e. 2 power 16 = 65536).

SCN = (SCN_WRAP * 4294967296) + SCN_BASE.

Now the question arises in our mind – what happens when SCN reaches it’s maximum? and how the Oracle handles that situation?

Let’s say you have fresh database with both SCN_WRAP and SCN_BASE at value 0. As the database is up and running, SCN value keeps on increasing for every second. For SCN_BASE to reach it’s maximum, it will take around 136 years( 4294967296/(365*24*60*60) – Assuming one transaction per second.). This is just for one Wrap and imagine you have still 65535 wraps to complete. So, no need to fear about reaching maximum.

Even if it does reach maximum, then SCN will be reseted to 0, thus causing a new incarnated database. So, all your old backups and Archived logs becomes useless. Need to take fresh backup’s.

CheckPoint

Checkpoint is an event that happens at a particular time(=SCN). When this happens, all datafiles and control files are updated with SCN at that time. 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 recovery.

So what kind of events makes checkpoint to occur –

	-When all dirty buffers in the Oracle SGA are written to datafiles 
              by DBWR.

	-When redo log switch occurs.

	-Whenever the time set by the LOG_CHECKPOINT_TIMEOUT init 
               parameter is reached  .

	-By issuing the command 'alter system switch logfile' or 
              'alter system checkpoint'.

Oracle stores the SCN associated with the Checkpoint in four places: three of them in the control file and one in the datafile header for each datafile.

System checkpoint SCN in the control file.

Datafile Checkpoint SCN individually in the control file for each datafile.

Checkpoint SCN value in the header of each datafile, which also reffered as Start SCN

The stop SCN is held in the control file for each datafile.

Now let us see how the SCN that is stored in different places by various events are useful during recovery scenarios.

Under the normal conditions, where database is running without any problem, the System Checkpoint SCN, Datafile Checkpoint SCN and Start SCN have equal values. Whereas, Stop SCN will always be NULL under normal conditions.

During a clean shutdown of the database, since it is an event, a checkpoint occurs and the stop SCN value is set to Start SCN of datafile header. Now, all the 4 SCN values are same.
(Just remember !!! if it is a clean shutdown – all the four SCN’s will be same.)

Now let us see what happens when the database is started –

Upon startup, Start SCN value is checked with Datafile Checkpoint SCN, if they both match then Start SCN value is checked with Stop SCN and if they do match, Oracle opens the database without any problem. Since in our case as mentioned above all the 4 values are same and so we have a clean startup. Once the database is opened, stop SCN of the control file will be resetted to NULL again.

So, as explained above, this is how the SCN i.e stored at various places behaves under normal conditions. Now let us know how SCN’s behaves under abnormal conditions like instance crash or media failure.

When an Instance Crash occurs, since a clean shutdown didn’t happend, Stop SCN value is not initiated with the start SCN value and it remains as NULL. Upon startup, Oracle checks the Start SCN with Stop SCN and found that they are not in match. Oracle determines that instance requires a crash recovery. Instance recovery happens automatically at database startup without database administrator (DBA) intervention.

At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is required.