How to Find SHOUG?

Size: px

Start display at page:

Download "How to Find SHOUG?"

短丁
5 years ago
Views:

1 bbed 使用实现 drop index 操作 by SHOUG.XIFENFEI 个人博客地址 : 个人新浪微博 :

2 How to Find SHOUG?

3 bbed 使用实现 drop index 操作这里个 bbed 的测试是为了实现通过 bbed 来实现删除 index, 该方法有两个用途 : 1. 数据库因为 index 出了问题不能启动, 使用该方法可以屏蔽 index, 来实现数据库正常启动 2.bootstrap$ 中的某个 index 异常准备环境 SQL> conn chf/xifenfei Connected. SQL> create table t_xifenfei 2 as 3 select * from dba_objects; Table created. SQL> create index ind_t_xifenfei on t_xifenfei(object_id); Index created. SQL> SET LINES 150 SQL> col owner for a5

4 SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='ind_t_xifenfei'; OWNER INDEX_NAME TABLE_NAME STATUS CHF IND_T_XIFENFEI T_XIFENFEI VALID SQL> select object_id from dba_objects where object_name='ind_t_xifenfei'; OBJECT_ID SQL> select obj#,dataobj#,ts#,file#,block#,bo#,flags from sys.ind$ where obj#=75558; OBJ# DATAOBJ# TS# FILE# BLOCK# BO# FLAGS SQL> select rowid,

5 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from sys.ind$ where obj#=75558; ROWID REL_FNO BLOCKNO ROWNO AAAAACAABAAAT50AAA SQL> alter system checkpoint; System altered. SQL> select dump(75558,'16') from dual; DUMP(75558,'16') Typ=2 Len=4: c3,8,38,3b

6 SQL> select dump(4,'16') from dual; DUMP(4,'16') Typ=2 Len=2: c1,5 SQL> select dump(298,'16') from dual; DUMP(298,'16') Typ=2 Len=3: c2,3,63 SQL> select dump(75557,'16') from dual; DUMP(75557,'16') Typ=2 Len=4: c3,8,38,3a SQL> conn / as sysdba Connected.

7 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 删除 ind$ 中记录 ~]$ bbed listfile=listfile mode=edit password=blockedit BBED: Release Limited Production on Thu Aug 9 17:09: Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. *************!!! For Oracle Internal Use only!!! *************** BBED> info all File# Name Size(blks) /u01/oracle/oradata/ora11g/system01.dbf 0 2 /u01/oracle/oradata/ora11g/sysaux01.dbf 0 3 /u01/oracle/oradata/ora11g/undotbs01.dbf 0

8 4 /u01/oracle/oradata/ora11g/users01.dbf 0 BBED> set file 1 block FILE# 1 BLOCK# BBED> map File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Dba:0x00413e KTB Data Block (Table/Cluster) struct kcbh, 20 struct ktbbh, 72 struct kdbh, 14

9 struct kdbt[6], 24 sb2 ub1 ub1 ub4 BBED> p kdbr sb sb sb sb sb sb sb sb2 5524

10 sb sb2 sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb sb2 5784

11 sb sb 这里使用直接查看的方法, 来找出来 ind$ 中相关记录, 实际中方法很多 find/ 第三方工具都可以 BBED> p *kdbr[0] rowdata[2550] ub1 0xac BBED> x /rn flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@8167: 0x00 cols@8168: 1 kref@8169: 1 mref@8171: 1 hrid@8173:0x nrid@8179:0x col 80

12 BBED> p *kdbr[1] rowdata[2463] ub1 0xac BBED> x /rn flag@8079: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@8080: 0x00 cols@8081: 1 kref@8082: 1 mref@8084: 1 hrid@8086:0x004000a1.1 nrid@8092:0x004000a1.1 col 330 BBED> p *kdbr[2] rowdata[2372]

13 ub1 0xac BBED> x /rn flag@7988: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@7989: 0x00 cols@7990: 1 kref@7991: 1 mref@7993: 1 hrid@7995:0x004000a7.6 nrid@8001:0x004000a7.6 col 471 BBED> p *kdbr[3] rowdata[2094] ub1 0xac

14 BBED> x /rn flag@7710: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@7711: 0x00 cols@7712: 1 kref@7713: 1 mref@7715: 1 hrid@7717:0x0040eb9a.6 nrid@7723:0x0040eb9a.6 col BBED> p *kdbr[4] rowdata[1999] ub1 0xac BBED> x /rn flag@7615: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

15 0x nrid@7628:0x col 8871 BBED> p *kdbr[5] rowdata[1176] ub1 0xac BBED> x /rn flag@6792: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@6793: 0x00 cols@6794: 1 kref@6795: 18 mref@6797: 18

16 col BBED> p *kdbr[6] rowdata[1049] ub1 0x6c BBED> x /rn flag@6665: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@6666: 0x00 cols@6667: 36 ckix@6668: 5 col col 4 col 4

17 col 170 col *NULL* col *NULL* col 15 col *NULL* col 10 col 40 col 1 col 255 col col 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d col *NULL* col *NULL* col *NULL* col *NULL* col *NULL* col *NULL* col *NULL* col *NULL*

18 col *NULL* col *NULL* col *NULL* col *NULL* col 15 col 15 col col 0 col 736 col *NULL* col *NULL* col *NULL* col *NULL* col ######################################### BBED> p *kdbr[7] rowdata[0] ub1 0x6c BBED> x /rn

19 x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) 0x col col col 4 col 4 col 298 col 0 col 1 col 10 col 2 col 255 col *NULL* col 1 col 2 col 0 col 1 col 165

20 col col 1 col 1 col 1720 col ######################################### col col col 1 col *NULL* col *NULL* col *NULL* col 1 col *NULL* col *NULL* col *NULL* col *NULL* col ######################################### BBED> set count 64 COUNT 64 BBED> d

21 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 5616 to 5679 Dba:0x00413e c c b04c b02c1 0502c105 03c c1 0202c10b 02c10303 c20338ff 02c10202 c c10203 c c3082d5c 02c10202 <32 bytes per line> BBED> m /x 7c Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 5616 to 5679 Dba:0x00413e c c b04c b02c1 0502c105 03c c1 0202c10b 02c10303 c20338ff 02c10202 c c10203 c c3082d5c 02c10202 <32 bytes per line>

22 BBED> sum apply Check value for File 1, Block 81524: current = 0x88be, required = 0x88be BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = Block Checking: DBA = , Block Type = KTB-managed data block data header at 0xb53c625c kdbchk: key comref count wrong keyslot=5 Block failed with check code 6121 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0

23 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=rdbms; facility=bbed BBED> p *kdbr[5] rowdata[1176] ub1 0xac BBED> x /rn flag@6792: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@6793: 0x00 cols@6794: 1 kref@6795: 18 mref@6797: 18 hrid@6799:0x00413e74.5 nrid@6805:0x00413e74.5

24 col BBED> d offset 6797 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 6797 to 6860 Dba:0x00413e e e c a7c c10602c1 0602c d52 4f4c4c b5f 4f4e4c59 02c16102 c ffff0180 ffff02c1 <32 bytes per line> BBED> m /x 11 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 6797 to 6860 Dba:0x00413e e e c a7c c10602c1 0602c d52 4f4c4c b5f 4f4e4c59 02c16102 c ffff0180 ffff02c1

25 <32 bytes per line> BBED> sum apply Check value for File 1, Block 81524: current = 0x8bbe, required = 0x8bbe BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = Block Checking: DBA = , Block Type = KTB-managed data block data header at 0xb53c625c kdbchk: the amount of space used is not equal to block size used=1835 fsc=0 avsp=6156 dtl=8096 Block failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1

26 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=rdbms; facility=bbed BBED> p kdbh struct kdbh, 14 ub1 0x00 (NONE) sb1 6 sb2 33 sb2 9 sb2 104 sb sb sb2 6156

27 BBED> d offset 102 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 102 to 165 Dba:0x00413e c180c a a1f331f d81ec21d 631d2c1a ad ffff 5b1e171e da1d7a1d 231de51c a21c631c <32 bytes per line> BBED> m /x 7518 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 102 to 165 Dba:0x00413e c a a1f331f d81ec21d 631d2c1a ad ffff 5b1e171e da1d7a1d 231de51c a21c631c <32 bytes per line>

28 BBED> m /x 7518 offset 104 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 104 to 167 Dba:0x00413e a a1f 331fd81e c21d631d 2c1aad ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419 <32 bytes per line> BBED> p kdbh struct kdbh, 14 ub1 0x00 (NONE) sb1 6 sb2 33 sb2 9 sb2 104 sb2 5524

29 sb sb BBED> sum apply Check value for File 1, Block 81524: current = 0x8bbe, required = 0x8bbe BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = Block Checking: DBA = , Block Type = KTB-managed data block data header at 0xb53c625c kdbchk: space available on commit is incorrect tosp=6261 fsc=0 stb=4 avsp=6261 Block failed with check code 6111 DBVERIFY - Verification complete

30 Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=rdbms; facility=bbed BBED> m /x 7918 offset 104 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 104 to 167 Dba:0x00413e a a1f 331fd81e c21d631d 2c1aad ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419 <32 bytes per line> BBED> sum apply

31 Check value for File 1, Block 81524: current = 0x8bb2, required = 0x8bb2 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=rdbms; facility=bbed 启动数据库测试 ind$ 是否修改成功 SQL> startup

32 ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. Database opened. SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='ind_t_xifenfei'; select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='ind_t_xifenfei' * ERROR at line 1: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] SQL> set autot trace exp SQL> set lines 150 SQL> select /*+ full(t) */obj#,dataobj#,ts#,file#,block#,bo#,flags,rowid from sys.ind$ t where obj#=75558;

33 Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:03 * 1 TABLE ACCESS FULL IND$ (0) 00:00: Predicate Information (identified by operation id): filter("obj#"=75558) SQL> select obj#,dataobj#,ts#,file#,block#,bo#,flags,rowid from sys.ind$ t where obj#=75558; Execution Plan

34 Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID IND$ (0) 00:00:01 * 2 INDEX UNIQUE SCAN I_IND1 1 1 (0) 00:00: Predicate Information (identified by operation id): access("obj#"=75558) SQL> select count(*) from ind$ where obj#=75558; Execution Plan Plan hash value:

35 Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 SORT AGGREGATE 1 5 * 2 INDEX UNIQUE SCAN I_IND (0) 00:00: Predicate Information (identified by operation id): access("obj#"=75558) SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558; Execution Plan Plan hash value:

36 Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:03 1 SORT AGGREGATE 1 5 * 2 TABLE ACCESS FULL IND$ (0) 00:00: Predicate Information (identified by operation id): filter("obj#"=75558) SQL> set autot off SQL> select /*+ full(t) */obj#,dataobj#,ts#,file#,block#,bo#,flags,rowid from sys.ind$ t where obj#=75558; no rows selected SQL> select obj#,dataobj#,ts#,file#,block#,bo#,flags,rowid from sys.ind$ t where obj#=75558;

37 OBJ# DATAOBJ# TS# FILE# BLOCK# BO# FLAGS ROWID AAAAACAABAAAT50AAA SQL> select count(*) from ind$ where obj#=75558; COUNT(*) SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558; COUNT(*) SQL> COL COLUMN_NAME FOR A15 SQL> SELECT INDEX_OWNER,COLUMN_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='I TABLE_OWNER='SYS';

38 INDEX_OWNER COLUMN_NAME INDEX_NAME SYS OBJ# I_IND1 通过上面的查询我们可以知道 ind$ 本身有一个关于 obj# 列的 index, 当我们查询使用该 index 的时候出现上面的 ora-600[kdsgrp1] 错误. 而因为 ind$ 相关 index 是 bootstarp$ 中对象, 不能直接或者 upgrade, 甚至 event 都不能 drop 或者 rebuid 分析 I_IND1 索引信息 SQL> select obj#,dataobj# from obj$ where name='i_ind1'; OBJ# DATAOBJ# SQL> alter session set events 'immediate trace name treedump level 41'; Session altered. SQL> select value from v$diag_info where name='default Trace File';

39 VALUE /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17321.trc -- 通过这个 dump 出来的 rdba 信息, 结合我们 dump 出来其他数据块信息可以找到叶子节点的值用来匹配我们需要 delete 值在 begin tree dump branch: 0x (0: nrow: 10, level: 1) leaf: 0x40017a (-1: nrow: 575 rrow: 575) leaf: 0x40017b (0: nrow: 569 rrow: 567) leaf: 0x40017c (1: nrow: 540 rrow: 540) leaf: 0x40017d (2: nrow: 533 rrow: 533) leaf: 0x40017e (3: nrow: 362 rrow: 361) leaf: 0x40017f (4: nrow: 533 rrow: 533) leaf: 0x411d (5: nrow: 533 rrow: 532) leaf: 0x411d (6: nrow: 533 rrow: 533) leaf: 0x411d9a (7: nrow: 533 rrow: 533) leaf: 0x411d9b (8: nrow: 386 rrow: 386) end tree dump

40 SQL> set serveroutput on SQL> declare 2 p_dba VARCHAR2 (255) :='0x00411d9b'; 3 l_str VARCHAR2 (255) DEFAULT NULL; 4 BEGIN 5 l_str := 6 'datafile# is:' 7 DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx')) 8 chr(10) 'datablock is:' 9 DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx')); 10 dbms_output.put_line(l_str); 11 END; 12 / datafile# is:1 datablock is:73115 PL/SQL procedure successfully completed.

41 SQL> alter system dump datafile 1 block 73115; System altered. SQL> select value from v$diag_info where name='default Trace File'; VALUE /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17583.trc -- 找到对应块在叶子节点中的块的信息 Block header dump: 0x00411d9b Object id on Block? Y seg/obj: 0x29 csc: 0x00.c92c9 itc: 2 flg: O typ: 2 - INDEX fsl: 0 fnx: 0x411d9c ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x c6 0x00c0483f.004a.01 CB-- 0 scn 0x a66a1 0x02 0x b3 0x00c e --U- 1 fsc 0x c92cb

42 row#385[2538] flag: , lock: 2, len=13, data:(6): e col 0; len 4; (4): c b -- 对于 ASSM:76+(itc-1)*24 -- 对于 MSSM:68+(itc-1)*24 SQL> select (2-1)*24 from dual; (2-1)* bbed 修改 I_IND1 中记录 ~]$ bbed listfile=listfile mode=edit password=blockedit BBED: Release Limited Production on Thu Aug 9 17:36: Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

43 *************!!! For Oracle Internal Use only!!! *************** BBED> set block BLOCK# BBED> set offset 2630 OFFSET 2630 BBED> x /rn flag@2630: 0x00 (NONE) lock@2631: 0x02 keydata[6]: 0x00 0x41 0x3e 0x74 0x00 0x00 data key: col BBED> set count 64 COUNT 64

44 BBED> d File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 2630 to 2693 Dba:0x00411d9b e c b a c a c a c a c30838 <32 bytes per line> BBED> m /x 01 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 2630 to 2693 Dba:0x00411d9b e c b a c a c a c a c30838 <32 bytes per line>

45 BBED> sum apply Check value for File 1, Block 73115: current = 0xe027, required = 0xe027 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = Block Checking: DBA = , Block Type = KTB-managed data block **** actual free space credit for itl 2 = 15!= # in trans. hdr = 0 <---- 修改 _ktbitfsc 信息 **** actual rows marked deleted = 1!= kdxlende = 0 <---- 修改 kdxlende 信息 ---- end index block validation Block failed with check code 6401 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0

46 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 1 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=rdbms; facility=bbed BBED> map File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Dba:0x00411d9b KTB Data Block (Index Leaf) struct kcbh, 20 struct ktbbh, 72 struct kdxle, 32

47 sb2 ub1 ub1 ub4 BBED> p kdxle struct kdxle, 32 struct kdxlexco, 16 ub1 0x00 ub1 0x00 ub1 0x80 ub1 0x01 ub4 0x sb2 386 sb2 808

48 sb sb sb2 0 sb2 0 <---- 需要修改 ub4 0x ub4 0x00411d9a ub1 0x06 ub1 0x00 (NONE) BBED> d offset 110 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 110 to 173 Dba:0x00411d9b a1d e00601f f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e <32 bytes per line>

49 BBED> m /x 01 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 110 to 173 Dba:0x00411d9b a1d e00601f f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e <32 bytes per line> BBED> p ktbbh struct ktbbh, 72 ub1 0x02 (KDDBTINDEX) union ktbbhsid, 4 ub4 0x ub4 0x struct ktbbhcsc, 8 ub4 0x000c92c9 ub2 0x0000

50 sb2 2 ub1 0x03 (KTBFONFL) ub1 0x00 ub4 0x00411d9c struct ktbbhitl[0], 24 struct ktbitxid, 8 ub2 0x0001 ub2 0x0002 ub4 0x000001c6 struct ktbituba, 8 ub4 0x00c0483f ub2 0x004a ub1 0x01 ub2 0xc000 (KTBFIBI, KTBFCOM) union _ktbitun, 2 sb2 0 ub2 0x0000

51 ub4 0x000a66a1 struct ktbbhitl[1], 24 struct ktbitxid, 8 ub2 0x0006 ub2 0x0009 ub4 0x000002b3 struct ktbituba, 8 ub4 0x00c02389 ub2 0x0075 ub1 0x2e ub2 0x2001 (KTBFUPB) union _ktbitun, 2 sb2 0 <---- 需要修改 ub2 0x0000 ub4 0x000c92cb BBED> d offset 86

52 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 86 to 149 Dba:0x00411d9b cb92 0c ea09 a a1d e00601f f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e <32 bytes per line> BBED> m /x 0f File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: Offsets: 86 to 149 Dba:0x00411d9b f00cb92 0c ea09 a a1d e00601f f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e <32 bytes per line> BBED> sum apply

53 Check value for File 1, Block 73115: current = 0xe029, required = 0xe029 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=rdbms; facility=bbed 启动数据库测试 SQL> startup

54 ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. Database opened. SQL> select count(*) from ind$ where obj#=75558; COUNT(*) SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558; COUNT(*)

55 SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='ind_t_xifenfei'; no rows selected 扫尾和测试工作 SQL> delete from obj$ where obj# =75558; 1 row deleted. SQL> delete from icol$ where obj#=75558; 1 row deleted. SQL> delete from seg$ where ts#=4 and file#=4 and block#=298; 1 row deleted. SQL> commit;

56 Commit complete. -- 重新创建 / 删除一个同名的 index 成功 SQL> create index chf.ind_t_xifenfei on chf.t_xifenfei(object_id); Index created. SQL> drop index chf.ind_t_xifenfei; Index dropped. 通过以上对于 ind$ 和 I_IND1 操作大体上完成对于 ind_t_xifenfei 索引的手工删除, 比较完美的实现了 bbed drop index 操作过程.

作者个人简介程飞 (xifenfei) QQ 号 :107644445 个人博客地址 :www.xifenfei.com 个人新浪微博 :http://weibo.

57 作者个人简介程飞 (xifenfei) QQ 号 : 个人博客地址 : 个人新浪微博 : 曾就职国内主要股票交易软件商核心同花顺, 担任 ORACLE 运维和开发 DBA 现就职于云和恩墨, 负责华东区域 ORACLE 数据库技术支持擅长 ORACLE 数据库各种场景异常恢复, 熟悉 ORACLE 的调优, 排错, 安装实施等热衷于 oracle 技术的研究与分享, 长期坚持通过技术 BLOG 分享 ORACLE 知识服务行业 : 金融, 军队, 保险, 运营商, 物流, 传媒, 支付, 超市, 政府等

三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1

三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1 使用 odu 恢复被 drop 表过程一. 数据库版本 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release