[20190312]关于增量检查点的疑问(补充).txt

来源：这里教程网时间：2026-03-03 13:03:44 作者：

[20190312]关于增量检查点的疑问(补充).txt --//有人问我以前写一个帖子的问题,关于增量检查点的问题,链接如下:http://blog.itpub.net/267265/viewspace-2136817/ --//实际上我自己看以前写的帖子一下子有点蒙,主要出现low_rba16=0xffffffff.ffffffff.ffff,为什么恢复的起点是on_disk_rba16. --//先简单说明一下: --//oracle现在写脏块基本采用增量检查点,即使日志切换,实际上执行也是增量检查点,除非执行alter system checkpoint,或者 --//shutdown immediate(normal)正常关闭数据库,如果异常关闭数据库,启动时执行崩溃恢复(crash recovery),恢复起点从low_rba. --//先验证这样的情况: 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------- -------------- ---------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//写一个脚本check.sql,以前写的太复杂,简单一点: --// x$kccrt 记录全检查点 --// x$kcccp 记录增量检查点 $ cat check.sql column "full checkpoint_rba" format a21 column low_rba format a20 column low_rba16 format a20 column on_disk_rba format a20 column on_disk_rba16 format a20 column rtckp_rba format a20 column diff_date format 9999999.99 rem column CPOSD_ono_disk_rba_scn format 99999999999999999999999999999999 column cpdrt heading "检查点队列|脏块数量|CPDRT" column cpodt_on_disk_rba heading "检查点队列|on disk rba|时间戳|CPODT" column cpods heading "检查点队列|on disk rba scn|CPODS" column cphbt heading "检查点心跳|CPHBT" column current_sysdate heading "当前时间|SYSDATE" set num 12 SELECT b.cplrba_seq || '.' || b.cplrba_bno || '.' || b.cplrba_bof "low_rba" ,b.cpodr_seq || '.' || b.cpodr_bno || '.' || b.cpodr_bof "on_disk_rba" ,b.CPODS "on_disk_rba_scn(CPODS)" ,TO_DATE (b.CPODT, 'MM-DD-YYYY HH24:MI:SS') "on_disk_rba_time(CPODT)" ,a.rtckp_rba_seq || '.' || a.rtckp_rba_bno || '.' || a.rtckp_rba_bof "full checkpoint_rba" ,a.rtckp_scn "full_checkpoint(rtckp_scn)" ,TO_DATE (a.rtckp_tim, 'MM-DD-YYYY HH24:MI:SS') "full_checkpoint_time_rtckp_tim" ,b.CPODS - a.rtckp_scn "diff_scn(on_disk_rdb-ch_scn)" ,a.rtcln "current_group" ,sysdate current_sysdate ,CPDRT FROM x$kccrt a, x$kcccp b WHERE a.rtnum = b.cptno AND A.INST_ID = b.inst_id; 2.测试: SYS@book> shutdown abort ; ORACLE instance shut down. SYS@book> startup mount ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. SYS@book> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/archivelog/book/ Oldest online log sequence 787 Next log sequence to archive 789 Current log sequence 789 SYS@book> @ check 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT ----------- ----------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 789.5775.0 789.5955.0 13278979623 2019-03-12 11:20:53 789.1890.16 13278977341 2019-03-12 10:52:50 2282 2 2019-03-12 11:21:42 12 --//看看日志应用的起点是否从low_rba开始. SYS@book> alter database open ; Database altered. --//查看alert.log日志: Beginning crash recovery of 1 threads parallel recovery started with 23 processes Started redo scan Completed redo scan read 90 KB redo, 12 data blocks need recovery Started redo application at Thread 1: logseq 789, block 5775 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>起点对应low_rba=789.5775.0 Recovery of Online Redo Log: Thread 1 Group 2 Seq 789 Reading mem 0 Mem# 0: /mnt/ramdisk/book/redo02.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 789, block 5956, scn 13278999624 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>结束对应on_disk_rba=789.5955.0加1个块(512字节redo),scn号对应on_disk_rba_scn+1. 12 data blocks read, 12 data blocks written, 90 redo k-bytes read ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//5955-5775 = 180,应用日志180块,日志文件每块512字节. --//180*512/1024 = 90k,这些正好对上. Tue Mar 12 11:23:26 2019 LGWR: STARTING ARCH PROCESSES Tue Mar 12 11:23:26 2019 ARC0 started with pid=45, OS id=56804 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Thread 1 advanced to log sequence 790 (thread open) Thread 1 opened at log sequence 790 --//日志切换使用新日志. Current log# 3 seq# 790 mem# 0: /mnt/ramdisk/book/redo03.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Mar 12 11:23:27 2019 SMON: enabling cache recovery --//也就是异常关闭后,crash recovery的起点从low_rba到on_disk_rba,完成后scn号+1,日志块号加1.日志切换使用新日志. 3.如果low_rba16=0xffffffff.ffffffff.ffff呢? SYS@book> alter system checkpoint ; System altered. SYS@book> @ check 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 4294967295.429496729 790.659.0 13279000486 2019-03-12 11:32:34 790.658.16 13279000485 2019-03-12 11:32:33 1 3 2019-03-12 11:32:35 0 5.65535 --//等一会执行: SYS@book> @ check 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 4294967295.429496729 790.678.0 13279000505 2019-03-12 11:32:53 790.658.16 13279000485 2019-03-12 11:32:33 20 3 2019-03-12 11:32:54 0 5.65535 --//你可以发现alter system checkpoint 后,如果没有事务low_rba16=0xffffffff.ffffffff.ffff,而on_disk_rba一直在增加.而CPDRT=0. --//似乎11g不知道为什么在"空转"(没有事务产生的情况下)的情况,日志也在不断增加,不知道为什么? SYS@book> shutdown abort ; ORACLE instance shut down. SYS@book> startup mount ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. SYS@book> @ check 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 4294967295.429496729 790.705.0 13279000532 2019-03-12 11:33:20 790.658.16 13279000485 2019-03-12 11:32:33 47 3 2019-03-12 11:36:09 0 5.65535 --//可以发现这个时候low_rba16=0xffffffff.ffffffff.ffff,这个时候恢复的起点从那里开始,实际上从on_disk_rba开始,或者讲根本没 --//有恢复,CPDRT=0也是佐证,虽然当时on_disk_rba还在不断增加. SYS@book> alter database open ; Database altered. --//查看alert.log: Beginning crash recovery of 1 threads parallel recovery started with 23 processes Started redo scan Completed redo scan read 0 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 790, block 705, scn 13279000532 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>起点对应on_disk_rba=790.705.0 Recovery of Online Redo Log: Thread 1 Group 3 Seq 790 Reading mem 0 Mem# 0: /mnt/ramdisk/book/redo03.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 790, block 706, scn 13279020533 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>结束对应on_disk_rba=790.705.0加1个块(512字节redo),scn号对应on_disk_rba_scn+1. 0 data blocks read, 0 data blocks written, 0 redo k-bytes read ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=> 日志应用0K. Tue Mar 12 11:39:33 2019 LGWR: STARTING ARCH PROCESSES Tue Mar 12 11:39:33 2019 ARC0 started with pid=45, OS id=56980 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Thread 1 advanced to log sequence 791 (thread open) Thread 1 opened at log sequence 791 --//日志切换使用新日志. Current log# 1 seq# 791 mem# 0: /mnt/ramdisk/book/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Mar 12 11:39:34 2019 SMON: enabling cache recovery 4.我在原链接写一个脚本: SCOTT@book> create table t1 as select * from all_objects ; Table created. $ cat a.sql alter system checkpoint; alter system checkpoint; alter system checkpoint; @ check update scott.t1 set object_name=object_name where rownum=1 ; commit; host sleep 3 @ check SYS@book> @ a.sql System altered. System altered. System altered. 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 4294967295.429496729 791.21362.0 13279021797 2019-03-12 11:52:59 791.21362.16 13279021800 2019-03-12 11:53:00 -3 1 2019-03-12 11:53:01 0 5.65535 1 row updated. Commit complete. 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 791.21363.0 791.21366.0 13279021805 2019-03-12 11:53:02 791.21362.16 13279021800 2019-03-12 11:53:00 5 1 2019-03-12 11:53:04 3 --//注意看发生事务前后的low_rba,on_disk_rba.不好描述,自己看.^_^. --//一旦有事务产生,你可以发现low_rba不再是4294967295.4294967295.65535. --//很奇怪不知道为什么11g下在没有事务的情况下会"空转",这样11g的日志即使是很空闲的数据库日志增加也会比10g大. 5.看看10g的情况: SYS@192.168.100.33:1521/test> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi SYS@192.168.100.33:1521/test> alter system checkpoint ; System altered. SYS@192.168.100.33:1521/test> @ check 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 4294967295.429496729 1497.42866.0 14987614992 2019-03-12 11:55:37 1497.42866.16 14987615031 2019-03-12 11:57:34 -39 3 2019-03-12 11:57:35 0 5.65535 SYS@192.168.100.33:1521/test> @ check 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 4294967295.429496729 1497.42866.0 14987614992 2019-03-12 11:55:37 1497.42866.16 14987615031 2019-03-12 11:57:34 -39 3 2019-03-12 11:58:29 0 5.65535 --//注意看执行时间2019-03-12 11:57:35 -2019-03-12 11:58:29 之间,没有任何事务产生,on_disk_rba根本不变化.这样10g日志产生量 --//明显比11g小. 6.我改上面的脚本check.sql: --//最后加入host sleep 1.执行如下: $ rlsql -s -l sys/oracle as sysdba <<EOF > $(seq 100| xargs -I{} cat /home/oracle/sqllaji/check.sql) > EOF 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 791.24582.0 791.24711.0 13279023352 2019-03-12 12:11:39 791.21362.16 13279021800 2019-03-12 11:53:00 1552 1 2019-03-12 12:11:41 8 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 791.24582.0 791.24712.0 13279023353 2019-03-12 12:11:40 791.21362.16 13279021800 2019-03-12 11:53:00 1553 1 2019-03-12 12:11:42 8 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 791.24582.0 791.24713.0 13279023354 2019-03-12 12:11:41 791.21362.16 13279021800 2019-03-12 11:53:00 1554 1 2019-03-12 12:11:43 8 --//在没有事务的情况下.每秒scn增加1,日志块增加1,是否更我访问这些内存"表"有关,换1个方式测试,取消check.sql后面的host sleep 1,建立脚本b.sql: $ cat b.sql @ check.sql host sleep 30 @ check.sql SYS@book> @ b.sql 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 791.24582.0 791.24852.0 13279023481 2019-03-12 12:13:41 791.21362.16 13279021800 2019-03-12 11:53:00 1681 1 2019-03-12 12:13:43 19 检查点队列当前时间脏块数量 low_rba on_disk_rba on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE CPDRT -------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------ 791.24582.0 791.24882.0 13279023511 2019-03-12 12:14:11 791.21362.16 13279021800 2019-03-12 11:53:00 1711 1 2019-03-12 12:14:13 19 --//确实每秒scn增加1,on_disk_rba也是增加每秒1块.