时间：2021-07-01 10:21:17 帮助过：8人阅读
1.Bad header - the beginning of the block (cache header) is corrupt with invalid values
2.The block is Fractured/Incomplete - header and footer of the block do not match
3.The block checksum is invalid
4.The block is misplaced
5.Zeroed out blocks/ORA-8103模拟
物理坏块的模拟--分别模拟5中情况
1.Bad header - the beginning of the block (cache header) is corrupt with invalid values
create tablespace yhqt datafile ‘/u01/app/oracle/oradata/orcl/yhqt01.dbf‘ size 50M; create user yhqt identified by yhqt default tablespace yhqt; grant dba to yhqt; SYS@ orcl >conn yhqt/*** Connected. YHQT@ orcl >create table yhqtest_1(id int,name varchar2(100)); Table created. YHQT@ orcl >insert into yhqtest_1 values(1,‘yhq‘); 1 row created. YHQT@ orcl >commit; Commit complete. YHQT@ orcl >select dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno from yhqtest_1; 2 3 4 REL_FNO BLOCKNO ---------- ---------- 10 135
--使用bbed，手动修改数据块
BBED> set file 10 block 135 FILE# 10 BLOCK# 135 BBED> map /v File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10) Block: 135 Dba:0x02800087 ------------------------------------------------------------ BBED-00400: invalid blocktype (00) YHQT@ orcl >alter system flush buffer_cache; System altered. BBED> set file 10 block 135 FILE# 10 BLOCK# 135 BBED> map /v File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10) Block: 135 Dba:0x02800087 ------------------------------------------------------------ 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 0x02800087 ub4 bas_kcbh @8 0x009e4d78 ub2 wrp_kcbh @12 0x0000 ub1 seq_kcbh @14 0x01 >>01 修改为ff ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV) ub2 chkval_kcbh @16 0xf4f8 ub2 spare3_kcbh @18 0x0000 BBED> modify /x ff offset 14 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10) Block: 135 Offsets: 14 to 525 Dba:0x02800087 ------------------------------------------------------------------------ ff06f8f4 00000100 00003d59 0100764d 9e000000 000002f8 32008000 80020500 BBED> sum apply Check value for File 10, Block 135: current = 0xf406, required = 0xf406
--重启db，并查询
SYS@ orcl >conn yhqt/*** Connected. YHQT@ orcl >select * from yhqtest_1; select * from yhqtest_1 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 10, block # 135) ORA-01110: data file 10: ‘/u01/app/oracle/oradata/orcl/yhqt01.dbf‘ --报错ORA-01578: ORACLE data block corrupted [oracle@DSI ~]$ tail -n 20 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log Data in bad block: type: 6 format: 2 rdba: 0x02800087 last change scn: 0x0000.009e4d78 seq: 0xff flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x4d780601 check value in block header: 0xf406 computed block checksum: 0x0 Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data Checker run found 1 new persistent data failures Fri Jul 12 10:46:57 2019 Dumping diagnostic data in directory=[cdmp_20190712104657], requested by (instance=1, osid=2812), summary=[incident=63755]. Dumping diagnostic data in directory=[cdmp_20190712104658], requested by (instance=1, osid=2812), summary=[incident=63756]. Fri Jul 12 10:47:16 2019 Sweep [inc][63756]: completed Sweep [inc2][63756]: completed Sweep [inc2][63755]: completed YHQT@ orcl >insert into yhqtest_1 values(1,‘yhq‘); insert into yhqtest_1 values(1,‘yhq‘) * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 10, block # 135) ORA-01110: data file 10: ‘/u01/app/oracle/oradata/orcl/yhqt01.dbf‘
[oracle@DSI ~]$ dbv file=/u01/app/oracle/oradata/orcl/yhqt01.dbf DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jul 12 10:50:42 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf Page 135 is influx - most likely media corrupt Corrupt block relative dba: 0x02800087 (file 10, block 135) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x02800087 last change scn: 0x0000.009e4d78 seq: 0xff flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x4d780601 check value in block header: 0xf406 computed block checksum: 0x0 DBVERIFY - Verification complete Total Pages Examined : 6400 Total Pages Processed (Data) : 4 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 130 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 6265 Total Pages Marked Corrupt : 1 Total Pages Influx : 1 Total Pages Encrypted : 0 Highest block SCN : 10374518 (0.10374518) YHQT@ orcl >select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 10 135 1 0 FRACTURED ##发现数据块物理损坏 用rman恢复，发现没有datafile=10的备份，无法恢复 RMAN> run {blockrecover datafile 10 block 135;} Starting recover at 12-JUL-19 starting full resync of recovery catalog full resync complete allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=142 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/12/2019 10:52:22 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 10 found to restore --跳过坏块的修复，但是坏的数据丢失了 YHQT@ orcl >alter session set db_file_multiblock_read_count=1; Session altered. YHQT@ orcl >execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘YHQT‘,‘YHQTEST_1‘); BEGIN DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘YHQT‘,‘YHQTEST_1‘); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier ‘DBMS_REPAIR.SKIP_CORRUPT_BLOCKS‘ must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored YHQT@ orcl >conn / as sysdba Connected. SYS@ orcl >execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘YHQT‘,‘YHQTEST_1‘); PL/SQL procedure successfully completed. SYS@ orcl >create table YHQT.yhqtest_1_new as select * from YHQT.yhqtest_1; Table created. SYS@ orcl >conn yhqt/*** Connected. YHQT@ orcl >select * from yhqtest_1_new; no rows selected YHQT@ orcl >select * from yhqtest_1; no rows selected
2.The block is Fractured/Incomplete - header and footer of the block do not match
YHQT@ orcl >create table yhqtest_2(id int,name varchar2(100)); Table created. YHQT@ orcl >insert into yhqtest_2 values(1,‘yhq‘); 1 row created. YHQT@ orcl >commit; Commit complete. YHQT@ orcl >alter system flush buffer_cache; System altered. YHQT@ orcl >select dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno from yhqtest_2; 2 3 4 REL_FNO BLOCKNO ---------- ---------- 10 143 BBED> set file 10 block 143; FILE# 10 BLOCK# 143 BBED> dump /v offset 8188 File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10) Block: 143 Offsets: 8188 to 8191 Dba:0x0280008f ------------------------------------------------------- 0106f553 l .. <16 bytes per line> BBED> modify /x 0106f554 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10) Block: 143 Offsets: 8188 to 8191 Dba:0x0280008f ------------------------------------------------------------------------ 0106f554 <32 bytes per line> BBED> sum apply Check value for File 10, Block 143: current = 0x46ca, required = 0x46ca --刷新并查询 YHQT@ orcl >alter system flush buffer_cache; System altered. YHQT@ orcl >select * from yhqtest_2; select * from yhqtest_2 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 10, block # 143) ORA-01110: data file 10: ‘/u01/app/oracle/oradata/orcl/yhqt01.dbf‘ --报错01578 >>>Fractured block ALTER SYSTEM: Flushing buffer cache Hex dump of (file 10, block 143) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2890.trc Corrupt block relative dba: 0x0280008f (file 10, block 143) Fractured block found during multiblock buffer read Data in bad block: type: 6 format: 2 rdba: 0x0280008f last change scn: 0x0000.009e53f5 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x54f50601 check value in block header: 0x46ca computed block checksum: 0x0
3.The block checksum is invalid
checksum是oracle写入后，其他外部因素导致块checksum改变的情况
checksum只有DBWR进程写入，或者直接从磁盘上读取
YHQT@ orcl >create table yhqtest_3(id int,name varchar2(100)); Table created. YHQT@ orcl >insert into yhqtest_3 values(1,‘yhq33333‘); 1 row created. YHQT@ orcl >commit; Commit complete. YHQT@ orcl >select dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno from yhqtest_3; 2 3 4 REL_FNO BLOCKNO ---------- ---------- 10 151 YHQT@ orcl >alter system flush buffer_cache; System altered. BBED> set file 10 block 151 FILE# 10 BLOCK# 151 BBED> p chkval_kcbh ub2 chkval_kcbh @16 0xc428 BBED> sum Check value for File 10, Block 151: current = 0xc428, required = 0xc428 ###当前的checksum=0xc428，请求的checksum也是=0xc428，二者一致 --用dd命令导出10号文件的151号块 [oracle@DSI ~]$ dd if=/u01/app/oracle/oradata/orcl/yhqt01.dbf of=/tmp/yhqt01.dd count=1 skip=151 bs=8192 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 8.8344e-05 s, 92.7 MB/s [oracle@DSI ~]$ sz /tmp/yhqt01.dd --sz到windows下，用editplus工具打开（十六进制）--用UE工具能修改，editplus好像只能看不能改
现在我们将将ID为1的那条记录的ID值由1改为2，即将C1 02改成C1 03
YHQT@ orcl >select dump(1,16) from dual; DUMP(1,16) ----------------- Typ=2 Len=2: c1,2--1十六进制对应c102 修改之后上传到linux，用dd [oracle@DSI ~]$ dd if=/home/oracle/yhqt01.dd of=/u01/app/oracle/oradata/orcl/yhqt01.dbf bs=8192 seek=151 count=1 conv=notrunc 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.000101711 s, 80.5 MB/s 重启db SYS@ orcl >conn yhqt/yhqt Connected. YHQT@ orcl >select * from yhqtest_3; select * from yhqtest_3 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 10, block # 151) ORA-01110: data file 10: ‘/u01/app/oracle/oradata/orcl/yhqt01.dbf‘ 查看alertlog Hex dump of (file 10, block 151) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_64960/orcl_m000_3145_i64960_a.trc Corrupt block relative dba: 0x02800097 (file 10, block 151) Bad check value found during validation Data in bad block: type: 6 format: 2 rdba: 0x02800097 last change scn: 0x0000.009e57b3 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x57b30601 check value in block header: 0xc428 >>>块头记录的checksum值是0xc428 computed block checksum: 0x1 >>>oracle这里做异或操作后的checksum值是01 Reread of blocknum=151, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data 怎样计算出正确的checksum值（cont..） BBED> set file 10 block 151 FILE# 10 BLOCK# 151 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf BLOCK = 151 Block 151 is corrupt Corrupt block relative dba: 0x02800097 (file 0, block 151) Bad check value found during verification Data in bad block: type: 6 format: 2 rdba: 0x02800097 last change scn: 0x0000.009e57b3 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x57b30601 check value in block header: 0xc428 ======>>>> a computed block checksum: 0x1 =====>>>>>b 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 Message