Oracle Block Checksum

引入一段文档对于checksum的说明

A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every data file or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency.

数据块中的checksum值储存在每个块的第16个字节,可以通过bbed查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BBED> set dba 5,131
DBA 0x01400083 (20971651 5,131)

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01400083
ub4 bas_kcbh @8 0x0013f022
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x2dd5
ub2 spare3_kcbh @18 0x0000

flg_kcbh:

  • KCBHFNEW BIT 0×01 // new block – zeroed data area
  • KCBHFDLC BIT 0×02 // Delayed Logging Change advanced SCN/seq
  • KCBHFCKV BIT 0×04 // ChecK Value saved – block xor’s to zero
  • KCBHFTMP BIT 0×08 // Temporary block.
  • 0×06=0×02+0×04 KCBHFDLC+KCBHFCKV

而这个值就是用来控制是否对block进行checksum验证,可以通过DB_BLOCK_CHECKSUM参数来配置,如果设为typical和full时,读取块的时候就会重新计算checksum值,并与写出的时候的值做比对,设置其他值的时候就不会校验。

db_block_checksum设置为typical

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
SYS@xb> show parameter DB_BLOCK_CHECKSUM

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TYPICAL

SYS@xb> create table t_check(id number,name varchar2(30)) tablespace tbsxb;

表已创建。

insert into t_check values(1,'www.xbdba.com');


已创建 1 行。

SYS@xb> SYS@xb> commit;

提交完成。

select dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from t_check;

OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
87883 5 244 0

dump这个块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SYS@xb> alter system dump datafile 5 block 244;

系统已更改。

SYS@xb> oradebug setmypid;
已处理的语句
SYS@xb> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/xb/xb/trace/xb_ora_28067.trc


Block dump from disk:
buffer tsn: 9 rdba: 0x014000f4 (5/244)
scn: 0x0000.00155de9 seq: 0x02 flg: 0x04 tail: 0x5de90602
frmt: 0x02 chkval: 0xbfe6 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1


这里可以看到当设置为typical时,block查看到flg为0x04,checksum值为0xbfe6

用BBED查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BBED> set dba 5,244
DBA 0x014000f4 (20971764 5,244)

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x014000f4
ub4 bas_kcbh @8 0x00155de9
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xbfe6
ub2 spare3_kcbh @18 0x0000

比对可以看到flg_kcbh就是block dump里的flg,chkval_kcbh就是chkval

如果我们直接在bbed里做一些不同的操作,checksum值是否需要重新计算?

1
2
3
4
5
6
7
8
9
10
11
12
13
BBED> d /v offset 8150
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 8150 to 8191 Dba:0x014000f4
-------------------------------------------------------
8d45e048 89c241ff 95890600 004d89e3 l .E.H..A......M..
41ff9509 0a000048 89c64989 f64983e6 l A......H..I..I..
f8e929ff ffff0206 e95d l ..)......]

>16 bytes per line>

BBED> sum
Check value for File 5, Block 244:
current = 0xbfe6, required = 0xbfe6

可以看到如果只涉及dump操作,不涉及修改,重新计算的sum值与current值一样,所以并不影响。

进行修改操作

1
2
3
4
5
6
7
8
9
10
11
12
13
BBED> m /x 9d offset 8150
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 8150 to 8191 Dba:0x014000f4
------------------------------------------------------------------------
9d45e048 89c241ff 95890600 004d89e3 41ff9509 0a000048 89c64989 f64983e6
f8e929ff ffff0206 e95d

>32 bytes per line>

BBED> sum
Check value for File 5, Block 244:
current = 0xbfe6, required = 0xbff6

修改了第一个offset以后,重新计算的值就变成了0xbff6

将修改的内容保存以后,可以看到chkval_kcbh也变成了重新计算的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BBED> sum apply
Check value for File 5, Block 244:
current = 0xbff6, required = 0xbff6

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x014000f4
ub4 bas_kcbh @8 0x00155de9
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xbff6
ub2 spare3_kcbh @18 0x0000

将新的block进行dump

1
2
3
4
5
Block dump from disk:
buffer tsn: 9 rdba: 0x014000f4 (5/244)
scn: 0x0000.00155de9 seq: 0x02 flg: 0x04 tail: 0x5de90602
frmt: 0x02 chkval: 0xbff6 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

可以看到dump信息里的chkval依然与bbed的chkval_kcbh一致,flg信息同样一致

根据文档所说的,flg标志位代表是否要计算sum,那如果手动修改bbed的flg值,修改数据还会重新计算sum吗

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
BBED> d /v offset 15
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 15 to 526 Dba:0x014000f4
-------------------------------------------------------
04f6bf00 0001000f 00495701 00e95d15 l .........IW...].
000000e8 1f021f32 00f00040 01000000 l .......2...@....

BBED> m /x 02 offset 15
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 15 to 526 Dba:0x014000f4
------------------------------------------------------------------------
02f6bf00 0001000f 00495701 00e95d15 000000e8 1f021f32 00f00040 01000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x014000f4
ub4 bas_kcbh @8 0x00155de9
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x02 (KCBHFDLC)
ub2 chkval_kcbh @16 0xbff6
ub2 spare3_kcbh @18 0x0000

BBED> sum
Check value for File 5, Block 244:
current = 0xbff6, required = 0xbff6

BBED> d /v offset 8150
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 8150 to 8191 Dba:0x014000f4
-------------------------------------------------------
9d45e048 89c241ff 95890600 004d89e3 l .E.H..A......M..
41ff9509 0a000048 89c64989 f64983e6 l A......H..I..I..
f8e929ff ffff0206 e95d l ..)......]

>16 bytes per line>

BBED> m /x 8d offset 8150
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 8150 to 8191 Dba:0x014000f4
------------------------------------------------------------------------
8d45e048 89c241ff 95890600 004d89e3 41ff9509 0a000048 89c64989 f64983e6
f8e929ff ffff0206 e95d

>32 bytes per line>

BBED> sum
Check value for File 5, Block 244:
current = 0xbff6, required = 0xbff6

可以看到当手动把flg_kcbh修改成02 也就是KCBHFDLC以后,对数据块进行任何修改都不会触发checksum的重新计算。那是否可以通过参数的方式来控制这个值呢,那就是db_block_checksum

修改db_block_checksum为false

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
BBED> m /x 04 offset 15
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 244 Offsets: 15 to 526 Dba:0x014000f4
------------------------------------------------------------------------
04f6bf00 0001000f 00495701 00e95d15 000000e8 1f021f32 00f00040 01000000

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x014000f4
ub4 bas_kcbh @8 0x00155de9
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xbff6
ub2 spare3_kcbh @18 0x0000

SYS@xb> alter system set db_block_checksum=false;

系统已更改。

SYS@xb> drop table t_check purge;

表已删除。

SYS@xb> create table t_check(id number,name varchar2(30)) tablespace tbsxb;

表已创建。

SYS@xb> insert into t_check values(1,'www.xbdba.com');

已创建 1 行。

SYS@xb> commit;

提交完成。

select dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
5 from t_check;

OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
87884 5 243 0

SYS@xb> alter system checkpoint;

系统已更改。

block dump

1
2
3
4
5
6
7
8
9
10
11
12
Block dump from disk:
buffer tsn: 9 rdba: 0x014000f3 (5/243)
scn: 0x0000.001588bc seq: 0x01 flg: 0x02 tail: 0x88bc0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

block_row_dump:
tab 0, row 0, @0x1f84
tl: 20 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [13] 77 77 77 2e 78 62 64 62 61 2e 63 6f 6d
end_of_block_dump

看这里显示flg为0x02 chkval为0x0000 即为空,所以认定并为对block写入进行checksum操作

bbed的结果值也是一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BBED> set dba 5,243
DBA 0x014000f3 (20971763 5,243)

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x014000f3
ub4 bas_kcbh @8 0x001588bc
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x02 (KCBHFDLC)
ub2 chkval_kcbh @16 0x0000
ub2 spare3_kcbh @18 0x0000

在这种条件下对block进行bbed修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
BBED> d /v offset 8150
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 243 Offsets: 8150 to 8191 Dba:0x014000f3
-------------------------------------------------------
01020000 00000041 114d0000 0041114d l .......A.M...A.M
00002c01 0202c102 0d777777 2e786264 l ..,......www.xbd
62612e63 6f6d0106 bc88 l ba.com....

>16 bytes per line>

BBED> m /x 02 offset 8150
File: /u01/app/oracle/oradata/xb/tbsxb01.dbf (5)
Block: 243 Offsets: 8150 to 8191 Dba:0x014000f3
------------------------------------------------------------------------
02020000 00000041 114d0000 0041114d 00002c01 0202c102 0d777777 2e786264
62612e63 6f6d0106 bc88

>32 bytes per line>

BBED> sum
Check value for File 5, Block 243:
current = 0x0000, required = 0x0000

即使修改了数据块,current和required值依然都是0,所以可以认为如果db_check_sum值置为false以后,对块的写入操作并不会计算和修改checksum