详解oracle checkpoint
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 详解oracle checkpoint
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
这篇文章，源于群中几位网友的疑问，故打算写这篇文章给其释疑。 1. 何为检查点？其存在的意义？ 2. 检查点的有哪些分类？检查点的结构如何？什么是checkpoint queue？ 通俗的将，检查点仅仅是一个机制而已，其作用是通知DBWR进程将cache buffer中的脏块写入 到disk中，当然这个通知的动作是通过检查点进程CKPT来完成的。那么检查点存在的意义是什 么呢？其实很简单，目的就是减少db crash后的recover time。 这里可能存在一个误区，那就是并不是只有检查点的情况下才会导致DBWR进程去写脏块到disk 中，在cache buffer 空间不足的情况下，由于Cache buffer LRU的机制就决定了不管是否发生 检查点，DBWR都会将脏块写入到disk中，以此来保证cache buffer能容纳更多的new block。 所谓的checkpoint queue，可以理解为由脏块组成的一个LRU链表。 检查点有哪些分类呢？大概有如下几种： <div id="B" style="height:100px; width:70%;OVERFLOW-y:auto;border:blue 1px solid;margin:10px"> 1. Full Checkpoint 2. Thread Checkpoint (local checkpoint, global checkpoint) 3. Datafile Checkpoint 4. Mini-checkpoint (object checkpoint,基于DDL） 5. Parallel Query Checkpoint 6. Incremental Checkpoint (Log Switch Checkpoint) </div> 1. 首先我们来看下完全检查点，对于full checkpoint，在8i之前就存在了，很好理解，你目的是 一旦触发完全检查点，DBWR进程会将cache buffer drity LRU list上的所有脏块写入到disk中。 那么在哪些情况下会触发完全检查点呢？如下： (1) shutdown instance; (2) 日志组切换 (3) log_checkpoint_timeout,log_checkpoint_interval,fast_start_io_target,fast_start_mttr_target (4) 手工执行alter system switch logfile时 (5) 手工alter system checkpoint (6) alter tablespace XXX begin/end backup; (7) alter tablespace XXX datafile offline; 注意： 在oracle 8i以后，引入增量检查点以后，日志切换以及switch logfile都是增量检查点， 不过需要说明一下的是，虽然说其是增量检查点，不过同时触发时controlfile和datafile header 都会进行更新的，后面的实验可以说明。 完全检查点就说到这里，至于局部检查点以及全局检查点，注意是针对RAC而言，可以这样理解： alter system checkpoint local; 就是触发单实例的完全检查点 alter system checkpoint global; 就是RAC中所有实例同时触发完全检查点 2. 文件检查点 文件检查点相对完全检查点而言，你可以理解为属于其子集，其针对范围是某个表空间或表空间 中的某个数据文件，当执行如下命令的时候，会触发文件检查点： alter tablespace XXX begin/end backup; alter tablespace ,datafile offline; 下面通过bbed来验证一下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
BBED> info all File# Name Size(blks) ----- ---- ---------- 1 /home/ora10g/oradata/roger/system01.dbf 57600 2 /home/ora10g/oradata/roger/undotbs01.dbf 118400 3 /home/ora10g/oradata/roger/sysaux01.dbf 33280 4 /home/ora10g/oradata/roger/users01.dbf 640 5 /home/ora10g/oradata/roger/roger01.dbf 2560 6 /home/ora10g/oradata/roger/roger02.dbf 1280 BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> set file 6 block 1 FILE# 6 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> set file 4 block 1 FILE# 4 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 |
|
1 |
将表空间ROGER offline，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
SQL> alter tablespace roger offline; Tablespace altered. SQL> ! [ora10g@killdb ~]$ bbed parfile=parfile.bbed Password: BBED: Release 2.0.0.0.0 - Limited Production on Sun Nov 6 23:29:39 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set file 4 block 1 FILE# 4 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000df6b3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db074d1 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x000075ab ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000df6b3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db074d1 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x000075ab ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 ++++++ 我们可以看到仅仅是表空间roger中的几个datafile的检查点SCN发生了改变。++++++ |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
3. 对象检查点 那么object checkpoint是什么时候触发的呢？通常是如下两种情况： drop table xxx/xxx purge; drop index xxx; truncate table xxx; 那么如何理解object checkpoint呢？我认为可以这样简单理解： drop table killdb; 将触发一个object 检查点，将cache buffer中所有 跟killdb对象有关的脏块写入到disk中。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
SQL> alter system flush BUFFER_CACHE; System altered. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# >74 and a.statistic# <86 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR thread checkpoint buffers written 64 DBWR tablespace checkpoint buffers written 0 DBWR parallel query checkpoint buffers written 128 DBWR object drop buffers written 33 DBWR transaction table writes 76 DBWR undo block writes 169 DBWR revisited being-written buffer 0 DBWR make free requests 0 DBWR lru scans 0 DBWR checkpoints 15 DBWR fusion writes 0 11 rows selected. SQL> create table t1(id number); Table created. SQL> insert into t1 values(1); 1 row created. SQL> commit; Commit complete. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# >74 and a.statistic# <86 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR thread checkpoint buffers written 64 DBWR tablespace checkpoint buffers written 0 DBWR parallel query checkpoint buffers written 128 DBWR object drop buffers written 33 DBWR transaction table writes 76 DBWR undo block writes 169 DBWR revisited being-written buffer 0 DBWR make free requests 0 DBWR lru scans 0 DBWR checkpoints 15 DBWR fusion writes 0 11 rows selected. SQL> drop table t1; Table dropped. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# >74 and a.statistic# <86 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR thread checkpoint buffers written 64 DBWR tablespace checkpoint buffers written 0 DBWR parallel query checkpoint buffers written 128 DBWR object drop buffers written 41 DBWR transaction table writes 76 DBWR undo block writes 169 DBWR revisited being-written buffer 0 DBWR make free requests 0 DBWR lru scans 0 DBWR checkpoints 16 DBWR fusion writes 0 11 rows selected. |
|
1 |
另外，dbsnake大牛以前也写过一篇相关的文章，详见如下链接： |
详细解析truncate引发的object checkpoint
|
1 2 3 |
4. 并行查询检查点 对应并行查询，由于是走direct read，所以oracle会触发检查点将buffer中的脏块写入到disk中， |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
SQL> select statistic#,name from v$statname where name like '%DBWR%'; STATISTIC# NAME ---------- ---------------------------------------------------------------- 74 DBWR checkpoint buffers written 75 DBWR thread checkpoint buffers written 76 DBWR tablespace checkpoint buffers written 77 DBWR parallel query checkpoint buffers written 78 DBWR object drop buffers written 79 DBWR transaction table writes 80 DBWR undo block writes 81 DBWR revisited being-written buffer 82 DBWR make free requests 83 DBWR lru scans 84 DBWR checkpoints 85 DBWR fusion writes 12 rows selected. SQL> alter system flush BUFFER_CACHE; System altered. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# in (77, 84) 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 123 DBWR checkpoints 9 SQL> delete from t1 where rownum <2; 1 row deleted. SQL> commit; Commit complete. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# in (77, 84) 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 123 DBWR checkpoints 9 SQL> select/*+ parallel(t1,4)*/distinct id from t1; ID ---------- 1314 SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# in (77, 84) 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 124 DBWR checkpoints 10 SQL> delete from t1 where rownum <1000; 999 rows deleted. SQL> commit; Commit complete. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# in (77, 84) 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 124 DBWR checkpoints 10 SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> select/*+ parallel(t1,4)*/distinct id from t1; ID ---------- 1314 SQL> alter session set events '10046 trace name context off'; Session altered. SQL> select b.name, a.value 2 from v$sysstat a, v$statname b 3 where a.statistic# in (77, 84) 4 and a.statistic# = b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 128 DBWR checkpoints 11 |
|
1 2 3 |
我们可以看到，在第2次执行parallel query时，DBWR写了4个block (128-124)，但是 此时的checkpoint只发生了1次，这也直接验证了parallel query checkpoint。 这里其实还有object checkpoint产生，我们通过10046 trace可以看出，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
===================== PARSING IN CURSOR #3 len=45 dep=0 uid=60 oct=3 lid=60 tim=1289872258867729 hv=4038107070 ad='25f2dd08' select/*+ parallel(t1,4)*/distinct id from t1 END OF STMT PARSE #3:c=57992,e=56729,p=460,cr=81,cu=0,mis=1,r=0,dep=0,og=1,tim=1289872258867724 BINDS #3: WAIT #3: nam='enq: KO - fast object checkpoint' ela= 6 name|mode=1263468550 2=65557 0=1 obj#=52229 tim=1289872258868398 WAIT #3: nam='enq: KO - fast object checkpoint' ela= 5018 name|mode=1263468550 2=65557 0=1 obj#=52229 tim=1289872258873457 WAIT #3: nam='PX Deq: Join ACK' ela= 1732 sleeptime/senderid=268500992 passes=1 p3=0 obj#=52229 tim=1289872258875411 WAIT #3: nam='PX Deq: Join ACK' ela= 10 sleeptime/senderid=268500996 passes=1 p3=0 obj#=52229 tim=1289872258875734 WAIT #3: nam='PX Deq: Join ACK' ela= 1926 sleeptime/senderid=268500996 passes=2 p3=0 obj#=52229 tim=1289872258877717 WAIT #3: nam='PX Deq: Parse Reply' ela= 7 sleeptime/senderid=200 passes=1 p3=0 obj#=52229 tim=1289872258877917 WAIT #3: nam='PX Deq: Parse Reply' ela= 91227 sleeptime/senderid=200 passes=2 p3=0 obj#=52229 tim=1289872258969183 WAIT #3: nam='PX Deq: Parse Reply' ela= 7 sleeptime/senderid=200 passes=1 p3=0 obj#=52229 tim=1289872258969352 WAIT #3: nam='PX Deq: Parse Reply' ela= 2059 sleeptime/senderid=200 passes=2 p3=0 obj#=52229 tim=1289872258971446 |
|
1 2 3 4 5 6 7 8 |
5. 增量检查点 关于增量检查点，是本文需要描述的重点，增量检查点其实是在oracle 8i就引入了，引入的目的 当然是为了更大程度的降低instance crash时的recover time。 说道增量检查点，我们就需要讲讲其相关的一个重要结构，那就是checkpoint queue。 当然，checkpoint queue并不仅仅是为增量检查点服务的，也包括其他类型的检查点。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPLRBA_BOF,CPODR_SEQ,CPODR_BNO,CPODR_BOF,CPODS from x$kcccp; CPLRBA_SEQ CPLRBA_BNO CPLRBA_BOF CPODR_SEQ CPODR_BNO CPODR_BOF CPODS ---------- ---------- ---------- ---------- ---------- ---------- ---------------- 9 1303 0 9 1312 0 916350 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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 8 rows selected. '++++++ 相关字段的解释如下：++++++' low rba of cache: CPLRBA_SEQ log seq # CPLRBA_BNO block # in log file CPLRBA_BOF byte offset in block on disk rba: CPODR_SEQ log seq # CPODR_BNO block # in log file CPODR_BOF byte offset in block CPODS on disk scn SQL> select rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof,rtckp_scn from x$kccrt; RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 9 2 16 915739 '++++++ 相关字段解释如下：++++++' RTCKP_RBA_SEQ log seq # RTCKP_RBA_BNO block # in log file RTCKP_RBA_BOF byte offset in block RTCKP_SCN checkpoint scn |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
BBED> set file 1 block 1 FILE# 1 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000df91b ==> 转换为10进制为915739 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db07b87 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000009 ==> rba_seq ub4 kcrbabno @504 0x00000002 ==> rba_bno ub2 kcrbabof @508 0x0010 ==> rba_bof ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 |
|
1 |
下面switch logfile一下, 看看结果： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header order by 1; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 915739 2 915739 3 915739 4 915739 5 915747 6 915747 6 rows selected. SQL> alter system checkpoint; System altered. SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header order by 1; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 916589 2 916589 3 916589 4 916589 5 916589 6 916589 6 rows selected. SQL> set lines 200 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 7 52428800 1 NO INACTIVE 872908 03-NOV-11 2 1 8 52428800 1 NO INACTIVE 901680 05-NOV-11 3 1 9 52428800 1 NO CURRENT 915739 06-NOV-11 SQL> alter system switch logfile; System altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 10 52428800 1 NO CURRENT 916611 07-NOV-11 2 1 8 52428800 1 NO INACTIVE 901680 05-NOV-11 3 1 9 52428800 1 NO INACTIVE 915739 06-NOV-11 SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header order by 1; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 916611 2 916611 3 916611 4 916611 5 916611 6 916611 6 rows selected. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 10 52428800 1 NO CURRENT 916611 07-NOV-11 2 1 8 52428800 1 NO INACTIVE 901680 05-NOV-11 3 1 9 52428800 1 NO INACTIVE 915739 06-NOV-11 SQL> alter system switch logfile; System altered. SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header order by 1; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 916663 2 916663 3 916663 4 916663 5 916663 6 916663 6 rows selected. SQL> select rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof,rtckp_scn from x$kccrt; RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 11 2 16 916663 '++++++ 我们可以看到虽然把logfile switch归为增量检查点，其实datafile header也是被更新了。++++++' SQL> select rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof, rtckp_scn from x$kccrt; RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 11 2 16 916663 SQL> alter tablespace roger offline; Tablespace altered. SQL> select rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof, rtckp_scn from x$kccrt; RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 11 2 16 916663 |
|
1 |
我们可以看到，对于alter tablespace xxx offline操作，触发的是增量检查点。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000dfd78 ==> 916663 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db08628 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000000b ==> 11 ub4 kcrbabno @504 0x00000015 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
如果想关闭增量检查点功功能，可以调整参数 _disable_incremental_checkpoints 为true。 最后回到网友的问题上来，如下： <div id="C" style="height:100px; width:70%;OVERFLOW-y:auto;border:blue 1px solid;margin:10px"> 获思(280770144) 10:40:34 checkpoint 的rba 排序是从大到小，还是从小到大呢？ 东东堂(327356330) 10:41:28 只是说排序，好像没有说从大到小，还是从小到大 </div> 既然说道checkpoint rba，那么我们就需要先来了解下rba的结构，其实在上面的实验中 就已经给出了rba的结构： Checkpoint RBA = RTCKP_RBA_SEQ + RTCKP_RBA_BNO + RTCKP_RBA_BOF 在10g中，checkpoint rba一个占据12 bytes，换句话说，将上面三部分组成在一起就构成 我们所需要的RBA地址，如下； |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> col low_rba for a30 SQL> col on_disk_rba for a30 SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 258 11.55.0 11.1050.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
关于rba(redo block address)，有可以分为如下3种: low checkpoint rba; 某个block第一次被修改时所对应的redo recode记录，同时也就意味着 low rba是redo apply的起点 high cechkpoint rba; 某个block被经过多次修改后，最后一次修改时所对应的redo recode记录 on disk rba; 是指disk中redo log中的最后一条redo recode记录，在实例crash后进行 实例恢复时on disk rba将是redo log apply的终点。 下面通过实验来进行说明： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 |
SQL> create table t1 as select * from dba_objects where rownum<10; Table created. SQL> select dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) blk# 3 from t1; FILE# BLK# ---------- ---------- 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 9 rows selected. SQL> update t1 set owner='www.killdb.com' where object_id=20; 1 row updated. SQL> update t1 set owner='www.killdb.com' where object_id=25; 1 row updated. SQL> update t1 set owner='www.killdb.com' where object_id=29; 1 row updated. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 40 11.1153.0 11.1283.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 22 11.1432.0 11.1450.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> alter system switch logfile; System altered. SQL> commit; Commit complete. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 23 11.1432.0 12.3.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> alter system switch logfile; System altered. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 23 11.1432.0 13.2.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> update t1 set owner='www.killdb.com' where object_id=44; 1 row updated. SQL> update t1 set owner='www.killdb.com' where object_id=51; 1 row updated. SQL> commit; Commit complete. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 26 11.1432.0 13.4.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> alter system switch logfile; System altered. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 3 13.2.0 14.2.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 13 52428800 1 NO ACTIVE 918247 07-NOV-11 2 1 14 52428800 1 NO CURRENT 918274 07-NOV-11 3 1 12 52428800 1 NO ACTIVE 918241 07-NOV-11 SQL> SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 3 13.2.0 14.2.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> alter system switch logfile; System altered. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 0 4294967295.4294967295.65535 15.2.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 0 4294967295.4294967295.65535 15.2.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> update t1 set owner='www.killdb.com' where object_id=28; 1 row updated. SQL> commit; Commit complete. SQL> select cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 from x$kcccp; CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 3 15.2.0 15.4.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.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 8 rows selected. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 13 52428800 1 NO INACTIVE 918247 07-NOV-11 2 1 14 52428800 1 NO INACTIVE 918274 07-NOV-11 3 1 15 52428800 1 NO CURRENT 918300 07-NOV-11 |
|
1 2 |
我们可以看到，对应由脏块组成的checkpoint queue队列，DBWR是按照seq为顺序将脏块写入到disk中的。 另外通过controlfile的dump，我们也能看到low rba和on disk scn的信息，如下： |
|
1 2 3 |
SQL> alter session set events 'immediate trace name controlf level 8'; Session altered. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
++++++ trace 部分信息如下：++++++ 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:3 low cache rba:(0xf.2.0) on disk rba:(0xf.4.0) on disk scn: 0x0000.000e0330 11/07/2011 01:43:51 resetlogs scn: 0x0000.000cf315 11/03/2011 02:05:04 heartbeat: 766575498 mount id: 2470768355 THREAD #2 - status:0x0 flags:0x0 dirty:0 low cache rba:(0xf.2.0) -- （f->15,2->2,0->0),跟上面查询x$kcccp的结果是一样的。 |
|
1 |
最后补充一下，10g oracle引入了增量检查点自动调节机制，其中相关的几个参数如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> show parameter checkpoint NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _disable_incremental_checkpoints boolean FALSE _disable_selftune_checkpointing boolean FALSE _log_checkpoint_recovery_check integer 0 _selftune_checkpoint_write_pct integer 3 _selftune_checkpointing_lag integer 300 log_checkpoint_interval integer 0 log_checkpoint_timeout integer 1800 log_checkpoints_to_alert boolean FALSE _disable_incremental_checkpoints 决定是否启用增量检查点 _disable_selftune_checkpointing 决定是否启动增量检查点启动调节，默认是false _selftune_checkpoint_write_pct 是一个百分比 _selftune_checkpointing_lag 默认值是300s。 |
5 Responses to “详解oracle checkpoint”
好文章,帮顶
“并行查询，由于是走direct read”
并行查询未必走direct read
精文顶起，向ROGER大帅牛致敬!
[…] 是recover的起点，这个是checkpoint东西，大家可以参考这里：详解oracle checkpoint […]
嗯，学习了，消化中。。
Leave a Reply
You must be logged in to post a comment.