联系：手机/微信(+86 17813235971) QQ(107644445)
标题：通过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
如果修复该问题请见:使用bbed 修复I_OBJ4 index 报ORA-8102