数据库执行创建表操作报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错误
