Important Oracle mechanism: Resolution of SCN mechanism

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 When unexpected shutdown occurs, how can I know which data files have been written and which have not been written during the restart? (likewise, there are similar questions in DG and streams: what are the data that 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. It only adds features to ensure that the Oracle Know what should be restored and what should be copied.

In total, there are 4 medium SCN: System checkpoint SCN, datafile checkpoint SCN, stop SCN, and start 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, the system checkpoint SCN is global for the entire database. Therefore, the 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 (step 4), a redo record exists in the redo log, the system provides it with the latest SCN (the latest SCN can be known through the dbms_flashback.get_system_change_number function), which is recorded in this record. If the redo log is cleared (when the log is full for 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 a log switch or checkpoint occurs (the fifth step above), 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) (which can be queried through the checkpoint_change field in the view v $ datafile_header) update to next SCN and set the system checkpoint SCN in the control file (which can be queried through the field checkpoint_change # Of view v $ database) the datafile checkpoint corresponding to each data file (which can be queried through the V $ datafile field checkpoint_change # In the view) is also updated to next SCN. however, if the tablespace where the data file is located is set to read-only, the start SCN of the data file and the DA in the control file Tafile checkpoint SCN will not 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 a latest SCN to redo record, the system obtains the current Timestamp and converts it to a number as the SCN. we can use the scn_to_timestamp (10 Gb later) 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 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 in the system-the system will refresh the system SCN every 3 seconds or so.

Next, we will briefly introduce how the SCN is stored in the database. Recovery plays a role.

When the database is shut down normally (shutdown immediate/normal), 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 will not or only update part of the Stop SCN.

When the database is started Check whether each datafile checkpoint SCN in the control file is the same as the start SCN in the data file, and check 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 SCN mechanism, we have added some practical functions in Oracle10g and 11g-database flashback and database load reproduction.