Summary of Oracle SCN Implementation Mechanism

Author: runming QQ: 226399587 http://blog.csdn.net/runming918
As an important mechanism in oracle, SCN (System Chang Number) plays an important role in Data recovery, Data Guard, Streams replication, and synchronization between RAC nodes. Understanding the operating mechanism of SCN can help you gain a deeper understanding of the above functions.
Before understanding SCN, Let's first look at how data changes in oracle transactions write data files:
1. Start the transaction;
2. Find the required data block in the buffer cache. If no data block is found, load it into the buffer cache from the data file;
3. The transaction modifies the buffer cache data block. The data is identified as "dirty data" and written into the log buffer;
4. transaction commit. The LGWR process writes "dirty data" in the log buffer to the redo log file;
5. When a checkpoint occurs, the CKPT process updates the information in the file header of all data files, and the DBWn process writes the dirty data in the Buffer Cache to the data file.
After the above five steps, the data changes in the transaction are eventually written into the data file. However, once the database goes down unexpectedly during the intermediate stage, how can I know which data files have been written and which data files have not been written during the restart? (likewise, there are also similar questions in DG and streams: Which data has been copied in the last synchronization in the redo log )? The SCN mechanism can effectively solve the above problems.
SCN is a number, specifically a number that only increases or decreases. This only adds a feature that ensures that Oracle knows what should be restored and what should be copied.
The four are:
1. System Checkpoint SCN
After the checkpoint is completed, ORACLE stores the System Checkpoint SCN number in the control file. We can query through the following SQL statement:
Select checkpoint_change # from v $ database;
2. Datafile Checkpoint SCN
After the checkpoint is completed, ORACLE stores the Datafile Checkpoint SCN number in the control file. We can use the following SQL statement to query the Datafile Checkpoinnt SCN Number of all data files.
Select name, checkpoint_change # from v $ datafile;
3. Start SCN
ORACLE stores the Start SCN number in the data file header.
This SCN is used to check whether Media Recovery is required during database startup.
You can use the following SQL statement to query:
Select name, checkpoint_change # from v $ datafile_header;
4. End SCN (Stop SCN) No.
ORACLE stores the End SCN number in the control file.
This SCN number is used to check whether Instance Recovery is required during database startup.
You can use the following SQL statement to query:
Select name, last_change # from v $ datafile;
When the database runs normally, this SCN is NULL for the online data files that can be read and written.
SCN number and database startup
During database startup, when System Checkpoint SCN, Datafile Checkpoint SCN, and Start SCN are both the same, the database can be started normally without media recovery. media recovery is required if there is a difference between the three. If the End SCN is NULL during startup, You need to perform instance recovery. During ORACLE startup, first check whether media recovery is required, and then check whether instance recovery is required.
SCN number and database Shutdown
If the database is shut down normally, a checkpoint is triggered, and the end scn Number of the data file is set to the Start SCN Number of the corresponding data file.
When the database is started and finds they are consistent, you do not need to perform instance recovery. After the database is started normally, ORACLE sets the end scn number to NULL. If the database is shut down abnormally, the end scn number is NULL.
Why do we need System checkpoint SCN and Datafile Checkpoint SCN?
Why Does ORACLE record the System checkpoint SCN number in the control file?
Datafile Checkpoint SCN number?
There are two reasons:
1. For read-only tablespaces, The Datafile Checkpoint SCN, Start SCN, and end scn numbers of the data files are the same.
The three SCN will be frozen when the tablespace is read-only.
2. If the control file is not the current control file, the System checkpoint is smaller than the Start SCN or end scn number. Record these SCN numbers to identify whether the control file is the current control file.
Recovery database using backup controlfile
When a Start SCN number exceeds the System Checkpoit SCN number, it indicates that the control file is not the current control file. Therefore, you must use using backup controlfile for recovery. This is one of the reasons for recording SystemCheckpoint SCN.
It should be mentioned that when the control file is re-built, the System Checkpoint SCN is 0, and the data of the Datafile Checkpoint SCN comes from the Start SCN. According to the preceding description, using backup controlfile must be used for recovery.
Then how does the system generate the latest SCN? In fact, this number is converted from the timestamp at that time. Whenever you need to generate the latest SCN to redo records, the system obtains the timestamp at that time and converts it to a number as the SCN.
You can use the SCN_TO_TIMESTAMP (10 Gb later) function to convert it back to timestamp:
Select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) from dual;
You can also use the timestamp_to_scn function to convert a timestamp to SCN:
Select timestamp_to_scn (SYSTIMESTAMP) as scn from dual;
If you want to convert the DATE type to the TIMESTAMP type, use the CAST function.
Select cast (sysdate as timestamp) from dual;