转自 http://blog.itpub.net/post/29894/477752/
这个事件激活以后会触发数据库写进程(DBWR)将数据缓冲(DATA BUFFER CACHE)中的脏数据块写出到数据文件中。
checkpoint的作用是什么?
checkpoint主要2个作用:1、保证数据库的一致性，
这是指将脏数据写出到硬盘，保证内存和硬盘上的数据是一样的;
2、缩短实例恢复的时间，实例恢复要把实例异常关闭前没有写出到硬盘的脏数据通过日志进行恢复。
如果脏块过多，实例恢复的时间也会很长，检查点的发生可以减少脏块的数量，从而提高实例恢复的时间。
checkpoint就像word的自动保存一样。
checkpoint的类型:
完全检查点:
定义:清除脏列表(DIRTY LIST OR CHECKPOINT ENQUEUE)中所有数据块。
什么时候发生:ALTER SYSTEM CHECKPOINT; SHUTDOWN;
增量检查点:
定义:根据检查点的条件清除脏列表中的部分数据块，直到满足所有检查点条件为止。
什么时候发生:CKPT进程每3秒被唤醒，CKPT检查当前的所有checkpoint条件，
如果任何一个条件不能被满足，那么CKPT发出增量检查点。
检查点条件有哪些?
90% OF THE SMALLEST REDO LOGFILE
FAST_START_MTTR_TARGET
FAST_START_IO_TARGET
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_INTERVAL
90% OF THE SMALLEST REDO LOGFILE :
意味着最后一次增量检查点与当前日志文件末尾所差的redo block数量如果超过最小redo log的90%，那么就会触发增量检查点。
FAST_START_MTTR_TARGET:实例恢复的时间限制，
oracle将这个时间换算成redo blocks数量，当log buffer中未写入log file的redo block数量超过这个值，就会触发增量检查点。
FAST_START_IO_TARGET:实例恢复所需要读取的redo blocks数量，
当log buffer中未写入log file的redo block数量超过这个值，就会触发增量检查点。
LOG_CHECKPOINT_TIMEOUT:2次增量检查点的时间间隔。
LOG_CHECKPOINT_INTERVAL:最后一次增量检查点与当前日志文件末尾所差的redo block数量。
注意:增量检查点并不是将脏列表中的所有脏块都写出到数据文件中，而是写出一部分，保证满足所有条件即可。
[@more@]相关概念:RBA checkpoin rba on-disk rba RBA:redo block address 重作日志地址
logfile sequence number(4bytes)
logfile block number(4bytes)
redo entry offset(2bytes)
checkpoint rba:最后一次检查点对应的重作日志地址，意味着这个地址之前的redo log都是实例恢复不需要的。
实例恢复的起点
on-disk rba:当前日志中最新的重作日志地址。
实例恢复的终点
相关视图:xkcccpvinstance_recovery vinstancerecovery实例恢复对应的视图:actualredoblks:最后一次检查点到当前日志尾所差的redoblocks数量;targetredoblks:所有检查点条件中最小的条件相差的redoblocks数量;logfilesizeredoblks:最小日志组的90logchkpttimeoutredoblks:有logcheckpointtimeout参数所转换的redoblocks数量;−−这也是一个增量检查点条件targetmttr:有faststartmttrtarget参数所限制的实例恢复的最大时间estimatedmttr:根据当前最后一次检查点与日志尾所差的redoblocks数量估算出来的mttr。xkcccp 增量检查点对应的视图:
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
从vinstancerecovery中看到actualredoblks瞬间为0，说明完全检查点清除脏列表上的所有的脏块。同时也会完成之前没有完成的日志切换检查点，这时查询vlog，active的状态转变为inactive。
第1个窗口
SQL> alter system checkpoint;
系统已更改。
第2个窗口
10:24:54 SQL> select actual_redo_blks from vinstance_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 vLog;
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 vdatafile; 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 vbh where dirty='Y';
COUNT(1)
----------
42
SQL> alter system checkpoint;
系统已更改。
SQL> select count(1) from vbh where dirty='Y'; COUNT(1) ---------- 11 SQL> select file#,checkpoint_change# from vdatafile;
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:增量检查点的触发条件的当前值都可以在vinstancerecovery中看到，而xkcccp可以看到最后一次检查点的位置(用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 vinstancerecovery;ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−62967170577184320170577014−−LOGFILESIZEREDOBLKS的值是184320，这正好是日志文件的90−−日志块是0.5K一个，换算成M，184320∗0.5/1024=90日志文件是100M−−再看一下最后一次检查点的位置:SQL>selectCPLRBASEQ,CPLRBABNO,CPODRSEQ,CPODRBNOfromxkcccp;
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 --同时不断查询vinstancerecovery，可以看到新产生大量的日志块:SQL>selectactualredoblksact,targetredoblkstarget,LOGFILESIZEREDOBLKSlogfile,LOGCHKPTTIMEOUTREDOBLKSlogcheck,targetmttrtmttr,estimatedmttresmttrfromvinstance_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 vLog; 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 xkcccp;
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 vinstancerecoveryACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103361184320184320210971020−−没有满足，那么继续insert−−不断观察vinstance_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 vinstancerecovery;ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−63008170612184320170612014SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−82829184320184320190439014SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103118184320184320210728014SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103197184320184320210807020SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103361184320184320210971020SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−123466184320184320231076022SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−143954184320184320251564022SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−159205184320184320258499022SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−184159184320184320291769026−−这时actualredoblocks已经到了184149了，马上接近90−−这时47号日志的状态还是没有变化的，还是active:SQL>select∗fromvLog;
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就可以了。
--这时查询xkcccp，发现最后一次检查点的位置已经升高，但依旧在47号日志上面:SQL>selectCPLRBASEQ,CPLRBABNO,CPODRSEQ,CPODRBNOfromxkcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
47 185730 48 118320
--再次插入数据，并查询vinstancerecoverySQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−184588184320184320393585030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−184588184320184320393604030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183868184320184320393604030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183869184320184320393604030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183881184320184320393617028SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183881184320184320393617028−−由于再次超过阀值，增量检查点再次发生，并写出了一些脏块，此时查询xkcccp，发现最后一次检查点的位置已经提到48号日志:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from xkcccp;CPLRBASEQCPLRBABNOCPODRSEQCPODRBNO−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−48607248188945−−观察alertlog:logswitchcheckpoint完成。MonDec1115:29:082006CompletedcheckpointuptoRBA[0x30.2.10],SCN:0x0000.0012827b−−这里注意RBA已经指向日志0x30，转换成10进制就是，16×3=48，这与上面xkcccp查到的情况是相符的。
--由于最后一次检查点的位置已经超过47号日志，那么47号日志对于实例恢复来说就没有用了，来看看47号日志的状态:
--观察vlog，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 --查看vinstance_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 vinstance_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 xkcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
48 189440 48 189462
--插入数据，并观察vinstancerecoverySQL>insertintotestselect∗fromtestwhererownum99999rowscreated.−−观察vinstance_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 vinstancerecoverySQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−204332043318432020433010SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−568518432085010−−1分钟后发生了增量检查点，actualredoblocks减少了不少:−−看看增量检查点的位置:SQL>selectCPLRBASEQ,CPLRBABNO,CPODRSEQ,CPODRBNOfromxkcccp;
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的条件是吻合的。
这个事件激活以后会触发数据库写进程(DBWR)将数据缓冲(DATA BUFFER CACHE)中的脏数据块写出到数据文件中。
checkpoint的作用是什么?
checkpoint主要2个作用:1、保证数据库的一致性，
这是指将脏数据写出到硬盘，保证内存和硬盘上的数据是一样的;
2、缩短实例恢复的时间，实例恢复要把实例异常关闭前没有写出到硬盘的脏数据通过日志进行恢复。
如果脏块过多，实例恢复的时间也会很长，检查点的发生可以减少脏块的数量，从而提高实例恢复的时间。
checkpoint就像word的自动保存一样。
checkpoint的类型:
完全检查点:
定义:清除脏列表(DIRTY LIST OR CHECKPOINT ENQUEUE)中所有数据块。
什么时候发生:ALTER SYSTEM CHECKPOINT; SHUTDOWN;
增量检查点:
定义:根据检查点的条件清除脏列表中的部分数据块，直到满足所有检查点条件为止。
什么时候发生:CKPT进程每3秒被唤醒，CKPT检查当前的所有checkpoint条件，
如果任何一个条件不能被满足，那么CKPT发出增量检查点。
检查点条件有哪些?
90% OF THE SMALLEST REDO LOGFILE
FAST_START_MTTR_TARGET
FAST_START_IO_TARGET
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_INTERVAL
90% OF THE SMALLEST REDO LOGFILE :
意味着最后一次增量检查点与当前日志文件末尾所差的redo block数量如果超过最小redo log的90%，那么就会触发增量检查点。
FAST_START_MTTR_TARGET:实例恢复的时间限制，
oracle将这个时间换算成redo blocks数量，当log buffer中未写入log file的redo block数量超过这个值，就会触发增量检查点。
FAST_START_IO_TARGET:实例恢复所需要读取的redo blocks数量，
当log buffer中未写入log file的redo block数量超过这个值，就会触发增量检查点。
LOG_CHECKPOINT_TIMEOUT:2次增量检查点的时间间隔。
LOG_CHECKPOINT_INTERVAL:最后一次增量检查点与当前日志文件末尾所差的redo block数量。
注意:增量检查点并不是将脏列表中的所有脏块都写出到数据文件中，而是写出一部分，保证满足所有条件即可。
[@more@]相关概念:RBA checkpoin rba on-disk rba RBA:redo block address 重作日志地址
logfile sequence number(4bytes)
logfile block number(4bytes)
redo entry offset(2bytes)
checkpoint rba:最后一次检查点对应的重作日志地址，意味着这个地址之前的redo log都是实例恢复不需要的。
实例恢复的起点
on-disk rba:当前日志中最新的重作日志地址。
实例恢复的终点
相关视图:xkcccpvinstance_recovery vinstancerecovery实例恢复对应的视图:actualredoblks:最后一次检查点到当前日志尾所差的redoblocks数量;targetredoblks:所有检查点条件中最小的条件相差的redoblocks数量;logfilesizeredoblks:最小日志组的90logchkpttimeoutredoblks:有logcheckpointtimeout参数所转换的redoblocks数量;−−这也是一个增量检查点条件targetmttr:有faststartmttrtarget参数所限制的实例恢复的最大时间estimatedmttr:根据当前最后一次检查点与日志尾所差的redoblocks数量估算出来的mttr。xkcccp 增量检查点对应的视图:
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
从vinstancerecovery中看到actualredoblks瞬间为0，说明完全检查点清除脏列表上的所有的脏块。同时也会完成之前没有完成的日志切换检查点，这时查询vlog，active的状态转变为inactive。
第1个窗口
SQL> alter system checkpoint;
系统已更改。
第2个窗口
10:24:54 SQL> select actual_redo_blks from vinstance_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 vLog;
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 vdatafile; 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 vbh where dirty='Y';
COUNT(1)
----------
42
SQL> alter system checkpoint;
系统已更改。
SQL> select count(1) from vbh where dirty='Y'; COUNT(1) ---------- 11 SQL> select file#,checkpoint_change# from vdatafile;
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:增量检查点的触发条件的当前值都可以在vinstancerecovery中看到，而xkcccp可以看到最后一次检查点的位置(用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 vinstancerecovery;ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−62967170577184320170577014−−LOGFILESIZEREDOBLKS的值是184320，这正好是日志文件的90−−日志块是0.5K一个，换算成M，184320∗0.5/1024=90日志文件是100M−−再看一下最后一次检查点的位置:SQL>selectCPLRBASEQ,CPLRBABNO,CPODRSEQ,CPODRBNOfromxkcccp;
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 --同时不断查询vinstancerecovery，可以看到新产生大量的日志块:SQL>selectactualredoblksact,targetredoblkstarget,LOGFILESIZEREDOBLKSlogfile,LOGCHKPTTIMEOUTREDOBLKSlogcheck,targetmttrtmttr,estimatedmttresmttrfromvinstance_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 vLog; 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 xkcccp;
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 vinstancerecoveryACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103361184320184320210971020−−没有满足，那么继续insert−−不断观察vinstance_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 vinstancerecovery;ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−63008170612184320170612014SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−82829184320184320190439014SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103118184320184320210728014SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103197184320184320210807020SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−103361184320184320210971020SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−123466184320184320231076022SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−143954184320184320251564022SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−159205184320184320258499022SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−184159184320184320291769026−−这时actualredoblocks已经到了184149了，马上接近90−−这时47号日志的状态还是没有变化的，还是active:SQL>select∗fromvLog;
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就可以了。
--这时查询xkcccp，发现最后一次检查点的位置已经升高，但依旧在47号日志上面:SQL>selectCPLRBASEQ,CPLRBABNO,CPODRSEQ,CPODRBNOfromxkcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
47 185730 48 118320
--再次插入数据，并查询vinstancerecoverySQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−184588184320184320393585030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−184588184320184320393604030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183868184320184320393604030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183869184320184320393604030SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183881184320184320393617028SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−183881184320184320393617028−−由于再次超过阀值，增量检查点再次发生，并写出了一些脏块，此时查询xkcccp，发现最后一次检查点的位置已经提到48号日志:
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from xkcccp;CPLRBASEQCPLRBABNOCPODRSEQCPODRBNO−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−48607248188945−−观察alertlog:logswitchcheckpoint完成。MonDec1115:29:082006CompletedcheckpointuptoRBA[0x30.2.10],SCN:0x0000.0012827b−−这里注意RBA已经指向日志0x30，转换成10进制就是，16×3=48，这与上面xkcccp查到的情况是相符的。
--由于最后一次检查点的位置已经超过47号日志，那么47号日志对于实例恢复来说就没有用了，来看看47号日志的状态:
--观察vlog，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 --查看vinstance_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 vinstance_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 xkcccp;
CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO
---------- ---------- ---------- ----------
48 189440 48 189462
--插入数据，并观察vinstancerecoverySQL>insertintotestselect∗fromtestwhererownum99999rowscreated.−−观察vinstance_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 vinstancerecoverySQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−204332043318432020433010SQL>/ACTTARGETLOGFILELOGCHECKTMTTRESMTTR−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−568518432085010−−1分钟后发生了增量检查点，actualredoblocks减少了不少:−−看看增量检查点的位置:SQL>selectCPLRBASEQ,CPLRBABNO,CPODRSEQ,CPODRBNOfromxkcccp;
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的条件是吻合的。
