下面我們一起來看看關(guān)于oracle中OBJ4 ORA-8102故障恢復(fù)案例，希望本文章可以幫助到各位朋友。

在測試環(huán)境中對于OBJ$中i_obj4中出現(xiàn)ORA-8102進(jìn)行了重新并恢復(fù)測試,認(rèn)為自己已經(jīng)比較清楚的掌握了I_OBJ4的ORA-8102問題處理,可是實際的一個案例,還是比較比實驗中復(fù)雜,這里貼出來主要操作供大家參考,再次證明數(shù)據(jù)庫恢復(fù)的場景不可大意,客戶的故障只有你想不到的,沒有遇不到的

通過bbed修改obj$中dataobj$重現(xiàn)I_OBJ4索引報ORA-08102錯誤

使用bbed 修復(fù)I_OBJ4 index 報ORA-8102

數(shù)據(jù)庫創(chuàng)建表提示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,因此兩者不一致,從而出現(xiàn)ORA-8102錯誤

嘗試bbed解決ORA-8102問題

嘗試修改obj$和i_obj4中的dataobj#記錄一致，這里修改obj$中的對應(yīng)記錄

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

----------

: 0x2c (KDRHFL, KDRHFF, KDRHFH)

: 0x00

: 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

----------

: 0x2c (KDRHFL, KDRHFF, KDRHFH)

: 0x00

: 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$中對應(yīng)記錄已經(jīng)一致(條數(shù)和數(shù)據(jù)值),但是依然不能執(zhí)行創(chuàng)建表操作,依舊報ORA-8102錯誤.

進(jìn)一步分析錯誤原因

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查詢我們已經(jīng)的出來在obj$中有dataobj#為97109,而通過max(dataobj#)只有96815

分析max(dataobj#)執(zhí)行計劃

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)執(zhí)行計劃,從而的出來最大值為96815,

--而我們從ORA-8102錯誤中可以看到index中有dataobj#為97109,證明index中的鏈表可能出問題

--為什么懷疑是鏈表有問題呢?因為該index的ffs正常

嘗試把ORA-8102報錯標(biāo)記壞塊嘗試

并且通過event和隱含參數(shù)屏蔽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轉(zhuǎn)變?yōu)榱薕RA-1578

修復(fù)好制造壞塊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$中相應(yīng)記錄無法解決該問題,因為還有鏈表異常

3. 通過標(biāo)記為壞塊也無法繞過該問題

由于后續(xù)如果繼續(xù)修復(fù)修復(fù)i_obj4可能工作量過大,而且可以也比較急,通過人工直接刪除I_OBJ4數(shù)據(jù)字典記錄,然后結(jié)合bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)異常恢復(fù)—ORA-00701錯誤解決處理實現(xiàn)完美恢復(fù)

更多信息請查看IT技術(shù)專欄