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

作者:袖梨 2022-06-29


在测试环境中对于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错误解决处理实现完美恢复

相关文章

精彩推荐