Oracle SCN-system change number learning notes

Oracle SCN-system change number learning notes

SCN is the internal clock of Oracle, which is used to reflect changes in the database and is constantly updated during operation. SCN types include:

(1) Current SCN OF THE SYSTEM

(2) Checkpoint SCN (note that it will only be updated with the occurrence of the checkpoint: But in hot standby mode, the SCN will not change during Checkpoint)

A. Stop SCN (stored in the control file, also known as end scn)

When the instance runs normally, the stop scn is empty. When the instance is shut down normally, oracle records the stop scn number corresponding to each data file in the control file, it is used to check whether the stop scn numbers of all data files in the control file exist and are consistent during restart. If yes, the previous instance is shut down normally, cache data corresponding to all data files has been synchronized to the disk, so no redo/undo instance recovery is required. Otherwise, if the stop scn number of a data file in the control file is null, it indicates that the previous instance was shut down abnormally and the instance needs to be restored during this start. Therefore, the stop scn number is used to determine whether the instance needs to be restored at the next start. (If the system is powered off, the stop scn in the control file is still empty)

SQL> select name, last_change # from v $ datafile; -- last_change # corresponding to online data files is empty. last_change # corresponding to offline data files has a value.

SQL> shutdown immediate;

SQL> startup mount;

SQL> select name, last_change # from v $ datafile;

Example:

First, select name, last_change # from v $ datafile; The test01.dbf file has an SCN number because we have offline the data file. When we are online the data file, to report the data file, you must restore the media and directly recover the data file. Re-online the data file will not be a problem. When you re-Execute select name, last_change # from v $ datafile;, the corresponding SCN Number of the data file is gone.

**************************************** **************************************** **************************************

Start scn is the only basis for us to determine whether media recovery is required. Media recovery is to update the SCN number (when the shutdown, the stop SCN number in the control file is the start SCN number in the copy data file ).

**************************************** **************************************** ***************************************

**************************************** **************************************** ********************************

Summary: 1. SCN is divided into two categories: (1) the current SCN of the system is used to record all user operations on the database; (2) the checkpoint SCN is updated only with the refreshing of the checkpoint.

2. checkpoint SCN is divided into four sub-categories. The primary user determines whether media recovery and instance recovery are required when the database is started.

3. First, judge the start SCN number to determine the consistency of all data files at startup. The stop SCN number is used to determine whether the instance is shut down normally (instance recovery ); datafile checkpoint SCN is used to determine the consistency between the data file and the control file. system checkpoint SCN is used to determine the consistency of the control file.

Supplement: In the open stage of the database, in addition to checking the SCN, a data check is also performed, that is, the Checkpoint count (Checkpoint CNT), and the Checkpoint count is the first check.