标签归档：ORA-8102

Exadata火线救援：10TB级数据恢复—强制拉库篇

发表于 2016 年 7 月 23 日 由 惜分飞

这个库的恢复有一些历史故事(【力荐】Exadata火线救援：10TB级数据修复经典案例详解！)：xx运营商x2的1/4配置的oracle exadata机器，跑了近6年，最近有一个cell节点主机异常,在rebalance过程中,只有两个节点的cell其中一个节点坏了一个硬盘导致.导致asm diskgroup无法正常mount,最后该运营商运维三方通过amdu把该一体机中的数据文件全部抽出来,然后在恢复过程中出现大量错误无法解决,请求我们支持
数据库open过程报ORA-01555错误

Thu Jul  14 00:01:04 2016
alter database open
Thu Jul  14 00:01:04 2016
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /data/amdu/redo/DATA_EC_260.f
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jul  14 00:01:05 2016
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0b26.9f080238):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_59546.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 83 with name "_SYSSMU83_1078760807$" too small
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_59546.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 83 with name "_SYSSMU83_1078760807$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 59546): terminating the instance due to error 704
Instance terminated by USER, pid = 59546
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (59546) as a result of ORA-1092

这个错误比较常见，可以通过推scn就可以解决,由于已经安装了scn patch,通过oradebug推scn解决该问题.

ORA-600 4194
这个ora 600 4194相对比较特殊,在SMON: enabling cache recovery之后立马报出来,然后实例直接open失败.

Thu Jul  14 00:06:15 2016
alter database open
Thu Jul  14 00:06:15 2016
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /data/amdu/redo/DATA_EC_263.f
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jul  14 00:06:15 2016
SMON: enabling cache recovery
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_60038.trc  (incident=1080450):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Block recovery from logseq 3, block 3 to scn 12269096776739
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /data/amdu/redo/DATA_EC_263.f
Block recovery stopped at EOT rba 3.5.16
Block recovery completed at rba 3.5.16, scn 2856.2670179361
Block recovery from logseq 3, block 3 to scn 12269096776736
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /data/amdu/redo/DATA_EC_263.f
Block recovery completed at rba 3.5.16, scn 2856.2670179361
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_60038.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_60038.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 60038): terminating the instance due to error 600
Instance terminated by USER, pid = 60038
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (60038) as a result of ORA-1092

trace文件分析
打开数据库报ORA-600[4194]错误,对启动过程进行10046跟踪并且分析trace文件发现

PARSING IN CURSOR #140375370511672 len=148 dep=1 uid=0 oct=6 lid=0 tim=3501342849457766 hv=3540833987 
ad='a47df47a8' sqlid='5ansr7r9htpq3'
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,
scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
END OF STMT
PARSE #140375370511672:c=27996,e=28041,p=66,cr=224,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=3501342849457765
BINDS #140375370511672:
 Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=a47e093ca  bln=32  avl=20  flg=09
  value="_SYSSMU1_2856534670$"
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b92b0  bln=24  avl=03  flg=05
  value=1024
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b9280  bln=24  avl=03  flg=05
  value=128
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b9248  bln=24  avl=02  flg=05
  value=5
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b9218  bln=24  avl=02  flg=05
  value=1
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b91e8  bln=24  avl=03  flg=05
  value=3398
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b91b8  bln=24  avl=05  flg=05
  value=1485261
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b9180  bln=24  avl=06  flg=05
  value=1946693999
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b8ec8  bln=24  avl=03  flg=05
  value=2847
 Bind#9
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b8e98  bln=24  avl=02  flg=05
  value=1
 Bind#10
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b8e68  bln=24  avl=02  flg=05
  value=2
 Bind#11
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b8e38  bln=24  avl=02  flg=05
  value=2
 Bind#12
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fabae3b92e0  bln=22  avl=02  flg=05
  value=1
WAIT #140375370511672: nam='db file sequential read' ela= 21 file#=1 block#=179020 blocks=1 obj#=0 tim=3501342849459353

*** 2016-07-14 03:14:09.548
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

很明显数据库是在update undo$的时候,读取到file 1 block 179020的时候报错,继续分析trace文件

Error 600 in redo application callback
Dump of change vector:
TYP:0 CLS:16 AFN:1 DBA:0x0042bb4c OBJ:4294967295 SCN:0x0b26.a88e815e SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 272 spc: 4906 flg: 0x0012 seq: 0x0f4c rec: 0x0d
            xid:  0x0000.01b.00000b63  
ktubl redo: slt: 27 rci: 0 opc: 11.1 [objn: 15 objd: 15 tsn: 0]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x0042bb4c.0f4c.0c 
prev ctl max cmt scn:  0x0b23.ccb9eb89  prev tx cmt scn:  0x0b23.ccb9ebac 
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 4373321  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0000.01c.00000b65 uba: 0x0042bb4a.0f4c.1d
                      flg: C---    lkc:  0     scn: 0x0b25.7391ee5c
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x004000e1  hdba: 0x004000e0
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 17 nnew: 12 size: 0
col  1: [20]  5f 53 59 53 53 4d 55 31 5f 32 38 35 36 35 33 34 36 37 30 24
col  2: [ 2]  c1 02
col  3: [ 3]  c2 0b 19
col  4: [ 3]  c2 02 1d
col  5: [ 6]  c5 14 2f 46 28 64
col  6: [ 3]  c2 1d 30
col  7: [ 5]  c4 02 31 35 3e
col  8: [ 3]  c2 22 63
col  9: [ 2]  c1 02
col 10: [ 2]  c1 04
col 11: [ 2]  c1 03
col 16: [ 2]  c1 03
Block after image is corrupt: 
buffer tsn: 0 rdba: 0x0042bb4c (1/179020)
scn: 0x0b26.a88e815e seq: 0x01 flg: 0x04 tail: 0x815e0201
frmt: 0x02 chkval: 0xf022 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000094E07A000 to 0x000000094E07C000
94E07A000 0000A202 0042BB4C A88E815E 04010B26  [....L.B.^...&...]
94E07A010 0000F022 004E0000 00000B5B 1D1D0F4C  [".....N.[...L...]

我们知道在file 1 block 179020的时候redo和undo信息不匹配,出现了上述的ORA 600 4194的错误.进一步分析

Block image after block recovery:
buffer tsn: 0 rdba: 0x00400080 (1/128)
scn: 0x0b26.6389e19d seq: 0x01 flg: 0x04 tail: 0xe19d0e01
frmt: 0x02 chkval: 0x7c95 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000094DAB2000 to 0x000000094DAB4000
94DAB2000 0000A20E 00400080 6389E19D 04010B26  [......@....c&...]
94DAB2010 00007C95 00000000 00000000 00000000  [.|..............]
94DAB2020 00000000 00000015 000002FF 00001020  [............ ...]
94DAB2030 0000000D 0000004C 00000080 0042BB4C  [....L.......L.B.]


  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 21     #blocks: 767   
                  last map  0x00000000  #maps: 0      offset: 4128  
      Highwater::  0x0042bb4c  ext#: 13     blk#: 76     ext size: 128   
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 13    
                   Unlocked
     Map Header:: next  0x00000000  #extents: 21   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x00400081  length: 7     
   0x00413a38  length: 8     
   0x00400088  length: 8     
   0x00413a30  length: 8     
   0x0042b888  length: 8     
   0x0042b890  length: 8     
   0x0042b898  length: 8     
   0x0042b8a0  length: 8     
   0x0042b8a8  length: 8     
   0x0042b8b0  length: 8     
   0x0042b8b8  length: 8     
   0x0042b8c0  length: 8     
   0x0042ba80  length: 128   
   0x0042bb00  length: 128   
   0x0042bc00  length: 128   
   0x0042bc80  length: 128   
   0x0042bb80  length: 128   
   0x00400210  length: 8     
   0x00400218  length: 8     
   0x00400220  length: 8     
   0x00400228  length: 8     
  
  TRN CTL:: seq: 0x0f4c chd: 0x001b ctl: 0x0043 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x0042bb4c.0f4c.0c scn: 0x0b23.ccb9eb89
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x0042bb4c.0f4c.0c ext: 0xd  spc: 0x132a  
    uba: 0x00000000.0f4c.0c ext: 0xd  spc: 0x12f6  
    uba: 0x00000000.0f4c.01 ext: 0xd  spc: 0x1ec8  
    uba: 0x00000000.0f4c.04 ext: 0xd  spc: 0x1b86  
    uba: 0x00000000.0f4c.09 ext: 0xd  spc: 0x162c  
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0b62  0x0011  0x0b25.2dacaf61  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x01    9    0x00  0x0b64  0x0024  0x0b24.a6a2cf7b  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x02    9    0x00  0x0b65  0x0036  0x0b25.7391eda0  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x03    9    0x00  0x0b4f  0x0007  0x0b24.337bf49b  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x04    9    0x00  0x0b64  0x0051  0x0b23.ff22c637  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x05    9    0x00  0x0b64  0x0022  0x0b26.4393eb1e  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x06    9    0x00  0x0b66  0x0058  0x0b24.335c794d  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x07    9    0x00  0x0b4f  0x001d  0x0b24.4e05f2af  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x08    9    0x00  0x0b65  0x005e  0x0b23.ff22c618  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x09    9    0x00  0x0b5f  0x0035  0x0b24.337bf3d9  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x0a    9    0x00  0x0b64  0x004f  0x0b25.7391ee5f  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x0b    9    0x00  0x0b64  0x0040  0x0b24.335c7bd7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x0c    9    0x00  0x0b65  0x0002  0x0b25.7391e929  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x0d    9    0x00  0x0b4f  0x0033  0x0b24.a6a2caa5  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x0e    9    0x00  0x0b65  0x0008  0x0b23.ff22c616  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x0f    9    0x00  0x0b61  0x0038  0x0b26.6389e195  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x10    9    0x00  0x0b4f  0x002a  0x0b24.bcff18b3  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x11    9    0x00  0x0b5c  0x0059  0x0b25.2dacaf69  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x12    9    0x00  0x0b65  0x0026  0x0b25.2dacb0a8  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x13    9    0x00  0x0b66  0x0021  0x0b24.a6a2caaa  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x14    9    0x00  0x0b62  0x0009  0x0b24.337bf3d7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x15    9    0x00  0x0b63  0x0031  0x0b25.1b4e13ba  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x16    9    0x00  0x0b66  0x003b  0x0b25.2dacee5d  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x17    9    0x00  0x0b63  0x0034  0x0b26.6389e199  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x18    9    0x00  0x0b5d  0x002f  0x0b24.bcff18af  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x19    9    0x00  0x0b5b  0x004d  0x0b24.d60f78e3  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x1a    9    0x00  0x0b60  0x005b  0x0b25.1b4e13be  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x1b    9    0x00  0x0b62  0x003e  0x0b23.ccb9ebac  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x1c    9    0x00  0x0b65  0x000a  0x0b25.7391ee5c  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x1d    9    0x00  0x0b64  0x002c  0x0b24.4e05f2b1  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x1e    9    0x00  0x0b64  0x0045  0x0b24.33255ae9  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x1f    9    0x00  0x0b64  0x0015  0x0b25.1b4e13b5  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x20    9    0x00  0x0b63  0x0050  0x0b24.335c79a4  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x21    9    0x00  0x0b5d  0x0001  0x0b24.a6a2caf0  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x22    9    0x00  0x0b65  0x000f  0x0b26.4393eb20  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x23    9    0x00  0x0b62  0x0042  0x0b24.337bf3d2  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x24    9    0x00  0x0b65  0x003c  0x0b24.a6a2d137  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x25    9    0x00  0x0b62  0x0020  0x0b24.335c795d  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x26    9    0x00  0x0b63  0x0052  0x0b25.2dacee48  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x27    9    0x00  0x0b4e  0x003a  0x0b25.7391ee58  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x28    9    0x00  0x0b65  0x0049  0x0b25.2dacb089  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x29    9    0x00  0x0b61  0x0030  0x0b24.bcff18bb  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x2a    9    0x00  0x0b65  0x0057  0x0b24.bcff18b5  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x2b    9    0x00  0x0b64  0x0054  0x0b25.2dacee55  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x2c    9    0x00  0x0b61  0x000d  0x0b24.4e05f2b3  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x2d    9    0x00  0x0b64  0x000e  0x0b23.ff22c611  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x2e    9    0x00  0x0b65  0x0053  0x0b25.7391e78a  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x2f    9    0x00  0x0b66  0x0010  0x0b24.bcff18b1  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x30    9    0x00  0x0b63  0x0019  0x0b24.d60f78e1  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x31    9    0x00  0x0b65  0x001a  0x0b25.1b4e13bc  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x32    9    0x00  0x0b65  0x0037  0x0b24.a6a2d369  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x33    9    0x00  0x0b4f  0x0013  0x0b24.a6a2caa7  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x34    9    0x00  0x0b63  0x0043  0x0b26.6389e19b  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x35    9    0x00  0x0b65  0x0046  0x0b24.337bf409  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x36    9    0x00  0x0b52  0x0061  0x0b25.7391eda2  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x37    9    0x00  0x0b64  0x0018  0x0b24.bcff18ad  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x38    9    0x00  0x0b65  0x0017  0x0b26.6389e197  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x39    9    0x00  0x0b62  0x0006  0x0b24.335c7947  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3a    9    0x00  0x0b64  0x001c  0x0b25.7391ee5a  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3b    9    0x00  0x0b4d  0x002e  0x0b25.7391e730  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3c    9    0x00  0x0b65  0x0032  0x0b24.a6a2d144  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x3d    9    0x00  0x0b65  0x0016  0x0b25.2dacee59  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x3e    9    0x00  0x0b63  0x002d  0x0b23.ff22c60b  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x3f    9    0x00  0x0b63  0x005f  0x0b24.335c7b57  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x40    9    0x00  0x0b65  0x0044  0x0b24.335c7bd9  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x41    9    0x00  0x0b65  0x0029  0x0b24.bcff18b9  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x42    9    0x00  0x0b61  0x0014  0x0b24.337bf3d4  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x43    9    0x00  0x0b5b  0xffff  0x0b26.6389e19d  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x44    9    0x00  0x0b4c  0x004b  0x0b24.335c7bdb  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x45    9    0x00  0x0b61  0x0039  0x0b24.335c7945  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x46    9    0x00  0x0b65  0x005a  0x0b24.337bf421  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x47    9    0x00  0x0b65  0x0027  0x0b25.7391eda8  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x48    9    0x00  0x0b62  0x004e  0x0b24.335c7953  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x49    9    0x00  0x0b63  0x0012  0x0b25.2dacb09f  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x4a    9    0x00  0x0b63  0x0023  0x0b24.335c7bf8  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4b    9    0x00  0x0b5b  0x004a  0x0b24.335c7bf3  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4c    9    0x00  0x0b63  0x003f  0x0b24.335c7b55  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4d    9    0x00  0x0b61  0x001f  0x0b25.1b4e13b3  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x4e    9    0x00  0x0b5a  0x0025  0x0b24.335c795b  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x4f    9    0x00  0x0b5f  0x005c  0x0b25.8ff588bb  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x50    9    0x00  0x0b63  0x004c  0x0b24.335c79a7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x51    9    0x00  0x0b64  0x005d  0x0b24.0c84cbf4  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x52    9    0x00  0x0b65  0x002b  0x0b25.2dacee49  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x53    9    0x00  0x0b64  0x000c  0x0b25.7391e927  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x54    9    0x00  0x0b63  0x003d  0x0b25.2dacee56  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x55    9    0x00  0x0b64  0x0005  0x0b26.4393eada  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x56    9    0x00  0x0b64  0x0055  0x0b26.4393ead3  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x57    9    0x00  0x0b60  0x0041  0x0b24.bcff18b7  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x58    9    0x00  0x0b65  0x0060  0x0b24.335c794f  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x59    9    0x00  0x0b60  0x0028  0x0b25.2dacaf74  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x5a    9    0x00  0x0b61  0x0003  0x0b24.337bf499  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
   0x5b    9    0x00  0x0b62  0x0000  0x0b25.1b4e13c0  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x5c    9    0x00  0x0b62  0x0056  0x0b25.950a6e4b  0x0042bb4c  0x0000.000.00000000  0x00000001   0x00000000
   0x5d    9    0x00  0x0b63  0x001e  0x0b24.33255ae7  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x5e    9    0x00  0x0b5f  0x0004  0x0b23.ff22c635  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x5f    9    0x00  0x0b64  0x000b  0x0b24.335c7bd5  0x0042bb49  0x0000.000.00000000  0x00000001   0x00000000
   0x60    9    0x00  0x0b61  0x0048  0x0b24.335c7950  0x0042bb4b  0x0000.000.00000000  0x00000001   0x00000000
   0x61    9    0x00  0x0b65  0x0047  0x0b25.7391eda6  0x0042bb4a  0x0000.000.00000000  0x00000001   0x00000000
KQRCMT: Write failed with error=600 po=0xa47e092c0 cid=3
diagnostics : cid=3 hash=35e74caf flag=2a
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

到这里我们基本上明白了报错的file 1 block 179020是由FREE BLOCK POOL分配出来的,现在解决给问题的思路就是直接使用bbed分配一个新块即可.

ORA-600 6711
数据库无法正常open报ORA 600 6711错误

Thu Jul  14 04:04:28 2016
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 1 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 2, block 3
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /data/amdu/redo/DATA_EC_260.f
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 2, block 5, scn 12269633687653
 0 data blocks read, 0 data blocks written, 1 redo k-bytes read
Thu Jul  14 04:04:29 2016
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /data/amdu/redo/DATA_EC_263.f
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
After successful startup of the database, please remove
the parameters _allow_error_simulation and _smu_debug_mode
and restart the database
Thu Jul  14 04:04:29 2016
SMON: enabling cache recovery
Undo initialization finished serial:0 start:270626334 end:270626544 diff:210 (2 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Corrected file 19 plugged in read-only status in control file
Corrected file 81 plugged in read-only status in control file
Corrected file 88 plugged in read-only status in control file
Corrected file 93 plugged in read-only status in control file
Corrected file 128 plugged in read-only status in control file
Corrected file 130 plugged in read-only status in control file
Corrected file 131 plugged in read-only status in control file
Corrected file 163 plugged in read-only status in control file
Corrected file 181 plugged in read-only status in control file
Corrected file 184 plugged in read-only status in control file
Corrected file 186 plugged in read-only status in control file
Corrected file 191 plugged in read-only status in control file
Corrected file 214 plugged in read-only status in control file
Corrected file 220 plugged in read-only status in control file
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Updating character set in controlfile to ZHS16GBK
WARNING: event 8105 is set. This event disables failed
         online index [re]build cleanup
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jul  14 04:04:30 2016
QMNC started with pid=57, OS id=3549 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_3401.trc  (incident=1440450):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440450/xifenfei_ora_3401_i1440450.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Error 600 in kwqmnpartition(), aborting txn 
Thu Jul  14 04:04:32 2016
Dumping diagnostic data in directory=[cdmp_20801214040432], requested by (instance=1, osid=3401), summary=[incident=1440450].
Thu Jul  14 04:04:32 2016
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_mmon_3329.trc  (incident=1440178):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440178/xifenfei_mmon_3329_i1440178.trc
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_3401.trc  (incident=1440451):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440451/xifenfei_ora_3401_i1440451.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_mmon_3329.trc  (incident=1440179):
ORA-00600: internal error code, arguments: [6711], [4293062], [1], [4318348], [0], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_1440179/xifenfei_mmon_3329_i1440179.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database open...

数据库正常open失败,但是可以upgrade启动.根据对trace文件的分析,定位到问题在HISTGRM$表上面,进一步分析该表结构为

CREATE TABLE SYS.HISTGRM$
(
  OBJ#      NUMBER,
  COL#      NUMBER,
  ROW#      NUMBER,
  BUCKET    NUMBER,
  ENDPOINT  NUMBER,
  INTCOL#   NUMBER,
  EPVALUE   VARCHAR2(1000 BYTE),
  SPARE1    NUMBER,
  SPARE2    NUMBER
)
CLUSTER SYS.C_OBJ#_INTCOL#(OBJ#, INTCOL#);

这个和ORA-600 6711错误相匹配了

ERROR:
ORA-600 [6711] [a] [b] 1 [d]
VERSIONS:
versions 6.0 to 12.1
DESCRIPTION:
This error is generated when we find more blocks on a cluster key
chain than are supposed to be there.
Usually this indicates that the chain contains a loop within itself. We
cannot have more than 65535 blocks in a chain.
ARGUMENTS:
Arg [a] beginning DBA
Arg [b] table slot number (in table index)
Arg {c} dba of key next in chain
Arg [d] row slot of key next in chain

需要处理该错误,也就是需要处理CLUSTER SYS.C_OBJ#_INTCOL#,这个可以通过重建来实现,但是当重建之时发生

ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [0], [0], [], [], [], [], [], [], [], [], []
ORA-08102: index key not found, obj# 39, file 1, block 1374829 (2)

这里错误比较明显obj#=39为obj$的i_obj4的index的记录和表不匹配,导致任何ddl无法执行,因此如果要处理C_OBJ#_INTCOL#就必须要先处理i_obj4的问题.通过一些技巧重建i_obj4,然后重建C_OBJ#_INTCOL#,数据库终于可以正常打开.由于大量数据字典不一致,exp/expdp导出依旧有问题,通过dblink直接拉数据到新库,完成本次恢复
补充说明：1. 在这个库的恢复过程中,我们还使用了大量的event和隐含参数,因为比较常规而且不涉及核心环节,因为未列举出来;2. 由于当时操作记录未能够保留日志因此相关操作步骤无法贴出来,本文只能提供恢复处理思路
再次提醒各位数据库做好备份,做好巡检工作,哪怕是强大的Oracle exadata也禁不起无备份折腾,数据重于一切

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

分享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备份恢复 | 标签为 , , , | 评论关闭