************************************************ * Description: Knowledge of Oracle database SCN * Compatiablity: RDBMS 11g, 12c * Date: 05:33 PM EST, 04/24/2017 ************************************************ <1> SCN and CheckPoint: | |__ 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. | | |__ SQL> select name,checkpoint_change# from v$datafile_header; 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 <2> 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. <4> Checking Data File Checkpoint SCN, Start SCN, and Stop SCN: | |__ SQL> select file#,name,checkpoint_change#, last_change# from v$datafile; -- Data File CheckPoint SCN, and Stop SCN in control file select file#,name,checkpoint_change# from v$datafile_header; -- Start SCN in each data file header <3> Reference: | |__ https://saruamit4.wordpress.com/2014/11/01/checkpoint-scn/
Your Comments