How to Find SHOUG? - PDF 免费下载

bbed 使用实现 drop index 操作 by SHOUG.XIFENFEI 个人博客地址 :www.xifenfei.com 个人新浪微博 :http://weibo.com/u/1820095585

How to Find SHOUG?

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

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 ---------- 75558 SQL> select obj#,dataobj#,ts#,file#,block#,bo#,flags from sys.ind$ where obj#=75558; OBJ# DATAOBJ# TS# FILE# BLOCK# BO# FLAGS ---------- ---------- ---------- ---------- ---------- ---------- ---------- 75558 75558 4 4 298 75557 2 SQL> select rowid,

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 1 81524 0 SQL> alter system checkpoint; System altered. SQL> select dump(75558,'16') from dual; DUMP(75558,'16') ----------------------- Typ=2 Len=4: c3,8,38,3b

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.

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

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

struct kdbt[6], 24 bytes @106 sb2 kdbr[33] @130 ub1 freespace[5420] @196 ub1 rowdata[2572] @5616 ub4 tailchk @8188 BBED> p kdbr sb2 kdbr[0] @130 8074 sb2 kdbr[1] @132 7987 sb2 kdbr[2] @134 7896 sb2 kdbr[3] @136 7618 sb2 kdbr[4] @138 7523 sb2 kdbr[5] @140 6700 sb2 kdbr[6] @142 6573 sb2 kdbr[7] @144 5524

sb2 kdbr[8] @146 5633 sb2 kdbr[9] @148-1 sb2 kdbr[10] @150 7771 sb2 kdbr[11] @152 7703 sb2 kdbr[12] @154 7642 sb2 kdbr[13] @156 7546 sb2 kdbr[14] @158 7459 sb2 kdbr[15] @160 7397 sb2 kdbr[16] @162 7330 sb2 kdbr[17] @164 7267 sb2 kdbr[18] @166 6516 sb2 kdbr[19] @168 6450 sb2 kdbr[20] @170 6384 sb2 kdbr[21] @172 6327 sb2 kdbr[22] @174 6265 sb2 kdbr[23] @176 6202 sb2 kdbr[24] @178 6147 sb2 kdbr[25] @180 6086 sb2 kdbr[26] @182 6025 sb2 kdbr[27] @184 5967 sb2 kdbr[28] @186 5906 sb2 kdbr[29] @188 5845 sb2 kdbr[30] @190 5784

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

BBED> p *kdbr[1] rowdata[2463] ------------- ub1 rowdata[2463] @8079 0xac BBED> x /rn rowdata[2463] @8079 ------------- 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 0[3] @8098: 330 BBED> p *kdbr[2] rowdata[2372]

------------- ub1 rowdata[2372] @7988 0xac BBED> x /rn rowdata[2372] @7988 ------------- 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 0[3] @8007: 471 BBED> p *kdbr[3] rowdata[2094] ------------- ub1 rowdata[2094] @7710 0xac

BBED> x /rn rowdata[2094] @7710 ------------- 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 0[4] @7729: 59484 BBED> p *kdbr[4] rowdata[1999] ------------- ub1 rowdata[1999] @7615 0xac BBED> x /rn rowdata[1999] @7615 ------------- flag@7615: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@7616: 0x00 cols@7617: 1 kref@7618: 4 mref@7620: 4 hrid@7622:0x00403371.6 nrid@7628:0x00403371.6 col 0[3] @7634: 8871 BBED> p *kdbr[5] rowdata[1176] ------------- ub1 rowdata[1176] @6792 0xac BBED> x /rn rowdata[1176] @6792 ------------- 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 col 0[4] @6811: 75557 BBED> p *kdbr[6] rowdata[1049] ------------- ub1 rowdata[1049] @6665 0x6c BBED> x /rn rowdata[1049] @6665 ------------- flag@6665: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@6666: 0x00 cols@6667: 36 ckix@6668: 5 col 0[4] @6669: 75557 col 1[2] @6674: 4 col 2[2] @6677: 4

col 3[3] @6680: 170 col 4[0] @6684: *NULL* col 5[0] @6685: *NULL* col 6[2] @6686: 15 col 7[0] @6689: *NULL* col 8[2] @6690: 10 col 9[2] @6693: 40 col 10[2] @6696: 1 col 11[3] @6699: 255 col 12[6] @6703: 1073741825 col 13[38] @6710: 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 14[0] @6749: *NULL* col 15[0] @6750: *NULL* col 16[0] @6751: *NULL* col 17[0] @6752: *NULL* col 18[0] @6753: *NULL* col 19[0] @6754: *NULL* col 20[0] @6755: *NULL* col 21[0] @6756: *NULL*

col 22[0] @6757: *NULL* col 23[0] @6758: *NULL* col 24[0] @6759: *NULL* col 25[0] @6760: *NULL* col 26[2] @6761: 15 col 27[2] @6764: 15 col 28[6] @6767: 536870912 col 29[1] @6774: 0 col 30[3] @6776: 736 col 31[0] @6780: *NULL* col 32[0] @6781: *NULL* col 33[0] @6782: *NULL* col 34[0] @6783: *NULL* col 35[7] @6784: ######################################### BBED> p *kdbr[7] rowdata[0] ---------- ub1 rowdata[0] @5616 0x6c BBED> x /rn rowdata[0] @5616

---------- flag@5616: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@5617: 0x02 cols@5618: 33 ckix@5619: 5 col 0[4] @5620: 75558 col 1[4] @5625: 75558 col 2[2] @5630: 4 col 3[2] @5633: 4 col 4[3] @5636: 298 col 5[1] @5640: 0 col 6[2] @5642: 1 col 7[2] @5645: 10 col 8[2] @5648: 2 col 9[3] @5651: 255 col 10[0] @5655: *NULL* col 11[2] @5656: 1 col 12[2] @5659: 2 col 13[1] @5662: 0 col 14[2] @5664: 1 col 15[3] @5667: 165

col 16[4] @5671: 74491 col 17[2] @5676: 1 col 18[2] @5679: 1 col 19[3] @5682: 1720 col 20[7] @5686: ######################################### col 21[4] @5694: 74491 col 22[4] @5699: 74491 col 23[2] @5704: 1 col 24[0] @5707: *NULL* col 25[0] @5708: *NULL* col 26[0] @5709: *NULL* col 27[2] @5710: 1 col 28[0] @5713: *NULL* col 29[0] @5714: *NULL* col 30[0] @5715: *NULL* col 31[0] @5716: *NULL* col 32[7] @5717: ######################################### BBED> set count 64 COUNT 64 BBED> d

File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 81524 Offsets: 5616 to 5679 Dba:0x00413e74 ------------------------------------------------------------------------ 6c022105 04c30838 3b04c308 383b02c1 0502c105 03c20363 018002c1 0202c10b 02c10303 c20338ff 02c10202 c1030180 02c10203 c2024204 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: 81524 Offsets: 5616 to 5679 Dba:0x00413e74 ------------------------------------------------------------------------ 7c022105 04c30838 3b04c308 383b02c1 0502c105 03c20363 018002c1 0202c10b 02c10303 c20338ff 02c10202 c1030180 02c10203 c2024204 c3082d5c 02c10202 <32 bytes per line>

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 = 81524 Block Checking: DBA = 4275828, Block Type = KTB-managed data block data header at 0xb53c625c kdbchk: key comref count wrong keyslot=5 Block 81524 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

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 rowdata[1176] @6792 0xac BBED> x /rn rowdata[1176] @6792 ------------- 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

col 0[4] @6811: 75557 BBED> d offset 6797 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 81524 Offsets: 6797 to 6860 Dba:0x00413e74 ------------------------------------------------------------------------ 12000041 3e740005 00413e74 000504c3 08383a7c 02140502 c10602c1 0602c102 01800d52 4f4c4c42 41434b5f 4f4e4c59 02c16102 c1020180 ffff0180 ffff02c1 <32 bytes per line> BBED> m /x 11 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 81524 Offsets: 6797 to 6860 Dba:0x00413e74 ------------------------------------------------------------------------ 11000041 3e740005 00413e74 000504c3 08383a7c 02140502 c10602c1 0602c102 01800d52 4f4c4c42 41434b5f 4f4e4c59 02c16102 c1020180 ffff0180 ffff02c1

<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 = 81524 Block Checking: DBA = 4275828, 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 81524 failed with check code 6110 DBVERIFY - Verification complete 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> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) sb1 kdbhntab @93 6 sb2 kdbhnrow @94 33 sb2 kdbhfrre @96 9 sb2 kdbhfsbo @98 104 sb2 kdbhfseo @100 5524 sb2 kdbhavsp @102 6156 sb2 kdbhtosp @104 6156

BBED> d offset 102 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 81524 Offsets: 102 to 165 Dba:0x00413e74 ------------------------------------------------------------------------ 0c180c18 00000600 06000100 07000000 07000100 08000200 0a001700 8a1f331f d81ec21d 631d2c1a ad199415 0116ffff 5b1e171e da1d7a1d 231de51c a21c631c <32 bytes per line> BBED> m /x 7518 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 81524 Offsets: 102 to 165 Dba:0x00413e74 ------------------------------------------------------------------------ 75180c18 00000600 06000100 07000000 07000100 08000200 0a001700 8a1f331f d81ec21d 631d2c1a ad199415 0116ffff 5b1e171e da1d7a1d 231de51c a21c631c <32 bytes per line>

BBED> m /x 7518 offset 104 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 81524 Offsets: 104 to 167 Dba:0x00413e74 ------------------------------------------------------------------------ 75180000 06000600 01000700 00000700 01000800 02000a00 17008a1f 331fd81e c21d631d 2c1aad19 94150116 ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419 <32 bytes per line> BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) sb1 kdbhntab @93 6 sb2 kdbhnrow @94 33 sb2 kdbhfrre @96 9 sb2 kdbhfsbo @98 104 sb2 kdbhfseo @100 5524

sb2 kdbhavsp @102 6261 sb2 kdbhtosp @104 6261 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 = 81524 Block Checking: DBA = 4275828, 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 81524 failed with check code 6111 DBVERIFY - Verification complete

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: 81524 Offsets: 104 to 167 Dba:0x00413e74 ------------------------------------------------------------------------ 79180000 06000600 01000700 00000700 01000800 02000a00 17008a1f 331fd81e c21d631d 2c1aad19 94150116 ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419 <32 bytes per line> BBED> sum apply

Check value for File 1, Block 81524: current = 0x8bb2, required = 0x8bb2 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 81524 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

ORACLE instance started. Total System Global Area 230162432 bytes Fixed Size 1344088 bytes Variable Size 88083880 bytes Database Buffers 134217728 bytes Redo Buffers 6516736 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;

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

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

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

--------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------- 0 SELECT STATEMENT 1 5 206 (0) 00:00:03 1 SORT AGGREGATE 1 5 * 2 TABLE ACCESS FULL IND$ 1 5 206 (0) 00:00:03 --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - 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;

OBJ# DATAOBJ# TS# FILE# BLOCK# BO# FLAGS ROWID ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------ 75558 75558 4 4 298 75557 2 AAAAACAABAAAT50AAA SQL> select count(*) from ind$ where obj#=75558; COUNT(*) ---------- 1 SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558; COUNT(*) ---------- 0 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';

INDEX_OWNER COLUMN_NAME INDEX_NAME ------------------------------ --------------- ------------------------------ SYS OBJ# I_IND1 通过上面的查询我们可以知道 ind$ 本身有一个关于 obj# 列的 index, 当我们查询使用该 index 的时候出现上面的 ora-600[kdsgrp1] 错误. 而因为 ind$ 相关 index 是 bootstarp$ 中对象, 不能直接或者 upgrade, 甚至 event 38003 都不能 drop 或者 rebuid 分析 I_IND1 索引信息 SQL> select obj#,dataobj# from obj$ where name='i_ind1'; OBJ# DATAOBJ# ---------- ---------- 41 41 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';

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

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.

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 0x0001.002.000001c6 0x00c0483f.004a.01 CB-- 0 scn 0x0000.000a66a1 0x02 0x0006.009.000002b3 0x00c02389.0075.2e --U- 1 fsc 0x0000.000c92cb

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

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

BBED> d File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 73115 Offsets: 2630 to 2693 Dba:0x00411d9b ------------------------------------------------------------------------ 00020041 3e740000 04c30838 3b000000 40264a00 0104c308 38380100 0040264a 000004c3 08383701 00004026 4a000004 c3083832 01000040 264a0001 04c30838 <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: 73115 Offsets: 2630 to 2693 Dba:0x00411d9b ------------------------------------------------------------------------ 01020041 3e740000 04c30838 3b000000 40264a00 0104c308 38380100 0040264a 000004c3 08383701 00004026 4a000004 c3083832 01000040 264a0001 04c30838 <32 bytes per line>

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 = 73115 Block Checking: DBA = 4267419, 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 73115 failed with check code 6401 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): 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: 73115 Dba:0x00411d9b ------------------------------------------------------------ KTB Data Block (Index Leaf) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdxle, 32 bytes @92

sb2 kd_off[386] @124 ub1 freespace[1730] @896 ub1 rowdata[5494] @2626 ub4 tailchk @8188 BBED> p kdxle struct kdxle, 32 bytes @92 struct kdxlexco, 16 bytes @92 ub1 kdxcolev @92 0x00 ub1 kdxcolok @93 0x00 ub1 kdxcoopc @94 0x80 ub1 kdxconco @95 0x01 ub4 kdxcosdc @96 0x00000001 sb2 kdxconro @100 386 sb2 kdxcofbo @102 808

sb2 kdxcofeo @104 2538 sb2 kdxcoavs @106 2210 sb2 kdxlespl @108 0 sb2 kdxlende @110 0 <---- 需要修改 ub4 kdxlenxt @112 0x00000000 ub4 kdxleprv @116 0x00411d9a ub1 kdxledsz @120 0x06 ub1 kdxleflg @121 0x00 (NONE) BBED> d offset 110 File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 73115 Offsets: 110 to 173 Dba:0x00411d9b ------------------------------------------------------------------------ 00000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e <32 bytes per line>

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

sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x03 (KTBFONFL) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00411d9c struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0001 ub2 kxidslt @46 0x0002 ub4 kxidsqn @48 0x000001c6 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c0483f ub2 kubaseq @56 0x004a ub1 kubarec @58 0x01 ub2 ktbitflg @60 0xc000 (KTBFIBI, KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000

ub4 ktbitbas @64 0x000a66a1 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006 ub2 kxidslt @70 0x0009 ub4 kxidsqn @72 0x000002b3 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c02389 ub2 kubaseq @80 0x0075 ub1 kubarec @82 0x2e ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 <---- 需要修改 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x000c92cb BBED> d offset 86

File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 73115 Offsets: 86 to 149 Dba:0x00411d9b ------------------------------------------------------------------------ 0000cb92 0c000000 80010100 00008201 2803ea09 a2080000 01000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e <32 bytes per line> BBED> m /x 0f File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 73115 Offsets: 86 to 149 Dba:0x00411d9b ------------------------------------------------------------------------ 0f00cb92 0c000000 80010100 00008201 2803ea09 a2080000 01000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e <32 bytes per line> BBED> sum apply

Check value for File 1, Block 73115: current = 0xe029, required = 0xe029 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 73115 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

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

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;

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.com/u/1820095585 曾就职国内主要股票交易软件商核心同花顺, 担任 ORACLE 运维和开发 DBA 现就职于云和恩墨, 负责华东区域 ORACLE 数据库技术支持擅长 ORACLE 数据库各种场景异常恢复, 熟悉 ORACLE 的调优, 排错, 安装实施等热衷于 oracle 技术的研究与分享, 长期坚持通过技术 BLOG 分享 ORACLE 知识服务行业 : 金融, 军队, 保险, 运营商, 物流, 传媒, 支付, 超市, 政府等