db_block_checksum 用于DBWn和direct loader数据块写入到磁盘时，基于块内的所有字节计算得出一个校验值并将其写入块头。在该参数设置为typical和full时，当读入时候重新计算校验和写出时候的校验对比，如果不同则认为是块损坏。如果设置为FULL模式,则基于update/delete应用程序语句级别的改变发生后，校验值会被重新计算并写入。同时对于日志块，在写入之前，同样会生产校验值并写入到块头。该参数主要是防止IO硬件和IO子系统的错误。
这里提示我们是在系统参数级别使用db_block_checksum来控制block是否进行验证，那在block本身级别，是否有类似的值来控制，实现对block值的checksum？通过dump结合bbed给出相关答案
db_block_checksum为TYPICAL测试
CDB_CDB$ROOT@SYS> show parameter db_block_checksum;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TYPICAL
CDB_CDB$ROOT@SYS> conn / as sysdba
已连接。
CDB_CDB$ROOT@SYS> alter session set container=pdb;
会话已更改。
CDB_CDB$ROOT@SYS> alter database open;
数据库已更改。
CDB_CDB$ROOT@SYS> alter session set current_schema=chf;
会话已更改。
CDB_CDB$ROOT@SYS> create table t_xifenfei(id number,name varchar2(100));
表已创建。
CDB_CDB$ROOT@SYS> insert into t_xifenfei values(1,'www.xifenfei.com');
已创建 1 行。
CDB_CDB$ROOT@SYS> alter system checkpoint;
系统已更改。
CDB_CDB$ROOT@SYS> select dbms_rowid.rowid_relative_fno(rowid) file_no,
2 dbms_rowid.rowid_block_number(rowid) block_no from t_xifenfei;
FILE_NO BLOCK_NO
---------- ----------
9 19229
CDB_CDB$ROOT@SYS> SELECT NAME FROM V$DATAFILE WHERE FILE#=9;
NAME
---------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF
dump 该block
buffer tsn: 3 rdba: 0x02404b1d (9/19229) scn: 0xb8c.3c232935 seq: 0x01 flg: 0x06 tail: 0x29350601 frmt: 0x02 chkval: 0xe08b type: 0x06=trans data Hex dump of block: st=0, typ_found=1 tab 0, row 0, @0x1f81 tl: 23 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d end_of_block_dump End dump data blocks tsn: 3 file#: 9 minblk 19229 maxblk 19229
这里可以看到因为db_block_checksum=TYPICAL，因此插入记录的时候，dump block发现flg: 0x06 和 chkval: 0xe08b
使用bbed查看相关记录
补充:在win系统中,bbed查看block和block num相差1
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 0x02404b1d ub4 bas_kcbh @8 0x3c232935 ub2 wrp_kcbh @12 0x0b8c ub1 seq_kcbh @14 0x01 ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV) ub2 chkval_kcbh @16 0xe08b ub2 spare3_kcbh @18 0x0000
通过对比bbed和dump出来数据，可以得出flg: 0x06(flg_kcbh),chkval: 0xe08b(chkval_kcbh)
使用bbed修改block验证chkval_kcbh
提问：在使用bbed修改block的时候，一般都需要使用sum apply 处理下，为什么呢？
BBED> d /v File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19230 Offsets: 8165 to 8191 Dba:0x00000000 ------------------------------------------------------- 2c010202 c1021077 77772e78 6966656e l ,...?.www.xifen 6665692e 636f6d01 063529 l fei.com..5) <16 bytes per line> --没有修改任何值，sum的current和required值相同，而且和chkval_kcbh也相同 BBED> sum Check value for File 0, Block 19230: current = 0xe08b, required = 0xe08b --尝试修改值 BBED> m /x 78 offset 8173 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19230 Offsets: 8173 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 78772e78 6966656e 6665692e 636f6d01 063529 <32 bytes per line> BBED> d /v offset 8165 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19230 Offsets: 8165 to 8191 Dba:0x00000000 ------------------------------------------------------- 2c010202 c1021077 78772e78 6966656e l ,...?.wxw.xifen 6665692e 636f6d01 063529 l fei.com..5) <16 bytes per line> --发现current<>required BBED> sum Check value for File 0, Block 19230: current = 0xe08b, required = 0xef8b --apply把current修改为required值 BBED> sum apply Check value for File 0, Block 19230: current = 0xef8b, required = 0xef8b --发现chkval_kcbh也修改为了required值 BBED> p kcbh.chkval_kcbh ub2 chkval_kcbh @16 0xef8b
通过这里可以发现，在flg_kcbh含(KCBHFCKV)的情况下，如果block发生改变，则运行sum apply之后chkval_kcbh也发生改变
继续dump block
buffer tsn: 3 rdba: 0x02404b1d (9/19229) scn: 0xb8c.3c232935 seq: 0x01 flg: 0x06 tail: 0x29350601 frmt: 0x02 chkval: 0xef8b type: 0x06=trans data Hex dump of block: st=0, typ_found=1 tab 0, row 0, @0x1f81 tl: 23 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [16] 77 78 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d end_of_block_dump End dump data blocks tsn: 3 file#: 9 minblk 19229 maxblk 19229
证明上述结论正确:dump.flg=bbed.flg_kcbh，dump.chkval=bbed.chkval_kcbh
db_block_checksum为FALSE测试
CDB_CDB$ROOT@SYS> alter system set db_block_checksum=false;
系统已更改。
CDB_CDB$ROOT@SYS> drop table t_xifenfei_new purge;
表已删除。
CDB_CDB$ROOT@SYS> alter session set current_schema=chf;
会话已更改。
CDB_CDB$ROOT@SYS> create table t_xifenfei_new(id number,name varchar2(100));
表已创建。
CDB_CDB$ROOT@SYS> insert into t_xifenfei_new values(1,'www.orasos.com');
已创建 1 行。
CDB_CDB$ROOT@SYS> commit;
提交完成。
CDB_CDB$ROOT@SYS> select dbms_rowid.rowid_relative_fno(rowid) file_no,
2 dbms_rowid.rowid_block_number(rowid) block_no from t_xifenfei_new;
FILE_NO BLOCK_NO
---------- ----------
9 19237
CDB_CDB$ROOT@SYS> alter system checkpoint;
系统已更改。
CDB_CDB$ROOT@SYS> alter system dump datafile 9 block 19237;
系统已更改。
dump block分析
buffer tsn: 3 rdba: 0x02404b25 (9/19237) scn: 0xb8c.3c23c8b2 seq: 0x01 flg: 0x02 tail: 0xc8b20601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 tab 0, row 0, @0x1f83 tl: 21 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [14] 77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d end_of_block_dump End dump data blocks tsn: 3 file#: 9 minblk 19237 maxblk 19237
这里可以发现当设置db_block_checksum=false之时，插入数据,显示flg: 0x02，chkval: 0x0000，由此猜测无对block写入进行部分验证(tailchk依然验证)
使用bbed修改block
BBED> set filename 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF'
FILENAME E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF
BBED> set blocksize 19238
BBED-00108: illegal BLOCKSIZE (19238) specified
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set block 8192
BLOCK# 8192
BBED> set blocksize 19238
BBED-00108: illegal BLOCKSIZE (19238) specified
BBED> set block 19238
BLOCK# 19238
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 0x02404b25
ub4 bas_kcbh @8 0x3c23c8b2
ub2 wrp_kcbh @12 0x0b8c
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x02 (KCBHFDLC)
ub2 chkval_kcbh @16 0x0000
ub2 spare3_kcbh @18 0x0000
--这里看到flg_kcbh与chkval_kcbh和dump结果一致
BBED> sum
Check value for File 0, Block 19238:
current = 0x0000, required = 0x0000
--required为0，表示不验证
BBED> d /v offset 8174
File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0)
Block: 19238 Offsets: 8174 to 8191 Dba:0x00000000
-------------------------------------------------------
7777772e 6f726173 6f732e63 6f6d0106 l www.orasos.com..
b2c8 l 踩
<16 bytes per line>
BBED> set mode edit
MODE Edit
BBED> m /x 78
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0)
Block: 19238 Offsets: 8174 to 8191 Dba:0x00000000
------------------------------------------------------------------------
7877772e 6f726173 6f732e63 6f6d0106 b2c8
<32 bytes per line>
BBED> d /v
File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0)
Block: 19238 Offsets: 8174 to 8191 Dba:0x00000000
-------------------------------------------------------
7877772e 6f726173 6f732e63 6f6d0106 l xww.orasos.com..
b2c8 l 踩
<16 bytes per line>
--修改了block，但是sum依然提示required为0
BBED> sum
Check value for File 0, Block 19238:
current = 0x0000, required = 0x0000
BBED> sum apply
Check value for File 0, Block 19238:
current = 0x0000, required = 0x0000
dump block核对
buffer tsn: 3 rdba: 0x02404b25 (9/19237) scn: 0xb8c.3c23c8b2 seq: 0x01 flg: 0x02 tail: 0xc8b20601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 tab 0, row 0, @0x1f83 tl: 21 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [14] 78 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d end_of_block_dump End dump data blocks tsn: 3 file#: 9 minblk 19237 maxblk 19237
修改flg_kcbh测试
BBED> m /x 06 offset 15 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 15 to 30 Dba:0x00000000 ------------------------------------------------------------------------ 06000000 00010000 00d86b01 00aec823 <32 bytes per line> BBED> sum Check value for File 0, Block 19238: current = 0x0000, required = 0x04b3 --修改flg_kcbh=x06后，sum中的required出现了非0值，表示已经启动了block完整性检测 BBED> m /x 79 offset 1876 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 1876 to 1891 Dba:0x00000000 ------------------------------------------------------------------------ 79391b01 0000c24e 07000205 c406573f <32 bytes per line> BBED> sum Check value for File 0, Block 19238: current = 0x0000, required = 0x049d --修改了chkval_kcbh值 BBED> sum apply Check value for File 0, Block 19238: current = 0x049d, required = 0x049d
再次检查dump block
buffer tsn: 3 rdba: 0x02404b25 (9/19237) scn: 0xb8c.3c23c8b2 seq: 0x01 flg: 0x06 tail: 0xc8b20601 frmt: 0x02 chkval: 0x049d type: 0x06=trans data Hex dump of block: st=0, typ_found=1 tl: 21 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [14] 78 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d end_of_block_dump End dump data blocks tsn: 3 file#: 9 minblk 19237 maxblk 19237
证明修改flg_kcbh后，block的sum验证起效
完整测试
--flg_kcbh=2,chkval_kcbh为04b3，修改block观察变化 BBED> sum Check value for File 0, Block 19238: current = 0x04b3, required = 0x04b3 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 0x02404b25 ub4 bas_kcbh @8 0x3c23c8b2 ub2 wrp_kcbh @12 0x0b8c ub1 seq_kcbh @14 0x01 ub1 flg_kcbh @15 0x02 (KCBHFDLC) ub2 chkval_kcbh @16 0x04b3 ub2 spare3_kcbh @18 0x0000 BBED> m /x 11 offset 7184 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 7184 to 7199 Dba:0x00000000 ------------------------------------------------------------------------ 110000c2 39b50002 05c40721 07280000 <32 bytes per line> BBED> undo BBED> modify /x 00 filename 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF' block 19238. offset 7184. File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 7184 to 7199 Dba:0x00000000 ------------------------------------------------------------------------ 000000c2 39b50002 05c40721 07280000 <32 bytes per line> BBED> m /x 11 offset 8174 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 8174 to 8189 Dba:0x00000000 ------------------------------------------------------------------------ 1177772e 6f726173 6f732e63 6f6d0106 <32 bytes per line> BBED> sum Check value for File 0, Block 19238: current = 0x04b3, required = 0x04b3 --证明当flg_kcbh=2修改block之后chkval_kcbh依然为04b3，证明flg_kcbh不含(KCBHFCKV), --修改block不会导致chkval_kcbh改变，也就是说,该block为启用db_block_checksum --flg_kcbh=6的时候验证修改block导致的chkval_kcbh变化 BBED> m /x 06 offset 15 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 15 to 30 Dba:0x00000000 ------------------------------------------------------------------------ 06b30400 00010000 00d86b01 00aec823 <32 bytes per line> --刚刚修改flg_kcbh=6,马上看到required非0 BBED> sum Check value for File 0, Block 19238: current = 0x04b3, required = 0x04da BBED> sum apply Check value for File 0, Block 19238: current = 0x04da, required = 0x04da --尝试修改block BBED> m /x 22 offset 8174 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 8174 to 8189 Dba:0x00000000 ------------------------------------------------------------------------ 2277772e 6f726173 6f732e63 6f6d0106 <32 bytes per line> --required发生改变 BBED> sum Check value for File 0, Block 19238: current = 0x04da, required = 0x04e9 BBED> sum apply Check value for File 0, Block 19238: current = 0x04e9, required = 0x04e9 --修改为以前值，验证required BBED> m /x 11 offset 8174 File: E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF (0) Block: 19238 Offsets: 8174 to 8189 Dba:0x00000000 ------------------------------------------------------------------------ 1177772e 6f726173 6f732e63 6f6d0106 <32 bytes per line> BBED> sum Check value for File 0, Block 19238: current = 0x04e9, required = 0x04da BBED> sum apply Check value for File 0, Block 19238: current = 0x04da, required = 0x04da --发现当block修改回来后，required值和以前一致(也就是说chkval_kcbh值还原) --进步一说明chkval_kcbh取决于block内部值
通过相关测试db_block_checksum是在实例级别启动block checksum，但是具体到每个block是通过flg_kcbh来控制，而具体体现是在chkval_kcbh值上