标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,680)
- DB2 (22)
- MySQL (73)
- Oracle (1,542)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 手工删除19c rac
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
标签归档:ORA-00353
硬件恢复之后,数据库无法open故障恢复
由于硬件故障,客户恢复硬件之后,数据库无法正常启动,报ORA-00354 ORA-00353错误
/tmp/> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 1 17:10:30 2021 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 86088 change 16135545783340 time 02/23/2021 13:53:24 ORA-00312: online log 2 thread 1: '/oradata02/redo02b.log' ORA-00312: online log 2 thread 1: '/oradata01/redo02a.log'
由于redo损坏,数据库无法继续正常恢复,通过屏蔽一致性,force open库
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [3756], [3571444619], [3756], [3648471803], [4194545] Process ID: 5104 Session ID: 576 Serial number: 3
这个错误比较简单,是由于scn问题导致,修改数据库scn启动库
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: 5536 Session ID: 576 Serial number: 1
这个错误比较明显,修改回滚段,尝试启动库
SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6033 Session ID: 576 Serial number: 3
数据库依旧无法正常open,alert日志报错如下
ARC3 started with pid=30, OS id=6078 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Exception[type:SIGSEGV Address not mapped to object][ADDR:0x60173487F5][PC:0xC003B1C20,_memcpy()+64][flags:0x0,count:1] Exception[type:SIGSEGV,Address not mapped to object][ADDR:0x60173487F5][PC:0xC003B1C20,_memcpy()+64][flags:0x2,count:2] Exception[type:SIGSEGV,Address not mapped to object][ADDR:0x60173487F5][PC:0xC003B1C20,_memcpy()+64][flags:0x2,count:2] Archived Log entry 2 added for thread 1 sequence 2 ID 0x506cafbb dest 1: ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Mon Mar 01 17:44:44 2021 PMON (ospid: 5993): terminating the instance due to error 397 Mon Mar 01 17:44:45 2021 System state dump requested by (instance=1, osid=5993 (PMON)), summary=[abnormal instance termination]. System State dumped to trace file /oracle/diag/rdbms/xff/xff/trace/xff_diag_6001.trc Instance terminated by PMON, pid = 5993
通过其启动过程分析,发现数据库卡在如下对象:
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="_SYSSMU1_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 #11529215044940435280: nam='db file sequential read' ela= 21 file#=1 block#=530 blocks=1 obj#=0 tim=223080944352 Incident 528204 created, dump file: /oracle/diag/rdbms/xff/xff/incident/incdir_528204/xff_ora_6593_i528204.trc ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
至此基本上可以确认是由于出现回滚段异常,继续查看日志发现
Error 600 in redo application callback Dump of change vector: TYP:0 CLS:16 AFN:1 DBA:0x00400212 OBJ:4294967295 SCN:0x0ea6.f4f2da14 SEQ:1 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 320 spc: 5892 flg: 0x0012 seq: 0x0072 rec: 0x08 xid: 0x0000.004.000000bc ktubl redo: slt: 4 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: 0x00400212.0072.07 prev ctl max cmt scn: 0x0eac.d42963be prev tx cmt scn: 0x0eac.d4296f48 txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 4194446 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.060.000000bb uba: 0x00400212.0072.04 flg: C--- lkc: 0 scn: 0x0eac.d9736b46 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x004000e1 hdba: 0x004000e0 itli: 4 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 33 39 33 35 32 37 35 38 36 35 24 col 2: [ 2] c1 02 col 3: [ 2] c1 04 col 4: [ 3] c2 02 1d col 5: [ 6] c5 0d 12 63 43 38 col 6: [ 3] c2 25 16 col 7: [ 5] c4 02 19 35 3f col 8: [ 4] c3 03 5a 16 col 9: [ 1] 80 col 10: [ 2] c1 04 col 11: [ 2] c1 03 col 16: [ 2] c1 03 Block after image is corrupt: buffer tsn: 0 rdba: 0x00400212 (1/530) scn: 0x0ea6.f4f2da14 seq: 0x01 flg: 0x04 tail: 0xda140201 frmt: 0x02 chkval: 0x9dd8 type: 0x02=KTU UNDO BLOCK
使用bbed对file 1 block 530进行处理
struct ktuxcscn, 8 bytes @4148 ub4 kscnbas @4148 0xd42963be ub2 kscnwrp @4152 0x0eac struct ktuxcuba, 8 bytes @4156 ub4 kubadba @4156 0x00400212 ub2 kubaseq @4160 0x0072 ub1 kubarec @4162 0x07 sb2 ktuxcflg @4164 1 (KTUXCFSK) ub2 ktuxcseq @4166 0x0072 sb2 ktuxcnfb @4168 1 ub4 ktuxcinc @4172 0x00000000 sb2 ktuxcchd @4176 4 sb2 ktuxcctl @4178 3 ub2 ktuxcmgc @4180 0x8002 ub4 ktuxcopt @4188 0x7ffffffe
后续发现比较核心报错
SQL> rename FPT_XIFENFEI to FPT_XIFENFEI_0302; rename FPT_XIFENFEI to FPT_XIFENFEI_0302 * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 39, file 1, block 732605 (2)
通过分析是I_OBJ4 记录和obj$不匹配,通过bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决方法重建obj$表处理
发表在 非常规恢复
标签为 obj# 39, ORA-00353, ORA-00354, ORA-08102: index key not found, ORA-600 2662, ORA-600 4194, update undo$
评论关闭
ORA-00354 ORA-00353 ORA-00312异常处理
数据库启动报错
WIN平台oracle 9.2.0.6版本数据库redo log block header损坏,ORA-00354 ORA-00353 ORA-00312错误导致数据库无法启动
SQL >alter database open; * ERROR at line 1: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 1892904 change 281470950178815 ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\ZOYO\REDO03.LOG'
Sun Jan 24 15:44:05 2016 Database mounted in Exclusive Mode. Completed: alter database mount exclusive Sun Jan 24 15:44:05 2016 alter database open Sun Jan 24 15:44:05 2016 Beginning crash recovery of 1 threads Sun Jan 24 15:44:05 2016 Started redo scan ORA-354 signalled during: alter database open... Shutting down instance: further logons disabled Shutting down instance (immediate) License high water mark = 3 Sun Jan 24 15:44:32 2016 ALTER DATABASE CLOSE NORMAL ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
通过分析,确定损坏的redo03为当前redo,无法使用正常方法打开,加上_allow_resetlogs_corruption参数,尝试打开库,依旧失败
数据库报ORA-600 2662错误
Sun Jan 24 16:26:30 2016 SMON: enabling cache recovery Sun Jan 24 16:26:30 2016 Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_640.trc: ORA-00600: 内部错误代码,参数: [2662], [0], [31563641], [0], [31563654], [4194721], [], [] Sun Jan 24 16:26:31 2016 Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_640.trc: ORA-00704: 引导程序进程失败 ORA-00600: 内部错误代码,参数: [2662], [0], [31563641], [0], [31563654], [4194721], [], [] Sun Jan 24 16:26:31 2016 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 640 ORA-1092 signalled during: alter database open resetlogs...
ORA 600 2662的错误处理
根据经验,这个错误只需要推scn即可,可以通过bbed,隐含参数,event,oradebug,修改控制文件等方法进行,推scn之后,数据库报熟悉的ORA-00604 ORA-00607 ORA-600 4194错误,以前我们遇到的block大部分是128,这次报异常block为9.实际中跟版本有关系,在ORACLE 9.2.0.6中该错误为file 1 block 9.大部分版本为128
Sun Jan 24 16:29:39 2016 SMON: enabling cache recovery Sun Jan 24 16:29:39 2016 Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_3432.trc: ORA-00600: 内部错误代码,参数: [4194], [14], [5], [], [], [], [], [] Sun Jan 24 16:29:39 2016 Doing block recovery for fno: 1 blk: 401 Sun Jan 24 16:29:39 2016 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: D:\ORACLE\ORADATA\ZOYO\REDO01.LOG Doing block recovery for fno: 1 blk: 9 Sun Jan 24 16:29:40 2016 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: D:\ORACLE\ORADATA\ZOYO\REDO01.LOG Sun Jan 24 16:29:40 2016 Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_3432.trc: ORA-00604: 递归 SQL 层 1 出现错误 ORA-00607: 当更改数据块时出现内部错误 ORA-00600: 内部错误代码,参数: [4194], [14], [5], [], [], [], [], [] Error 604 happened during db open, shutting down database USER: terminating instance due to error 604 Instance terminated by USER, pid = 3432
ORA-00604 ORA-00607 ORA-600 4194分析trace文件
*** 2016-01-24 16:29:40.031 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Block image after block recovery: buffer tsn: 0 rdba: 0x00400009 (1/9) scn: 0x0000.01e112e1 seq: 0x01 flg: 0x04 tail: 0x12e10e01 frmt: 0x02 chkval: 0xba76 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 6 #blocks: 47 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x00400191 ext#: 4 blk#: 0 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 4 Unlocked Map Header:: next 0x00000000 #extents: 6 obj#: 0 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x0040000a length: 7 0x00400011 length: 8 0x00400181 length: 8 0x00400189 length: 8 0x00400191 length: 8 0x00400199 length: 8 TRN CTL:: seq: 0x008e chd: 0x0060 ctl: 0x0024 inc: 0x00000000 nfb: 0x0001 mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00400191.008e.04 scn: 0x0000.01ded29c Version: 0x01 FREE BLOCK POOL:: uba: 0x00400191.008e.04 ext: 0x4 spc: 0x1c3e uba: 0x00000000.002f.21 ext: 0x5 spc: 0x1334 uba: 0x00000000.002e.37 ext: 0x4 spc: 0x788 uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0 uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0 TRN TBL::
从这里可以确定undo segment header中的分配block记录有问题,清除ktuxc.fbp.fbp[N].kuba.kdba相关记录,数据库正常打开
. struct ktuxc kernel transaction undo xaction table control with 15 members . { . struct kscn scn with 3 members . { 04148 ub4 bas = 0X9CD2DE01 = 31380124 04152 ub2 wrp = 0X0000 = 0 04154 cc32 pad = 0X0000 = 0 . } . struct kuba uba with 4 members . { 04156 kdba dba = 0X91014000 = 0x00400191 file 1 block 401 04160 ub2 seq = 0X8E00 = 142 04162 ub1 rec = 0X04 = 4 04163 cc16 pad = 0X00 = 0 . } 04164 sb2 flg = 0X0100 = 1 04166 ub2 seq = 0X8E00 = 142 04168 sb2 nfb = 0X0100 = 1 04170 cc32 pad1 = 0X0000 = 0 04172 ub4 inc = 0X00000000 = 0 04176 sb2 chd = 0X6000 = 96 04178 sb2 ctl = 0X2400 = 36 04180 ub2x mgc = 0X0280 = 0x8002 04182 ub2 ver = 0X0100 = 1 04184 ub2 xts = 0X6800 = 104 04186 cc32 pad2 = 0X0000 = 0 04188 ub4 opt = 0XFEFFFF7F = 2147483646 . ktufb fbp[5] (array with 5 elements) . struct fbp [0] with 3 members . { . struct kuba uba with 4 members . { 04192 kdba dba = 0X91014000 = 0x00400191 file 1 block 401 04196 ub2 seq = 0X8E00 = 142 04198 ub1 rec = 0X04 = 4 04199 cc16 pad = 0X00 = 0 . } 04200 sb2 ext = 0X0400 = 4 04202 sb2 spc = 0X3E1C = 7230 . } . struct fbp [1] with 3 members . { . struct kuba uba with 4 members . { 04204 kdba dba = 0X00000000 = 0x00000000 file 0 block 0 04208 ub2 seq = 0X2F00 = 47 04210 ub1 rec = 0X21 = 33 04211 cc16 pad = 0X00 = 0 . } 04212 sb2 ext = 0X0500 = 5 04214 sb2 spc = 0X3413 = 4916 . } . struct fbp [2] with 3 members . { . struct kuba uba with 4 members . { 04216 kdba dba = 0X00000000 = 0x00000000 file 0 block 0 04220 ub2 seq = 0X2E00 = 46 04222 ub1 rec = 0X37 = 55 04223 cc16 pad = 0X00 = 0 . } 04224 sb2 ext = 0X0400 = 4 04226 sb2 spc = 0X8807 = 1928 . } . struct fbp [3] with 3 members . { . struct kuba uba with 4 members . { 04228 kdba dba = 0X00000000 = 0x00000000 file 0 block 0 04232 ub2 seq = 0X0000 = 0 04234 ub1 rec = 0X00 = 0 04235 cc16 pad = 0X00 = 0 . } 04236 sb2 ext = 0X0000 = 0 04238 sb2 spc = 0X0000 = 0 . } . struct fbp [4] with 3 members . { . struct kuba uba with 4 members . { 04240 kdba dba = 0X00000000 = 0x00000000 file 0 block 0 04244 ub2 seq = 0X0000 = 0 04246 ub1 rec = 0X00 = 0 04247 cc16 pad = 0X00 = 0 . } 04248 sb2 ext = 0X0000 = 0 04250 sb2 spc = 0X0000 = 0 . } . }
Sun Jan 24 16:44:52 2016 SMON: enabling tx recovery Sun Jan 24 16:44:52 2016 Database Characterset is ZHS16GBK replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN
发表在 非常规恢复
标签为 ORA-00312, ORA-00353, ORA-00354, ORA-00604, ORA-00607, ORA-600 2662, ORA-600 4194
评论关闭
分享某客户存储异常恢复之后oracle故障恢复—ORA-600 4155
某客户使用win 2003,Oracle 11.2.0.1+ASM架构方式,由于存储异常并且做了存储恢复之后,ASM可以正常mount起来,但是数据库无法打开
使用dbv检查system发现有少量坏块
DBVERIFY - 开始验证: FILE = +DATA/xifenfei/datafile/system.256.764288125 页 3117 标记为损坏 Corrupt block relative dba: 0x00400c2d (file 1, block 3117) Bad header found during dbv: Data in bad block: type: 11 format: 2 rdba: 0x00400001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000b01 check value in block header: 0xfeec computed block checksum: 0x0 Corrupt block relative dba: 0x0042002d (file 1, block 131117) Bad header found during dbv: Data in bad block: type: 11 format: 2 rdba: 0x00400001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000b01 check value in block header: 0xfeec computed block checksum: 0x0 Corrupt block relative dba: 0x0042003d (file 1, block 131133) Bad header found during dbv: Data in bad block: type: 11 format: 2 rdba: 0x00400001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000b01 check value in block header: 0xfeec computed block checksum: 0x0 DBVERIFY - 验证完成 检查的页总数: 222208 处理的页总数 (数据): 188939 失败的页总数 (数据): 19 处理的页总数 (索引): 17375 失败的页总数 (索引): 0 处理的页总数 (其他): 3190 处理的总页数 (段) : 1 失败的总页数 (段) : 0 空的页总数: 12701 标记为损坏的总页数: 3 流入的页总数: 0 加密的总页数 : 0 最高块 SCN : 0 (0.0)
很多”页 131125 失败, 校验代码为 6125″类似错误忽略.
我们对于这些坏块进行分析,这些坏块未涉及oracle 最核心的基表数据,从理论上可以open数据库
尝试打开数据库
ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 32 processes Started redo scan Mon Nov 16 14:12:45 2015 NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_5672.trc (incident=937262): ORA-00353: 日志损坏接近块 12509 更改 14199034494312 时间 02/05/2015 03:09:12 ORA-00312: 联机日志 2 线程 1: '+DATA/xifenfei/onlinelog/group_2.265.764288315' ORA-00312: 联机日志 2 线程 1: '+DATA/xifenfei/onlinelog/group_2.264.764288315' Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_937262\xifenfei_ora_5672_i937262.trc Media Recovery failed with error 399 ORA-355 signalled during: ALTER DATABASE RECOVER DATABASE ...
可以确定存储恢复的redo有问题(ORA-00353,ORA-00312),数据库无法直接打开
使用参数_allow_resetlogs_corruption屏蔽redo异常resetlogs库
Mon Nov 16 15:26:41 2015 alter database open resetlogs Mon Nov 16 15:26:41 2015 Starting background process ASMB Mon Nov 16 15:26:41 2015 ASMB started with pid=25, OS id=6612 Starting background process RBAL Mon Nov 16 15:26:41 2015 RBAL started with pid=26, OS id=6940 NOTE: initiating MARK startup Starting background process MARK Mon Nov 16 15:26:41 2015 MARK started with pid=27, OS id=1720 NOTE: MARK has subscribed NOTE: Loaded library: System SUCCESS: diskgroup DATA was mounted RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. Mon Nov 16 15:26:44 2015 NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established Archived Log entry 1 added for thread 1 sequence 1390429 ID 0xf6320db5 dest 1: Archived Log entry 2 added for thread 1 sequence 1390427 ID 0xf6320db5 dest 1: ARCH: Log corruption near block 16350 change 14199035261082 time ? CORRUPTION DETECTED: thread 1 sequence 1390428 log 3 at block 16350. Arch found corrupt blocks Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc (incident=951271): ORA-00353: 日志损坏接近块 16350 更改 14199035261082 时间 02/05/2015 03:12:49 ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.267.764288315' ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.266.764288315' Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951271\xifenfei_ora_8132_i951271.trc Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc: ORA-00354: 损坏重做日志块标头 ORA-00353: 日志损坏接近块 16350 更改 14199035261082 时间 02/05/2015 03:12:49 ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.267.764288315' ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.266.764288315' ARCH: All Archive destinations made inactive due to error 354 Committing creation of archivelog '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937207' (error 354) Deleted Oracle managed file +DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937207 ****************************************************** Detected premature EOF of log 3 at block 16350; re-trying archival ****************************************************** Mon Nov 16 15:26:49 2015 Sweep [inc][951271]: completed Mon Nov 16 15:26:49 2015 Trace dumping is performing id=[cdmp_20151116152649] Archived Log entry 3 added for thread 1 sequence 1390428 ID 0xf6320db5 dest 1: RESETLOGS after incomplete recovery UNTIL CHANGE 14199033899179 Resetting resetlogs activation ID 4130475445 (0xf6320db5) Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_m001_800.trc (incident=951319): ORA-00353: log corruption near block 2270 change 14199035131016 time 02/05/2015 03:12:58 ORA-00334: archived log: '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937209' Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951319\xifenfei_m001_800_i951319.trc Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_m001_800.trc (incident=951320): ORA-00355: change numbers out of order ORA-00353: log corruption near block 2270 change 14199035131016 time 02/05/2015 03:12:58 ORA-00334: archived log: '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937209' Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951320\xifenfei_m001_800_i951320.trc Trace dumping is performing id=[cdmp_20151116152651] Mon Nov 16 15:26:51 2015 Sweep [inc][951320]: completed Sweep [inc][951319]: completed Sweep [inc2][951320]: completed Sweep [inc2][951319]: completed Sweep [inc2][951271]: completed Trace dumping is performing id=[cdmp_20151116152653] Checker run found 1 new persistent data failures Mon Nov 16 15:26:53 2015 Setting recovery target incarnation to 2 Mon Nov 16 15:26:54 2015 Assigning activation ID 4262085362 (0xfe0a42f2) LGWR: STARTING ARCH PROCESSES Mon Nov 16 15:26:54 2015 ARC0 started with pid=29, OS id=2896 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Mon Nov 16 15:26:55 2015 ARC1 started with pid=30, OS id=1748 Mon Nov 16 15:26:55 2015 ARC2 started with pid=31, OS id=1920 ARC1: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: +DATA/xifenfei/onlinelog/group_1.262.764288315 Current log# 1 seq# 1 mem# 1: +DATA/xifenfei/onlinelog/group_1.263.764288315 Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Nov 16 15:26:55 2015 SMON: enabling cache recovery Mon Nov 16 15:26:55 2015 ARC3 started with pid=32, OS id=7236 ARC2: Archival started ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the heartbeat ARCH Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc (incident=951272): ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951272\xifenfei_ora_8132_i951272.trc Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc: ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], [] Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc: ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database Trace dumping is performing id=[cdmp_20151116152658] USER (ospid: 8132): terminating the instance due to error 600 Mon Nov 16 15:27:07 2015 Instance terminated by USER, pid = 8132 ORA-1092 signalled during: alter database open resetlogs...
在resetlogs过程中由于遭遇了ORA-600[4155]导致数据库无法正常打开.
分析相关trace文件
*** 2015-11-16 15:26:56.921 *** SESSION ID:(145.3) 2015-11-16 15:26:56.921 *** CLIENT ID:() 2015-11-16 15:26:56.921 *** SERVICE NAME:(SYS$USERS) 2015-11-16 15:26:56.921 *** MODULE NAME:(sqlplus.exe) 2015-11-16 15:26:56.921 *** ACTION NAME:() 2015-11-16 15:26:56.921 Dump continued from file: d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], [] ========= Dump for incident 951272 (ORA 600 [4155]) ======== ----- Beginning of Customized Incident Dump(s) ----- XID passed in =xid: 0x000b.001.00fcfb45 XID from Undo block = xid: 0x000b.009.00fcc561 ----- End of Customized Incident Dump(s) ----- *** 2015-11-16 15:26:57.203 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) ----- alter database open resetlogs ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst1()+129 CALL??? skdstdst() 009233DA2 000000000 000000000 000000000 ksedst()+69 CALL??? ksedst1() 000000002 000000000 006F605E0 000000000 dbkedDefDump()+4536 CALL??? ksedst() 000000287 000000000 000000000 000000000 ksedmp()+43 CALL??? dbkedDefDump() 000000003 000000002 000000000 000468E71 ksfdmp()+87 CALL??? ksedmp() 000000000 000000000 000000000 000000000 dbgexPhaseII()+1819 CALL??? ksfdmp() 000000000 000000000 000000000 000000000 dbgexExplicitEndInc CALL??? dbgexPhaseII() 000000000 000000000 000000000 ()+755 000000000 dbgeEndDDEInvocatio CALL??? dbgexExplicitEndInc 00CFB0570 00CFB7540 01F9012D8 nImpl()+748 () 01F901300 dbgeEndDDEInvocatio CALL??? dbgeEndDDEInvocatio 00CFB0570 00CFB7540 01F903130 n()+47 nImpl() 00000000A ktugce()+610 CALL??? dbgeEndDDEInvocatio 006E24498 01F8FF91E 00000002E n() 035636366 ktdgti()+609 CALL??? ktugce() 000000000 000001F68 000001F68 000000001 k2vGetCollectingInf CALL??? ktdgti() 000000000 008A34E71 01F902470 o()+324 000000018 k2vcbk()+182 CALL??? k2vGetCollectingInf 000000000 000000000 000000000 o() 000000008 kturRecoverTxn()+82 CALL??? k2vcbk() 000000000 FCFB450000000B 67 000610001 7FFDF055860 kturRecoverUndoSegm CALL??? kturRecoverTxn() 01F903448 009460001 000000000 ent()+1371 00147AE14 ktuiup()+1520 CALL??? kturRecoverUndoSegm 7FF0000000B 01F903628 ent() 000000000 00147AE14 ktuini()+80 CALL??? ktuiup() 000000001 00000000E 01F9038A0 003CB3D06 adbdrv()+44263 CALL??? ktuini() 000000000 01F9090C8 000000000 000000000 opiexe()+20842 CALL??? adbdrv() 000000023 000000003 7FF00000102 000000000 opiosq0()+5129 CALL??? opiexe()+16981 000000004 000000000 01F90A8E0 009361AB3 kpooprx()+357 CALL??? opiosq0() 000000003 00000000E 01F90ABB0 0000000A4 kpoal8()+940 CALL??? kpooprx() 000020C80 01E65CCD0 00CE91AD8 000000001 opiodr()+1662 CALL??? kpoal8() 00000005E 00000001C 01F90E120 00A4EF224 ttcpip()+1325 CALL??? opiodr() 480000000000005E 49004D000000001C 01F90E120 4100200000000000 opitsk()+2040 CALL??? ttcpip() 01E735200 000000000 000000000 000000000 opiino()+1258 CALL??? opitsk() 00000001E 000000000 000000000 01F90FA18 opiodr()+1662 CALL??? opiino() 00000003C 000000004 01F90FAD0 000000000 opidrv()+864 CALL??? opiodr() 00000003C 000000004 01F90FAD0 6F5C3A6400000000 sou2o()+98 CALL??? opidrv()+150 00000003C 000000004 01F90FAD0 000000000 opimai_real()+158 CALL??? sou2o() 01F90FB00 01F90FBC4 F0010000B07DF 1009C002B0019 opimai()+191 CALL??? opimai_real() 00000001B 01F90FC88 000000036 000000000 OracleThreadStart() CALL??? opimai() 01F90FE90 01F60FF38 000000002 +724 01F90FC88 0000000078D3B6DA CALL??? OracleThreadStart() 01F60FF38 000000000 000000000 01F90FFA8 --------------------- Binary Stack Dump --------------------- UNDO BLK: xid: 0x000b.009.00fcc561 seq: 0x513d cnt: 0x2f irb: 0x2f icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f64 0x02 0x1ee4 0x03 0x1e30 0x04 0x1d7c 0x05 0x1cc8 0x06 0x1c14 0x07 0x1b60 0x08 0x1aac 0x09 0x19f8 0x0a 0x1944 0x0b 0x1890 0x0c 0x17dc 0x0d 0x1728 0x0e 0x1674 0x0f 0x15c0 0x10 0x150c 0x11 0x1458 0x12 0x13a4 0x13 0x12f0 0x14 0x123c 0x15 0x1188 0x16 0x10d4 0x17 0x1050 0x18 0x0fd0 0x19 0x0f1c 0x1a 0x0e98 0x1b 0x0de4 0x1c 0x0d30 0x1d 0x0c7c 0x1e 0x0bc8 0x1f 0x0b44 0x20 0x0ac4 0x21 0x0a10 0x22 0x095c 0x23 0x08a8 0x24 0x07f4 0x25 0x0770 0x26 0x06f0 0x27 0x063c 0x28 0x0588 0x29 0x04d4 0x2a 0x0420 0x2b 0x039c 0x2c 0x031c 0x2d 0x0298 0x2e 0x0218 0x2f 0x0164
ORA-600 4155是由于在恢复过程中发现事务的id和undo segment中的事务表中id不匹配从而出现此类问题.针对此问题,可以通过bbed修改事务表记录,或者直接丢弃该事务,从而绕过该错误.
处理掉异常事务id后,继续open库
Mon Nov 16 16:16:07 2015 ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 32 processes Started redo scan Completed redo scan read 8 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 1, block 2, scn 14199161880578 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0 Mem# 0: +DATA/xifenfei/onlinelog/group_1.262.764288315 Mem# 1: +DATA/xifenfei/onlinelog/group_1.263.764288315 Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 1, block 19, scn 14199161900601 0 data blocks read, 0 data blocks written, 8 redo k-bytes read Current SCN is not changed: _minimum_giga_scn (scn 14199161880576) is too small Mon Nov 16 16:16:08 2015 LGWR: STARTING ARCH PROCESSES Mon Nov 16 16:16:08 2015 ARC0 started with pid=62, OS id=7612 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Mon Nov 16 16:16:09 2015 ARC1 started with pid=63, OS id=5620 Mon Nov 16 16:16:09 2015 ARC2 started with pid=64, OS id=7308 ARC1: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH Thread 1 advanced to log sequence 2 (thread open) Thread 1 opened at log sequence 2 Current log# 2 seq# 2 mem# 0: +DATA/xifenfei/onlinelog/group_2.264.764288315 Current log# 2 seq# 2 mem# 1: +DATA/xifenfei/onlinelog/group_2.265.764288315 Successful open of redo thread 1 Archived Log entry 1 added for thread 1 sequence 1 ID 0xfe09f6df dest 1: MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Nov 16 16:16:10 2015 SMON: enabling cache recovery Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. 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 Mon Nov 16 16:16:09 2015 ARC3 started with pid=65, OS id=7064 been restored. It may be necessary to add files to these ARC2: Archival started tablespaces. That can be done using the SQL statement: ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ARC0: Becoming the heartbeat ARCH Alternatively, if these temporary tablespaces are no longer needed, then they can be dropped. Empty temporary tablespace: TEMP ********************************************************************* Database Characterset is ZHS16GBK No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected. LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: ALTER DATABASE OPEN
因为该数据库是经过了存储恢复,除system之外,其他文件也有大量坏块,因为恢复过程相对比较麻烦,除了上面列出来的ORA-00353,ORA-00312,ORA-600 4155等各种错误之外,还有大量的ORA-01578,ORA-01110.由于11G比较常见的ORA-00283,ORA-16433问题。