标签归档：i_obj4

分享I_OBJ4 ORA-8102故障恢复案例

发表于 2015 年 3 月 15 日 由 惜分飞

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

发表在 非常规恢复 | 标签为 , | 评论关闭

使用bbed 修复I_OBJ4 index 报ORA-8102错误

发表于 2015 年 3 月 14 日 由 惜分飞

数据库执行创建表操作报ORA-8102错误

SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             469765360 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.

SQL> create table t1 as select * from dba_users;
create table t1 as select * from dba_users
                                 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 87404, file 1, block 97266 (2)

分析ORA-8102错误

SQL> col OBJECT_NAME for a30
SQL> select object_name,object_type from dba_objects where object_id=87404;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
I_OBJ4                         INDEX


SQL> select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$  t  
minus
  2    3  select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$  t1; 

  DATAOBJ#      TYPE#     OWNER#
---------- ---------- ----------
     87420          0          0

SQL> select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$  t1
  2  minus
  3  select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$  t  
  4  ;

  DATAOBJ#      TYPE#     OWNER#
---------- ---------- ----------
     87422          0          0

SQL> alter system dump datafile 1 block 97266;

System altered.

SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_27037.trc


SQL> ALTER SESSION SET EVENTS '802 trace name errorstack level 3';

Session altered.

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# 87404, file 1, block 97266 (2)


SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_27037.trc


*** 2015-03-14 14:46:33.640
kdk key 8102.2:
  ncol: 4, len: 16
  key: (16):  04 c3 09 4b 17 01 80 01 80 06 00 41 7f 25 00 28
  mask: (4096):

*** 2015-03-14 14:46:33.644
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=4yyb4104skrwj) -----
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,
oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and 
remoteowner is null and linkname is null and subname is null

这里可以的出来由于obj$中的dataobj#为87422,而i_obj4中的dataobj#为87420,因此两者不一致。
另外通过相关trace发现,在创建表操作中会调用update obj$的一个递归操作,而该操作会更新dataobj#,但是由于该值在表和index中不匹配,因此出现ORA-08102导致创建表不成功

使用bbed 修复ORA-8102

[oracle@localhost ~]$ bbed blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/xifenfei/system01.dbf'
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 14:55:22 2015

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

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

BBED> set block 97266
        BLOCK#          97266

BBED> f /x 04c3
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2714 to 3225           Dba:0x00000000
------------------------------------------------------------------------
 04c3094a 5f02c115 01800600 417f2500 0f000204 c3094b14 02c11501 80060041 
 7f25000e 000204c3 094b1202 c1140180 0600417f 25000d00 0004c309 4b150180 

 <32 bytes per line>

BBED> f
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2733 to 3244           Dba:0x00000000
------------------------------------------------------------------------
 04c3094b 1402c115 01800600 417f2500 0e000204 c3094b12 02c11401 80060041 
 7f25000d 000004c3 094b1501 80018006 00417f25 00280100 04c3094b 10018001 

 <32 bytes per line>

BBED> f
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2752 to 3263           Dba:0x00000000
------------------------------------------------------------------------
 04c3094b 1202c114 01800600 417f2500 0d000004 c3094b15 01800180 0600417f 
 25002801 0004c309 4b100180 01800600 417f2500 28000004 c3094b08 02c10201 

 <32 bytes per line>

BBED> f
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2771 to 3282           Dba:0x00000000
------------------------------------------------------------------------
 04c3094b 15018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 
 00280000 04c3094b 0802c102 01800600 417f2500 24000004 c3094b09 02c10201 
 
 <32 bytes per line>

BBED> f
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2789 to 3300           Dba:0x00000000
------------------------------------------------------------------------
 04c3094b 10018001 80060041 7f250028 000004c3 094b0802 c1020180 0600417f 
 25002400 0004c309 4b0902c1 02018006 00417f25 00250000 04c3094b 0a02c103 

 <32 bytes per line>


BBED> set count 32
        COUNT           32

BBED> set offset 2771
        OFFSET          2771

BBED> d
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2771 to 2802           Dba:0x00000000
------------------------------------------------------------------------
 04c3094b 15018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 

 <32 bytes per line>

BBED> set offset +4
        OFFSET          2775

BBED> d
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2775 to 2806           Dba:0x00000000
------------------------------------------------------------------------
 15018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 00280000 

 <32 bytes per line>

BBED> m /x 17  
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 2775 to 2806           Dba:0x00000000
------------------------------------------------------------------------
 17018001 80060041 7f250028 010004c3 094b1001 80018006 00417f25 00280000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 97266:
current = 0x7955, required = 0x7955

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf
BLOCK = 97266

Block 97266 is corrupt
Corrupt block relative dba: 0x00417bf2 (file 0, block 97266)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x00417bf2
 last change scn: 0x0000.00102ed8 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x2ed80602
 check value in block header: 0x7955
 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


BBED> set offset 8188
        OFFSET          8188

BBED> d
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0206d82e 

 <32 bytes per line>

BBED> m /x 01
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 97266            Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0106d82e 

 <32 bytes per line>

BBED> sum
Check value for File 0, Block 97266:
current = 0x7955, required = 0x7956

BBED> sum apply
Check value for File 0, Block 97266:
current = 0x7956, required = 0x7956

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf
BLOCK = 97266


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

通过bbed修改i_obj4中的dataobj#值使之和obj$中对应值一致

验证确认ORA-8102被修复

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             469765360 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL>  create table t1 as select * from dual; 

Table created.

通过使用bbed修改index值后,ORA-8102问题解决,可以执行创建表操作
姊妹篇见:通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误

发表在 Oracle备份恢复 | 标签为 , , , | 评论关闭

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

发表于 2015 年 3 月 14 日 由 惜分飞

在最近的数据库恢复中,经历了多次11.2库由于各种原因,数据库打开后,报ORA-8102错误,而且错误对象是OBJ$上的I_OBJ4这个index上,而且不能创建新表,周末开会闲着无事,进行了一个简单的模拟重现
数据库版本信息11.2.0.4

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

定位需要破坏的OBJ$上记录,为了使之和I_OBJ4中记录不一致,从而实现ORA-8102错误

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

 OBJECT_ID OBJECT_TYPE
---------- -------------------
     87404 INDEX

SQL> select max(DATAOBJ#) from obj$;

MAX(DATAOBJ#)
-------------
        87420

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

DUMP(87420,16)
-----------------------
Typ=2 Len=4: c3,9,4b,15

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,
dbms_rowid.rowid_row_number(rowid) row#
  2    3  from obj$ where DATAOBJ#=87420;

     FILE#     BLOCK#       ROW#
---------- ---------- ----------
         1      98085         40

SQL>  alter system dump datafile 1  block 98085;

System altered.

SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_26373.trc

--dump该记录显示
tab 0, row 40, @0x11fc
tl: 72 fb: --H-FL-- lb: 0x0  cc: 18
col  0: [ 2]  c1 02
col  1: [ 4]  c3 09 4b 15
col  2: [ 1]  80
col  3: [12]  5f 4e 45 58 54 5f 4f 42 4a 45 43 54
col  4: [ 2]  c1 02
col  5: *NULL*
col  6: [ 1]  80
col  7: [ 7]  78 71 08 18 0c 26 24
col  8: [ 7]  78 73 03 0d 15 2e 2b
col  9: [ 7]  78 71 08 18 0c 26 24
col 10: [ 1]  80
col 11: *NULL*
col 12: *NULL*
col 13: [ 1]  80
col 14: *NULL*
col 15: [ 1]  80
col 16: [ 4]  c3 07 38 24
col 17: [ 1]  80
tab 0, row 41, @0x9af
tl: 2 fb: --HDFL-- lb: 0x2

这里我们知道i_obj4中的dataobj#最大值为87420对应的16进制记录为04 c3 09 4b 15

使用bbed破坏记录,修改dataobj#中的值,使得obj$.dataobj#和i_obj4中的dataobj#不匹配

SQL> select name from v$datafile where file#=1;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/xifenfei/system01.dbf


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


[oracle@localhost ~]$ bbed blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/xifenfei/system01.dbf'
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 14:23:02 2015

Copyright (c) 1982, 2011, 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/xifenfei/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 98085
        BLOCK#          98085
BBED> p *kdbr[40]
rowdata[2446]
-------------
ub1 rowdata[2446]                           @4696     0x2c


BBED> x /rnnncnnncc
rowdata[2446]                               @4696    
-------------
flag@4696: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4697: 0x00
cols@4698:   18

col    0[2] @4699: 1 
col    1[4] @4702: 87420 
col    2[1] @4707: 0 
col   3[12] @4709: _NEXT_OBJECT
col    4[2] @4722: 1 
col    5[0] @4725: *NULL*
col    6[1] @4726: 0 
col    7[7] @4728: xq...&$
col    8[7] @4736: xs....+
col    9[7] @4744: xq...&$
col   10[1] @4752: .
col   11[0] @4754: *NULL*
col   12[0] @4755: *NULL*
col   13[1] @4756: .
col   14[0] @4758: *NULL*
col   15[1] @4759: .
col   16[4] @4761: Ã.8$
col   17[1] @4766: .


BBED> set block 98085
        BLOCK#          98085

BBED> set offset 4702
        OFFSET          4702

BBED> set count 32
        COUNT           32

BBED> d
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 98085            Offsets: 4702 to 4733           Dba:0x00000000
------------------------------------------------------------------------
 04c3094b 1501800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 7108180c 

 <32 bytes per line>

BBED> set offset +4
        OFFSET          4706

BBED> d
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 98085            Offsets: 4706 to 4737           Dba:0x00000000
------------------------------------------------------------------------
 1501800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 7108180c 26240778 

 <32 bytes per line>

BBED> m /x 17 
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 98085            Offsets: 4706 to 4737           Dba:0x00000000
------------------------------------------------------------------------
 1701800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 7108180c 26240778 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 98085:
current = 0xd361, required = 0xd361

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf
BLOCK = 98085


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
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

使用bbed 修改04 c3 09 4b 15为04 c3 09 4b 17

重现在obj$的I_OBJ4 index上报ORA-8102错误,而且不能创建新对象

SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             469765360 bytes
Database Buffers         1090519040 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.

SQL> create table t1 as select * from dba_users;
create table t1 as select * from dba_users
                                 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 87404, file 1, block 97266 (2)

SQL> col OBJECT_NAME for a30
SQL> select object_name,object_type from dba_objects where object_id=87404;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
I_OBJ4                         INDEX

如果修复该问题请见:使用bbed 修复I_OBJ4 index 报ORA-8102

发表在 Oracle | 标签为 , , , | 评论关闭