年归档：2021

有朋友找我，由于客户硬件故障,导致控制文件损坏，重建控制文件的时候,由于失误遗漏了100多个数据文件,并且进行了resetlogs操作,然后通过bbed修改文件头,再进行恢复,数据库在open过程中报ORA-03113错误

SQL> recover database;
Media recovery complete.
SQL> alter database Open ;
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 11207284
Session ID: 4537 Serial number: 1

对应的alert日志信息

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Dec 04 23:17:02 2021
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Sat Dec 04 23:17:03 2021
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0x1604971A0][PC:0x1000E1764,kgegpa()+36][flags:0x0,count:1]
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0x1604971A0][PC:0x1000E1764,kgegpa()+36][flags:0x2,count:2]
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0x1604971A0][PC:0x1000E1764,kgegpa()+36][flags:0x2,count:2]
Sat Dec 04 23:17:07 2021
PMON (ospid: 10879866): terminating the instance due to error 397
Sat Dec 04 23:17:08 2021
System state dump requested by (instance=1, osid=10879866 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_diag_11076320.trc
Dumping diagnostic data in directory=[cdmp_20211204231708], requested by (instance=1, osid=10879866 (PMON)), 
summary=[abnormal instance termination].
Sat Dec 04 23:17:09 2021
ORA-1092 : opitsk aborting process

通过对其启动过程进行跟踪确认在update undo$ 语句中异常

PARSING IN CURSOR #11529215044940435280 len=148 dep=1 uid=0 oct=6 lid=0 tim=223080942765 
hv=3540833987 ad='c000000d67a42778' 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 #11529215044940435280:c=10000,e=8182,p=6,cr=55,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=223080942764
BINDS #11529215044940435280:
 Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=c000000d5fd299aa  bln=32  avl=20  flg=09
  value="_SYSSMU29_3935275865$"
 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=9fffffffbcc6e078  bln=24  avl=02  flg=05
  value=3
 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=9fffffffbcc6e048  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=9fffffffbcc6e010  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=9fffffffbcc6dfe0  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=9fffffffbcc6dfb0  bln=24  avl=04  flg=05
  value=28921
 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=9fffffffbcc6df80  bln=24  avl=05  flg=05
  value=1245262
 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=9fffffffbcc6df48  bln=24  avl=06  flg=05
  value=1217986655
 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=9fffffffbcc6dc90  bln=24  avl=03  flg=05
  value=3621
 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=9fffffffbcc6dc60  bln=24  avl=01  flg=05
  value=0
 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=9fffffffbcc6dc30  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=9fffffffbcc6dc00  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=9fffffffbcc6e0a8  bln=22  avl=02  flg=05
  value=1
WAIT #4574659112: nam='db file sequential read' ela= 6835 file#=1 block#=842169 blocks=1 obj#=0 tim=90821816220340

通过分析,确认是由于undo事务异常,通过一系列处理之后,数据库启动报

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Process ID: 11206912
Session ID: 4537 Serial number: 1

对应alert日志报错

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Dec 05 02:42:02 2021
SMON: enabling cache recovery
SUCCESS: diskgroup FRA was mounted
Archived Log entry 1 added for thread 1 sequence 1 ID 0x14ba19ad dest 1:
Sun Dec 05 02:42:03 2021
NOTE: dependency between database xff and diskgroup resource ora.FRA.dg is established
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_11206912.trc  (incident=5681243):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xff/xff1/incident/incdir_5681243/xff1_ora_11206912_i5681243.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Sun Dec 05 02:42:05 2021
Dumping diagnostic data in directory=[cdmp_20211205024205], requested by (instance=1, osid=11206912), 
summary=[incident=5681243].
Sun Dec 05 02:42:05 2021
Block recovery from logseq 2, block 3 to scn 13867049493730
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: +DATA/xff/onlinelog/group_2.log
Block recovery stopped at EOT rba 2.5.16
Block recovery completed at rba 2.5.16, scn 3228.2895062240
Block recovery from logseq 2, block 3 to scn 13867049493727
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: +DATA/xff/onlinelog/group_2.log
Block recovery completed at rba 2.5.16, scn 3228.2895062240
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_11206912.trc:
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_11206912.trc:
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 11206912): terminating the instance due to error 600
Instance terminated by USER, pid = 11206912
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (11206912) as a result of ORA-1092
Sun Dec 05 02:42:07 2021
ORA-1092 : opitsk aborting process

trace中关键信息和bbed对看到结果对应,通过bbed进行修改,数据库启动正常

    uba: 0x004cd9b9.02fb.0c ext: 0x19 spc: 0x1462
    uba: 0x00000000.02fb.02 ext: 0x19 spc: 0x1f06
    uba: 0x00000000.02fb.0d ext: 0x19 spc: 0x1970
    uba: 0x00000000.02fa.03 ext: 0x18 spc: 0x1e34
    uba: 0x00000000.02fa.03 ext: 0x18 spc: 0x1e34

   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x004cd9b9
         ub2 kubaseq                        @4196     0x02fb
         ub1 kubarec                        @4198     0x0c
      sb2 ktufbext                          @4200     25
      sb2 ktufbspc                          @4202     5218

以前有过类似恢复硬件恢复之后,数据库无法open故障恢复