2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
增量检查点如何更新控制文件?
有同学在 T.askmaclean.com上提问关于增量检查点更新控制文件的问题：
Know more about checkpoint checkpoint 分成很多种 full 、file、thread、parallel query、 object 、incremental 、logfile switch 每一种checkpoint 都有其自身的特性，例如Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile 但是不更新datafile header， 而FULL CHECKPOINT要求立即完成(同步的) 且会同时更新 controlfile 和 datafile header。 Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile >>我想问的时：如何查看此时控制文件中更新的SCN?除了DUMP控制文件，有没有命令查询?我希望通过以下演示说明该问题：
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> oradebug setmypid; Statement processed. SQL> oradebug dump controlf 4; Statement processed. SQL> oradebug tracefile_name ; /s01/admin/G10R25/udump/g10r25_ora_4660.trc 另开一个窗口等待6s在做一次controlf DUMP SQL> exec dbms_lock.sleep(6); oradebug setmypid; oradebug dump controlf 4; oradebug tracefile_name ; PL/SQL procedure successfully completed. SQL> Statement processed. SQL> Statement processed. SQL> /s01/admin/G10R25/udump/g10r25_ora_4663.trc比较以上获得的2个前后有6s间隔的CONTROLFILE DUMP 文件：
[oracle@vrh8 udump]$ diff /s01/admin/G10R25/udump/g10r25_ora_4660.trc /s01/admin/G10R25/udump/g10r25_ora_4663.trc 1c1 < /s01/admin/G10R25/udump/g10r25_ora_4660.trc --- > /s01/admin/G10R25/udump/g10r25_ora_4663.trc 13c13 < Unix process pid: 4660, image: oracle@vrh8.oracle.com (TNS V1-V3) --- > Unix process pid: 4663, image: oracle@vrh8.oracle.com (TNS V1-V3) 15,18c15,19 < *** ACTION NAME:() 2012-07-22 07:59:08.215 < *** MODULE NAME:(sqlplus@vrh8.oracle.com (TNS V1-V3)) 2012-07-22 07:59:08.215 < *** SERVICE NAME:(SYS$USERS) 2012-07-22 07:59:08.215 < *** SESSION ID:(159.7) 2012-07-22 07:59:08.215 --- > *** 2012-07-22 07:59:31.779 > *** ACTION NAME:() 2012-07-22 07:59:31.779 > *** MODULE NAME:(sqlplus@vrh8.oracle.com (TNS V1-V3)) 2012-07-22 07:59:31.779 > *** SERVICE NAME:(SYS$USERS) 2012-07-22 07:59:31.779 > *** SESSION ID:(159.9) 2012-07-22 07:59:31.779 96,98c97,99 < THREAD #1 - status:0x2 flags:0x0 dirty:56 < low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0) < on disk scn: 0x0000.013fe7a8 07/22/2012 07:59:02 --- > THREAD #1 - status:0x2 flags:0x0 dirty:57 > low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0) > on disk scn: 0x0000.013fe7c2 07/22/2012 07:59:27 100,101c101,102 < heartbeat: 789262462 mount id: 2675014163 < Flashback log tail log# 15 thread# 1 seq 229 block 274 byte 0 --- > heartbeat: 789262470 mount id: 2675014163 > Flashback log tail log# 15 thread# 1 seq 229 block 275 byte 0 2490c2491 < V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456 2501c2502 < V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456 2511c2512 < V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456 2521c2522 < V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456 2531c2532 < V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456排除部分V$RMAN_STATUS记录存在差异外，最主要的差别在于： CHECKPOINT PROGRESS RECORDS 这是因为 ckpt 每3s一次对controlfile做heartbeat 更新 CHECKPOINT PROGRESS RECORDS。 第一次 controlf dump： *************************************************************************** CHECKPOINT PROGRESS RECORDS *************************************************************************** (size = 8180, compat size = 8180, section max = 11, section in-use = 0, last-recid= 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 2, numrecs = 11) THREAD #1 - status:0x2 flags:0x0 dirty:56 low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0) on disk scn: 0x0000.013fe7a8 07/22/2012 07:59:02 resetlogs scn: 0x0000.01394f1a 07/19/2012 07:27:21 heartbeat: 789262462 mount id: 2675014163 Flashback log tail log# 15 thread# 1 seq 229 block 274 byte 0 THREAD #2 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 Flashback log tail log# 0 thread# 0 seq 0 block 0 byte 0 第二次 controlf dump: *************************************************************************** CHECKPOINT PROGRESS RECORDS *************************************************************************** (size = 8180, compat size = 8180, section max = 11, section in-use = 0, last-recid= 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 2, numrecs = 11) THREAD #1 - status:0x2 flags:0x0 dirty:57 low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0) on disk scn: 0x0000.013fe7c2 07/22/2012 07:59:27 resetlogs scn: 0x0000.01394f1a 07/19/2012 07:27:21 heartbeat: 789262470 mount id: 2675014163 Flashback log tail log# 15 thread# 1 seq 229 block 275 byte 0 THREAD #2 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 差异在于 on disk rba on disk scn heartbeat Flashback log tail log# 即实际CKPT每3s更新heartbeat控制文件一次，更新的内容是 on disk rba、on disk scn、heartbeat 如果启用了闪回日志的话那么还有Flashback log ， 而并不更新数据库当前的SCN(CURRENT SCN)。 如果你想查看ckpt每3s更新的 on disk scn的话可以参考 内部视图X$KCCCP--[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress X$KCCCP Checkpoint Progress Records:
SQL> desc x$kcccp; Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER CPTNO NUMBER CPSTA NUMBER CPFLG NUMBER CPDRT NUMBER CPRDB NUMBER CPLRBA_SEQ NUMBER CPLRBA_BNO NUMBER CPLRBA_BOF NUMBER CPODR_SEQ NUMBER CPODR_BNO NUMBER CPODR_BOF NUMBER CPODS VARCHAR2(16) CPODT VARCHAR2(20) CPODT_I NUMBER CPHBT NUMBER CPRLS VARCHAR2(16) CPRLC NUMBER CPMID NUMBER CPSDR_SEQ NUMBER CPSDR_BNO NUMBER CPSDR_ADB NUMBER其中cpods 为 " on disk scn" ，cpodr_seq||cpodr_bno||cpodr_bof为"on disk rba",CPHBT为heartbeat number:
SQL> select cpods "on disk scn", 2 to_char(cpodr_seq, 'xxxxxx') || ',' || 3 to_char(cpodr_bno, 'xxxxxxxxx') || ',' || 4 to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", 5 CPHBT "heartbeat number" 6 from x$kcccp; on disk scn on disk rba heartbeat number ---------------- ------------------------------ ---------------- 20968609 1a, 240a, 0 789263152 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 8 rows selected. SQL> SQL> SQL> exec dbms_lock.sleep(3); PL/SQL procedure successfully completed. SQL> select cpods "on disk scn", 2 to_char(cpodr_seq, 'xxxxxx') || ',' || 3 to_char(cpodr_bno, 'xxxxxxxxx') || ',' || 4 to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", 5 CPHBT "heartbeat number" 6 from x$kcccp; on disk scn on disk rba heartbeat number ---------------- ------------------------------ ---------------- 20968613 1a, 2410, 0 789263154 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 8 rows selected. SQL> SQL> exec dbms_lock.sleep(3); PL/SQL procedure successfully completed. SQL> select cpods "on disk scn", 2 to_char(cpodr_seq, 'xxxxxx') || ',' || 3 to_char(cpodr_bno, 'xxxxxxxxx') || ',' || 4 to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", 5 CPHBT "heartbeat number" 6 from x$kcccp; on disk scn on disk rba heartbeat number ---------------- ------------------------------ ---------------- 20968623 1a, 241e, 0 789263156 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 0 0, 0, 0 0 8 rows selected.
posted on 2013-03-19 00:51 Oracle和MySQL 阅读(259) 评论(0) 编辑 收藏 举报
