Oracle SCN

1. What is SCN?

• A system change number (SCN) is a logical, internal timestamp used by Oracle Database which is necessary to satisfy the ACID properties of a transaction. SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time, and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time in the database. Several events may share the same SCN, which means that they occurred at the same time in the database.

• Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.

2. Four SCN

• Three in Control file:
  • System checkpoint SCN
    

                          SQL> select checkpoint_change# from v$database;
                                     CHECKPOINT_CHANGE#
                                     --------------------
                                     292767

• Datafile checkpoint SCN
  

                           SQL> select name,checkpoint_change# from v$datafile where name like '%users01%';
                                       NAME                                                        CHECKPOINT_CHANGE#
                                       ----------------------------------------------      --------------------
                                       /u02/oradata/OMFD1/users01.dbf       292767

• Stop SCN 

                           SQL> select name,last_change# from v$datafile where name like '%users01%';
                                       NAME                                                      LAST_CHANGE#
                                       ---------------------------------------------     ------------
                                       /u02/oradata/OMFD1/users01.dbf                                                               Normal datafile in read-write mode, last_change# should be NULL

• SCN in datafile header
  

• Start SCN
  

                           SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';
                                       NAME                                                     CHECKPOINT_CHANGE#
                                       ---------------------------------------------    ----------
                                       /u02/oradata/OMFD1/users01.dbf    292767

3.  Oracle transaction change data

• Transaction start;
• Check data block in buffer cache, if no, load it to buffer cache;
• Change data block in buffer cache, mark it as "dirty data", and write the change vector to log buffer;
• Commit the trasaction, LGWR write change vector from log buffer to redo log file on disk;
• When prompt a checkpoint, CKPT process update all datafile header, DBWn process write dirty data from buffer cache to datafile.
  

4. SCN mechanism to deal with the corruption during a transaction

     In the control file, System Checkpoint SCN is for the whole database, only one exist, but each datafile has one  Datafile Checkpoint SCN and Stop SCN in the control file. When database under running, Stop SCN is NULL or infinity.

     When transaction commit, system will record one SCN in redo log.  When switch logfile or prompt checkpoint, all redo records will write to datafile, and CKPT process will update Start SCN and Datafile Checkpoint of all datafiles, and System Checkpoint SCN in control file also update.

5.  Switch between timestamp and SCN

  SQL> select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number) from dual;

            GET_SYSTEM_CHANGE_NUMBER                    SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)

            ------------------------                    ---------------------------------------------------------

            2877076756                                                     17-AUG-07 02.15.26.000000000 PM

     SQL> select timestamp_to_scn(SYSTIMESTAMP) as scn from dual;

                 SCN

       --------

       2877078439


6. SCN in recovery

• Shutdown immediate/normal

             Prompt checkpoint, write log buffer to log file, and update all SCN in control file and datafile.

• Disorder shutdown 

       When database startup, oracle check the equality of Datafile Checkpoint SCN in control file and Start SCN in datafile, also check the equality of Datafile Checkpoint SCN and Stop SCN. If they are not the same, try to find SCN lost in redo log and rewrite it to datafile.