oracle ora00353,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