标签云
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,674)
- DB2 (22)
- MySQL (73)
- Oracle (1,536)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (22)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (562)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- 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)
-
最近发表
- 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
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
标签归档:ORA-00354
硬件恢复之后,数据库无法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
评论关闭
一起ORA-600 3020故障恢复的大体思路
recover database 报ORA-600 3020
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0 Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG Tue Aug 19 19:37:29 2014 Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_pr0s_4296.trc (incident=39403): ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 3, block# 240) ORA-10564: tablespace UNDOTBS1 ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF' ORA-10560: block type 'KTU SMU HEADER BLOCK' Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39403\yygdb_pr0s_4296_i39403.trc ORA-00600: internal error code, arguments: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586) ORA-10564: tablespace SYSAUX ORA-01110: data file 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087 Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39147): ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 3, block# 240) ORA-10564: tablespace UNDOTBS1 ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGD Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39147\yygdb_ora_12460_i39147.trc Tue Aug 19 19:37:31 2014 Trace dumping is performing id=[cdmp_20140819193731] Tue Aug 19 19:37:32 2014 Recovery Slave PR0S previously exited with an exception Shutting down recovery slaves due to error 10877 Media Recovery failed with error 10877 ORA-283 signalled during: ALTER DATABASE RECOVER database ...
使用allow 1 corruption跳3020错误继续恢复
Tue Aug 19 19:38:53 2014 ALTER DATABASE RECOVER database allow 1 corruption Media Recovery Start Fast Parallel Media Recovery enabled ALLOW CORRUPTION option must use serial recovery Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full database recovery and will not be recovered Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0 Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG CORRUPTING BLOCK 240 OF FILE 3 AND CONTINUING RECOVERY Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc: ORA-10567: Redo is inconsistent with data block (file# 3, block# 240) ORA-10564: tablespace UNDOTBS1 ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF' ORA-10560: block type 'KTU SMU HEADER BLOCK' Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39148): ORA-00600: 内部错误代码, 参数: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586) ORA-10564: tablespace SYSAUX ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087 Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39148\yygdb_ora_12460_i39148.trc Media Recovery failed with error 600 ORA-283 signalled during: ALTER DATABASE RECOVER database allow 1 corruption ... Tue Aug 19 19:38:56 2014 Trace dumping is performing id=[cdmp_20140819193856] Tue Aug 19 19:38:59 2014 Sweep Incident[39148]: completed Tue Aug 19 19:39:05 2014 ALTER DATABASE RECOVER database allow 1 corruption Media Recovery Start Fast Parallel Media Recovery enabled ALLOW CORRUPTION option must use serial recovery Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full database recovery and will not be recovered Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0 Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG CORRUPTING BLOCK 90586 OF FILE 2 AND CONTINUING RECOVERY Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc: ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586) ORA-10564: tablespace SYSAUX ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087 Completed: ALTER DATABASE RECOVER database allow 1 corruption
继续open数据库报ORA-01578错误,数据库无法open
Thread 1 opened at log sequence 5730 Current log# 3 seq# 5730 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO03.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Aug 19 19:39:34 2014 SMON: enabling cache recovery Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39149): ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240) ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF' Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39150): ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54 ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG' ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240) ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF' Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39150\yygdb_ora_12460_i39150.trc Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc: ORA-00354: 损坏重做日志块标头 ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54 ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG' ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240) ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF' Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39151): Error 1578 happened during db open, shutting down database USER (ospid: 12460): terminating the instance due to error 1578 Tue Aug 19 19:39:41 2014 Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw3_18508.trc (incident=38659): ORA-01578: ORACLE data block corrupted (file # , block # ) Tue Aug 19 19:39:41 2014 Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw5_12160.trc (incident=38675): ORA-01578: ORACLE data block corrupted (file # , block # ) Tue Aug 19 19:39:42 2014 Instance terminated by USER, pid = 12460 ORA-1092 signalled during: alter database open... ORA-1092 : opiodr aborting process unknown ospid (5084_12460)
由于undo 表空间有坏块,导致数据库open失败,尝试修改undo_management= “MANUAL”,继续启动数据库
Tue Aug 19 19:50:06 2014 alter database open Beginning crash recovery of 1 threads parallel recovery started with 16 processes Started redo scan Completed redo scan 3 redo blocks read, 0 data blocks need recovery Started redo application at Thread 1: logseq 5731, block 2, scn 101497289 Recovery of Online Redo Log: Thread 1 Group 1 Seq 5731 Reading mem 0 Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO01.LOG Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 5731, block 5, scn 101517294 0 data blocks read, 0 data blocks written, 3 redo blocks read Tue Aug 19 19:50:08 2014 Thread 1 advanced to log sequence 5732 (thread open) Thread 1 opened at log sequence 5732 Current log# 2 seq# 5732 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Aug 19 19:50:08 2014 SMON: enabling cache recovery Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. File #3 is offline, but is part of an online tablespace. data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF' File #10 is offline, but is part of an online tablespace. data file 10: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF' File #11 is offline, but is part of an online tablespace. data file 11: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT.DBF' 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 ********************************************************************* Database Characterset is ZHS16GBK Opening with internal Resource Manager plan : on 4 X 8 NUMA system ********************************************************** 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. ********************************************************** Hex dump of (file 1, block 7065) in trace file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc Corrupt block relative dba: 0x00401b99 (file 1, block 7065) Fractured block found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x00401b99 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xac3b0601 check value in block header: 0x2e13 computed block checksum: 0xa4ac Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42814): ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065) ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF' Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_ora_14296_i42814.trc Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42815): ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065) ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF' Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42815\yygdb_ora_14296_i42815.trc Tue Aug 19 19:50:12 2014 Trace dumping is performing id=[cdmp_20140819195012] Tue Aug 19 19:50:12 2014 Sweep Incident[42814]: completed Hex dump of (file 1, block 7065) in trace file d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_m000_11592_i42814_a.trc Corrupt block relative dba: 0x00401b99 (file 1, block 7065) Fractured block found during validation Data in bad block: type: 6 format: 2 rdba: 0x00401b99 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xac3b0601 check value in block header: 0x2e13 computed block checksum: 0xa4ac Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data Hex dump of (file 1, block 7065) in trace file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc Corrupt block relative dba: 0x00401b99 (file 1, block 7065) Fractured block found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x00401b99 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xac3b0601 check value in block header: 0x2e13 computed block checksum: 0xa4ac Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data Corrupt Block Found TSN = 0, TSNAME = SYSTEM RFN = 1, BLK = 7065, RDBA = 4201369 OBJN = 1164, OBJD = 1164, OBJECT = SYS_FBA_BARRIERSCN, SUBOBJECT = SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42816): ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065) ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF' Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42816\yygdb_ora_14296_i42816.trc Trace dumping is performing id=[cdmp_20140819195014] Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42817): Starting background process FBDA Tue Aug 19 19:50:18 2014 FBDA started with pid=86, OS id=17700 replication_dependency_tracking turned off (no async multimaster replication found) Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc (incident=42910): ORA-01578: ORACLE data block corrupted (file # 1, block # 7065) ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF' Trace dumping is performing id=[cdmp_20140819195018] Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc (incident=42911): ORA-01578: ORACLE data block corrupted (file # 1, block # 7065) ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF' LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete ORA-604 signalled during: alter database open...
数据库不完全open成功,报了604错误,通过分析undo$,直接使用_offline_rollback_segments屏蔽了status$=5的回滚段,数据库open正常,因为system有大量坏块,幸运的是使用exp导出来几个业务用户的表数据全部OK.
数据库备份重于一切,别寄希望数据库非常规恢复