增量检查点如何更新控制文件
来源:互联网 发布:java游戏编程下载 编辑:程序博客网 时间:2024/05/22 04:53
原文:http://www.askmaclean.com/archives/incremental-checkpoint-update-controlfile.html
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 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - ProductionSQL> 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.trc1c1< /s01/admin/G10R25/udump/g10r25_ora_4660.trc --- > /s01/admin/G10R25/udump/g10r25_ora_4663.trc13c13< 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.77996,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:27100,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 02490c2491< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562501c2502< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562511c2512< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562521c2522< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562531c2532< 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:0×2 flags:0×0 dirty:56
low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0)
on disk scn: 0×0000.013fe7a8 07/22/2012 07:59:02
resetlogs scn: 0×0000.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:0×0 flags:0×0 dirty:0
low cache rba:(0×0.0.0) on disk rba:(0×0.0.0)
on disk scn: 0×0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0×0000.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:0×2 flags:0×0 dirty:57
low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0)
on disk scn: 0×0000.013fe7c2 07/22/2012 07:59:27
resetlogs scn: 0×0000.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:0×0 flags:0×0 dirty:0
low cache rba:(0×0.0.0) on disk rba:(0×0.0.0)
on disk scn: 0×0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0×0000.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", to_char(cpodr_seq, 'xxxxxx') || ',' || to_char(cpodr_bno, 'xxxxxxxxx') || ',' || to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", CPHBT "heartbeat number" from x$kcccp;on disk scn on disk rba heartbeat number---------------- ------------------------------ ----------------20968609 1a, 240a, 0 7892631520 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 08 rows selected.SQL> SQL> SQL> exec dbms_lock.sleep(3);PL/SQL procedure successfully completed.SQL> select cpods "on disk scn", to_char(cpodr_seq, 'xxxxxx') || ',' || to_char(cpodr_bno, 'xxxxxxxxx') || ',' || to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", CPHBT "heartbeat number" from x$kcccp;on disk scn on disk rba heartbeat number---------------- ------------------------------ ----------------20968613 1a, 2410, 0 7892631540 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 08 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 7892631560 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 08 rows selected.
原文:http://www.askmaclean.com/archives/incremental-checkpoint-update-controlfile.html
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 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - ProductionSQL> 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.trc1c1< /s01/admin/G10R25/udump/g10r25_ora_4660.trc --- > /s01/admin/G10R25/udump/g10r25_ora_4663.trc13c13< 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.77996,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:27100,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 02490c2491< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562501c2502< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562511c2512< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562521c2522< V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288 --- > V$RMAN_STATUS: recid=140733792718464, stamp=1407337927184562531c2532< 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:0×2 flags:0×0 dirty:56
low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0)
on disk scn: 0×0000.013fe7a8 07/22/2012 07:59:02
resetlogs scn: 0×0000.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:0×0 flags:0×0 dirty:0
low cache rba:(0×0.0.0) on disk rba:(0×0.0.0)
on disk scn: 0×0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0×0000.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:0×2 flags:0×0 dirty:57
low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0)
on disk scn: 0×0000.013fe7c2 07/22/2012 07:59:27
resetlogs scn: 0×0000.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:0×0 flags:0×0 dirty:0
low cache rba:(0×0.0.0) on disk rba:(0×0.0.0)
on disk scn: 0×0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0×0000.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", to_char(cpodr_seq, 'xxxxxx') || ',' || to_char(cpodr_bno, 'xxxxxxxxx') || ',' || to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", CPHBT "heartbeat number" from x$kcccp;on disk scn on disk rba heartbeat number---------------- ------------------------------ ----------------20968609 1a, 240a, 0 7892631520 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 08 rows selected.SQL> SQL> SQL> exec dbms_lock.sleep(3);PL/SQL procedure successfully completed.SQL> select cpods "on disk scn", to_char(cpodr_seq, 'xxxxxx') || ',' || to_char(cpodr_bno, 'xxxxxxxxx') || ',' || to_char(cpodr_bof, 'xxxxxxxxx') "on disk rba", CPHBT "heartbeat number" from x$kcccp;on disk scn on disk rba heartbeat number---------------- ------------------------------ ----------------20968613 1a, 2410, 0 7892631540 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 08 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 7892631560 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 00 0, 0, 0 08 rows selected.
- 增量检查点如何更新控制文件
- 检查点与增量检查点
- 检查点队列与增量检查点
- 详细论述增量检查点
- oracle的增量检查点
- 什么是增量检查点?
- 控制文件简介-SCN-检查点-控制文件头
- oracle完全检查点和增量检查点详解
- 完全检查点和增量检查点详解
- ORACLE Checkpoint-检查点队列与增量检查点
- oracle检查点队列与增量检查点【转载】
- oracle完全检查点和增量检查点详解
- oracle完全检查点和增量检查点详解
- oracle完全检查点和增量检查点详解
- Oracle 进程之 增量检查点
- Unity5 如何做资源管理和增量更新,unity5增量
- android增量更新中增量文件的生成和合并
- android增量更新中增量文件的生成和合并
- 打开android4.0默认的虚拟键盘
- 圆形是否和正方形相交
- 找出环的初始节点
- struts中JSP表单提交路径的问题
- Linux下rpm 安装包方式安装
- 增量检查点如何更新控制文件
- Android异步加载图像小结
- android的UI事件处理
- 如何写一个正确的equals方法
- 如何防止Apache显示文件列表
- 单实例设计模式的实现
- win7共享xp打印机和文件解决方法(图解)
- C语言之位域小记
- zoj 1115 Digital Roots