check sum 值得算法
The checksum is the XOR of all the other 2-byte pairs in the block. Thus when a block with a checksum is checked, the XOR of all the 2-byte words in the block should be 0.
以2个byte为一组,除offset16,17(从offset 0开始算起)外对块中所有内容做异或运算XOR(异或运算:如果a、b两个值不相同,则异或结果为1。如果a、b两个值相同,异或结果为0),然后把这个数值写入offset16,17(从offset 0开始算起)。这样(未写数据前)整个块的异或计算之后的值就等于0。如果对数据块做更改,重新校验的值和原来offset(从offset 0开始算起)的值就不匹配。所以用BBED更改块后,必须重新计算和应用才能保证块的一致性。
验证Checksum算法
用dd复制数据块23,然后用od dump这个块内容。由于Linux为小端,需要将od输出内容做翻转,比如第一个a206,应该为06a2
[oracle@db1 ~]$ dd if=/oradata/upgr/ohsdba.dbf bs=8192 skip=23 count=1 |od -x 1+0 records in 1+0 records out 0000000 a206 0000 0017 0140 c0e4 0012 0000 0601 -->A665 0000020 fc2a 0000 0001 0000 55cc 0001 c0e0 0012 -->3E95 ,该行不要计算fc2a 0000040 0000 0000 0002 0032 0010 0140 0004 0003 -->6701 0000060 032e 0000 0130 00c0 00fd 000e 2001 0000 -->2C22 0000100 c0e4 0012 0000 0000 0000 0000 0000 0000 -->F6C0 0000120 0000 0000 0000 0000 0000 0000 0000 0000 -->0 0000140 0000 0000 0100 0001 ffff 0014 1f91 1f7b -->FE 0000160 1f7b 0000 0001 1f91 0000 0000 0000 0000 -->EB00 0000200 0000 0000 0000 0000 0000 0000 0000 0000 -->0 * 0017760 0000 0000 2c00 0101 c203 3803 0601 c0e4 -->E411 0020000 8192 bytes (8.2 kB) copied, 0.00386047 s, 2.1 MB/s [oracle@db1 ~]$
注意:
-->这个后面是对应的XOR操作的结果,然后再将每行计算结果做XOR,最后计算出结果为2AFC
如果感兴趣,自己可以动手算下其实数据文件、控制文件、日志文件os块,也就是常说的block 0,也有Checksum值。用那个验证更方便
大家也可以思考这样一个问题?
假定Checksum(chkval_kcbh)占用2个字节,可不可以是3个byte一组,4个byte一组或者更多为一组呢?
DB_BLOCK_CHECKING (都是这个sum值,数据修改后sum值也会变)这个参数主要用于检查Oracle的数据库在逻辑上的一致性,能够防止内存错误和数据错误。逻辑检查引起的额外负载比较高,可以达到10%,对于一个繁忙的OLTP系统来说,对性能的影响还是比较明显的,在设置该参数时要根据实际情况实际对待
DB_BLOCK_CHECKSUM这个参数主要是检测硬盘、存储和IO系统等错误。用于DBWR和Direct Loader将数据块写入到硬盘时,基于块内的所有字节(除16,17外)做异或计算得出一个校验值并将其写入块头Kcbh.chkval_kcbh。默认值是TYPICAL,这个会给系统带来额外的1%到2%负载,如果设置为FULL(update,delete语句级别的操作时,校验值会被重新计算并写入),负载能达到%4到%5。如果设置为OFF,Oracle不再写和检查Checksum的值(SYSTEM表空间除外)。
数据块Checksum值为0的情况
看到崔华的【怎样在windows上用DD配合ultraEdit修改数据】文章后进行试验时我发现了这个Checksum值为0的情况。这里是我的记录。我们都知道data block的offset位置是16-17是Checksum Value,它属于data block的Cache Layer,作用是oracle在通过DBWn常规写或者User Process直接路径加载时通过checksum value用来得知由底层磁盘是否损坏,那么这个Checksum Value可以为0吗?, 这个value如果为0,岂不是oracle会把它认为是个坏块儿?(跟checksum值是否为0没有任何关系),主要为了是防止IO硬件和IO子系统的错误。随后崔华在【数据块里的checksum值可能是0吗】又帮我验证了这个问题。
BBED里边能够看到的信息是
BBED> p kcbh
struct kcbh, 20 bytes @0
<-More->
ub2chkval_kcbh @16 0xddf1
<-More->
Dump里边能够看到的信息是
buffer tsn: 4 rdba: 0x0200000c (4/42772)
scn: 0x0000.000cd37a seq: 0x02 flg: 0x04 tail: 0x486e0602
frmt: 0x02 chkval: 0xddf1 type: 0x06=trans data
Checksum Value不为零的情况
BBED> set file 6
FILE# 6
BBED> set block 32
BLOCK# 32
BBED> dump
File:/u02/oradata/ETMCDB.dbf (6)
Block: 32 Offsets: 0 to 511 Dba:0x01800020
------------------------------------------------------------------------
06a20000 2000800152750900 00000106 084d0000 01000000 b9cd0000 dd740900
00000000 0200320019008001 08002900 48010000 d50d8000 f0000a00 01200000
52750900 0000000000000000 00000000 00000000 00000000 00000000 00000000
00000000 00010100ffff1400 ec0fd80f d80f0000 0100ec0f 00000000 00000000
<-More->
00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000
如何让不为零的checksumvalue变为零
Shutdownimmediate;后,随便找个offset,主要是找到一个值为0x0000的地方,因为根据checksum值的算法,如果数据块里有两个byte的值是0x0000,则算这个数据块的checksum值的时候,就相当于没有这两个byte(异或)。现在我在没改之前,块里记录的checksum值是0x084d,当我找到了一个值为0x0000的地方后,再把这个改为0x084d,那么根据checksum值的算法,这个块的checksum值就变成0了。不过不能直接修改offset为16、17的地方那里本身就是用来记录checksum值的。(别的块不是记录实际数据的,但改了应该还是有别的影响把)
checksum是每两个字节byete 对比的。
所以就在70的地方再修改成0x084d。
BBED> set offset 70
OFFSET 70
BBED> dump
File: /u02/oradata/ETMCDB.dbf(6)
Block:32 Offsets: 64to 575 Dba:0x01000020
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000
<-More->
BBED> modify /x 084d
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /dras20/testdb/drsys01.dbf (4)
Block:32 Offsets: 64to 575 Dba:0x01000020
------------------------------------------------------------------------
084d0000 00000000 00000000 00000000 00000000 00000000 0000000000000000
<-More->
BBED> sum apply
Check value for File 6, Block 32:
current = 0x0000, required = 0x0000
BBED> dump
File:/u02/oradata/ETMCDB.dbf (6)
Block: 32 Offsets: 0 to 511 Dba:0x01800020
------------------------------------------------------------------------
06a20000 2000800152750900 00000106 00000000 01000000 b9cd0000 dd740900
00000000 0200320019008001 08002900 48010000 d50d8000 f0000a00 01200000
52750900 0000000000000000 00000000 00000000 00000000 00000000 00000000
00000000 00010100ffff1400 ec0fd80f d80f0000 0100ec0f 00000000 00000000
<-More->
00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000
BBED>verify
SQL> Startup
做些查询都没有问题。看来Checksum Value为0是允许的这个是由Oracle算法决定的。
总结到这里又引出了一个问题,那么Oracle是如何标记坏块的哪?最近也综合看了几个案例稍后整理一下
转自:http://dbsnake.com/2010/08/checksum-value-zero.html
今天有朋友留言说他的一个块的checksum值是0,问我是为啥。
我开始本以为他说的这个块是空块,但从他随后的留言可以看出,这个块并不是空块。
其实在oracle里一个块的checksum值完全有可能是0的,我们来看一个实例:
BBED> set file 4
FILE# 4
BBED> set block 32
BLOCK# 32
可以看到现在这个块的checksum值是0x9339
BBED> dump
File: /dras20/testdb/drsys01.dbf (4)
Block: 32 Offsets: 0 to 511 Dba:0x01000020
------------------------------------------------------------------------
06020000 01000020 0002052e 00000204 93390000 0106001d 0000672f 0002052e
00008aa8 00023200 01000019 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 ffff000e 1f981f8a 1f8a0000 00000000 0006000d 000000d1
......省略显示部分内容
1b011b12 1b231b34 1b451b56 1b671b78 1b891b9a 1bab1bbc 1bcd1bde 1bef1c00
<32 bytes per line>
这个块是一个完好的块:
BBED> verify
DBVERIFY - Verification starting
FILE = /dras20/testdb/drsys01.dbf
BLOCK = 32
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
现在我们来让其checksum值变为0:
BBED> set offset 64
OFFSET 64
BBED> dump
File: /dras20/testdb/drsys01.dbf (4)
Block: 32 Offsets: 64 to 575 Dba:0x01000020
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 ffff000e 1f981f8a 1f8a0000 00000000 0006000d 000000d1
00806849 002f5600 00020000 00000000 0e000000 00409018 004003e1 00018503
......省略显示部分内容
1d1f1d30 1d401d51 1d621d73 1d841d95 1da61db7 1dc81dd9 1dea1dfb 1e0c1e1d
<32 bytes per line>
BBED> modify /x 9339
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /dras20/testdb/drsys01.dbf (4)
Block: 32 Offsets: 64 to 575 Dba:0x01000020
------------------------------------------------------------------------
93390000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 ffff000e 1f981f8a 1f8a0000 00000000 0006000d 000000d1
00806849 002f5600 00020000 00000000 0e000000 00409018 004003e1 00018503
......省略显示部分内容
1d1f1d30 1d401d51 1d621d73 1d841d95 1da61db7 1dc81dd9 1dea1dfb 1e0c1e1d
<32 bytes per line>
注意到oracle这里已经提示我新算出来的checksum值就是0了:
BBED> sum apply
Check value for File 4, Block 32:
current = 0x0000, required = 0x0000
可以看到现在这个块的checksum值就是0x0000
BBED> dump
File: /dras20/testdb/drsys01.dbf (4)
Block: 32 Offsets: 0 to 511 Dba:0x01000020
------------------------------------------------------------------------
06020000 01000020 0002052e 00000204 00000000 0106001d 0000672f 0002052e
00008aa8 00023200 01000019 00000000 00000000 00000000 00000000 00000000
93390000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
......省略显示部分内容
1b011b12 1b231b34 1b451b56 1b671b78 1b891b9a 1bab1bbc 1bcd1bde 1bef1c00
<32 bytes per line>
修改后的这个块也是一个完好的块:
BBED> verify
DBVERIFY - Verification starting
FILE = /dras20/testdb/drsys01.dbf
BLOCK = 32
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
这种现象其实本质是由oracle的checksum值算法决定的。
怎么计算出正确的checksum值?
1)创建一个测试表
SQL> create table test (id int, name varchar2(10));
Table created.
SQL> insert into test values(1,'AAAAA');
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blk# from test;
FILE# BLK#
---------- ----------
4 284
通过BBED查看当前的CHECKSUM值
[oracle@Mysql ~]$ bbed
BBED: Release 2.0.0.0.0 - Limited Production on Fri May 25 00:54:11 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 284
FILE# 4
BLOCK# 284
BBED> sum
Check value for File 4, Block 284:
current = 0xf9ff, required = 0xf9ff
把284号数据块dump出来。
[oracle@Mysql ~]$ dd if=/u01/app/oracle/oradata/dsidb/users01.dbf of=/tmp/test.dd count=1 skip=284 bs=8192
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000435816 s, 18.8 MB/s
我们使用UE编辑器打开test.dd数据块
然后把C1 02改成C1 03
然后把test.dd数据块copy回去
[oracle@Mysql tmp]$ dd if=/tmp/test.dd of=/u01/app/oracle/oradata/dsidb/users01.dbf count=1 seek=284 bs=8192 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000339993 s, 24.1 MB/s
然后重启一下数据库
SQL> startup force;
ORACLE instance started.
Total System Global Area 588746752 bytes
Fixed Size 2230592 bytes
Variable Size 201328320 bytes
Database Buffers 377487360 bytes
Redo Buffers 7700480 bytes
Database mounted.
Database opened.
SQL> conn scott/oracle
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 284)
ORA-01110: data file 4: '/u01/app/oracle/oradata/dsidb/users01.dbf'
可以看到数据库查询表test报错,我们再看一下数据库日志。
2018-05-25 01:16:29.248000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/dsidb/dsidb/trace/dsidb_ora_10666.trc (incident=102183):
ORA-01578: ORACLE data block corrupted (file # 4, block # 284)
ORA-01110: data file 4: '/u01/app/oracle/oradata/dsidb/users01.dbf'
Incident details in: /u01/app/oracle/diag/rdbms/dsidb/dsidb/incident/incdir_102183/dsidb_ora_10666_i102183.trc
Sweep [inc][102182]: completed
Hex dump of (file 4, block 284) in trace file /u01/app/oracle/diag/rdbms/dsidb/dsidb/incident/incdir_102182/dsidb_m000_10668_i102182_a.trc
Corrupt block relative dba: 0x0100011c (file 4, block 284)
Bad check value found during validation
Data in bad block:
type: 6 format: 2 rdba: 0x0100011c
last change scn: 0x0000.003ffe2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xfe2d0601
check value in block header: 0xf9ff
computed block checksum: 0x100
可以看到文件头上的check value值为0xf9ff,计算的check sum值为0x100
然后我们再使用BBED去sum一下这个数据块,可以看到,当前check value的值为f9ff,而需要的值为f8ff
[oracle@Mysql ~]$ bbed
BBED: Release 2.0.0.0.0 - Limited Production on Fri May 25 01:18:44 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 284
FILE# 4
BLOCK# 284
BBED> sum
Check value for File 4, Block 284:
current = 0xf9ff, required = 0xf8ff
我们根据 0xf9ff与0x100计算一下当前block正常的checksum值应该是多少。
F9FF= 1111 1001 1111 1111
100= 0000 0001 0000 0000
根据异或算法原理,这里很容易可以看出oracle计算出来的正确的checksum值应该是: 1111 1000 1111 1111, 也就是f8ff
好了,我们这里如法炮制再改一次上述block的checksum值,即将上述block的checksum值改为f8 ff
我们先verify一下
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/dsidb/users01.dbf
BLOCK = 284
Block 284 is corrupt
Corrupt block relative dba: 0x0100011c (file 0, block 284)
Bad check value found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x0100011c
last change scn: 0x0000.003ffe2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xfe2d0601
check value in block header: 0xf9ff
computed block checksum: 0x100
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0
BBED> sum
Check value for File 4, Block 284:
current = 0xf9ff, required = 0xf8ff
BBED> sum
Check value for File 4, Block 284:
current = 0xf9ff, required = 0xf8ff
BBED> modify /x 0xf8ff offset 16
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/dsidb/users01.dbf (4)
Block: 284 Offsets: 16 to 527 Dba:0x0100011c
------------------------------------------------------------------------
f8ff0000 01000000 fa2a0100 2cfe3f00 00000000 02003200 18010001 1d000900
a0000000 ba040002 4d001100 01200000 2dfe3f00 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00010100 ffff1400 8c1f781f
781f0000 01008c1f ffff3200 a0046e04 6e040000 1000c01d 961c7b1b b9190418
50167a14 9812f110 4b0f830d ba0bde09 29087606 a0040000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 284:
current = 0xf8ff, required = 0xf8ff
然后再次verify,可以看到,已经不报坏块了。
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/dsidb/users01.dbf
BLOCK = 284
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED> sum
Check value for File 4, Block 284:
current = 0xf8ff, required = 0xf8ff
数据也可以正常返回了。
SQL> select * from test;
ID NAME
---------- ----------
2 AAAAA
2.重现数据块内空间计算错误?(详细的实验操作步骤,BBED工具verify如下命令提示)
BBED>verify
kdbchk:the amount of space used is not equal to block size
Total Blocks Failing(Data)
SQL> create table t2 (id int,name varchar2(10));
Table created.
SQL> insert into t2 values(1,'AAAAA');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# from t2;
FILE# BLK#
---------- ----------
4 220
SQL> delete from t2;
1 row deleted.
SQL> alter system flush buffer_cache;
System altered.
BBED> set file 4 block 220
FILE# 4
BLOCK# 220
BBED> map
File: /u01/app/oracle/oradata/dsidb/users01.dbf (4)
Block: 220 Dba:0x010000dc
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8055] @120
ub1 rowdata[13] @8175
ub4 tailchk @8188
BBED> p rowdata
ub1 rowdata[0] @8175 0x3c
ub1 rowdata[1] @8176 0x02
ub1 rowdata[2] @8177 0x02
ub1 rowdata[3] @8178 0x02
ub1 rowdata[4] @8179 0xc1
ub1 rowdata[5] @8180 0x02
ub1 rowdata[6] @8181 0x06
ub1 rowdata[7] @8182 0x41
ub1 rowdata[8] @8183 0x41
ub1 rowdata[9] @8184 0x41
ub1 rowdata[10] @8185 0x41
ub1 rowdata[11] @8186 0x41
ub1 rowdata[12] @8187 0x0a
BBED> modify /x 2c offset 8175
File: /u01/app/oracle/oradata/dsidb/users01.dbf (4)
Block: 220 Offsets: 8175 to 8191 Dba:0x010000dc
------------------------------------------------------------------------
2c020202 c1020641 41414141 0a010621 59
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 220:
current = 0x1f3f, required = 0x1f3f
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/dsidb/users01.dbf
BLOCK = 220
Block Checking: DBA = 16777436, Block Type = KTB-managed data block
data header at 0x24d9064
kdbchk: the amount of space used is not equal to block size
used=33 fsc=11 avsp=8055 dtl=8088
Block 220 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 1
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 20
sb2 kdbhfseo @108 8075
sb2 kdbhavsp @110 8055
sb2 kdbhtosp @112 8068