【Oracle坏块】坏块类型：FRACTURED
一、模拟FRACTURED
模拟环境参考上一篇文章【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
模拟FRACTURED
BBED模拟，前后版本不一致（tailchk）
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> show FILE# 22 BLOCK# 131 OFFSET 0 DBA 0x05800083 (92274819 22,131) FILENAME /u01/app/oracle/oradata/T1/pdb/test.dbf BIFILE bifile.bbd LISTFILE /u01/app/bbed_profile/bbed_filelist.txt BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No
查看tailchk值
BBED> p bas_kcbh ub4 bas_kcbh @8 0x002dd509 BBED> p type_kcbh ub1 type_kcbh @0 0x06 BBED> p seq_kcbh ub1 seq_kcbh @14 0x01 BBED> p tailchk ub4 tailchk @8188 0xd5090601
------tailchk = bas_kcbh低两位字节（d509）+ type_kcbh（06）+ seq_kcbh（01） = d5090601
设置偏移量
BBED> set offset 8188 OFFSET 8188 BBED> d /v count 128 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 8188 to 8191 Dba:0x05800083 ------------------------------------------------------- 010609d5 l ...
修改tailchk
BBED> modify /x 12345678 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 8188 to 8191 Dba:0x05800083 ------------------------------------------------------------------------ 12345678 <32 bytes per line> BBED> sum apply Check value for File 22, Block 131: current = 0xa6eb, required = 0xa6eb BBED> d /v count 128 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 8188 to 8191 Dba:0x05800083 ------------------------------------------------------- 12345678 l .4Vx <16 bytes per line> BBED> p tailchk ub4 tailchk @8188 0x78563412
刷新数据库缓存查看数据
SQL> alter system flush buffer_cache; System altered. 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'
发现坏块
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 22 131 1 0 FRACTURED 3
二、恢复方法
1、BBED
该类型的坏块是由tail与块头值不一致，导致数据块版本不一致
BBED> d /v count 128 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 8188 to 8191 Dba:0x05800083 ------------------------------------------------------- 12345678 l .4Vx <16 bytes per line> BBED> p tailchk ub4 tailchk @8188 0x78563412 BBED> p kcbh struct kcbh, 20 bytes @0 ub1 type_kcbh @0 0x06 ub1 frmt_kcbh @1 0xa2 ub2 wrp2_kcbh @2 0x0000 ub4 rdba_kcbh @4 0x05800083 ub4 bas_kcbh @8 0x002dd509 ub2 wrp_kcbh @12 0x0000 ub1 seq_kcbh @14 0x01 ub1 flg_kcbh @15 0x04 (KCBHFCKV) ub2 chkval_kcbh @16 0xa6eb ub2 spare3_kcbh @18 0x0000
由上可以计算出数据块tailchk值为d5090601
由于机器低字节序的原因，所以修复使用值为010609d5
BBED> set offset 8188 OFFSET 8188 BBED> modify /x 010609d5 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 8188 to 8191 Dba:0x05800083 ------------------------------------------------------------------------ 010609d5 <32 bytes per line> BBED> sum apply Check value for File 22, Block 131: current = 0x39a7, required = 0x39a7 BBED> d /v count 128 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 8188 to 8191 Dba:0x05800083 ------------------------------------------------------- 010609d5 l ... <16 bytes per line>
验证
SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from test; COUNT(*) ---------- 20000
数据恢复成功
2、DBMS_REPAIR包恢复 (丢失数据)
3、CTAS方式复制（丢失数据）
4、备份恢复（不会丢数据）
以上省略，可以参照上一篇ALL ZERO的文章
