联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORACLE 8.1.7 数据库ORA-600 4000故障恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在数据库的恢复过程中遇到ORA-600 4000错误挺多的，但是在oracle 8i(8.1.7)中遇到此类问题，还是第一次，做个记忆，供参考：
数据库故障起因：因为存储异常，导致当前redo损坏，并_allow_resetlogs_corruption参数尝试打开数据库
Media Recovery Log kcrrga: Warning. Log sequence in archive filename wrapped to fix length as indicated by %S in LOG_ARCHIVE_FORMAT. Old log archive with same name might be overwritten. ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont... Wed Aug 20 23:01:43 2014 ALTER DATABASE RECOVER CANCEL Media Recovery Cancelled Completed: ALTER DATABASE RECOVER CANCEL Wed Aug 20 23:01:50 2014 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 262618871 Wed Aug 20 23:01:50 2014 Thread 1 opened at log sequence 1 Current log# 3 seq# 1 mem# 0: F:\REDO01.LOG Successful open of redo thread 1. Wed Aug 20 23:01:50 2014 SMON: enabling cache recovery Wed Aug 20 23:01:50 2014 Errors in file C:\oracle\admin\YCFD\udump\ORA00320.TRC: ORA-00600: ??????????: [4000], [3], [], [], [], [], [], [] SMON: disabling cache recovery Wed Aug 20 23:01:51 2014 ORA-704 signalled during: alter database open resetlogs
数据库遭遇ORA-600 4000错误，数据库无法打开，分析对应trace日志
Dump file C:\oracle\admin\YCFD\udump\ORA00320.TRC Wed Aug 20 23:01:50 2014 ORACLE V8.1.7.0.0 - Production vsnsta=0 vsnsql=e vsnxtr=3 Windows 2000 Version 5.2 Service Pack 2, CPU type 586 Oracle8i Release 8.1.7.0.0 - Production JServer Release 8.1.7.0.0 - Production Windows 2000 Version 5.2 Service Pack 2, CPU type 586 Instance name: ycfd Redo thread mounted by this instance: 1 Oracle process number: 8 Windows thread id: 320, image: ORACLE.EXE *** SESSION ID:(7.1) 2014-08-20 23:01:50.838 *** 2014-08-20 23:01:50.838 ksedmp: internal or fatal error ORA-00600: ??????????: [4000], [3], [], [], [], [], [], [] Current SQL statement for this session: select ctime, mtime, stime from obj$ where obj# = :1 ----- Call Stack Trace -----
这里可以看出来，是因为数据库在启动之时需要执行select ctime, mtime, stime from obj$ where obj# = :1语句，但是由于每种原因出现ORA-600 4000导致数据库无法正常启动，继续分析日志
lock header dump: 0x0040003e
Object id on Block? Y
seg/obj: 0x12 csc: 0x00.fb5c5c5 itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0003.012.0002ae94 uba: 0x00801f5b.5389.11 --U- 1 fsc 0x0000.0fb5c5c6
SQL> select checkpoint_change# from v$database;
263570122
此处比较明显,通过xid可以知道三号回滚段中对应一个事务出现问题：
1. 该block为file 1 bock 62，object_id为 18（obj$)上有一个事务
2. 该事务的scn为263,570,886>database scn(263570122)导致该故障发生
3. 当数据库访问到file 1 block 62的时候，发现有一个事务，而该事务的scn大于数据库scn，从而出现ORA-600[4000]
解决该问题有几种方法
1. 修改block 62，人工提交该事务
2. 修改数据库scn，让数据库scn大于itl scn
解决block 62 事务问题后出现如下错误
Wed Aug 20 23:03:55 2014 SMON: enabling cache recovery Wed Aug 20 23:03:55 2014 Dictionary check beginning Dictionary check complete Wed Aug 20 23:03:55 2014 SMON: enabling tx recovery Wed Aug 20 23:03:56 2014 Errors in file C:\oracle\admin\YCFD\bdump\ycfdSMON.TRC: ORA-00600: internal error code, arguments: [4193], [21173], [21181], [], [], [], [], [] Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: F:\REDO03.LOG SMON: terminating instance due to error 600 Instance terminated by SMON, pid = 2468
数据库出现ORA-600 4193,这个是常见错误,因为redo记录和undo记录不匹配导致,可以直接使用_corrupted_rollback_segments/_offline_rollback_segments屏蔽回滚段跳过
Wed Aug 20 23:08:10 2014 SMON: enabling cache recovery SMON: enabling tx recovery SMON: about to recover undo segment 1 SMON: mark undo segment 1 as needs recovery SMON: about to recover undo segment 2 SMON: mark undo segment 2 as needs recovery SMON: about to recover undo segment 3 SMON: mark undo segment 3 as needs recovery SMON: about to recover undo segment 4 SMON: mark undo segment 4 as needs recovery SMON: about to recover undo segment 5 SMON: mark undo segment 5 as needs recovery SMON: about to recover undo segment 6 SMON: mark undo segment 6 as needs recovery SMON: about to recover undo segment 7 SMON: mark undo segment 7 as needs recovery SMON: about to recover undo segment 1 SMON: mark undo segment 1 as needs recovery SMON: about to recover undo segment 2 SMON: mark undo segment 2 as needs recovery SMON: about to recover undo segment 3 SMON: mark undo segment 3 as needs recovery SMON: about to recover undo segment 4 SMON: mark undo segment 4 as needs recovery SMON: about to recover undo segment 5 SMON: mark undo segment 5 as needs recovery SMON: about to recover undo segment 6 SMON: mark undo segment 6 as needs recovery SMON: about to recover undo segment 7 SMON: mark undo segment 7 as needs recovery Wed Aug 20 23:08:15 2014 Completed: alter database open
其他类似文章：
ORA-600[4194]/[4193]解决
通过bbed解决ORA-600 4000案例
通过bbed解决ORA-00600[4000]案例
记录一次ORA-600 4000数据库故障恢复
加我QQ
