SCN and instance recovery in Oracle Architecture

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 to ensure Database Consistency.

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)------------------------ -------------------------------------------------------                 1819076 06-JUL-13 11.40.12.000000000 PMSQL>select  current_scn from v$database;CURRENT_SCN-----------    1819065

SCN is everywhere in the database. Common control files, data file headers, log files, and so on are 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;NAME                                          CHECKPOINT_CHANGE# LAST_CHANGE#--------------------------------------------- ------------------ ------------+DATA/orcl/datafile/system.256.817343229                 1809219+DATA/orcl/datafile/sysaux.257.817343231                 1809219+DATA/orcl/datafile/undotbs1.258.817343231               1809219+DATA/orcl/datafile/users.259.817343231                  1809219+DATA/orcl/datafile/example.265.817343543                1809219

Data File Header

• Start SCN (Start SCN)

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

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# STATUS           FIRST_CHANGE#       NEXT_CHANGE#---------- ---------- ---------------- ------------- ------------------         1         34 INACTIVE               1746572            1770739         2         35 INACTIVE               1770739            1808596         3         36 CURRENT                1808596    281474976710655

Instance crash recovery:

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

1. 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.

2. 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, 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 Size                  2214336 bytesVariable Size             289408576 bytesDatabase Buffers          159383552 bytesRedo Buffers                8298496 bytesDatabase mounted.SQL>  select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------           1822573SQL>  select name,checkpoint_change#,last_change# from v$datafile;NAME                                          CHECKPOINT_CHANGE# LAST_CHANGE#--------------------------------------------- ------------------ ------------+DATA/orcl/datafile/system.256.817343229                 1822573      1822573+DATA/orcl/datafile/sysaux.257.817343231                 1822573      1822573+DATA/orcl/datafile/undotbs1.258.817343231               1822573      1822573+DATA/orcl/datafile/users.259.817343231                  1822573      1822573+DATA/orcl/datafile/example.265.817343543                1822573      1822573SQL> select name,checkpoint_change# from v$datafile_header;NAME                                          CHECKPOINT_CHANGE#--------------------------------------------- ------------------+DATA/orcl/datafile/system.256.817343229                 1822573+DATA/orcl/datafile/sysaux.257.817343231                 1822573+DATA/orcl/datafile/undotbs1.258.817343231               1822573+DATA/orcl/datafile/users.259.817343231                  1822573+DATA/orcl/datafile/example.265.817343543                1822573SQL> select group#,sequence#,status,first_change#,next_change# from v$log;    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE#       NEXT_CHANGE#---------- ---------- ---------------- ------------- ------------------         1         37 CURRENT                1822207    281474976710655         3         36 INACTIVE               1808596            1822207         2         35 INACTIVE               1770739            1808596

When the database is open normally:

The end of the file. SCN is NULL (infinity)

SQL> alter database open;Database altered.SQL> select name,checkpoint_change#,last_change# from v$datafile;NAME                                          CHECKPOINT_CHANGE# LAST_CHANGE#--------------------------------------------- ------------------ ------------+DATA/orcl/datafile/system.256.817343229                 1822576+DATA/orcl/datafile/sysaux.257.817343231                 1822576+DATA/orcl/datafile/undotbs1.258.817343231               1822576+DATA/orcl/datafile/users.259.817343231                  1822576+DATA/orcl/datafile/example.265.817343543                1822576

Abnormal shutdown instance crash:

The end of the file SCN is still NULL (infinity)

SQL> shutdown abortORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  459304960 bytesFixed Size                  2214336 bytesVariable Size             289408576 bytesDatabase Buffers          159383552 bytesRedo Buffers                8298496 bytesDatabase mounted.SQL> select name,checkpoint_change#,last_change# from v$datafile;NAME                                          CHECKPOINT_CHANGE# LAST_CHANGE#--------------------------------------------- ------------------ ------------+DATA/orcl/datafile/system.256.817343229                 1822576+DATA/orcl/datafile/sysaux.257.817343231                 1822576+DATA/orcl/datafile/undotbs1.258.817343231               1822576+DATA/orcl/datafile/users.259.817343231                  1822576+DATA/orcl/datafile/example.265.817343543                1822576

Starting an instance will restore the instance:

SQL> alter database open;Database altered.$ tailf /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.logSun Jul 07 00:10:07 2013alter database openBeginning crash recovery of 1 threads parallel recovery started with 3 processesStarted redo scanCompleted redo scan read 192 KB redo, 87 data blocks need recoveryStarted redo application at Thread 1: logseq 37, block 533Recovery of Online Redo Log: Thread 1 Group 1 Seq 37 Reading mem 0  Mem# 0: +DATA/orcl/onlinelog/group_1.261.817343457  Mem# 1: +FRA/orcl/onlinelog/group_1.257.817343463Completed redo application of 0.15MBCompleted crash recovery at Thread 1: logseq 37, block 918, scn 1843004 87 data blocks read, 87 data blocks written, 192 redo k-bytes readSun Jul 07 00:10:13 2013Thread 1 advanced to log sequence 38 (thread open)Thread 1 opened at log sequence 38  Current log# 2 seq# 38 mem# 0: +DATA/orcl/onlinelog/group_2.262.817343467  Current log# 2 seq# 38 mem# 1: +FRA/orcl/onlinelog/group_2.258.817343473Successful open of redo thread 1Sun Jul 07 00:10:14 2013SMON: enabling cache recoverySuccessfully onlined Undo Tablespace 2.Verifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryDatabase Characterset is AL32UTF8No Resource Manager plan activeSun Jul 07 00:10:17 2013replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCSun Jul 07 00:10:21 2013QMNC started with pid=28, OS id=7140Sun Jul 07 00:10:31 2013Completed: alter database open

This article from the "ghost fireworks smile" blog, please be sure to keep this source http://vnimos.blog.51cto.com/2014866/1248546