Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置: 首页 » 技术文章 » 内部原理

如何计算Oracle Block的校验值

Checksum

An optional checksum of the block contents. When a block is written, the checksum is either cleared or set depending on the setting of the db_block_checksum parameter. When a block is read, the checksum is verified if present and if the parameter is set to TRUE. Checksums are always calculated and checked for blocks in the SYSTEM tablespace.

http://www.ixora.com.au/notes/cache_header.htm

意思是说Checksum是一个可选项。当一个块被写入后,checksum或许会被设置或许会被清除,这个取决于db_block_checksum参数的设置。

当读取一个块时,Checksum会被检验,如果db_block_checksum参数设置为true,并且这个块存在Checksum。

SYSTEM表空间总是会被校验和检查无论db_block_checksum设置为True或False。


曾经有过的关于Checksum猜想?
A.如果是对整个数据块做Checksum,然后写入数据块头部,如果是这样下次做Checksum和数据库头部原来Checksum值会不一致。
B.如果是对数据块除了头部(除Cache Layer外)做checksum,然后把这个Checksum值写入头部,理论上感觉这样也可以,但她不能保证头部的变化。


正确的算法

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(chkval_kcbh)占用2个字节,可不可以是3个byte一组,4个byte一组或者更多为一组呢?


kcbh内容

几乎所有的块都有这个信息,占用20个字节,记录着块类型、版本、格式、RDBA、SCN和数据块的校验信息等信息

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        0x01400017
   ub4 bas_kcbh                             @8        0x0012c0e4
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xfc2a
   ub2 spare3_kcbh                          @18       0x0000
BBED> 

type_kcbh:大部分块的类型是都是6(代表表、索引等类型的块)
frmt_kcbh:不同版本,块格式、大小也不同
Oracle 6,7 : 0x01
  8i~9i:all: 0x02
10g~12c:2k : 0x62
        4k : 0x82
        8k : 0xa2
       16k : 0xc2
Redo 6~12c : 0x22

rdba_kcbh:数据块地址
AODU> rdba 0x01400017
        rdba   : 0x01400017=20971543 (dba=5,23)
        rfile# : 5
        block# : 23
        Dump Block : alter system dump datafile 5 block 23;
AODU>

flg_kcbh

#define KCBHFNEW 0x01 /* new block */
#define KCBHFDLC 0x02 /* last change to the block was for a cleanout operation */
#define KCBHFCKV 0x04 /* checksum value is set */
#define KCBHFTMP 0x08 /* temporary data */

for formated block the value is always 0x06,which means 02+04. 
KCBHFCKV是Checksum的标识,如果不设置,修改块时将不会计算新的Checksum值

下面的测试时将flg_kcbh[0x06 (KCBHFDLC, KCBHFCKV)]修改为[0x02 (KCBHFDLC)]

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        0x0140000d
   ub4 bas_kcbh                             @8        0x0012c34a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1ff8
   ub2 spare3_kcbh                          @18       0x0000
BBED> m /x 02 offset 15
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
        MODE            Edit
BBED> m /x 02 offset 15
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oradata/upgr/ohsdba.dbf (5)
 Block: 13               Offsets:   15 to   30           Dba:0x0140000d
------------------------------------------------------------------------
 02f81f00 00010000 00c85501 0048c312
 <32 bytes per line>
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        0x0140000d
   ub4 bas_kcbh                             @8        0x0012c34a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x02 (KCBHFDLC)
   ub2 chkval_kcbh                          @16       0x1ff8
   ub2 spare3_kcbh                          @18       0x0000

修改flg_kcbh过后,再计算checksum,值保持不变

BBED> sum

Check value for File 5, Block 13:
current = 0x1ff8, required = 0x1ff8
BBED> m /x ff offset 90
 File: /oradata/upgr/ohsdba.dbf (5)
 Block: 13               Offsets:   90 to  105           Dba:0x0140000d
------------------------------------------------------------------------
 ff000000 00000000 00000001 0100ffff
 <32 bytes per line>
随便做个更改,重新计算Checksum,Checksum值依旧保持不变 BBED> sum
Check value for File 5, Block 13:
current = 0x1ff8, required = 0x1ff8
BBED>
为了测试db_block_checksum在不同取值情况对块头Checksum的影响,做了2组测试

1.在db_block_checksum为false情况下创建一个表ohsdba

  在db_block_checksum为false情况下创建一个1M的表空间,然后在该空间上创建一个表,插入一行记录

SQL> show parameter checksum
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
db_block_checksum                    string      FALSE
SQL>
SQL> create tablespace ohsdba datafile '/oradata/upgr/ohsdba.dbf' size 1m;
Tablespace created.
SQL> create user ohsdba identified by ohsdba default tablespace ohsdba;
User created.
SQL> grant dba to ohsdba;
Grant succeeded.
SQL> conn ohsdba/ohsdba
Connected.
SQL> create table ohsdba(a number);
Table created.
SQL> insert into ohsdba values(100);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> set lines 156
SQL> col location for a12
SQL> select a,rowid,dbms_rowid.rowid_relative_fno(rowid) || '_' ||dbms_rowid.rowid_block_number(rowid) || '_' || dbms_rowid.rowid_row_number(rowid) location from ohsdba;

         A ROWID              LOCATION
---------- ------------------ ------------

       100 AAAVXIAAFAAAAANAAA 5_13_0
BBED> set dba 5,13
        DBA             0x0140000d (20971533 5,13)
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        0x0140000d
   ub4 bas_kcbh                             @8        0x0012b603
   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
BBED>
可以看到表创建后,块头上标识(flg_kcbh)为0x02 (KCBHFDLC),Checksum(chkval_kcbh)为空

设置db_block_checksum为typical

SQL> alter system set db_block_checksum=typical;
System altered.
SQL>

然后对ohsdba表DML操作,过了一段时间flg_kcbh也有了变化,chkval_kcbh有了新值

db_block_checksum为False时的输出

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        0x0140000d
   ub4 bas_kcbh                             @8        0x0012b603
   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
BBED> 

db_block_checksum为True时的输出

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        0x0140000d
   ub4 bas_kcbh                             @8        0x0012c34a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1ff8
   ub2 spare3_kcbh                          @18       0x0000
BBED>

2.在db_block_checksum为typical情况下创建一个表ohsdba2
SQL> create table ohsdba2(a number);
Table created.
SQL> insert into ohsdba2 values(255);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select a,rowid,dbms_rowid.rowid_relative_fno(rowid) || '_' ||dbms_rowid.rowid_block_number(rowid) || '_' || dbms_rowid.rowid_row_number(rowid) location from ohsdba2;  

         A ROWID              LOCATION
---------- ------------------ ------------

       255 AAAVXMAAFAAAAAXAAA 5_23_0

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        0x01400017
   ub4 bas_kcbh                             @8        0x0012c0e4
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xfc2a
   ub2 spare3_kcbh                          @18       0x0000
BBED>

在db_block_checksum为typical情况下,可以看到表创建后,块头上标识(flg_kcbh)为0x06 (KCBHFDLC, KCBHFCKV),Checksum(chkval_kcbh)为0xfc2a

验证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值。用那个验证更方便

     

11g和12c这2个参数默认值
db_block_checking          string      FALSE
db_block_checksum        string      TYPICAL

DB_BLOCK_CHECKING这个参数主要用于检查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表空间除外)。

以下参数的描述取自Oracle 12c的官方文档
DB_BLOCK_CHECKING specifies whether Oracle Database performs block checking for database blocks.

OFF or FALSE
No block checking is performed for blocks in user tablespaces. However, semantic block checking for SYSTEM tablespace blocks is always turned on.

LOW
Basic block header checks are performed after block contents change in memory (for example, after UPDATE, INSERT or DELETE statements, or after inter-instance block transfers in Oracle RAC).

MEDIUM
All LOW checks and full semantic checks are performed for all objects except indexes (whose contents can be reconstructed by a drop+rebuild on encountering a corruption).

FULL or TRUE
All LOW and MEDIUM checks and full semantic checks are performed for all objects.

Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead in most applications, depending on workload and the parameter value. Specific DML overhead may be higher. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable.


For backward compatibility, the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

DB_BLOCK_CHECKING链接中关于DBMS_REPAIR部分

DBMS_REPAIR实际是通过扫描表对象(CHECK_OBJECT),发现坏块后标记这些坏块(FIX_CORRUPT_BLOCKS),然后读取块时跳过这些坏块(SKIP_CORRUPT_BLOCKS),要不会遇到ORA-01578等错误。最好还是通过备份去修改这些坏块,备份大于一切

DB_BLOCK_CHECKSUM

DB_BLOCK_CHECKSUM OFF | FALSE | TYPICAL | TRUE | FULL
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read - only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied. In addition, Oracle gives every log block a checksum before writing it to the current log.

Most of the log block checksum is done by the generating foreground processes, while the LGWR or the LGWR slave processes (LGnn processes) perform the rest of the work, for better CPU and cache efficiency.

If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. In addition, no log checksum is performed when this parameter is set to OFF.

Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk. Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.

For backward compatibility the use of TRUE (implying TYPICAL) and FALSE (implying OFF) values is preserved.

DB_BLOCK_CHECKSUM链接中关于RMAN部分

CHECK LOGICAL

Tests data and index blocks that pass physical corruption checks for logical corruption, for example, corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert log and server session trace file.

If the total number of physical and logical corruptions detected in a file is less than its SET MAXCORRUPT setting, then the RMAN command completes and the database populates the V$DATABASE_BLOCK_CORRUPTION view with corrupt block ranges. If MAXCORRUPT is exceeded, then the command terminates without populating the views.
When restoring a backup data file, RMAN honors the DB_BLOCK_CHECKSUM initialization parameter setting. RMAN clears the checksum if DB_BLOCK_CHECKSUM is set to false. If set to typical, then RMAN verifies the checksum when restoring from the backup and writing to the data file. If the initialization parameter DB_BLOCK_CHECKSUM=typical, and if MAXCORRUPT is not set, then specifying CHECK LOGICAL detects all types of corruption that are possible to detect.
Note: The MAXCORRUPT setting represents the total number of physical and logical corruptions permitted on a file.

还原数据文件时,根据DB_BLOCK_CHECKSUM值采取不同的行为False(clear checksum),typical(verifies the checksum)


Reference

http://www.ixora.com.au/notes/cache_header.htm

http://docs.oracle.com/database/121/REFRN/GUID-A0F72B65-BC1C-441B-824E-03ADCE66063A.htm#REFRN10030

http://docs.oracle.com/database/121/REFRN/GUID-23700E5C-6BFC-48C2-9728-EB1F93F95DD6.htm#REFRN10029

http://docs.oracle.com/database/121/RCMRF/rcmsynta2008.htm#RCMRF90307

http://docs.oracle.com/database/121/ADMIN/repair.htm#ADMIN11821


关键词:block 

相关文章

如何用BBED使Offline的数据文件Online
Interpret The Datafile OS Header/Block Zero
如何计算Oracle Block的校验值
10g,11g,12c X$KCVFH(一)
Top