ITPub博客

首页 > 数据库 > Oracle > DB_BLOCK_CHECKSUM VS DB_BLOCK_CHECKING

DB_BLOCK_CHECKSUM VS DB_BLOCK_CHECKING

原创 Oracle 作者:oliseh 时间:2015-08-18 10:04:02 1 删除 编辑

db_block_checksum:数据块被写入磁盘时是否计算校验值放到块头部,这个校验值放在名为chkval_kcbh的变量里,对应数据块里的chkval

db_block_checking:
数据块发生更改后对数据块内部结构的逻辑完整性进行检验,具体的说就是检测块里的metadata值是否合理

先简单了解一下数据块的组成,通过alter system dump datafile X block Y命令得到一个数据块的结构信息:

<Cache Layer>

scn: 0x0000.009701ee seq: 0x02 flg: 0x06 tail: 0x01ee0602

frmt: 0x02 chkval: 0xa196 type: 0x06=trans data

<Transaction Layer>

seg/obj: 0x15d31  csc: 0x00.9701e8  itc: 3  flg: E  typ: 1 - DATA

     brn: 0  bdba: 0x1000590 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.00c.00004aec  0x00c00891.1859.13  --U-    1  fsc 0x0000.009701ee

0x02   0x0008.01f.00000581  0x00c0090c.0760.32  C---    0  scn 0x0000.0096f971

0x03   0x0001.00d.000005a6  0x00c00081.077e.27  C---    0  scn 0x0000.0096ffaa

bdba: 0x01000593

<Table Directory>

tsiz: 0x1f80

hsiz: 0x50

pbl: 0x11085fa7c

     76543210

flag=--------

ntab=1

nrow=31

frre=-1

fsbo=0x50

fseo=0x174d

avsp=0x1c80

tosp=0x1c80

0xe:pti[0]      nrow=31 offs=0

<Row Directory>

0x12:pri[0]     offs=0x1f66

0x14:pri[1]     offs=0x1818

0x16:pri[2]     offs=0x184a

0x18:pri[3]     offs=0x185e

。。。省略部分输出

<Row Data>

tab 0, row 0, @0x1f66

tl: 26 fb: --H-FL-- lb: 0x0  cc: 3

col  0: [ 7]  58 53 24 4e 55 4c 4c

col  1: [ 6]  c5 16 30 31 25 27

col  2: [ 7]  78 6f 0a 15 04 39 24

tab 0, row 1, @0x1818

tl: 27 fb: --H-FL-- lb: 0x0  cc: 3

col  0: [12]  4e 45 57 55 53 45 52 31 31 31 31 31

col  1: [ 2]  c1 55

col  2: [ 7]  78 72 03 0c 12 09 0e

。。。省略部分输出

数据块由如下5个部分组成

<cache layer>:数据块的SCN、校验值、该块内容是否已经提交、块是否Fracture等信息

<Transaction layer>cleanout SCN、数据块属于哪个object、块类型、事务列表等信息

<Table Directory>:块里包含的行数、块地址、块里的空间使用情况等信息

<Row directory>:行偏移地址

<Row data>:存放用户数据

需要指出的是在数据块的结构里有两个值起到了校验的作用,

一个是我们之前提到的chkval,数据块的5个部分里任何一部分的任何一个bit的值发生变化,chkval都会重新计算,当然前提是db_block_checksum=TYPICAL,如果db_block_checksum=OFF,表示不计算校验值,chkval值永远为0

另一个是tailtail值是检测数据块是否头尾一致的重要依据,正常情况下tail的值由以下计算方法得到:由cache layerscn base部分(4个数字)+type(2个数字)+seq(2个数字)组成,若不等于这个值,该块就是一个Fractured block tail值实际存放在数据块的最底部,只不过dump的时候显示在了块开始的部分。tail值随着数据库的运行会不断的更新,不受db_block_checksum参数的控制

本文主要通过数个不同场景体验一下db_block_checksumdb_block_checking在数据块里除了校验方面所起到的不同效果:

(说明一下:对于chkval计算值与块里保存的实际值不一致产生物理坏块的错误以及tail计算值与块里保存的实际值不一致所引起的block Fractured的场景比较好理解,这里就不另作测试了)

先建立测试用的表空间和表:

create tablespace ts0811 datafile '/oradata06/testaaaaa/ts0811_1.dbf' size 128m;

create table scott.t0811_1 tablespace ts0811 as select * from dba_objects where rownum<500;

select distinct dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from scott.t0811_1;

      RFNO      BLKNO

---------- ----------

         7        131

         7        134

         7        135

         7        132

         7        137

         7        136

         7        133


###dump 7/132

alter system dump datafile 7 block 132;

------> Cache layer:

buffer tsn: 43 rdba: 0x01c00084 (7/132)

scn: 0x0ba2.a5413aa1 seq: 0x01 flg: 0x04 tail: 0x3aa10601

frmt: 0x02 chkval: 0x5516 type: 0x06=trans data

------> Transaction layer:

Block header dump:  0x01c00084

 Object id on Block? Y

 seg/obj: 0x77de2e  csc: 0xba2.a5413a70  itc: 3  flg: E  typ: 1 - DATA

     brn: 0  bdba: 0x1c00080 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0ba2.a53f8511

0x02   0x002b.019.00001d10  0x02806cb1.0389.04  C---    0  scn 0x0ba2.a540fade

0x03   0x002b.014.00001d1f  0x02806d20.0389.07  C---    0  scn 0x0ba2.a54103fa

bdba: 0x01c00084

------> Table directory

tsiz: 0x1f80

hsiz: 0xb8

pbl: 0x11085ea7c

     76543210

flag=--------

ntab=1

nrow=83

frre=-1

fsbo=0xb8

fseo=0x40c

avsp=0xef6

tosp=0x354

/////////////////////////////////////////////////

///////////// 1db_block_checksum=typicalfullTransaction Layer的数据被修改

/////////////////////////////////////////////////

A1 db_block_checksum=typical ,用BBED修改transaction layer数据,测试Select操作是否会受影响

###使用bbed修改 block 7/132 transaction layer层的ktbbhitl[0].ktbitflg

BBED> set filename '/oradata06/testaaaaa/ts0811_1.dbf'

BBED> set block 132

        BLOCK#          132

       

BBED> map /v

 File: /oradata06/testaaaaa/ts0811_1.dbf (0)

 Block: 132                                   Dba:0x00000000

------------------------------------------------------------

 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0      

    ub1 type_kcbh                           @0      

    ub1 frmt_kcbh                           @1      

    ub1 spare1_kcbh                         @2      

    ub1 spare2_kcbh                         @3      

    ub4 rdba_kcbh                           @4      

    ub4 bas_kcbh                            @8      

    ub2 wrp_kcbh                            @12     

    ub1 seq_kcbh                            @14     

    ub1 flg_kcbh                            @15     

    ub2 chkval_kcbh                         @16     

    ub2 spare3_kcbh                         @18     

 struct ktbbh, 96 bytes                     @20     

    ub1 ktbbhtyp                            @20     

    union ktbbhsid, 4 bytes                 @24     

    struct ktbbhcsc, 8 bytes                @28     

    sb2 ktbbhict                            @36     

    ub1 ktbbhflg                            @38     

    ub1 ktbbhfsl                            @39     

    ub4 ktbbhfnx                            @40     

    struct ktbbhitl[3], 72 bytes            @44     

 struct kdbh, 14 bytes                      @124    

    ub1 kdbhflag                            @124    

    sb1 kdbhntab                            @125    

    sb2 kdbhnrow                            @126    

    sb2 kdbhfrre                            @128    

    sb2 kdbhfsbo                            @130    

    sb2 kdbhfseo                            @132     

    sb2 kdbhavsp                            @134    

    sb2 kdbhtosp                            @136    

 struct kdbt[1], 4 bytes                    @138    

    sb2 kdbtoffs                            @138    

    sb2 kdbtnrow                            @140    

 sb2 kdbr[83]                               @142    

 ub1 freespace[852]                         @308    

 ub1 rowdata[7028]                          @1160   

 ub4 tailchk                                @8188   

 

BBED> print ktbbhitl[0]

struct ktbbhitl[0], 24 bytes                @44     

   struct ktbitxid, 8 bytes                 @44     

      ub2 kxidusn                           @44       0xffff

      ub2 kxidslt                           @46       0x0000

      ub4 kxidsqn                           @48       0x00000000

   struct ktbituba, 8 bytes                 @52     

      ub4 kubadba                           @52       0x00000000

      ub2 kubaseq                           @56       0x0000

      ub1 kubarec                           @58       0x00

   ub2 ktbitflg                             @60       0x8000 (KTBFCOM)

   union _ktbitun, 2 bytes                  @62     

      sb2 _ktbitfsc                         @62       2978

      ub2 _ktbitwrp                         @62       0x0ba2

   ub4 ktbitbas                             @64       0xa53f8511

BBED> print /x offset 60

ktbbh.ktbbhitl[0].ktbitflg

--------------------------

ub2 ktbitflg                                @60       0x8000 (KTBFCOM)

BBED> set mode edit;

        MODE            Edit

       

BBED> modify /x 2000 offset 60

BBED> print /x offset 60

ktbbh.ktbbhitl[0].ktbitflg

--------------------------

ub2 ktbitflg                                @60       0x2000 (KTBFUPB)   <----0x8000修改为0x2000

BBED> sum apply

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

Check value for File 0, Block 132:

current = 0xf516, required = 0xf516

###查询不受影响

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1;

  COUNT(*)

----------

       499


我们上面针对ktbitflg的修改反应到数据块上就是把Itl0x01

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0ba2.a53f8511

改成了

0x01   0xffff.000.00000000  0x00000000.0000.00  --U-    0  fsc 0x0ba2.a53f8511

结论A1db_block_checking=FALSEdb_block_checksum=typical,用BBED修改transaction layer层的ktbitflg后,Select操作不受影响


A2 db_block_checksum=full BBED修改transaction layer层的ktbitflg后,测试Select操作是否受影响

紧接着A1的操作我们把db_block_checksum设置成full,再次测试select

alter system set db_block_checksum=full scope=memory;

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      FULL

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1;

  COUNT(*)

----------

       499

结论A2db_block_checking=FALSEdb_block_checksum=full,用BBED修改transaction layer层的ktbitflg后,Select操作不受影响

A3 db_block_checksum=typical db_block_checking=lowBBED修改transaction layer层的ktbitflg后,测试Select操作是否受影响

延用着A2的操作结果,将db_block_checksum改回typical、修改db_block_checkinglow,执行select

 SYS@tstdb1-SQL> alter system set db_block_checking=low scope=memory;

System altered.

SYS@tstdb1-SQL> alter system set db_block_checksum=typical scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      TYPICAL     

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1;

  COUNT(*)

----------

       499

结论A3db_block_checking=lowdb_block_checksum=typical,用BBED修改transaction layer层的ktbitflg后,Select操作不受影响

A4db_block_checksum=typical db_block_checking=mediumBBED修改transaction layer层的ktbitflg后,测试Select操作是否受影响

延用着A3的操作结果,将db_block_checking修改为medium,执行select

SYS@tstdb1-SQL> alter system set db_block_checking=medium scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      MEDIUM

db_block_checksum                    string      TYPICAL


结论A4db_block_checking=MEDIUMdb_block_checksum=typical,用BBED修改transaction layer层的ktbitflg后,Select操作不受影响

B1db_block_checksum=typical db_block_checking=FALSEBBED修改transaction layer层的ktbitflg后,测试update操作是否受影响

延用着A4的操作结果,将db_block_checking修改为false,执行update操作

SYS@tstdb1-SQL> alter system set db_block_checking=FALSE scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      TYPICAL

首先找到7/132块对应的某条rowidAAd94uAAHAAAACEAAA

select rfno,blkno,min(rowid) from (select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno,rowid from scott.t0811_1) t1 group by rfno,blkno;

      RFNO      BLKNO MIN(ROWID)

---------- ---------- ------------------

         7        131 AAd94uAAHAAAACDAAA

         7        134 AAd94uAAHAAAACGAAA

         7        135 AAd94uAAHAAAACHAAA

         7        132 AAd94uAAHAAAACEAAA

         7        137 AAd94uAAHAAAACJAAA

         7        136 AAd94uAAHAAAACIAAA

         7        133 AAd94uAAHAAAACFAAA

对这条rowid所在的记录做更新

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACEAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.

结论B1: db_block_checksum=typical db_block_checking=FALSEBBED修改transaction layer层的ktbitflg后,update操作不受影响

B2db_block_checksum=typical db_block_checking=lowBBED修改transaction layer层的ktbitflg后,测试update操作是否受影响

延用着B1的操作结果,将db_block_checking修改为low,执行update操作

SYS@tstdb1-SQL> alter system set db_block_checking=low scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      TYPICAL

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACEAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.

结论B2: db_block_checksum=typical db_block_checking=lowBBED修改transaction layer层的ktbitflg后,update操作不受影响


B3db_block_checksum=typical db_block_checking=mediumBBED修改transaction layer层的ktbitflg后,测试update操作是否受影响

延用B2的操作结果,把db_block_checking修改为medium

alter system set db_block_checking=medium scope=memory;   

SYS@tstdb1-SQL>  show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      MEDIUM

db_block_checksum                    string      TYPICAL

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACEAAA';

update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACEAAA'

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 132)

ORA-01110: data file 7: '/oradata06/testaaaaa/ts0811_1.dbf'

这时我们使用BBED观察block 7/132这个blockseq被标记为了0xff,表示该块被标记为logical corrupt

BBED> print block 132 offset 14

kcbh.seq_kcbh

-------------

ub1 seq_kcbh                                @14       0xff

之后针对block 7/132select操作也报错了

SYS@tstdb1-SQL> select * from scott.t0811_1 where rowid='AAd94uAAHAAAACEAAA';

select * from scott.t0811_1 where rowid='AAd94uAAHAAAACEAAA'

                    *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 132)

ORA-01110: data file 7: '/oradata06/testaaaaa/ts0811_1.dbf'

运行rman validate datafile后能在v$database_block_corruption视图里观察到此块被标记为corrupt

RMAN> validate datafile 7;

Starting validate at 20150813 15:42:14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=795 device type=DISK

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00007 name=/oradata06/testaaaaa/ts0811_1.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

7    OK     1              16247        16384           12793185207836

  File Name: /oradata06/testaaaaa/ts0811_1.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              6              

  Index      0              0              

  Other      0              131            

Finished validate at 20150813 15:42:16

SYS@tstdb1-SQL> select * from v$database_block_corruption where file#=7;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

         7        132          1                  0 CORRUPT

        

结论B3: db_block_checksum=typical db_block_checking=mediumBBED修改transaction layer层的ktbitflg后,update操作报错ORA-01578,且之后的select操作也报ORA-01578错误

/////////////////////////////////////////////////

///////////// 2db_block_checksum=typicalfullTable Directory的数据被修改

/////////////////////////////////////////////////

使用block 7/133来做这部分的测试

-----初始状态正常

alter system flush buffer_cache;

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

        1

A5 db_block_checksum=typical ,用BBED修改Table Directory数据,测试Select操作是否会受影响

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      TYPICAL


###
使用bbed修改 block 7/133 Table Directory层的kdbhavsp,该值表示block里的空闲空间

BBED> set filename '/oradata06/testaaaaa/ts0811_1.dbf'

BBED> set block 133

        BLOCK#          133

       

BBED> map /v

 File: /oradata06/testaaaaa/ts0811_1.dbf (0)

 Block: 133                                   Dba:0x00000000

------------------------------------------------------------

 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0      

    ub1 type_kcbh                           @0      

    ub1 frmt_kcbh                           @1      

    ub1 spare1_kcbh                         @2      

    ub1 spare2_kcbh                         @3      

    ub4 rdba_kcbh                           @4      

    ub4 bas_kcbh                            @8      

    ub2 wrp_kcbh                            @12     

    ub1 seq_kcbh                            @14     

    ub1 flg_kcbh                            @15     

    ub2 chkval_kcbh                         @16     

    ub2 spare3_kcbh                         @18     

 struct ktbbh, 96 bytes                     @20     

    ub1 ktbbhtyp                            @20     

    union ktbbhsid, 4 bytes                 @24     

    struct ktbbhcsc, 8 bytes                @28     

    sb2 ktbbhict                            @36     

    ub1 ktbbhflg                            @38     

    ub1 ktbbhfsl                            @39     

    ub4 ktbbhfnx                            @40     

    struct ktbbhitl[3], 72 bytes            @44     

 struct kdbh, 14 bytes                      @124    

    ub1 kdbhflag                            @124    

    sb1 kdbhntab                            @125    

    sb2 kdbhnrow                            @126    

    sb2 kdbhfrre                            @128    

    sb2 kdbhfsbo                            @130    

    sb2 kdbhfseo                            @132    

    sb2 kdbhavsp                            @134    

    sb2 kdbhtosp                            @136    

 struct kdbt[1], 4 bytes                    @138    

    sb2 kdbtoffs                            @138    

    sb2 kdbtnrow                            @140    

 sb2 kdbr[80]                               @142    

 ub1 freespace[656]                         @302    

 ub1 rowdata[7230]                          @958    

 ub4 tailchk                                @8188    

 

BBED> print kdbhavsp

struct ktbbhitl[0], 24 bytes                @44     

   struct ktbitxid, 8 bytes                 @44     

      ub2 kxidusn                           @44       0xffff

      ub2 kxidslt                           @46       0x0000

      ub4 kxidsqn                           @48       0x00000000

   struct ktbituba, 8 bytes                 @52     

      ub4 kubadba                           @52       0x00000000

      ub2 kubaseq                           @56       0x0000

      ub1 kubarec                           @58       0x00

   ub2 ktbitflg                             @60       0x8000 (KTBFCOM)

   union _ktbitun, 2 bytes                  @62     

      sb2 _ktbitfsc                         @62       2978

      ub2 _ktbitwrp                         @62       0x0ba2

   ub4 ktbitbas                             @64       0xa53f8511

BBED> print /x kdbhavsp

sb2 kdbhavsp                                @134      0x344

BBED> set mode edit;

        MODE            Edit

       

BBED> modify /x 999 offset 134

BBED> print /x kdbhavsp

sb2 kdbhavsp                                @134      0x999  <---avsp0x344改为了0x999

BBED> sum apply

Check value for File 0, Block 133:

current = 0xb5c2, required = 0xb5c2


###
查询不受影响

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1

结论A5db_block_checking=FALSEdb_block_checksum=typical,用BBED修改Table directory层的kdbhavsp后,Select操作不受影响


A6 db_block_checksum=fullBBED修改Table directory层的kdbhavsp后,测试Select操作是否受影响

紧接着A5的操作我们把db_block_checksum设置成full,再次测试select

SYS@tstdb1-SQL> alter system set db_block_checksum=full scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      FULL


###
查询不受影响

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1

结论A6db_block_checking=FALSEdb_block_checksum=FULL,用BBED修改Table directory层的kdbhavsp后,Select操作不受影响

        

A7 db_block_checksum=typicaldb_block_checking=lowBBED修改Table directory层的kdbhavsp后,测试Select操作是否受影响

紧接着A6的操作我们把db_block_checksum设置成typicaldb_block_checking设置成low,再次测试select

SYS@tstdb1-SQL> alter system set db_block_checksum=typical scope=memory;

System altered.

SYS@tstdb1-SQL> alter system set db_block_checking=low scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      TYPICAL


###
查询不受影响

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1


结论A7db_block_checking=lowdb_block_checksum=typical,用BBED修改Table directory层的kdbhavsp后,Select操作不受影响

A8 db_block_checksum=typical db_block_checking=mediumBBED修改Table directory层的kdbhavsp后,测试Select操作是否受影响】

延用着A7的操作结果,将db_block_checking修改为medium,执行select

SYS@tstdb1-SQL> alter system set db_block_checking=medium scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      MEDIUM

db_block_checksum                    string      TYPICAL


结论A8db_block_checking=MEDIUMdb_block_checksum=typical,用BBED修改Table directory层的kdbhavsp后,Select操作不受影响

B5db_block_checksum=typical BBED修改Table directory层的kdbhavsp后,测试update操作是否受影响

SYS@tstdb1-SQL> alter system set db_block_checking=FALSE scope=memory;

System altered.

SYS@tstdb1-SQL> alter system set db_block_checksum=typical scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      TYPICAL

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.


结论B5db_block_checking=falsedb_block_checksum=typical,用BBED修改Table directory层的kdbhavsp后,update操作不受影响

B6db_block_checksum=FULL BBED修改Table directory层的kdbhavsp后,测试update操作是否受影响

SYS@tstdb1-SQL> alter system set db_block_checksum=FULL scope=memory;

System altered.

SYS@tstdb1-SQL>

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      FULL

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.


结论B6db_block_checking=falsedb_block_checksum=full,用BBED修改Table directory层的kdbhavsp后,update操作不受影响

B7db_block_checksum=typical db_block_checking=lowBBED修改Table directory层的kdbhavsp后,测试update操作是否受影响

延用着B1的操作结果,将db_block_checking修改为low,执行update操作

SYS@tstdb1-SQL> alter system set db_block_checking=low scope=memory;

System altered.

SYS@tstdb1-SQL> alter system set db_block_checksum=typical scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      TYPICAL

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACFAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.

结论B7: db_block_checksum=typical db_block_checking=lowBBED修改Table directorykdbhavsp后,update操作不受影响


B8db_block_checksum=typical db_block_checking=mediumBBED修改Table directory层的kdbhavsp后,测试update操作是否受影响

SYS@tstdb1-SQL> alter system set db_block_checksum=typical scope=memory;

System altered.

SYS@tstdb1-SQL> alter system set db_block_checking=medium scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      MEDIUM

db_block_checksum                    string      TYPICAL

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACFAAA';

update scott.t0811_1 set owner='TEST111' where rowid='AAd94uAAHAAAACFAAA'

             *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 133)

ORA-01110: data file 7: '/oradata06/testaaaaa/ts0811_1.dbf'

这时我们使用BBED观察block 7/133这个blockseq被标记为了0xff,表示该块被标记为logical corrupt

BBED> print block 133 offset 14

kcbh.seq_kcbh

-------------

ub1 seq_kcbh                                @14       0xff


之后针对block 7/133select操作也报错了

SYS@tstdb1-SQL> select * from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

select * from scott.t0811_1 where rowid='AAd94uAAHAAAACEAAA'

                    *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 133)

ORA-01110: data file 7: '/oradata06/testaaaaa/ts0811_1.dbf'


运行rman validate datafile后能在v$database_block_corruption视图里观察到此块被标记为corrupt

RMAN> validate datafile 7;

Starting validate at 20150813 16:51:19

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00007 name=/oradata06/testaaaaa/ts0811_1.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:03

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

7    FAILED 1              16247        16384           12793185012644

  File Name: /oradata06/testaaaaa/ts0811_1.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       1              6              

  Index      0              0              

  Other      0              131            

validate found one or more corrupt blocks

See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_38928648.trc for details

Finished validate at 20150813 16:51:22

SYS@tstdb1-SQL> select * from v$database_block_corruption where file#=7;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

         7        132          1                  0 CORRUPT

         7        133          1                  0 CORRUPT

        

结论B8: db_block_checksum=typical db_block_checking=mediumBBED修改Table directory层的kdbhavsp后,update操作报错ORA-01578,且之后的select操作也报ORA-01578错误


/////////////////////////////////////////////////

///////////// 3db_block_checksum=OFFCache Layer的数据被修改

/////////////////////////////////////////////////

A9db_block_checksum=OFFdb_block_checking=FALSE,修改Cache Layerchkval_kcbh,测试select是否受影响

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      OFF

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf1f

BBED> modify /x bf1e offset 16

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf1e

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1

结论A9chkval_kcbh就是根据整块内容生成的校验值,因为db_block_checksum=OFF,表示关闭了校验值检验,所以select操作不受影响

A10db_block_checksum=OFFdb_block_checking=FALSE,修改Cache Layerbas_kcbh,测试select是否受影响

紧接着A9的测试,执行revert回退A9的修改操作

BBED> revert

All changes made in this session will be rolled back. Proceed? (Y/N) Y

Reverted file '/oradata06/testaaaaa/ts0811_1.dbf', block 133

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      OFF

BBED> print bas_kcbh

ub4 bas_kcbh                                @8        0xa542861c

BBED> modify /x 96 offset 10

BBED> print bas_kcbh

ub4 bas_kcbh                                @8        0xa542961c

BBED> print tailchk

ub4 tailchk                                 @8188     0x861c0601

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA'

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 133)

ORA-01110: data file 7: '/oradata06/testaaaaa/ts0811_1.dbf'


alert.log里也会看到一段fractured block的报错:

Fractured block found during multiblock buffer read

Data in bad block:

 type: 6 format: 2 rdba: 0x01c00085

 last change scn: 0x0ba2.a542961c seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x861c0601

 check value in block header: 0xbf1f

 computed block checksum: 0x1000

Reading datafile '/oradata06/testaaaaa/ts0811_1.dbf' for corruption at rdba: 0x01c00085 (file 7, block 133)


结论A10db_block_checksum=OFF表示读块额时候不计算校验值,即不会与chkval值进行比较;但block首尾是否一致的检查还是雷打不动必须得要检查的:比较tailchk的值与scn base部分(4个数字)+type(2个数字)+seq(2个数字)的组合结果是否一致,之前我们修改的bas_kcbh对应就是scn base的末四位,从0xa542861c=>0xa542961c,而tailchk仍为0x861c0601,造成了Fractured block,如果不想出现Fractured block要同时将tailchk值改为0x961c0601,所以Block是否Fractured的检验是必然要发生的,与db_block_checksum值无关

A11db_block_checksum=OFFdb_block_checking=low、修改cache layer层的chkval_kcbh,测试select是否会受影响

紧接着A10的结果,执行revert回退之前的修改

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      OFF

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf1f

BBED> modify /x bf11 offset 16

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf11

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1


结论A11db_block_checksum=OFF db_block_checking=low的情况下select数据块时不会计算校验值与chkval进行比较,select操作不受影响


A12db_block_checksum=OFFdb_block_checking=medium、修改cache layer层的chkval_kcbh,测试select是否会受影响

紧接着A11的结果,执行revert回退之前的修改

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      MEDIUM

db_block_checksum                    string      OFF

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf1f

BBED> modify /x bf11 offset 16

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf11

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1

结论A12db_block_checksum=OFF db_block_checking=medium的情况下select数据块时不会计算校验值与chkval进行比较,select操作不受影响


B9db_block_checksum=OFFdb_block_checking=FALSE,修改Cache Layerchkval_kcbh,测试update是否受影响

紧接着A12的结果,执行revert回退之前的修改

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      OFF

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf1f

BBED> modify /x bf1e chkval_kcbh

BBED> sum apply

Check value for File 0, Block 133:

current = 0xbf1f, required = 0xbf1f

SYS@tstdb1-SQL>  alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.

结论B9db_block_checksum=OFF db_block_checking=FALSE的情况下,修改Cache Layerchkval_kcbhupdate数据块时不受影响。


B10db_block_checksum=OFFdb_block_checking=low,修改Cache Layerchkval_kcbh,测试update是否受影响

紧接着A12的结果,执行revert回退之前的修改

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      OFF

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf1f

BBED> modify /x bf11 chkval_kcbh

BBED> print chkval_kcbh

ub2 chkval_kcbh                             @16       0xbf11

SYS@tstdb1-SQL>  alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.


结论B10db_block_checksum=OFF db_block_checking=LOW的情况下,修改Cache Layerchkval_kcbhupdate数据块时不受影响。

B11db_block_checksum=OFFdb_block_checking=FALSE,修改Cache Layerbas_kcbh前四位(因为修改后四位会造成Fractured block),测试update是否受影响

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      OFF

BBED> print bas_kcbh

ub4 bas_kcbh                                @8        0xa542861c

BBED> modify /x a543 bas_kcbh

BBED> print bas_kcbh

ub4 bas_kcbh                                @8        0xa543861c

BBED> sum apply

Check value for File 0, Block 133:

current = 0xbf1e, required = 0xbf1e

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

1 row updated.

SYS@tstdb1-SQL> rollback;

Rollback complete.


结论B11db_block_checksum=OFFdb_block_checking=FALSE,修改Cache Layerbas_kcbh前四位后,update操作不受影响

B12db_block_checksum=OFFdb_block_checking=low、修改cache layer层的bas_kcbh前四位,测试update是否会受影响

SYS@tstdb1-SQL>  show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      LOW

db_block_checksum                    string      OFF

BBED> print bas_kcbh

ub4 bas_kcbh                                @8        0xa542861c

BBED> modify /x a654 bas_kcbh

BBED> print bas_kcbh

ub4 bas_kcbh                                @8        0xa654861c

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA'

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [2663], [2978], [2773155088],

[2978], [2790557212], [], [], [], [], [], [], []

      

结论B12db_block_checksum=OFFdb_block_checking=low,修改Cache Layerbas_kcbh前四位后,update操作报ORA-00600错误,从这个例子也可以看出db_block_checking=low的设置会引导Oraclecache layer的部分数据作完整性做检查

/////////////////////////////////////////////////

 4db_block_checksum=OFFdb_block_checking=FALSE时,除了进行Fractured block外,oracle还会对data block里的关键结构进行检测

/////////////////////////////////////////////////

***当前参数设置:db_block_checksum=OFFdb_block_checking=FALSE

SYS@tstdb1-SQL> show parameter db_block_check

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checking                    string      FALSE

db_block_checksum                    string      OFF


举个例子:

(1)、修改cache layerwrp_kcbh,也就是块头scn:0x0ba2.a542861c里的前半部分0x0ba2修改成0x0ba3

BBED> print wrp_kcbh

ub2 wrp_kcbh                                @12       0x0ba2

BBED> modify /x ba3 wrp_kcbh

BBED> print wrp_kcbh

ub2 wrp_kcbh                                @12       0x0ba3

BBED> sum apply

Check value for File 0, Block 133:

current = 0xbf1e, required = 0xbf1e

SYS@tstdb1-SQL> alter system flush buffer_cache;

System altered.


***select
没问题

SYS@tstdb1-SQL> select count(*) from scott.t0811_1 where rowid='AAd94uAAHAAAACFAAA';

  COUNT(*)

----------

         1


***update
出现ORA-00600错误

SYS@tstdb1-SQL> update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA';

update scott.t0811_1 set owner='TEST133' where rowid='AAd94uAAHAAAACFAAA'

             *

ERROR at line 1:

ORA-00600: internal error code, arguments: [2663], [2978], [2773149260],

[2979], [2772600348], [], [], [], [], [], [], []


cash layer
里的wrp_kcbh不是拼接tailchk的组成部分,DML发起时oracle会将block头部的SCN与当前SCN作比较,这些内部的检测机制会对block里的关键结构进行检测,这些检测同样不受制于db_block_checksumdb_block_checking两个参数


总结:

1db_block_checksum不为OFF时表示开启了数据块的校验,开启校验意味着将内存里的数据块写入到磁盘时会计算校验值存放到chkval里,这个校验值是以当前数据块里的所有bit作为输入经过一定的算法得到的一个结果。之后如果块里的任何一个bit发生了变化都会触发再次计算出的一个新的校验值。在读取数据块时也会重新计算校验值并与前一次写入时产生的chkval值进行比较,如果存在不一致就会出现ORA-01578错误,在v$database_block_corruption.corruption_type里会把这种错误类型标记为checksum。如果dump出来的blockchkval值不为0则表示数据块上一次写入时校验处于开启的状态,若为0则表示上一次写入时校验处于关闭状态。db_block_checksum是检测坏块的首道防线,因为其完成的是物理层的检测(不检测数据块存放的内容在逻辑上是否完整),能有效检测出I/O层面的故障所引起的corrupted block,如果物理检测不通过,即便数据块里的内容在逻辑上是完整的,这个块也不能被使用。


2
、另一种物理层面的坏块检测是Fractured block检测,也叫做块分裂的检测,不管db_block_checksumdb_block_checking参数如何设置,Fractured block检测始终保持开启状态。在每个数据块的底部有一个名为tailchk的结构,它的值由数据块头部的scn base部分(4个数字)+type(2个数字)+seq(2个数字)组合而成,Fractured block检测主要是为了检测数据块头部被更新,但是尾部没有被更新,即数据块头尾不一致的问题,比如I/O操作进行到一半时服务器断电就很容易引起Fractured block。同样不受制于db_block_checksumdb_block_checking的还有对Data block内部的关键结构的检测,比如Cache layer层的wrp_kcbh,这些检测出问题时往往会伴随以ORA-00600错误以及一堆trace文件,需要上传oracle support进一步分析


3
db_block_checking设置为medium或者full时会对数据块内容在逻辑上是否完整进行校验,比如table directory层的nrow=30表示块里总共存放了30条记录,那么在row directoryrow data层也要有30行的数据与之对应;比如Avsp表示块里的可用空间,Avsp的值不能比db_block_size还要大;再比如Itl事务表里如果某一个事务的lck=1,表示该事务已经锁定了这个块,那么在row data部分也必须至少有一行lockbytes值不为零,表示该行正在被某个事务锁定。当block物理层的检测通过后,如果要检测因应用程序或者oraclebug引起数据块内容在逻辑上出现的不完整的问题,就需要设置db_block_checkingmedium或者full,逻辑错误如果不加以及时发现并修正可能会使得错误潜伏与蔓延,最终的结果很可能触发ORA-00600错误


4
db_block_checksumdb_block_checkingcorrupted block的检测效果总结如下:

 

Block Error type

db_block_checksum

db_block_checking

OFF

Typical

FULL

FALSE

low

medium

high

block checksum error

不能检测

能检测

能检测

取决于db_block_checksum

逻辑错误 in
Cache Layer

通过Fractured block检测以及Oracle内部的其它检测机制实现
(
db_block_checksumdb_block_checking设置值无关),后者检测出的会报ORA-00600错误;当db_block_checking=low时也会进行部分结构的完整性检测

逻辑错误 in
Transaction layer
Table Directory layer
Row Directory layer
Row data layer

不能检测

不能检测

不能检测

不能检测

不能检测

能检测

能检测

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1774868/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1644663