Detailed analysis of the Oracle SCN mechanism

Oracle SCNSystem Chang Number) is an important mechanism in Oracle, it plays an important role in Data recovery, Data Guard, Streams replication, and related synchronization between RAC nodes.

Understanding the operating mechanism of SCN can help you gain a deeper understanding of the above functions.

Before understanding Oracle 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, 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.

In total, there are 4 middle SCN: System Checkpoint) SCN, data file Checkpoint Datafile Checkpoint) SCN, end SCNStop SCN), and start SCNStart SCN ). The SCN in Area 3 exists in the control file, and the last one exists in the data file header.

In the control file, System Checkpoint SCN is global for the entire database, so only one exists, while Datafile Checkpoint SCN and Stop SCN are for each data file, therefore, a data file corresponds to a Datafile Checkpoint SCN and Stop SCN in the control file. During the normal operation of the database, the Stop SCN (which can be queried through the field last_change # In the view v $ datafile) is an infinite number or NULL.

After a transaction is committed), a redo record exists in the redo log, the system provides it with the latest Oracle SCN. The latest SCN can be known through the dbms_flashback.get_system_change_number function) and recorded in this record.

If this record is when the redo log is cleared for full switchover or when the checkpoint occurs, all change logs have been written into the data file ), the SCN is recorded as the low SCN of the redo log. In the redo records written before the log is cleared again, the SCN becomes Next SCN.

When the log switches or the fifth step of checkpoint occurs, all redo records between Low SCN and Next SCN are written to the data file by the DBWn process, the CKPT process records the Start SCN on all data files regardless of whether the data in the redo log affects the data file. (You can query the checkpoint_change # field in the view v $ datafile_header) update to Next SCN.

At the same time, the System Checkpoint SCN in the control file can be queried through the field checkpoint_change # In the view v $ database), and the Datafile Checkpoint corresponding to each data file can be queried through the field checkpoint_change # In the view v $ datafile) it is also updated to Next SCN. However, if the tablespace where the data file is located is set to read-only, neither the Start SCN nor the Datafile Checkpoint SCN in the control file will be updated.

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 Oracle SCN to redo records, the system obtains the timestamp at that time and converts it to a number as the SCN. We can use the SCN_TO_TIMESTAMP10g function to convert it back to timestamp:

 
 

  
  
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  
 
 

You can also use the timestamp_to_scn function to convert a timestamp to SCN:

 
 

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

In addition to reflecting transaction data changes and maintaining synchronization, SCN also plays a "Heartbeat" Role of the system-the system refreshes the system SCN every 3 seconds or so.

Next, we will briefly introduce how SCN plays a role in database recovery.

When the database shuts down immediate/normal normally, it first performs a checkpoint to write data in the log file to the data file, update the SCN in the control file and data file, including the Stop SCN in the control file, to the latest SCN.

Database exception/unexpected shutdown does not or only updates part of the Stop SCN.

When the database is started, Oracle first checks whether each Datafile Checkpoint SCN in the control file is the same as the Start SCN in the data file, and then checks whether each Datafile Checkpoint SCN and Stop SCN are the same. If there are differences, find the lost SCN from the Redo Log and re-write the data file for recovery. The specific data recovery process will not be described here.

As an important mechanism in Oracle, SCN plays a "controller" role in multiple important functions. Understand the generation and implementation of SCN, and help DBAs understand and handle the problems of restoration, DG, and Streams replication.

Finally, using the Oracle SCN mechanism, some practical functions are added to Oracle 10g and 11g-database flashback and database load reproduction.