下面我们一起来看看关于oracle中OBJ4 ORA-8102故障恢复案例，希望本文章可以帮助到各位朋友。

　　在测试环境中对于OBJ$中i_obj4中出现ORA-8102进行了重新并恢复测试,认为自己已经比较清楚的掌握了I_OBJ4的ORA-8102问题处理,可是实际的一个案例,还是比较比实验中复杂,这里贴出来主要操作供大家参考,再次证明数据库恢复的场景不可大意,客户的故障只有你想不到的,没有遇不到的

　　通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误

　　使用bbed 修复I_OBJ4 index 报ORA-8102

　　数据库创建表提示ORA-8102错误

　　SQL> startup

　　ORACLE instance started.

　　Total System Global Area 2.6991E+10 bytes

　　Fixed Size 2213976 bytes

　　Variable Size 1.9327E+10 bytes

　　Database Buffers 7516192768 bytes

　　Redo Buffers 145174528 bytes

　　Database mounted.

　　Database opened.

　　SQL> create table t1 as select * from dual;

　　create table t1 as select * from dual

　　*

　　ERROR at line 1:

　　ORA-00604: error occurred at recursive SQL level 1

　　ORA-08102: index key not found, obj# 39, file 1, block 93842 (2)

　　分析ORA-08102错误

　　SQL> select object_name,object_type from dba_objects where object_id=39;

　　OBJECT_NAME OBJECT_TYPE

　　------------------------------ -------------------

　　I_OBJ4 INDEX

　　SQL> create table t1 as select * from dual;

　　create table t1 as select * from dual

　　*

　　ERROR at line 1:

　　ORA-00604: error occurred at recursive SQL level 1

　　ORA-08102: index key not found, obj# 39, file 1, block 93842 (2)

　　SQL> select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t

　　minus

　　select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1;

　　2 3

　　DATAOBJ# TYPE# OWNER#

　　---------- ---------- ----------

　　97109 0 0

　　SQL> select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1

　　minus

　　select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t

　　;

　　2 3 4

　　DATAOBJ# TYPE# OWNER#

　　---------- ---------- ----------

　　97094 0 0

　　SQL> SET LINES 122

　　COL INDEX_OWNER FOR A20

　　COL INDEX_NAME FOR A30

　　COL TABLE_OWNER FOR A20

　　COL COLUMN_NAME FOR A25

　　SELECT TABLE_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION

　　FROM Dba_Ind_Columns

　　WHERE table_name = upper('&TABLE_NAME') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION

　　and index_name='I_OBJ4';

　　SQL> SQL> SQL> SQL> SQL> 2 3

　　Enter value for table_name: OBJ$

　　old 3: WHERE table_name = upper('&TABLE_NAME') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION

　　new 3: WHERE table_name = upper('OBJ$') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION

　　TABLE_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION

　　-------------------- ------------------------------ ------------------------- ---------------

　　SYS I_OBJ4 DATAOBJ# 1

　　SYS I_OBJ4 TYPE# 2

　　SYS I_OBJ4 OWNER# 3

　　SQL> SELECT DATAOBJ# FROM OBJ$ WHERE OBJ#=97109;

　　no rows selected

　　SQL> SELECT DATAOBJ# FROM OBJ$ WHERE OBJ#=97094;

　　DATAOBJ#

　　----------

　　97094

　　SQL> select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t

　　minus

　　select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1;

　　2 3

　　ROWID DATAOBJ# TYPE# OWNER#

　　------------------ ---------- ---------- ----------

　　AAAAASAABAAAADxAAb 97109 0 0

　　SQL> select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1

　　minus

　　select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t

　　;

　　2 3 4

　　ROWID DATAOBJ# TYPE# OWNER#

　　------------------ ---------- ---------- ----------

　　AAAAASAABAAAADxAAb 97094 0 0

　　SQL> select name,obj#,dataobj# from obj$ where rowid='AAAAASAABAAAADxAAb';

　　NAME OBJ# DATAOBJ#

　　------------------------------ ---------- ----------

　　_NEXT_OBJECT 1 97094

　　到此也比较清楚,rowid为AAAAASAABAAAADxAAb的dataobj#记录在obj$表中为97094而在I_OBJ4中记录为97109,因此两者不一致,从而出现ORA-8102错误

　　尝试bbed解决ORA-8102问题

　　尝试修改obj$和i_obj4中的dataobj#记录一致，这里修改obj$中的对应记录

　　SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,

　　dbms_rowid.rowid_row_number(rowid) row#

　　from obj$ where rowid='AAAAASAABAAAADxAAb' 2 3

　　4 /

　　FILE# BLOCK# ROW#

　　---------- ---------- ----------

　　1 241 27

　　SQL> select dump(97109,16) from dual;

　　DUMP(97109,16)

　　----------------------

　　Typ=2 Len=4: c3,a,48,a

　　SQL> select dump(97094,16) from dual;

　　DUMP(97094,16)

　　-----------------------

　　Typ=2 Len=4: c3,a,47,5f

　　-bash-4.1$ bbed blocksize=8192 mode=edit filename=/u01/app/oracle/oradata/oa/system01.dbf

　　Password:

　　BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 19:30:18 2015

　　Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

　　************* !!! For Oracle Internal Use only !!! ***************

　　BBED> show all

　　FILE# 0

　　BLOCK# 1

　　OFFSET 0

　　DBA 0x00000000 (0 0,1)

　　FILENAME /u01/app/oracle/oradata/oa/system01.dbf

　　BIFILE bifile.bbd

　　LISTFILE

　　BLOCKSIZE 8192

　　MODE Edit

　　EDIT Unrecoverable

　　IBASE Dec

　　OBASE Dec

　　WIDTH 80

　　COUNT 512

　　LOGFILE log.bbd

　　SPOOL No

　　BBED> set block 241

　　BLOCK# 241

　　BBED> map

　　File: /u01/app/oracle/oradata/oa/system01.dbf (0)

　　Block: 241 Dba:0x00000000

　　------------------------------------------------------------

　　KTB Data Block (Table/Cluster)

　　struct kcbh, 20 bytes @0

　　struct ktbbh, 48 bytes @20

　　struct kdbh, 14 bytes @68

　　struct kdbt[1], 4 bytes @82

　　sb2 kdbr[105] @86

　　ub1 freespace[87] @296

　　ub1 rowdata[7805] @383

　　ub4 tailchk @8188

　　BBED> p *kdbr[27]

　　rowdata[0]

　　----------

　　ub1 rowdata[0] @383 0x2c

　　BBED> x /rnnncnnncc

　　rowdata[0] @383

　　----------

　　flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH)

　　lock@384: 0x00

　　cols@385: 18

　　col 0[2] @386: 1

　　col 1[4] @389: 97094

　　col 2[1] @394: 0

　　col 3[12] @396: _NEXT_OBJECT

　　col 4[2] @409: 1

　　col 5[0] @412: *NULL*

　　col 6[1] @413: 0

　　col 7[7] @415: xm....4

　　col 8[7] @423: xs....6

　　col 9[7] @431: xm....4

　　col 10[1] @439: .

　　col 11[0] @441: *NULL*

　　col 12[0] @442: *NULL*

　　col 13[1] @443: .

　　col 14[0] @445: *NULL*

　　col 15[1] @446: .

　　col 16[4] @448: ..8$

　　col 17[1] @453: .

　　BBED> set count 32

　　COUNT 32

　　BBED> set offset 389

　　OFFSET 389

　　BBED> d

　　File: /u01/app/oracle/oradata/oa/system01.dbf (0)

　　Block: 241 Offsets: 389 to 420 Dba:0x00000000

　　----------------------------------------