Oracle checkpoint scn研究

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

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

 

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未写的),数据文件是不一致的

 

 

 

阅读(723) | 评论(0) | 转发(0) |

0

上一篇：event 10231 skip corrupted blocks on _table_scans_

下一篇：Master Replication (一)