Oracle Study之--CHECKPOINT探讨
来源:互联网 发布:matlab怎么表示零矩阵 编辑:程序博客网 时间:2024/05/21 06:57
Oracle Study之--CHECKPOINT探讨
Know more about checkpointcheckpoint 分成很多种 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:0x2 flags:0x0 dirty:56low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0)on disk scn: 0x0000.013fe7a8 07/22/2012 07:59:02resetlogs scn: 0x0000.01394f1a 07/19/2012 07:27:21heartbeat: 789262462 mount id: 2675014163Flashback log tail log# 15 thread# 1 seq 229 block 274 byte 0THREAD #2 – status:0x0 flags:0x0 dirty:0low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)on disk scn: 0x0000.00000000 01/01/1988 00:00:00resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00heartbeat: 0 mount id: 0Flashback 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:57low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0)on disk scn: 0x0000.013fe7c2 07/22/2012 07:59:27resetlogs scn: 0x0000.01394f1a 07/19/2012 07:27:21heartbeat: 789262470 mount id: 2675014163Flashback log tail log# 15 thread# 1 seq 229 block 275 byte 0THREAD #2 – status:0x0 flags:0x0 dirty:0low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)on disk scn: 0x0000.00000000 01/01/1988 00:00:00resetlogs 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 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", 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 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.
Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: ckpt, incremental checkpoint, on disk rba
logfile switch causes incremental checkpoint?
2010/11/03 by Maclean Liu 4条评论
不少Oracle的初学者都会因为checkpoint这个知识点而头痛,绝大多数Oracle文档对完全检查点和增量检查点的描述又都略显朦胧;譬如在线日志的切换引起的是完全检查点还是增量检查点这个问题,就有不少的争论。实际上增量检查点与完全检查点有一个显著的区别:完全检查点发生时控制文件和数据文件头中的checkpoint scn都会被更新,而增量检查点发生时只有控制文件中的checkpoint scn更新;
我们可以通过以下演示证明日志切换引发的到底是何种检查点?:
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1665476
1665476
1665476
1665476
1665476
1665476
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 手动执行checkpoint,数据文件头的checkpoint scn立即更新了 */
SQL> alter system flush buffer_cache;
System altered.
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 单纯flush buffer cache冲刷数据库高速缓存不会更新数据文件头的checkpoint scn */
SQL> alter system set log_checkpoints_to_alert=true;
System altered.
SQL> alter system set log_checkpoint_timeout=20;
System altered.
/* 设置log_checkpoint_timeout为20s,频繁引发增量检查点 */
alert log:
Wed Nov 3 20:24:49 2010
Incremental checkpoint up to RBA [0x3d.dff1.0], current log tail at RBA [0x3d.dff6.0]
Wed Nov 3 20:25:07 2010
Incremental checkpoint up to RBA [0x3d.dff7.0], current log tail at RBA [0x3d.dffc.0]
Wed Nov 3 20:25:25 2010
Incremental checkpoint up to RBA [0x3d.dffd.0], current log tail at RBA [0x3d.e002.0]
Wed Nov 3 20:25:43 2010
Incremental checkpoint up to RBA [0x3d.e003.0], current log tail at RBA [0x3d.e008.0]
Wed Nov 3 20:26:01 2010
Incremental checkpoint up to RBA [0x3d.e009.0], current log tail at RBA [0x3d.e00e.0]
SQL> set time on;
20:26:38 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 可以看到增量检查点并不会引起数据文件头的checkpoint scn 被更新 */
20:26:43 SQL> alter system set log_checkpoint_timeout=1800;
System altered.
/* 那么日志文件切换就会引起数据文件头的checkpoint scn被更新吗?*/
20:28:10 SQL> alter system switch logfile;
System altered.
20:29:16 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* logfile switch 日志文件切换引起的是一种slow慢的完全检查点,它不同于alter system checkpoint(ASC),
ASC要求的脏块写出和控制文件及数据文件头更新时要立即完成的,也就是说当alter system checkpoint语句返回"System altered."
后以上工作都已经完成了;而alter system switch logfile或者自然的日志切换引发的是一种慢的完全检查点,
它在返回"System altered"时不要求写脏块等工作必须已经完成
*/
/* 我们可以用冲刷高速缓存的方式保证脏块写出的工作被督促完成 */
20:33:39 SQL> alter system flush buffer_cache;
System altered.
20:33:45 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697544
1697544
1697544
1697544
1697544
1697544
6 rows selected.
/* 虽然日志切换所引发的slow checkpoint(慢的检查点)并无立即完成的要求,但也并非全无限制;
当某次日志切换由1号日志组切换到2号日志组时,
将引发一个slow checkpoint,之后日志连续切换又要切到1号日志组时要求之前的那个slow checkpoint在切换前必须完成
*/
20:41:35 SQL> set timing on;
20:42:02 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 67 52428800 2 YES INACTIVE 1698288 2010-11-03 20:41:19
2 1 68 52428800 2 YES INACTIVE 1698292 2010-11-03 20:41:21
3 1 69 52428800 2 NO CURRENT 1698302 2010-11-03 20:41:35
Elapsed: 00:00:00.00
20:42:17 SQL> delete tv;
51134 rows deleted.
Elapsed: 00:00:01.68
20:42:34 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
20:42:36 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
20:42:40 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
20:42:43 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:02.00
20:45:28 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------ 1700686
1700686
1700686
1700686
1700686
1700686
6 rows selected.
Elapsed: 00:00:00.00
alter.log告警日志中的内容:
Wed Nov 3 20:42:40 2010
Beginning log switch checkpoint up to RBA [0x46.2.10], SCN: 1700686...........................
Wed Nov 3 20:42:45 2010
Thread 1 cannot allocate new log, sequence 72
Checkpoint not complete
....................
Completed checkpoint up to RBA [0x46.2.10], SCN: 1700686
/* 最近一次的日志切换耗费2s,在告警日志中可以看到此次slow checkpoint的相关记录 */
转自:http://www.oracledatabase12g.com/archives/tag/incremental-checkpoint 感谢作者!
- Oracle Study之--CHECKPOINT探讨
- Oracle之checkpoint
- oracle 之 checkpoint
- oracle之检查点(Checkpoint)
- oracle之检查点(Checkpoint)
- oracle之检查点(Checkpoint)
- oracle之检查点(Checkpoint)
- Oracle Study之--NLS_DATE_FORMAT
- oracle checkpoint
- Oracle RAC Study之--10gR2 RAC环境中在线添加、删除Votedisk和在线替换OCR的探讨
- Oracle Study之案例--Oracle Sqlplus错误
- Oracle Study之--Oracle TimeZone升级
- Oracle Study之--Oracle 触发器(Trigger)
- Oracle Study之--Oracle正则表达式
- Oracle Study之--Oracle数据仓库备份方案
- ORACLE checkpoint cnt和checkpoint scn
- Oracle检查点ckpt (checkpoint)
- Oracle checkpoint详解
- hdu 5193 分块 树状数组 逆序对
- MySQL Study案例之--快速了解MySQL服务器
- 户外生活之--九龙山冬日美景
- Linux Study之--yum install错误
- Oracle DataGuard Study之--DataGuard FailOver案例
- Oracle Study之--CHECKPOINT探讨
- Oracle Study之--Logical Standby日志应用错误案例
- Oracle Study之--Oracle TimeZone升级
- IBM PVM Study之--IBM PVM技术概述
- Oracle Study之--ORA-12537(TNS:connection closed) 错误案例
- angular-ui/bootstrap
- Oracle Study之--Oracle RAC重建控制文件
- 将博客搬至CSDN
- 函数