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

在最近的数据库恢复中,经历了多次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