下面我們一起來看看關於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
------------------------------------------------------------------------
04c30a47 5f01800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 6d080f01
<32 bytes per line>
BBED> set offset +3
OFFSET 392
BBED> d
File: /u01/app/oracle/oradata/oa/system01.dbf (0)
Block: 241 Offsets: 392 to 423 Dba:0x00000000
------------------------------------------------------------------------
475f0180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786d080f 01113407
<32 bytes per line>
BBED> m /x 480a
File: /u01/app/oracle/oradata/oa/system01.dbf (0)
Block: 241 Offsets: 392 to 423 Dba:0x00000000
------------------------------------------------------------------------
480a0180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786d080f 01113407
<32 bytes per line>
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: 97109
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> sum apply
Check value for File 0, Block 241:
current = 0x913d, required = 0x913d
驗證bbed修改後效果
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> 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
no rows selected
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
no rows selected
SQL> ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
SQL> create table t_xifenfei as select * from dual;
create table t_xifenfei 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)
這裡比較悲劇,我們看到i_obj4和obj$中對應記錄已經一致(條數和數據值),但是依然不能執行創建表操作,依舊報ORA-8102錯誤.
進一步分析錯誤原因
SQL> ALTER SESSION SET EVENTS '802 trace name errorstack level 3';
Session altered.
SQL> create table t as select * from dual;
create table t 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 value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6163.trc
oer 8102.2 - obj# 39, rdba: 0x00416e92(afn 1, blk# 93842)
kdk key 8102.2:
ncol: 4, len: 16
key: (16): 04 c3 0a 48 0a 01 80 01 80 06 00 40 00 f1 00 1b
--這裡可以看出來,提示ORA-8102錯誤依舊在I_OBJ4,97109記錄上
SQL> select max(dataobj#) from obj$;
MAX(DATAOBJ#)
-------------
96815
SQL> select name,obj#,dataobj# from obj$ where rowid='AAAAASAABAAAADxAAb';
NAME OBJ# DATAOBJ#
------------------------------ ---------- ----------
_NEXT_OBJECT 1 97109
--這裡很奇怪，通過rowid查詢我們已經的出來在obj$中有dataobj#為97109,而通過max(dataobj#)只有96815
分析max(dataobj#)執行計劃
SQL> SET AUTOT TRACE
SQL> select max(dataobj#) from obj$;
Execution Plan
----------------------------------------------------------
Plan hash value: 721075849
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_OBJ4 | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--這裡知曉,由於max(dataobj#)使用了INDEX FULL SCAN (MIN/MAX)執行計劃,從而的出來最大值為96815,
--而我們從ORA-8102錯誤中可以看到index中有dataobj#為97109,證明index中的鏈表可能出問題
--為什麼懷疑是鏈表有問題呢?因為該index的ffs正常
嘗試把ORA-8102報錯標記壞塊嘗試
並且通過event和隱含參數屏蔽index壞塊
-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 20:30:58 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 93842
BLOCK# 93842
BBED> set offset 8188
OFFSET 8188
BBED> d
File: /u01/app/oracle/oradata/oa/system01.dbf (0)
Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
010675ad
<32 bytes per line>
BBED> m /x 02
File: /u01/app/oracle/oradata/oa/system01.dbf (0)
Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
020675ad
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 93842:
current = 0x9186, required = 0x9186
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/oa/system01.dbf
BLOCK = 93842
Block 93842 is corrupt
Corrupt block relative dba: 0x00416e92 (file 0, block 93842)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x00416e92
last change scn: 0x0000.c007ad75 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xad750602
check value in block header: 0x9186
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 14 20:33:19 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/tmp/pfile'
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 t as select * from dual;
create table t as select * from dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 93842)
ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf'
通過這一步測試證明,在該ora-8102的錯誤中,通過壞塊是無法解決繞過去該錯誤,只是把錯誤從ORA-8102轉變為了ORA-1578
修復好制造壞塊block
BBED> m /x 01
File: /u01/app/oracle/oradata/oa/system01.dbf (0)
Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
010675ad
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 93842:
current = 0x9185, required = 0x9185
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/oa/system01.dbf
BLOCK = 93842
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> shutdown abort
ORACLE instance shut down.
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)
至此我們大體出來信息：
1. ORA-8102的是I_OBJ4中的_NEXT_OBJECT記錄異常和該index鏈表異常
2. 通過bbed修改I_OBJ4和obj$中相應記錄無法解決該問題,因為還有鏈表異常
3. 通過標記為壞塊也無法繞過該問題
由於後續如果繼續修復修復i_obj4可能工作量過大,而且可以也比較急,通過人工直接刪除I_OBJ4數據字典記錄,然後結合bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)異常恢復—ORA-00701錯誤解決處理實現完美恢復
