【Oracle坏块】坏块类型：CHECKSUM
一、模拟checksum
模拟环境参考文章【Oracle坏块】Oracle坏块介绍 & 坏块类型：ALL ZERO
清除v$database_block_corruption命令：
execute dbms_backup_restore.resetCfileSection(35) -------该命令不能再pdb中执行
前期环境检查
SQL> select count(*) from test; COUNT(*) ---------- 20000 SQL> select * from v$database_block_corruption; no rows selected SQL> select dbms_rowid.rowid_relative_fno(rowid) rfn,dbms_rowid.rowid_block_number(rowid) bln,dbms_rowid.rowid_row_number(rowid) rn from test where rownum<=5; RFN BLN RN ---------- ---------- ---------- 22 131 0 22 131 1 22 131 2 22 131 3 22 131 4
模拟checksum
查看checkval_kcbh的值
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> p chkval_kcbh ub2 chkval_kcbh
map查看当前块，chkval_kcbh的offset值为16
BBED> map /v File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Dba:0x05800083 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub2 wrp2_kcbh @2 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktbbh, 96 bytes @20 ub1 ktbbhtyp @20 union ktbbhsid, 4 bytes @24 struct ktbbhcsc, 8 bytes @28 sb2 ktbbhict @36 ub1 ktbbhflg @38 ub1 ktbbhfsl @39 ub4 ktbbhfnx @40 struct ktbbhitl[3], 72 bytes @44 struct kdbh, 14 bytes @124 ub1 kdbhflag @124 sb1 kdbhntab @125 sb2 kdbhnrow @126 sb2 kdbhfrre @128 sb2 kdbhfsbo @130 sb2 kdbhfseo @132 sb2 kdbhavsp @134 sb2 kdbhtosp @136 struct kdbt[1], 4 bytes @138 sb2 kdbtoffs @138 sb2 kdbtnrow @140 sb2 kdbr[611] @142 ub1 freespace[819] @1364 ub1 rowdata[6005] @2183 ub4 tailchk @8188
dump命令查看16位置的内容
BBED> d /v offset 16 count 20 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 16 to 35 Dba:0x05800083 ------------------------------------------------------- a7390000 01000000 a71d0100 09d52d00 l §9......§....ӭ. 00800000 l .... <16 bytes per line>
对数据进行修改
BBED> modify /x 1111 offset 16 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 16 to 35 Dba:0x05800083 ------------------------------------------------------------------------ 11110000 01000000 a71d0100 09d52d00 00800000 <32 bytes per line>
数据库中查询是否出现坏块
SQL> select count(*) from test; select count(*) from test * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 22, block # 131) ORA-01110: data file 22: '/u01/app/oracle/oradata/T1/pdb/test.dbf'
查看视图（如果没有，可以在RMAN下执行validate datafile file_id）
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 22 131 1 0 CHECKSUM 3
二、恢复方法
1、使用原来未推出的BEED环境，使用revert命令恢复，当然实际环境不可取。
2、退出当前环境重新进入BEED，恢复。
OS下使用dd命令查看当前坏块的checksum值
[oracle@T1 ~]$ dd if=/u01/app/oracle/oradata/T1/pdb/test.dbf bs=8192 skip=131 count=1 conv=swab|od -x 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 6.8171e-05 s, 120 MB/s 0000000 06a2 0000 8300 8005 09d5 2d00 0000 0104 0000020 1111 0000 0100 0000 a71d 0100 09d5 2d00 ......
进入BBED直接使用sum命令求值，即为当前数据块检查值要求的值
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> set offset 16 OFFSET 16 BBED> sum Check value for File 22, Block 131: current = 0x1111, required = 0x39a7
执行sum apply确认更改
BBED> sum apply Check value for File 22, Block 131: current = 0x39a7, required = 0x39a7
刷新数据库缓存，查看数据
SQL> select count(*) from test; COUNT(*) ---------- 20000
查看视图（现在RMAN下更新一下试图）
SQL> select * from v$database_block_corruption; no rows selected
数据块恢复成功
注意: 该坏块类型要么使用备份恢复（即当前备份）,要么使用BBED在开启参数DB_BLOCK_CHECKSUM=TRUE(typical）默认开启，直接使用sum命令检查，在sum apply应用即可，其他丢数据的方式就不考虑了。
还可以使用dbv命令算出当前块的检查值
[oracle@T1 ~]$ dbv file=/u01/app/oracle/oradata/T1/pdb/test.dbf DBVERIFY: Release 19.0.0.0.0 - Production on Mon Aug 2 09:23:09 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/T1/pdb/test.dbf Page 131 is marked corrupt Corrupt block relative dba: 0x05800083 (file 22, block 131) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x05800083 last change scn: 0x0000.0000.002dd509 seq: 0x1 flg: 0x04 spare3: 0x0 consistency value in tail: 0xd5090601 check value in block header: 0x1111 ------坏块当前值 computed block checksum: 0x28b6 ------计算后的值（可以理解为current到required的差值。即current+插值=required需要进行异或运算） DBVERIFY - Verification complete Total Pages Examined : 4096 Total Pages Processed (Data) : 34 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 132 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 3929 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3235226 (0.3235226)
