使用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 | 标签为 , , , | 评论关闭

注意系统bug—linux在E5、E5 V2、E7 V2 cpu之上的bug 765720

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

今天晚上群里面兄弟说了一个linux 6上面bug,会导致系统在运行200天以上(hardware uptime),然后进行热重启后,可能在几分钟或者几个小时内出发该bug,导致系统异常.

主要影响条件为:
Red Hat Enterprise Linux 6.1 (kernel-2.6.32-131.26.1.el6 and newer)
Red Hat Enterprise Linux 6.2 (kernel-2.6.32-220.4.2.el6 and newer)
Red Hat Enterprise Linux 6.3 (kernel-2.6.32-279 series)
Red Hat Enterprise Linux 6.4 (kernel-2.6.32-358 series)
Any Intel® Xeon® E5, Intel® Xeon® E5 v2, or Intel® Xeon® E7 v2 series processor
从这里可以看出来该问题主要影响E5、E5 V2、E7 V2 cpu上的redhat 6.1-6.4版本,在6.5版本中修复,具体参考:bug 765720
另外对已ORACLE Linux,如果使用EL Kernel影响和redhat一致,如果使用Unbreakable Enterprise Kernel则在6.2版本中进行了修复该问题。
MOS上类似文章：Oracle Linux 6 RHCK system hang: processes blocked in ext4_file_open(), pick_next_task_fair()

补充说明：
1. 在Red Hat/OEL 5.x版本中不存在。
2. 在32和64位操作系统都有可能发生
3. 鉴于该bug短期内无法修复,而且真的发生了,考虑冷重启主机,临时规避

再次提醒:系统版本选定也很重要,大家在选择Linux版本之时尽量选择避开该bug(el kernel 6.5及其以后版本,uek kernel 6.2及其以后版本)。个人倾向：如果是部署ORACLE db,而且还是redhat系列Linux,更加倾向OEL(省事,相信Oracle)

发表在 Linux | 评论关闭