DB_BLOCK_CHECKSUM VS DB_BLOCK_CHECKING

最新推荐文章于 2024-07-16 10:28:31 发布

cuiqiong6888

最新推荐文章于 2024-07-16 10:28:31 发布

阅读量118

点赞数

文章标签：数据库

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

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

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

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

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

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

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

0x12:pri[0] offs=0x1f66

0x14:pri[1] offs=0x1818

0x16:pri[2] offs=0x184a

0x18:pri[3] offs=0x185e

。。。省略部分输出

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

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

本文主要通过数个不同场景体验一下db_block_checksum和db_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

/ 1、db_block_checksum=typical或full，Transaction 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的修改反应到数据块上就是把Itl：0x01

从

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

改成了

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

结论A1：db_block_checking=FALSE，db_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

结论A2：db_block_checking=FALSE，db_block_checksum=full，用BBED修改transaction layer层的ktbitflg后，Select操作不受影响

【 A3 、db_block_checksum=typical ，db_block_checking=low，BBED修改transaction layer层的ktbitflg后，测试Select操作是否受影响 】

延用着A2的操作结果，将db_block_checksum改回typical、修改db_block_checking为low，执行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

结论A3：db_block_checking=low，db_block_checksum=typical，用BBED修改transaction layer层的ktbitflg后，Select操作不受影响

【 A4、db_block_checksum=typical ，db_block_checking=medium，BBED修改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

结论A4：db_block_checking=MEDIUM，db_block_checksum=typical，用BBED修改transaction layer层的ktbitflg后，Select操作不受影响

【 B1、db_block_checksum=typical ，db_block_checking=FALSE，BBED修改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块对应的某条rowid是AAd94uAAHAAAACEAAA

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=FALSE，BBED修改transaction layer层的ktbitflg后，update操作不受影响

【 B2、db_block_checksum=typical ，db_block_checking=low，BBED修改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=low，BBED修改transaction layer层的ktbitflg后，update操作不受影响

【 B3、db_block_checksum=typical ，db_block_checking=medium，BBED修改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这个block的seq被标记为了0xff，表示该块被标记为logical corrupt

BBED> print block 132 offset 14

kcbh.seq_kcbh

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

ub1 seq_kcbh @14 0xff

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

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=medium，BBED修改transaction layer层的ktbitflg后，update操作报错ORA-01578，且之后的select操作也报ORA-01578错误

/ 2、db_block_checksum=typical或full，Table Directory的数据被修改

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

-----初始状态正常

alter system flush buffer_cache;

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

COUNT(*)

----------

【 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 <---avsp从0x344改为了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(*)

----------

结论A5：db_block_checking=FALSE，db_block_checksum=typical，用BBED修改Table directory层的kdbhavsp后，Select操作不受影响

【 A6 、db_block_checksum=full，BBED修改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(*)

----------

结论A6：db_block_checking=FALSE，db_block_checksum=FULL，用BBED修改Table directory层的kdbhavsp后，Select操作不受影响

【 A7 、db_block_checksum=typical，db_block_checking=low，BBED修改Table directory层的kdbhavsp后，测试Select操作是否受影响 】

紧接着A6的操作我们把db_block_checksum设置成typical，db_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(*)

----------

结论A7：db_block_checking=low，db_block_checksum=typical，用BBED修改Table directory层的kdbhavsp后，Select操作不受影响

【 A8 、db_block_checksum=typical ，db_block_checking=medium，BBED修改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

结论A8：db_block_checking=MEDIUM，db_block_checksum=typical，用BBED修改Table directory层的kdbhavsp后，Select操作不受影响

【 B5、db_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.

结论B5：db_block_checking=false，db_block_checksum=typical，用BBED修改Table directory层的kdbhavsp后，update操作不受影响

【 B6、db_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.

结论B6：db_block_checking=false，db_block_checksum=full，用BBED修改Table directory层的kdbhavsp后，update操作不受影响

【 B7、db_block_checksum=typical ，db_block_checking=low，BBED修改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=low，BBED修改Table directory的kdbhavsp后，update操作不受影响

【 B8、db_block_checksum=typical ，db_block_checking=medium，BBED修改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这个block的seq被标记为了0xff，表示该块被标记为logical corrupt

BBED> print block 133 offset 14

kcbh.seq_kcbh

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

ub1 seq_kcbh @14 0xff

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

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=medium，BBED修改Table directory层的kdbhavsp后，update操作报错ORA-01578，且之后的select操作也报ORA-01578错误

/ 3、db_block_checksum=OFF，Cache Layer的数据被修改

【A9、db_block_checksum=OFF、db_block_checking=FALSE，修改Cache Layer的chkval_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(*)

----------

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

【A10、db_block_checksum=OFF、db_block_checking=FALSE，修改Cache Layer的bas_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)

结论A10：db_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值无关

【A11、db_block_checksum=OFF、db_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(*)

----------

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

【A12、db_block_checksum=OFF、db_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(*)

----------

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

【B9、db_block_checksum=OFF、db_block_checking=FALSE，修改Cache Layer的chkval_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.

结论B9：db_block_checksum=OFF、 db_block_checking=FALSE的情况下，修改Cache Layer的chkval_kcbh，update数据块时不受影响。

【B10、db_block_checksum=OFF、db_block_checking=low，修改Cache Layer的chkval_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.

结论B10：db_block_checksum=OFF、 db_block_checking=LOW的情况下，修改Cache Layer的chkval_kcbh，update数据块时不受影响。

【B11、db_block_checksum=OFF、db_block_checking=FALSE，修改Cache Layer的bas_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.

结论B11：db_block_checksum=OFF、db_block_checking=FALSE，修改Cache Layer的bas_kcbh前四位后，update操作不受影响

【B12、db_block_checksum=OFF、db_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], [], [], [], [], [], [], []

结论B12：db_block_checksum=OFF、db_block_checking=low，修改Cache Layer的bas_kcbh前四位后，update操作报ORA-00600错误，从这个例子也可以看出db_block_checking=low的设置会引导Oracle对cache layer的部分数据作完整性做检查

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

***当前参数设置：db_block_checksum=OFF，db_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 layer的wrp_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(*)

----------

***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_checksum、db_block_checking两个参数

总结：

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

2、另一种物理层面的坏块检测是Fractured block检测，也叫做块分裂的检测，不管db_block_checksum、db_block_checking参数如何设置，Fractured block检测始终保持开启状态。在每个数据块的底部有一个名为tailchk的结构，它的值由数据块头部的scn base部分(末4个数字)+type(2个数字)+seq(2个数字)组合而成，Fractured block检测主要是为了检测数据块头部被更新，但是尾部没有被更新，即数据块头尾不一致的问题，比如I/O操作进行到一半时服务器断电就很容易引起Fractured block。同样不受制于db_block_checksum、db_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 directory和row data层也要有30行的数据与之对应；比如Avsp表示块里的可用空间，Avsp的值不能比db_block_size还要大；再比如Itl事务表里如果某一个事务的lck=1，表示该事务已经锁定了这个块，那么在row data部分也必须至少有一行lockbytes值不为零，表示该行正在被某个事务锁定。当block物理层的检测通过后，如果要检测因应用程序或者oracle的bug引起数据块内容在逻辑上出现的不完整的问题，就需要设置db_block_checking为medium或者full，逻辑错误如果不加以及时发现并修正可能会使得错误潜伏与蔓延，最终的结果很可能触发ORA-00600错误

4、db_block_checksum、db_block_checking对corrupted 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_checksum、db_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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1774868/

cuiqiong6888

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DB_BLOCK_CHECKSUM VS DB_BLOCK_CHECKING

db_block_checksum：数据块被写入磁盘时是否计算校验值放到块头部，这个校验值放在名为chkval_kcbh的变量里，对应数据块里的chkval db_block_checking: 数据块发生更改后对...
复制链接

扫一扫