数据恢复：模拟2个逻辑坏块

TESTA:   Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> SQL> SQL> create table corrupt_id (t1 int) tablespace users; Table created. SQL> SQL> insert into corrupt_id values(1); 1 row created. SQL> commit; Commit complete. SQL> alter system checkpoint; System altered. SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from corrupt_id; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------ 527 4 SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /s01/diag/rdbms/fixit/FIXIT/trace/FIXIT_ora_7227.trc Block header dump: 0x0100020f Object id on Block? Y seg/obj: 0x12c20 csc: 0x00.10d097 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0006.01f.000003e8 0x00c00c61.00a9.07 --U- 1 fsc 0x0000.0010d098 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x0100020f data_block_dump,data header at 0x7f15d62b2264 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x7f15d62b2264 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f92 avsp=0x1f7b tosp=0x1f7b 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f92 block_row_dump: tab 0, row 0, @0x1f92 tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 02 end_of_block_dump fsbo=0x14 FSBO Free Space Begin offset 除去row dict 后面的可以放数据的空间的起始位置，也可以看成是从这个区域的开始”flag”到最后一个 “row offs”占用的空间 fseo=0x1f92 FSEO Free Space End offset ( 9.2.0 )参与db_block_checking的计算剩余空间，select 的时候oracle不是简单的根据offset定位row.这个值也是参与了定位row的     FSBO 总是小于FSEO ，若Oracle进程读取数据块 发现FSBO>FSEO 且blocksum正确则认为出现了逻辑讹误   SQL> update corrupt_id set t1=t1+1; 1 row updated. SQL> alter system checkpoint; System altered. SQL> alter system flush buffer_cache; System altered. SQL> / System altered.         [oracle@lab1 ~]$ bbed filename=/s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf blocksize=8192 mode=edit Password: BBED: Release 2.0.0.0.0 - Limited Production on Mon Sep 24 01:40:46 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set block 527 BLOCK# 527 BBED> map File: /s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf (0) Block: 527 Dba:0x00000000 ------------------------------------------------------------ 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[8062] @120 ub1 rowdata[6] @8182 ub4 tailchk @8188 BBED> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8182 0x2c BBED> set offset 8182 OFFSET 8182 BBED> x /rn rowdata[0] @8182 ---------- flag@8182: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8183: 0x01 cols@8184: 1 col 0[2] @8185: 3       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 8082 sb2 kdbhavsp @110 8059 sb2 kdbhtosp @112 8059 BBED> set offset 108 OFFSET 108 BBED> d File: /s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf (0) Block: 527 Offsets: 108 to 619 Dba:0x00000000 ------------------------------------------------------------------------ 921f7b1f 7b1f0000 0100921f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> x kdbh.kdbhfseo @108 ------------- 0x92 BBED> modify /x 0x1000 File: /s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf (0) Block: 527 Offsets: 108 to 619 Dba:0x00000000 ------------------------------------------------------------------------ 10007b1f 7b1f0000 0100921f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> 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 16 sb2 kdbhavsp @110 8059 sb2 kdbhtosp @112 8059 BBED> sum Check value for File 0, Block 527: current = 0xd30a, required = 0xcc88 BBED> sum apply Check value for File 0, Block 527: current = 0xcc88, required = 0xcc88       [oracle@lab1 ~]$ dbv file=/s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf start=526 end=528 DBVERIFY: Release 11.2.0.3.0 - Production on Mon Sep 24 01:44:36 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf Block Checking: DBA = 16777743, Block Type = KTB-managed data block data header at 0x7fa1288a9064 kdbchk: fseo(16) < fsbo(20) Page 527 failed with check code 6130 DBVERIFY - Verification complete Total Pages Examined : 3 Total Pages Processed (Data) : 2 Total Pages Failing (Data) : 1 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1102645 (0.1102645) SQL> shutdown abort; ORACLE instance shut down. RMAN> backup validate check logical datafile 4; Starting backup at 24-SEP-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=139 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 4 FAILED 0 18 668 1101975 File Name: /s01/oradata/FIXIT/datafile/o1_mf_users_85ztlh77_.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 1 96 Index 0 39 Other 0 487 validate found one or more corrupt blocks See trace file /s01/diag/rdbms/fixit/FIXIT/trace/FIXIT_ora_8036.trc for details Finished backup at 24-SEP-12 Block Checking: DBA = 16777743, Block Type = KTB-managed data block data header at 0x7f00bc1f6064 kdbchk: fseo(16) < fsbo(20) Error backing up file 4, block 527: logical corruption dbkh_create_finding: BEGIN dbkhu_prepare_default_msgobj: BEGIN dbkhu_prepare_default_msgobj:; name_id=25, type=2, flags=1 dbkhu_get_default_msg_def: BEGIN dbkhu_get_default_msg_def: END dbkhu_prepare_default_msgobj:: MSG PARAMS-1; i=2 dbkhu_prepare_default_msgobj: END dbkhu_prepare_default_msgobj: BEGIN dbkhu_prepare_default_msgobj:; name_id=25, type=2, flags=2 dbkhu_get_default_msg_def: BEGIN dbkhu_get_default_msg_def: END dbkhu_prepare_default_msgobj:: MSG PARAMS-2; i=1 dbkhu_prepare_default_msgobj: END *** 2012-09-24 01:48:25.079 dbkhu_prepare_default_msgobj: BEGIN dbkhu_prepare_default_msgobj:; name_id=38, type=2, flags=1 dbkhu_get_default_msg_def: BEGIN dbkhu_get_default_msg_def: END dbkhu_prepare_default_msgobj:: MSG PARAMS-1; i=3 dbkhu_prepare_default_msgobj: END dbkhu_prepare_default_msgobj: BEGIN dbkhu_prepare_default_msgobj:; name_id=38, type=2, flags=2 dbkhu_get_default_msg_def: BEGIN dbkhu_get_default_msg_def: END dbkhu_prepare_default_msgobj:: MSG PARAMS-2; i=2 dbkhu_prepare_default_msgobj: END dbkh_create_finding: END   kdbchk: fseo(16) < fsbo(20) Page 527 failed with check code 6130 可能引起 以下错误： ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] [] ORA-00607: Internal error occurred while making a change to a data block ORA-00602: internal programming exception ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] [] ORA-00607: Internal error occurred while making a change to a data block ORA-00602: internal programming exception ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] [] ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []     TESTB:   SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 24 09:30:18 2012 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 1048576000 bytes Fixed Size 2101544 bytes Variable Size 738201304 bytes Database Buffers 301989888 bytes Redo Buffers 6283264 bytes Database mounted. Database opened.   SQL> create table corrupt_id (t1 int) tablespace users; Table created. SQL> insert into corrupt_id values(1); 1 row created. SQL> commit; Commit complete. SQL> alter system checkpoint; System altered. SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from corrupt_id; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ------------------------------------ ------------------------------------ 917060 4 SQL> alter system dump datafile 4 block 917060; System altered. SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /s01/admin/G10R25/udump/g10r25_ora_4497.trc Block header dump: 0x010dfe44 Object id on Block? Y seg/obj: 0xf0e4 csc: 0x00.17d1aac itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x10dfe41 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.00c.0000515d 0x008001c2.1138.08 --U- 1 fsc 0x0000.017d1ab2 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 data_block_dump,data header at 0x82d8864 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x082d8864 bdba: 0x010dfe44 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f92 avsp=0x1f7b tosp=0x1f7b 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f92 block_row_dump: tab 0, row 0, @0x1f92 tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 02 end_of_block_dump End dump data blocks tsn: 4 file#: 4 minblk 917060 maxblk 917060   fsbo=0x14 FSBO Free Space Begin offset 除去row dict 后面的可以放数据的空间的起始位置，也可以看成是从这个区域的开始”flag”到最后一个 “row offs”占用的空间 fseo=0x1f92 FSEO Free Space End offset ( 9.2.0 )参与db_block_checking的计算剩余空间，select 的时候oracle不是简单的根据offset定位row.这个值也是参与了定位row的         FSBO 总是小于FSEO ，若Oracle进程读取数据块 发现FSBO>FSEO 且blocksum正确则认为出现了逻辑讹误     SQL> update corrupt_id set t1=t1+1; 1 row updated. SQL> alter system checkpoint; System altered. SQL> alter system flush buffer_cache; System altered. SQL> / System altered.             [oracle@vrh8 ~]$ bbed filename=/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf blocksize=8192 mode=edit Password:   BBED> set block 917060 BLOCK# 917060 BBED> map File: /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf (0) Block: 917060 Dba:0x00000000 ------------------------------------------------------------ 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[8062] @120 ub1 rowdata[6] @8182 ub4 tailchk @8188 BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) b1 kdbhntab @101 1 b2 kdbhnrow @102 1 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 20 sb2 kdbhfseo @108 8082 b2 kdbhavsp @110 8059 b2 kdbhtosp @112 8059 BBED> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8182 0x2c BBED> set offset 8182 OFFSET 8182 BBED> x /rn rowdata[0] @8182 ---------- flag@8182: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8183: 0x02 cols@8184: 1 col 0[2] @8185: 2 BBED> set offset 108 OFFSET 108 BBED> d File: /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf (0) Block: 917060 Offsets: 108 to 619 Dba:0x00000000 ------------------------------------------------------------------------ 921f7b1f 7b1f0000 0100921f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> modify /x 0x3d00 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf (0) Block: 917060 Offsets: 108 to 619 Dba:0x00000000 ------------------------------------------------------------------------ 3d007b1f 7b1f0000 0100921f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) b1 kdbhntab @101 1 b2 kdbhnrow @102 1 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 20 sb2 kdbhfseo @108 61 b2 kdbhavsp @110 8059 b2 kdbhtosp @112 8059 BBED> set offset 106 OFFSET 106 BBED> modify /x 0x4000 File: /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf (0) Block: 917060 Offsets: 106 to 617 Dba:0x00000000 ------------------------------------------------------------------------ 40003d00 7b1f7b1f 00000100 921f0000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) b1 kdbhntab @101 1 b2 kdbhnrow @102 1 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 64 sb2 kdbhfseo @108 61 b2 kdbhavsp @110 8059 b2 kdbhtosp @112 8059 BBED> sum Check value for File 0, Block 917060: current = 0x52c9, required = 0x4d32 BBED> sum apply Check value for File 0, Block 917060: current = 0x4d32, required = 0x4d32 [oracle@vrh8 ~]$ dbv file=/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf start=917059 end=917060 DBVERIFY: Release 10.2.0.5.0 - Production on Mon Sep 24 10:11:16 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf Block Checking: DBA = 17694276, Block Type = KTB-managed data block data header at 0x7f49e04e8064 kdbchk: fsbo(64) wrong, (hsz 20) Page 917060 failed with check code 6129 DBVERIFY - Verification complete Total Pages Examined : 2 Total Pages Processed (Data) : 1 Total Pages Failing (Data) : 1 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 24976162 (0.24976162)