联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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