open阶段的一致性检验

open阶段的一致性检验

oracle在open阶段时，需要进行一致性检验，然后才可以打开数据库，到底做了哪些检验呢？
首先会检查数据文件头的Checkpoint CNT是否与对应的控制文件中的Checkpoing CNT一致，如果相等，则会接下来的检验
然后检查数据文件头的开始SCN和对应控制文件中的结束SCN是否一致，如果结束SCN等于开始SCN,则不需要对那个数据文件恢复
下面会通过转储分析控制文件和1号数据文件：
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> startup force mount;
ORACLE 例程已经启动。
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 360712576 bytes
Database Buffers 54525952 bytes
Redo Buffers 6094848 bytes
数据库装载完毕。
SQL> alter session set events 'immediate trace name controlf level 8';

会话已更改。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_8858.trc


***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
09/28/2014 17:41:29
DB Name "ORCL3939"
Database flags = 0x00404001 0x00001200
Controlfile Creation Timestamp 09/28/2014 17:41:31
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.000b8338 Resetlogs Timestamp 09/28/2014 17:41:34
Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 08/13/2009 23:00:48
Redo Version: compatible=0xb200000
#Data files = 10, #Online files = 10
Database checkpoint: Thread=1 scn: 0x0000.0076948a
Threads: #Enabled=1, #Open=0, Head=0, Tail=0
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
....
....
Max log members = 3, Max data members = 1
Arch list: Head=3, Tail=3, Force scn: 0x0000.00746fa2scn: 0x0000.00768ce3
Activation ID: 3848061321
Controlfile Checkpointed at scn: 0x0000.00768dc1 05/05/2015 12:24:31
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
....
....

***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 13,
last-recid= 2877, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #7: /u01/app/oracle/oradata/orcl3939/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:1110 scn: 0x0000.0076948a 05/05/2015 12:41:20
Stop scn: 0x0000.0076948a 0
5/05/2015 12:41:20
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
....
....
Offline scn: 0x0000.000b8337 prev_range: 0
Online Checkpointed at scn: 0x0000.000b8338 09/28/2014 17:41:34
thread:1 rba:(0x1.2.0)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
....
....
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0





SQL> alter session set events 'immediate trace name file_hdrs level 10';
会话已更改。
SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_8858.trc

DATA FILE #1:
name #7: /u01/app/oracle/oradata/orcl3939/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:1110 scn: 0x0000.0076948a 05/05/2015 12:41:20
Stop scn: 0x0000.0076948a 05/05/2015 12:41:20
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
....
....
Offline scn: 0x0000.000b8337 prev_range: 0
Online Checkpointed at scn: 0x0000.000b8338 09/28/2014 17:41:34
thread:1 rba:(0x1.2.0)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
....
....
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0
上面的信息来自控制文件
下面的信息来自数据文件头（如果数据文件丢失，则数据文件头不能读取）
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3848072073=0xe55ceb89, Db Name='ORCL3939'
Activation ID=0=0x0
Control Seq=14952=0x3a68, File size=96000=0x17700
File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000007 08/13/2009 23:00:53
Backup taken at scn: 0x0000.00713a30 04/29/2015 13:41:44 thread:1
reset logs count:0x333ab14e scn: 0x0000.000b8338
prev reset logs count:0x296a3120 scn: 0x0000.00000001
recovered at 05/05/2015 12:24:15
status:0x2000 root dba:0x00400208 chkpt cnt: 1110 ctl cnt:1109
begin-hot-backup file size: 96000
Checkpointed at scn: 0x0000.0076948a 05/05/2015 12:41:20
thread:1 rba:(0x1ce.1314.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
....
....
Backup Checkpointed at scn: 0x0000.00713a30 04/29/2015 13:41:44
thread:1 rba:(0x1bd.b6c8.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
....
....

首先会检查数据文件头的Checkpoint CNT是否与对应的控制文件中的Checkpoing CNT一致：
由上知控制文件中记录了chkpt cnt 1110 数据文件头记录了chkpt cnt 1110 ctl cnt 1109
为什么数据文件头的chkpt cnt 比 ctl cnt大1呢，这是因为检查点在更新控制文件和数据文件头上的chkpt cnt时，可以获得当前的ctl cnt，把当前的ctl cnt 写入到了数据文件头，即1109
这一步验证已经通过
然后检查数据文件头的开始SCN和对应控制文件中的结束SCN是否一致：
Checkpointed at scn: 0x0000.0076948a 05/05/2015 12:41:20
两者一致，可以正常启动
控制文件记录的scn 是数据库最后一次成功完成检查点的scn,数据文件头记录的scn 是最后一次完成检查点的scn，两者相等，则不需要对你数据文件进行恢复，如果不一致，则需要对数据文件

进行恢复。假如数据库异常关闭后，数据文件头记录的scn比较旧，与控制文件记录的不一致，则需要进行恢复，并且数据文件头记录的scn是恢复的起点

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 360712576 bytes
Database Buffers 54525952 bytes
Redo Buffers 6094848 bytes
数据库装载完毕。
SQL> select file#,checkpoint_change# from v$datafile;


FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 7771815
2 7771815
3 7771815
4 7771815
5 7771815
7 7771815
8 7771815
9 7771815
11 7771815
12 7771815


已选择10行。


SQL> select file#,checkpoint_change# from v$datafile_header;


FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 7771815
2 7771815
3 7771815
4 7771815
5 7771815
7 7771815
8 7771815
9 7771815
11 7771815
12 7771815

已选择10行。

上面两者是相等的，没有问题，读者可以模拟需要恢复案例。