Oracle check sum

jnrjian

于 2022-12-16 15:36:58 发布

阅读量1k

点赞数

文章标签： oracle 数据库 dba

本文链接：https://blog.csdn.net/jnrjian/article/details/128340221

版权

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数据文件

[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

************* !!! 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

************* !!! 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

<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

jnrjian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle check sum

我开始本以为他说的这个块是空块，但从他随后的留言可以看出，这个块并不是空块。其实在oracle里一个块的checksum值完全有可能是0的，我们来看一个实例：BBED> set file 4 FILE# 4 BBED> set block 32 BLOCK# 32 可以看到现在这个块的checksum值是0x9339BBED> dump File: /dras20/testdb/drsys01.dbf (4) Block: 32
复制链接

扫一扫