logfile sequence number(4bytes)
logfile block number(4bytes)
redo entry offset(2bytes)
checkpoint rba:最后一次检查点对应的重作日志地址,意味着这个地址之前的redo log都是实例恢复不需要的。
实例恢复的起点
on-disk rba:当前日志中最新的重作日志地址。
实例恢复的终点
相关视图:x$kcccp v$instance_recovery v$instance_recovery实例恢复对应的视图:
actual_redo_blks:最后一次检查点到当前日志尾所差的redo blocks数量;
target_redo_blks:所有检查点条件中最小的条件相差的redo blocks数量;
log_file_size_redo_blks:最小日志组的90%大小所对应的redo blocks数量;--这是一个增量检查点条件
log_chkpt_timeout_redo_blks:有log_checkpoint_timeout参数所转换的redo blocks数量; --这也是一个增量检查点条件
target_mttr:有fast_start_mttr_target参数所限制的实例恢复的最大时间
estimated_mttr:根据当前最后一次检查点与日志尾所差的redo blocks数量估算出来的mttr。
x$kcccp 增量检查点对应的视图:
CPLRBA_SEQ:最后一次增量检查点对应rba的第一部分--日志序列号;
CPLRBA_BNO:最后一次增量检查点对应rba的第二部分--日志块数;
CPLRBA_BOF:最后一次增量检查点对应rba的第三部分--日志偏移量;
CPODR_SEQ:日志尾的rda的第一部分--日志序列号;
CPODR_BNO:日志尾的rda的第二部分--日志块数;
CPODR_BOF:日志尾的rda的第二部分--日志偏移量;
CPHBT:检查点心跳数。
实验测试:
1、完全检查点:
SQL> show parameter log_checkpoint_
NAME TYPE VALUE
------------------------------------ ---------------------- --------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 0
log_checkpoints_to_alert boolean TRUE
SQL> alter system checkpoint;
系统已更改。
日志中的信息:完全检查点立即执行。
Beginning global checkpoint up to RBA [0x52f.5c2.10], SCN: 0x0000.0045bf00
Completed checkpoint up to RBA [0x52f.5c2.10], SCN: 0x0000.0045bf00
从v$instance_recovery 中看到actual_redo_blks瞬间为0,说明完全检查点清除脏列表上的所有的脏块。
同时也会完成之前没有完成的日志切换检查点,这时查询v$log,active的状态转变为inactive。
第1个窗口
SQL> alter system checkpoint;
系统已更改。
第2个窗口
10:24:54 SQL> select actual_redo_blks from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
128
10:25:09 SQL> /
ACTUAL_REDO_BLKS
----------------
128
10:25:11 SQL> /
ACTUAL_REDO_BLKS
----------------
0
10:25:45 SQL>
此时查看警报日志:
Beginning global checkpoint up to RBA [0x531.8f.10], SCN: 0x0000.00461fd1
Completed checkpoint up to RBA [0x531.8f.10], SCN: 0x0000.00461fd1
531转成十进制就是1329,是当前的在线日志序列号:
SQL> select group#,sequence#,status from v$Log;
GROUP# SEQUENCE# STATUS
---------- ---------- ---------------------------
4 1328 INACTIVE
5 1329 CURRENT
6 1327 INACTIVE
SQL> alter system dump logfile 'D:ORACLEORADATATEST9REDO05.LOG';
系统已更改。
转储日志,找到对应的rba
REDO RECORD - Thread:1 RBA: 0x000531.0000008f.0010 LEN: 0x0428 VLD: 0x02
SCN: 0x0000.00461fd1 SUBSCN: 1 12/05/2006 10:25:43
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 1 rdba: 0x00403162(1,12642)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00403159(1,12633)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00403158(1,12632)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x0040314b(1,12619)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x0040314a(1,12618)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00403149(1,12617)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401efa(1,7930)
scn: 0x0000.00461fca seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401eba(1,7866)
scn: 0x0000.00461fcc seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401387(1,4999)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401386(1,4998)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401373(1,4979)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401372(1,4978)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401370(1,4976)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x0040136a(1,4970)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401365(1,4965)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401363(1,4963)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00401347(1,4935)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400e8c(1,3724)
scn: 0x0000.00461f62 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400cae(1,3246)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400cac(1,3244)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400cab(1,3243)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400ca9(1,3241)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x004005da(1,1498)
scn: 0x0000.00461f90 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x004005ca(1,1482)
scn: 0x0000.00461f90 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400181(1,385)
scn: 0x0000.00461f5d seq: 0x01 flg:0x04
Block Written - afn: 1 rdba: 0x0040006a(1,106)
scn: 0x0000.00461f5e seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x0040002b(1,43)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400028(1,40)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400027(1,39)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400026(1,38)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400023(1,35)
scn: 0x0000.00461f88 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400018(1,24)
scn: 0x0000.00461f4b seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0081645c(2,91228)
scn: 0x0000.00461f6d seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x0081645b(2,91227)
scn: 0x0000.00461fc8 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x008072d3(2,29395)
scn: 0x0000.00461f65 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x008072d2(2,29394)
scn: 0x0000.00461fcb seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802ffa(2,12282)
scn: 0x0000.00461f6a seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802ff9(2,12281)
scn: 0x0000.00461fc3 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802df1(2,11761)
scn: 0x0000.00461f6c seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802df0(2,11760)
scn: 0x0000.00461fc6 seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802d66(2,11622)
scn: 0x0000.00461fc9 seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802d65(2,11621)
scn: 0x0000.00461f63 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802d64(2,11620)
scn: 0x0000.00461f74 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802c18(2,11288)
scn: 0x0000.00461f67 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802c17(2,11287)
scn: 0x0000.00461fb4 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802b92(2,11154)
scn: 0x0000.00461f68 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802b89(2,11145)
scn: 0x0000.00461fbf seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x008028e0(2,10464)
scn: 0x0000.00461f69 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x008028df(2,10463)
scn: 0x0000.00461fc1 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00801472(2,5234)
scn: 0x0000.00461f6b seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00801471(2,5233)
scn: 0x0000.00461fc5 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800a64(2,2660)
scn: 0x0000.00461f66 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00800a63(2,2659)
scn: 0x0000.00461fcc seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800099(2,153)
scn: 0x0000.00461fc9 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800089(2,137)
scn: 0x0000.00461fc7 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800079(2,121)
scn: 0x0000.00461fc6 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800069(2,105)
scn: 0x0000.00461fc4 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800059(2,89)
scn: 0x0000.00461fc2 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800049(2,73)
scn: 0x0000.00461fc0 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800039(2,57)
scn: 0x0000.00461fb5 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800029(2,41)
scn: 0x0000.00461fcd seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800019(2,25)
scn: 0x0000.00461fcc seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800009(2,9)
scn: 0x0000.00461fca seq: 0x01 flg:0x04
Block Written - afn: 1 rdba: 0x00400009(1,9)
scn: 0x0000.00461f5e seq: 0x01 flg:0x04
注意 这条redo record的scn:SCN: 0x0000.00461fd1,和检查点scn是一致的。
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 4595665
2 4595665
3 4595665
4 4595665
5 4595665
6 4595665
7 4595665
已选择7行。
SQL> select to_char(4595665,'xxxxxxx') from dual;
TO_CHAR(4595665,
----------------
461fd1
研究写出脏块的数量:
SQL> select count(1) from v$bh where dirty='Y';
COUNT(1)
----------
42
SQL> alter system checkpoint;
系统已更改。
SQL> select count(1) from v$bh where dirty='Y';
COUNT(1)
----------
11
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 4596768
2 4596768
3 4596768
4 4596768
5 4596768
6 4596768
7 4596768
已选择7行。
SQL> select to_char(4596768,'xxxxxxx') from dual;
TO_CHAR(4596768,
----------------
462420
找到462420的redo record:正好是31个脏块
REDO RECORD - Thread:1 RBA: 0x000531.0000028d.0010 LEN: 0x0218 VLD: 0x02
SCN: 0x0000.00462420 SUBSCN: 1 12/05/2006 10:48:14
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 1 rdba: 0x00401efa(1,7930)
scn: 0x0000.00462413 seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400e8c(1,3724)
scn: 0x0000.0046237f seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400d5a(1,3418)
scn: 0x0000.0046227e seq: 0x01 flg:0x06
Block Written - afn: 2 rdba: 0x0081645c(2,91228)
scn: 0x0000.004623fd seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0081645b(2,91227)
scn: 0x0000.0046215e seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x008072d2(2,29394)
scn: 0x0000.0046240a seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802ffa(2,12282)
scn: 0x0000.00462412 seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802ff9(2,12281)
scn: 0x0000.004622f8 seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802df1(2,11761)
scn: 0x0000.00462415 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802df0(2,11760)
scn: 0x0000.004622a9 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802d66(2,11622)
scn: 0x0000.004620ea seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802d65(2,11621)
scn: 0x0000.00462408 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00802c18(2,11288)
scn: 0x0000.0046240e seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00802c17(2,11287)
scn: 0x0000.0046207e seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802b92(2,11154)
scn: 0x0000.0046240f seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00802b89(2,11145)
scn: 0x0000.004622f4 seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x008028df(2,10463)
scn: 0x0000.00462411 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00801472(2,5234)
scn: 0x0000.00462414 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00801471(2,5233)
scn: 0x0000.004623c1 seq: 0x03 flg:0x04
Block Written - afn: 2 rdba: 0x00800a63(2,2659)
scn: 0x0000.0046240c seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x00800099(2,153)
scn: 0x0000.004623fe seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800089(2,137)
scn: 0x0000.00462416 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800079(2,121)
scn: 0x0000.00462415 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800069(2,105)
scn: 0x0000.00462413 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800059(2,89)
scn: 0x0000.00462412 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800049(2,73)
scn: 0x0000.00462410 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800039(2,57)
scn: 0x0000.0046240f seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800029(2,41)
scn: 0x0000.0046240d seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800019(2,25)
scn: 0x0000.0046240b seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00800009(2,9)
scn: 0x0000.00462409 seq: 0x01 flg:0x04
Block Written - afn: 1 rdba: 0x00401eba(1,7866)
scn: 0x0000.00462415 seq: 0x01 flg:0x06
REDO RECORD - Thread:1 RBA: 0x000531.0000028f.0010 LEN: 0x01bc VLD: 0x01
SCN: 0x0000.00462422 SUBSCN: 1 12/05/2006 10:48:17
总结一下:完全检查点会清空buffer cache中所有脏块(有些特殊块不包含在内),
当alter system checkpoint 命令发出,完全检查点会立刻执行。
如果是在生产库,由于脏块数量比较多,完全检查点的时间会很长,并占用一定的系统资源,这时操作系统的IO会变忙。
2、增量检查点:设置增量检查点的意义是通过提高检查点发生的次数,将脏块不断的,一点一点的写出到数据文件,
这样可以避免由于完全检查点引起的高IO负载。
a、第一个发生增量检查点的条件:90% OF THE SMALLEST REDO LOGFILE
一般很少有人更改log_checkpoint和fast_start参数,那些参数的默认值都设置的很大,
所以还没到那些参数的阀值的时候,90%最小日志的条件就生效了(增量检查点是任何一个条件成立以后就会发生)。
实验一把:
SQL> show parameter log_checkpoint
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean TRUE
SQL> show parameter fast_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string HIGH
SQL> create table test as select * from dba_objects;
Table created.
ps:增量检查点的触发条件的当前值都可以在v$instance_recovery中看到,而x$kcccp可以看到最后一次检查点的位置(用rba表示)和当前日志尾的位置(用rba表示)。
看看当前各个增量检查点触发条件的值:
SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
62967 170577 184320 170577 0 14
--LOG_FILE_SIZE_REDO_BLKS的值是184320,这正好是日志文件的90%
--日志块是0.5K一个,换算成M,184320*0.5/1024=90 日志文件是100M
--再看一下最后一次检查点的位置:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
47 107610 47 170607
--最后一次检查点发生在47号日志的17610块,当前日志也是47号,日志尾在170607块。
--这时从另一个窗口不断的执行 insert into test as select * from test where rownum < 100000;
--同时不断查询v$instance_recovery,可以看到新产生大量的日志块:
SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
63008 170612 184320 170612 0 14
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
82829 184320 184320 190439 0 14
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
103118 184320 184320 210728 0 14
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
103197 184320 184320 210807 0 20
--此时redo log也发生了切换:
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 47 104857600 3 NO ACTIVE 1210034 11-DEC-06
2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06
3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06
--这里注意:当当前日志被写满以后,进行日志切换,这时触发了一个log switch checkpoint,但是仅仅是触发,而并没有完成。
--后台警报日志:
Mon Dec 11 15:06:26 2006
Beginning log switch checkpoint up to RBA [0x30.2.10], SCN: 0x0000.0012827b
Thread 1 advanced to log sequence 48
Current log# 2 seq# 48 mem# 0: /u01/app/oracle/oradata/novo/redo02.log
--另外注意,47号日志的状态是ACTIVE,active的意思是这个日志组还包含实例恢复所需要的日志。这也说明log switch checkpoint并没有立即工作
--这时看看最后一次检查点的位置:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
47 107610 48 6161
--最后一次检查点的位置没变,还在47号日志上面,而当前日志尾已经到了48号的6161块上。这也印证了为什么44号日志的状态是ACTIVE的。
--再看看当前是否满足增量检查点的触发条件:
SQL> l
1* select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
103361 184320 184320 210971 0 20
--没有满足,那么继续insert
--不断观察v$instance_recovery:
SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
63008 170612 184320 170612 0 14
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
82829 184320 184320 190439 0 14
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
103118 184320 184320 210728 0 14
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
103197 184320 184320 210807 0 20
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
103361 184320 184320 210971 0 20
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
123466 184320 184320 231076 0 22
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
143954 184320 184320 251564 0 22
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
159205 184320 184320 258499 0 22
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
184159 184320 184320 291769 0 26
--这时actual redo blocks已经到了184149了,马上接近90% of redologfile,意味着再有一些日志进来以后,就会触发增量检查点。
--这时47号日志的状态还是没有变化的,还是active:
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 47 104857600 3 NO ACTIVE 1210034 11-DEC-06
2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06
3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06
--再次插入数据:
--actual redo blocks超过90% of logfile,这时应该触发增量检查点,最后一次查询是增量检查点发生之后的actual redo blocks值,已经小于90% of logfile。
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
204281 184320 184320 372918 0 28
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
204281 184320 184320 372918 0 28
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
183929 184320 184320 373265 0 28
--注意增量检查点只是写出一部分脏数据,只要保证actual redo blocks小于90% of logfile就可以了。
--这时查询x$kcccp,发现最后一次检查点的位置已经升高,但依旧在47号日志上面:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
47 185730 48 118320
--再次插入数据,并查询v$instance_recovery
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
184588 184320 184320 393585 0 30
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
184588 184320 184320 393604 0 30
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
183868 184320 184320 393604 0 30
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
183869 184320 184320 393604 0 30
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
183881 184320 184320 393617 0 28
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
183881 184320 184320 393617 0 28
--由于再次超过阀值,增量检查点再次发生,并写出了一些脏块,此时查询x$kcccp,发现最后一次检查点的位置已经提到48号日志:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
48 6072 48 188945
--观察alert log:log switch checkpoint 完成。
Mon Dec 11 15:29:08 2006
Completed checkpoint up to RBA [0x30.2.10], SCN: 0x0000.0012827b
--这里注意RBA已经指向日志 0x30,转换成10进制就是,16×3=48,这与上面x$kcccp查到的情况是相符的。
--由于最后一次检查点的位置已经超过47号日志,那么47号日志对于实例恢复来说就没有用了,来看看47号日志的状态:
--观察v$log,47号日志的状态已经变为INACTIVE
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 47 104857600 3 NO INACTIVE 1210034 11-DEC-06
2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06
3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06
总结一下:通常所说的logfile switch 触发检查点,实际上就是给出一个标记,而并不真正去写脏块。等待增量检查点做到了那个标记,再标识完成。
b、第二个发生增量检查点的条件:LOG_CHECKPOINT_TIMEOUT
这个参数说白了就是控制2次增量检查点之间所发生的时间间隔,超过这个间隔,就会触发增量检查点。
从另外一个角度理解,就是脏块在buffer cache中所能存在的最大时间。
为了让实验更明显,把这个参数设小点,1分钟:
SQL> show parameter log_checkpoint
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean TRUE
SQL> alter system set log_checkpoint_timeout = 60;
System altered.
SQL> show parameter log_checkpoint;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 60
log_checkpoints_to_alert boolean TRUE
--查看v$instance_recovery视图:
SQL> l
1* select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
7 15 184320 15 0 7
--看到target 和 log_CHECKPOINT_TIMEOUT的值是一样的,而且变的特别小,只要actual redo blocks超过这个数量就会触发增量检查点。
--下面重复上面的那个实验,不断的插入数据,以产生redo log,然后观察检查点发生的情况:
--先看看redo log的状态:
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 47 104857600 3 NO INACTIVE 1210034 11-DEC-06
2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06
3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06
--增量检查点的位置:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
48 189440 48 189462
--插入数据,并观察v$instance_recovery
SQL> insert into test select * from test where rownum < 100000;
99999 rows created.
--观察v$instance_recovery
SQL> l
1* select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
20433 20433 184320 20433 0 10
SQL> /
ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR
---------- ---------- ---------- ---------- ---------- ----------
56 85 184320 85 0 10
--1分钟后发生了增量检查点,actual redo blocks减少了不少:
--看看增量检查点的位置:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
48 189486 49 5126
SQL> /
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
49 5126 49 5183
--检查点的位置提升了2次,并提升到了49号日志,这时看到alert log中的log switch checkpoint也完成了:
Mon Dec 11 16:12:50 2006
Beginning log switch checkpoint up to RBA [0x31.2.10], SCN: 0x0000.00128ded
Thread 1 advanced to log sequence 49
Current log# 3 seq# 49 mem# 0: /u01/app/oracle/oradata/novo/redo03.log
Mon Dec 11 16:13:56 2006
Completed checkpoint up to RBA [0x31.2.10], SCN: 0x0000.00128ded
--这里注意从checkpoint begin到end的时间间隔是1分钟,这与log_checkpoint_timeout的条件是吻合的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18921899/viewspace-1016700/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18921899/viewspace-1016700/