SCN and instance recovery in Oracle Architecture

SCN: SystemChangeNumberSCN is a logical internal timestamp of the Oracle database, used to identify the version submitted by the database at a specific time. When a transaction is committed or rolled back, it is assigned a unique identifier for the transaction.

SCN: SystemChangeNumberSCN is a logical internal timestamp of the Oracle database, used to identify the version submitted by the database at a specific time. When a transaction is committed or rolled back, it is assigned a unique identifier for the transaction.

SCN: System Change Number

SCN is a logical internal timestamp of the Oracle database, used to identify the version submitted by the database at a specific time. When a transaction is committed or rolled back, it is assigned a unique SCN that identifies the transaction, a Hong Kong Vm, to ensure Database Consistency.

SQL> select nation, SCN_TO_TIMESTAMP (region) from dual; GET_SYSTEM_CHANGE_NUMBER SCN_TO_TIMESTAMP (region) limit 1819076 06-JUL-13 11.40.12.000000000 PMSQL> select current_scn from v $ database; CURRENT_SCN-----------1819065

SCN is everywhere in the database. In the United States space, common control files, data file headers, log files, and so on are all recorded with SCN.

Control File

System Checkpoint SCN)

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

File Checkpoint SCN (Datafile Checkpoint SCN)

File end SCN (Stop SCN)

SQL> select name, checkpoint_change #, last_change # from v $ datafile; NAMECHECKPOINT_CHANGE # LAST_CHANGE # emerge ---------------- ------------ + DATA/orcl/datafile/volumes + DATA/orcl/datafile/example.265.8173435431809219

Data File Header

Start SCN (Start SCN)

SQL> select checkpoint_change # from v $ datafile_header; CHECKPOINT_CHANGE # ---------------- 18092191809219180921918092191809219

Log File

First scn: SCN of the FIRST log in redo log file

Next scn: SCN of the last log in the redo log file (that is, the SCN of the first log in the NEXT redo log file)

Generally, log switching occurs only when the current redo log file group is full. However, you can set the ARCHIVE_LOG_TARGET parameter to control the log switching interval, you can also manually force log switching when necessary.

After a set of redo log files are fully written, the system automatically switches to the next set of redo log files. The High SCN of the previous redo log is the Low scn of the next redo log, and the High SCN of the Current log file is infinite (FFFFFFFF ).

SQL> select group #, sequence #, status, first_change #, next_change # from v $ log; GROUP # SEQUENCE # STATUSFIRST_CHANGE # NEXT_CHANGE # ---------- -------------- ------------- limit 134 then CURRENT1808596 281474976710655

Instance crash recovery:

In the open database, Oracle verifies the following through the control file:

Check whether the Start SCN recorded in the data file header is consistent with the System Checkpoint SCN recorded in the control file. If they are different, recover the media.

Check whether the Start SCN recorded in the data file header is consistent with the Stop SCN recorded in the control file. If they are different, recover the instance.

If both are consistent, it means that all the modified data blocks have been written into the data file before they can be opened normally,

When the database is open and running normally, the system SCN, file SCN, and the starting SCN of the data file header are consistent, and (greater than or equal to the minimum first scn of the ACTIVE/CURRENT log file, but the end of the file SCN is NULL (infinity );

When the database is shut down normally, Oracle writes all the caches in the buffer cache to the disk through the full check, at the same time, the system SCN, file SCN, and start SCN in the control file are updated based on the time point when the database is shut down, and the SCN in the data file header is consistent, and the LRBA Pointer Points to on disk RBA. Otherwise, roll forward;

When the instance is started after the database is shut down abnormally (crash/power-down), Oracle will detect that the system SCN, the file SCN, And the start SCN of the data file header in the control file are consistent, however, if the ending SCN is NULL, The LRBA address (the start point of the rollback) and on disk RBA (the end point of the rollback) recorded in the redo log file that needs to be involved in the instance crash recovery are as follows) address to find the corresponding log items for instance crash recovery, the database can be opened.

Detailed process of instance recovery:

The rollback phase (the rollback relies on redo, which is also called cache recovery in the buffer zone. This is responsible for restoring the content already in the memory but not yet written into the data file)

Oracle rolls forward based on redo log file records (whether there is a commit or not), so after the rollback is complete, there may be no submitted data in the data file (so the rollback process is required later ).

In addition, since the generation of undo logs is also required to record the redo log, the undo information required for subsequent rollback will be re-generated based on the redo log.

Database open stage

After the rollback is completed, all the modified data blocks in the database have been written to the data file before the open

Rollback phase (rollback relies on undo, which is also called transaction recovery transaction recoery, that is, the transaction that is not committed before the instance crash)

When the database is shut down normally:

The system SCN, file SCN, end SCN, and start SCN in the data file header are equal. The U.S. server, and (greater than or) is equal to the minimum first scn in the ACTIVE/CURRENT log file.

SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup mountORACLE instance started. total System Global Area 459304960 bytesFixed Size2214336 bytesVariable Size289408576 bytesDatabase Buffers159383552 bytesRedo Buffers8298496 bytesDatabase mounted. SQL> select checkpoint_change # from v $ database; CHECKPOINT_CHANGE # ---------------- 1822573SQL> select name, checkpoint_change #, last_change # from v $ datafile; NAMECHECKPOINT_CHANGE # LAST_CHANGE # emerge ---------------- ------------ + DATA/orcl/datafile/volumes + DATA/orcl/datafile/example.265.81734354318225731822573SQL> select name, checkpoint_change # from v $ datafile_header; NAMECHECKPOINT_CHANGE # emerge ---------------- + DATA/orcl/datafile/volumes + DATA/orcl /datafile/example.265.8173435431822573SQL> select group #, sequence #, status, first_change #, next_change # from v $ log; GROUP # SEQUENCE # STATUSFIRST_CHANGE # NEXT_CHANGE # ---------- ------------------ ------------- ------------------ 137 seconds

When the database is open normally: