oracle中OBJ4 ORA-8102故障恢、复案例

 　　下面我们一起来看看关于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

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