数据库启动过程图及SCN讲解

本文主要是介绍数据库启动过程图及SCN讲解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

数据库启动过程图

日志文件中

FIRST SCN：redo log file中第一条日志的SCN
NEXT SCN：redo log file中最后一条日志的SCN(即下一个redo log file的第一条日志的SCN)

通常,只有当前的重做日志文件组写满后才发生日志切换,但是可以通过设置参数ARCHIVE_LOG_TARGET控制日志切换的时间间隔,在必要时也可以采用手工强制进行日志切换.

一组redo log file写满后，会自动切换到下一组redo log file。上一组redo log的High SCN就是下一组redo log的Low SCN,且对于Current日志文件的High SCN为无穷大(FFFFFFFF)。

在open数据库时,Oracle通过控制文件进行了以下验证：

检查数据文件头部所记录的Start SCN 和控制文件中所记录的System Checkpoint SCN 是否一致,若不同则需要进行介质恢复
检查数据文件头部所记录的Start SCN 和控制文件中记录的Stop SCN是否也一致,若不同则需要进行实例恢复.

如果两个都一致了,说明所有已被修改的数据块已经写入到了数据文件中,才可以正常open,

当数据库open并正常运行期间,系统SCN、文件SCN和数据文件头部的开始SCN都是一致的,且(大于或)等于ACTIVE/CURRENT日志文件的最小FIRST SCN,但文件结束SCN为NULL(无穷大);

当数据库正常关闭时,Oracle通过完全检查点将buffer cache中的所有缓存写到磁盘上,同时根据关闭数据库的时间点更新控制文件中的系统SCN、文件SCN、结束SCN和数据文件头部中的开始SCN,且SCN都是一致的,且LRBA指针指向on disk RBA,否则需要前滚;

当数据库非正常关闭(崩溃/掉电)后启动实例时,Oracle将检测到控制文件中的系统SCN、文件SCN和数据文件头部的开始SCN都是一致的,但是结束SCN为NULL,则在需要参与实例崩溃恢复的redo log file中根据控制文件中记录的LRBA地址(前滚起点)和on disk RBA(前滚终点)地址找出相应的日志项进行实例崩溃恢复,最终才可将数据库open.

回滚阶段(回滚靠undo,又叫事务恢复transaction recoery,即负责回退实例崩溃前没有提交的事务)

正常关闭数据库时：

系统SCN、文件SCN、结束SCN和数据文件头部中的开始SCN都是相等的，且(大于或)等于ACTIVE/CURRENT日志文件中的最小FIRST SCN

  SQL> shutdown immediate  
 Database  closed.  
 Database  dismounted.  
 ORACLE instance shut down.  
 SQL> startup mount  
 ORACLE instance started.  
 Total System  Global Area  459304960 bytes  
 Fixed  Size                  2214336 bytes  
 Variable  Size             289408576 bytes  
 Database  Buffers          159383552 bytes  
 Redo Buffers                8298496 bytes  
 Database mounted.  
 SQL>   select checkpoint_change#  from v$ database ;  
 CHECKPOINT_CHANGE#  
 ------------------  
             1822573  
 SQL>   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      1822573  
 SQL>  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                1822573  
 SQL>  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 
 

正常open数据库时：

文件结束SCN为NULL(无穷大)

  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 
 

异常关机（实例崩溃）时：

文件结束SCN仍为NULL(无穷大)

  SQL> shutdown abort  
 ORACLE instance shut down.  
 SQL> startup mount  
 ORACLE instance started.  
 Total System  Global Area  459304960 bytes  
 Fixed  Size                  2214336 bytes  
 Variable  Size             289408576 bytes  
 Database  Buffers          159383552 bytes  
 Redo Buffers                8298496 bytes  
 Database  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 
 

启动实例将进行实例恢复：

  SQL>  alter database open ;  
 Database  altered.  
 $ tailf /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log  
 Sun Jul 07 00:10:07 2013  
 alter  database open 
 Beginning crash recovery  of 1 threads  
   parallel recovery started  with 3 processes  
 Started redo scan  
 Completed redo scan  
   read 192 KB redo, 87 data blocks need recovery  
 Started redo application  at 
   Thread 1: logseq 37, block 533  
 Recovery  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.817343463  
 Completed redo application  of 0.15MB  
 Completed crash recovery  at 
   Thread 1: logseq 37, block 918, scn 1843004  
   87 data blocks  read , 87 data blocks written, 192 redo k-bytes  read 
 Sun Jul 07 00:10:13 2013  
 Thread 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.817343473  
 Successful  open of  redo thread 1  
 Sun Jul 07 00:10:14 2013  
 SMON: enabling cache recovery  
 Successfully onlined Undo Tablespace 2.  
 Verifying file header compatibility  for 11g tablespace encryption..  
 Verifying 11g file header compatibility  for tablespace encryption completed  
 SMON: enabling tx recovery  
 Database Characterset  is  AL32UTF8  
 No  Resource Manager plan active  
 Sun Jul 07 00:10:17 2013  
 replication_dependency_tracking turned  off ( no async multimaster replication found)  
 Starting background process QMNC  
 Sun Jul 07 00:10:21 2013  
 QMNC started  with pid=28, OS id=7140  
 Sun Jul 07 00:10:31 2013  
 Completed:  alter database  open 
 