Oracle checkpoint scn研究
时间：2010-06-06 来源：rushmeplz
Oracle里关于checkpoint的scn有这几种
1.system checkpoint scn
select checkpoint_change# from v$database;
2.datafile checkpoint scn
select checkpoint_change# from v$datafile;
3.datafile header checkpoint scn
select checkpoint_change# from v$datafile_header;
4.datafile checkpoint stop scn
select last_change# from v$datafile;
其中system scn,datafile scn,stop scn记录在control file中,datafile header scn记录在数据
文件头中,在open下，各个scn都相等，instance crash或者media failure下,会有不同情况
instance crash
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
595718 SQL> select checkpoint_change#,last_change# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
595732
595732
595732
595732 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
595732
595732
595732
595732 system scn<datafile scn=data header scn stop scn无穷大 所以判断为instance crash，恢复界限从datafile scn(595732)开始,如果redolog完整，recover database就可以应用到最新的redo entries,不需要resetlogs 如果redolog不完整,recover database不起作用，因为此时datafile scn大于上一个redolog 的high scn,而用于恢复的当前redolog不可用,只能从backup里恢复或者_allow_resetlogs_corruption=true来打开 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata/gc/system01.dbf' restore database后的状态 SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
621223
621223
621223
621223 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
595732
595732
595732
595732 recover database until cancel后的状态 SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
621223
621223
621223
621223 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
621223
621223
621223
621223 然后就可以open resetlogs 或者recover出现ORA-01194: file 1 needs more recovery to be consistent后强行打开 using backup controlfile 此时会出现datafile scn<dataheader scn,此语句说明恢复时忽略datafile scn直到完全恢复或until cancel; SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
621349
621349
621349
621349 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
622097
622097
622097
622097 using backup datafile 此时需要media recovery,datafile scn>dataheader scn SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
622059
622059
622059
622059 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
622059
622059
622059
621820 关于重建controlfile noresetlogs和resetlogs no resetlogs 在重建时取current redolog的最新scn作为datafile scn此时数据库一致 resetlogs 在重建时取datafile header的scn作为datafile scn 两种的system checkpoint scn都为0 resetlogs 重置redolog 的sequence,如果日志文件不存在就重建,resetlogs为避免恢复后的库用到恢复前的redo entries产生不一致而重置关于redolog的信息 redolog和checkpoint 根据 select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like '%redo%'; 来查看每次操作产生的redolog的量 alter system dump logfile '/xxx' REDO RECORD - Thread:1 RBA: 0x000001.00000068.014c LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0007463f SUBSCN: 1 06/05/2010 14:35:34
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 2 rdba: 0x00800029 BFT:(1024,8388649) non-BFT:(2,41)
scn: 0x0000.000745da seq: 0x01 flg:0x04 redo entries样例 各种操作首先放在sga log buffer, buffer cache,undo cache里，其中log buffer满1/3,1MB,commit时，由lgwr写入redo log 里,记录当时checkpoint scn 由于数据库还没checkpoint，所以controlfile scn,datafile scn,header scn和redo entries的scn不一致，在checkpoint后达到一致 所以数据库的各个操作产生的redo首先放在log buffer里,此时commit就写入redolog,dbwr还未写磁盘, 此时redolog出现问题,这个操作相当于没有发生过，dbwr写入磁盘的，因为checkpoint本身的关系(增量checkpoint,数据写而undo未写的),数据文件是不一致的
------------------
595718 SQL> select checkpoint_change#,last_change# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
595732
595732
595732
595732 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
595732
595732
595732
595732 system scn<datafile scn=data header scn stop scn无穷大 所以判断为instance crash，恢复界限从datafile scn(595732)开始,如果redolog完整，recover database就可以应用到最新的redo entries,不需要resetlogs 如果redolog不完整,recover database不起作用，因为此时datafile scn大于上一个redolog 的high scn,而用于恢复的当前redolog不可用,只能从backup里恢复或者_allow_resetlogs_corruption=true来打开 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata/gc/system01.dbf' restore database后的状态 SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
621223
621223
621223
621223 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
595732
595732
595732
595732 recover database until cancel后的状态 SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
621223
621223
621223
621223 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
621223
621223
621223
621223 然后就可以open resetlogs 或者recover出现ORA-01194: file 1 needs more recovery to be consistent后强行打开 using backup controlfile 此时会出现datafile scn<dataheader scn,此语句说明恢复时忽略datafile scn直到完全恢复或until cancel; SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
621349
621349
621349
621349 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
622097
622097
622097
622097 using backup datafile 此时需要media recovery,datafile scn>dataheader scn SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE#
------------------
622059
622059
622059
622059 SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE#
------------------
622059
622059
622059
621820 关于重建controlfile noresetlogs和resetlogs no resetlogs 在重建时取current redolog的最新scn作为datafile scn此时数据库一致 resetlogs 在重建时取datafile header的scn作为datafile scn 两种的system checkpoint scn都为0 resetlogs 重置redolog 的sequence,如果日志文件不存在就重建,resetlogs为避免恢复后的库用到恢复前的redo entries产生不一致而重置关于redolog的信息 redolog和checkpoint 根据 select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like '%redo%'; 来查看每次操作产生的redolog的量 alter system dump logfile '/xxx' REDO RECORD - Thread:1 RBA: 0x000001.00000068.014c LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0007463f SUBSCN: 1 06/05/2010 14:35:34
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 2 rdba: 0x00800029 BFT:(1024,8388649) non-BFT:(2,41)
scn: 0x0000.000745da seq: 0x01 flg:0x04 redo entries样例 各种操作首先放在sga log buffer, buffer cache,undo cache里，其中log buffer满1/3,1MB,commit时，由lgwr写入redo log 里,记录当时checkpoint scn 由于数据库还没checkpoint，所以controlfile scn,datafile scn,header scn和redo entries的scn不一致，在checkpoint后达到一致 所以数据库的各个操作产生的redo首先放在log buffer里,此时commit就写入redolog,dbwr还未写磁盘, 此时redolog出现问题,这个操作相当于没有发生过，dbwr写入磁盘的，因为checkpoint本身的关系(增量checkpoint,数据写而undo未写的),数据文件是不一致的
相关阅读 更多 +
