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数据库故障恢复

记录一次ORA-600 3004 恢复过程和处理思路

10.1.0.2数据库在启动的时候报ORA-00600[3004]错误

SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1042254360 bytes
Fixed Size 743960 bytes
Variable Size 503316480 bytes
Database Buffers 536870912 bytes
Redo Buffers 1323008 bytes
数据库装载完毕。
ORA-00600: 内部错误代码,参数: [3004], [1], [0], [0], [], [], [], []

alert日志信息

Tue Jul 15 10:57:11 2014
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Tue Jul 15 10:57:12 2014
ALTER DATABASE OPEN
Tue Jul 15 10:57:12 2014
Beginning crash recovery of 1 threads
 attempting to start a parallel recovery with 3 processes
 parallel recovery started with 3 processes
Tue Jul 15 10:57:12 2014
Started first pass scan
Tue Jul 15 10:57:12 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_1084.trc:
ORA-00600: 内部错误代码, 参数: [3004], [1], [0], [0], [], [], [], []

Tue Jul 15 10:57:13 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\bdump\orcl_p000_3780.trc:
ORA-10388: parallel query server interrupt (failure)

Tue Jul 15 10:57:13 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\bdump\orcl_p002_3420.trc:
ORA-10388: parallel query server interrupt (failure)

Tue Jul 15 10:57:14 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\bdump\orcl_p001_3784.trc:
ORA-10388: parallel query server interrupt (failure)

Tue Jul 15 10:57:14 2014
Aborting crash recovery due to error 600
Tue Jul 15 10:57:14 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_1084.trc:
ORA-00600: 内部错误代码, 参数: [3004], [1], [0], [0], [], [], [], []

ORA-600 signalled during: ALTER DATABASE OPEN...

根据老杨的blog,出现该问题,很可能是crontrolfile异常导致,所以这里可以考虑通过重建控制文件或者把当前控制文件当作备份控制文件来使用

Tue Jul 15 13:42:31 2014
ALTER DATABASE RECOVER  database using backup controlfile   
Tue Jul 15 13:42:31 2014
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 9 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 10 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 11 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
 attempting to start a parallel recovery with 3 processes
 parallel recovery started with 3 processes
Starting datafile 1 with incarnation depth 0 in thread 1 sequence 794
Datafile 1: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF'
Starting datafile 2 with incarnation depth 0 in thread 1 sequence 794
Datafile 2: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF'
Starting datafile 3 with incarnation depth 0 in thread 1 sequence 794
Datafile 3: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF'
Starting datafile 4 with incarnation depth 0 in thread 1 sequence 794
Datafile 4: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'
Starting datafile 5 with incarnation depth 0 in thread 1 sequence 794
Datafile 5: 'E:\ORADB\USER1_1.ORA'
Starting datafile 6 with incarnation depth 0 in thread 1 sequence 794
Datafile 6: 'E:\ORADB\USER1_2.ORA'
Starting datafile 7 with incarnation depth 0 in thread 1 sequence 794
Datafile 7: 'E:\ORADB\USER1_3.ORA'
Starting datafile 8 with incarnation depth 0 in thread 1 sequence 794
Datafile 8: 'E:\ORADB\USER1_4.ORA'
Starting datafile 9 with incarnation depth 0 in thread 1 sequence 794
Datafile 9: 'E:\ORADB\INDEX1_1.ORA'
Starting datafile 10 with incarnation depth 0 in thread 1 sequence 794
Datafile 10: 'E:\ORADB\INDEX1_2.ORA'
Starting datafile 11 with incarnation depth 0 in thread 1 sequence 794
Datafile 11: 'E:\ORADB\TEMP1_1.ORA'
Media Recovery Log 
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup cont...
Tue Jul 15 13:43:03 2014
ALTER DATABASE RECOVER    LOGFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG'  
Tue Jul 15 13:43:03 2014
Media Recovery Log D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG
Completed: ALTER DATABASE RECOVER    LOGFILE 'D:\ORACLE\PRODU
Tue Jul 15 13:43:17 2014
alter database open resetlogs
RESETLOGS after complete recovery through change 3142208
Resetting resetlogs activation ID 1378452168 (0x522982c8)
Setting recovery target incarnation to 3
Tue Jul 15 13:43:18 2014
Setting recovery target incarnation to 3
Tue Jul 15 13:43:18 2014
Flashback Database Disabled 
Tue Jul 15 13:43:19 2014
Assigning activation ID 1380750902 (0x524c9636)
Maximum redo generation record size = 120832 bytes
Maximum redo generation change vector size = 116476 bytes
Private_strands 7 at log switch
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Tue Jul 15 13:43:19 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jul 15 13:43:19 2014
SMON: enabling cache recovery
Tue Jul 15 13:43:20 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_1484.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [3142214], [0], [3142438], [8388617], [], []

Tue Jul 15 13:43:21 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_1484.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [3142214], [0], [3142438], [8388617], [], []
Tue Jul 15 13:43:21 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600

这里出现ORA-600[2662]因为scn相差较小,直接重启几次,然后数据库出现如下启动正常但是出现ORA-01595和ORA-00600[4194]错误

Tue Jul 15 13:48:26 2014
Starting background process MMON
Starting background process MMNL
MMON started with pid=17, OS id=2896
MMNL started with pid=18, OS id=3828
Tue Jul 15 13:48:26 2014
Block recovery completed at rba 2.90.16, scn 0.3162303
Tue Jul 15 13:48:26 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\bdump\orcl_smon_3724.trc:
ORA-01595: error freeing extent (3) of rollback segment (1))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [57], [6], [], [], [], [], []

Tue Jul 15 13:48:26 2014
Completed: alter database open
Tue Jul 15 13:48:27 2014
Errors in file d:\oracle\product\10.1.0\admin\orcl\bdump\orcl_j000_2956.trc:
ORA-00600: internal error code, arguments: [4193], [1375], [1379], [], [], [], [], []

通过设置undo_management=manual,重建undo表空间解决,至此本数据库恢复完成,建议对其进行逻辑方式重建

记录一次系统回滚段坏块恢复

在上一篇中深入分析一次ORA-00314错误的数据库继续恢复,出现file 1 block 403和404等坏块,使得后面的恢复进一步写入了复杂境地.更加麻烦的是,这个库里面有一张核心表有40个字段,包括long,nvarchar2等一些字段,但是使用aul,dul,odu挖取都出现异常(表的行数正确,但是有些列的数据不能正常被挖出来),原因是该表中有特殊字段数据,被逼无赖只能继续拉库
数据库启动报错

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

查看alert日志

SMON: enabling cache recovery
Fri May 16 22:49:53 2014
Hex dump of (file 1, block 404) in trace file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc
Corrupt block relative dba: 0x00400194 (file 1, block 404)
Fractured block found during buffer read
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled
Reread of rdba: 0x00400194 (file 1, block 404) found same corrupted data
Fri May 16 22:49:55 2014
Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 404)
ORA-01110: data file 1: 'C:\ORADATA\INTERLIB\SYSTEM01.DBF'

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604

跟踪数据库启动过程
这里可以清晰的看到是因为block 404有坏块,数据库递归sql访问到该坏块报错,从而数据库无法继续open,对数据库启动过程做10046跟踪

PARSING IN CURSOR #2 len=148 dep=1 uid=0 oct=6 lid=0 tim=80533759 hv=3540833987 ad='2a6ce1ec'
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 #2:c=62500,e=211766,p=14,cr=85,cu=0,mis=1,r=0,dep=1,og=4,tim=80533755
BINDS #2:
kkscoacd
 Bind#0
  oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=2a6ceac2  bln=32  avl=09  flg=09
  value="_SYSSMU1$"
 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=099b9b7c  bln=24  avl=02  flg=05
  value=2
 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=099b9b58  bln=24  avl=02  flg=05
  value=9
 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=099b9b34  bln=24  avl=02  flg=05
  value=2
 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=099b9b10  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=099b9aec  bln=24  avl=04  flg=05
  value=13332
 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=099b9ac8  bln=24  avl=04  flg=05
  value=24672
 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=099b9aa4  bln=24  avl=05  flg=05
  value=47727002
 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=099b9a80  bln=24  avl=01  flg=05
  value=0
 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=099b9a5c  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=099b9a38  bln=24  avl=02  flg=05
  value=1
 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=099b9a14  bln=24  avl=02  flg=05
  value=1
 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=099b9ba0  bln=22  avl=02  flg=05
  value=1
WAIT #2: nam='db file sequential read' ela= 10794 file#=1 block#=404 blocks=1 obj#=-1 tim=80546515
Hex dump of (file 1, block 404)
Dump of memory from 0x1FB9C000 to 0x1FB9E000
1FB9C000 00000000 00000000 00000000 00000000  [................]
        Repeat 286 times
1FB9D1F0 00000000 00000000 00000000 00000001  [................]
1FB9D200 0000A200 0001D831 00000000 05010000  [....1...........]
1FB9D210 00007F30 00000000 00000000 00000000  [0...............]
1FB9D220 00000000 00000000 00000000 00000000  [................]
  Repeat 221 times
Corrupt block relative dba: 0x00400194 (file 1, block 404)
Fractured block found during buffer read
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled
Reread of rdba: 0x00400194 (file 1, block 404) found same corrupted data
EXEC #2:c=0,e=45015,p=1,cr=1,cu=3,mis=1,r=0,dep=1,og=4,tim=80578919
ERROR #2:err=1578 tim=811723
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 404)
ORA-01110: data file 1: 'C:\ORADATA\INTERLIB\SYSTEM01.DBF'
EXEC #1:c=328125,e=3468857,p=52,cr=619,cu=10,mis=0,r=0,dep=0,og=1,tim=82331842
ERROR #1:err=1092 tim=811898

dbv检测坏块

C:\oracle\product\10.2.0\db_1\BIN>dbv  FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF

DBVERIFY: Release 10.2.0.3.0 - Production on 星期一 5月 19 15:22:41 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF
页 403 标记为损坏
Corrupt block relative dba: 0x00400193 (file 1, block 403)
Bad header found during dbv:
Data in bad block:
 type: 70 format: 1 rdba: 0x00030030
 last change scn: 0x0000.3a6c2e79 seq: 0x0 flg: 0x00
 spare1: 0x4c spare2: 0x45 spare3: 0x2
 consistency value in tail: 0x00070000
 check value in block header: 0x1
 block checksum disabled

页 404 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00400194 (file 1, block 404)
Fractured block found during dbv: 
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x0
 block checksum disabled

页 498 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x004001f2 (file 1, block 498)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x004001f2
 last change scn: 0x0000.02d7eb9d seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x37290601
 check value in block header: 0x6c5e
 computed block checksum: 0xc2b5

页 61078 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040ee96 (file 1, block 61078)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040ee96
 last change scn: 0x0000.02d5cf11 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xa6e30601
 check value in block header: 0x51d4
 computed block checksum: 0xf572

页 61147 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040eedb (file 1, block 61147)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040eedb
 last change scn: 0x0000.02d7f7e6 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x6ed80601
 check value in block header: 0x4ae8
 computed block checksum: 0x893f

页 61502 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040f03e (file 1, block 61502)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040f03e
 last change scn: 0x0000.02d810dd seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaf4c0601
 check value in block header: 0xd99b
 computed block checksum: 0xbf91

页 61989 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0040f225 (file 1, block 61989)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040f225
 last change scn: 0x0000.02d80f65 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xdff70601
 check value in block header: 0x4e2a
 computed block checksum: 0xd092



DBVERIFY - 验证完成

检查的页总数: 62720
处理的页总数 (数据): 37740
失败的页总数 (数据): 0
处理的页总数 (索引): 7021
失败的页总数 (索引): 0
处理的页总数 (其它): 1784
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 16169
标记为损坏的总页数: 7
流入的页总数: 5
最高块 SCN            : 316431787 (0.316431787)

C:\oracle\product\10.2.0\db_1\BIN>

根据我们的经验,对数据库启动影响很大的,主要是403,404,498三个坏块,在10201的库中查询得出结论(403,404是rollback,498是seq$).那现在我们可以理解在数据库启动过程中需要undo$表中相关列信息,但是由于rollback对应的block有坏块,使得数据库无法操作update操作,从而无法正常启动.

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME

  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  1
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 1
输入 block_id 的值:  404
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 404 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------

SYS
SYSTEM
ROLLBACK           SYSTEM

常用方法
设置event跳过坏块,使用隐含参数屏蔽回滚段

  _corrupted_rollback_segments= _SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$, _SYSSMU11$, _SYSSMU12$, _SYSSMU13$, _SYSSMU14$, _SYSSMU15$, _SYSSMU16$, _SYSSMU17$, _SYSSMU18$, _SYSSMU19$, _SYSSMU20$
  undo_management          = MANUAL
  event                    = 10231 trace name context forever, level 10, 10232 trace name context forever, level 10, 10233 trace name context forever, level 10

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

这里可以看出来,数据库在update undo$的时候因为rollback异常无法通过屏蔽回滚段和跳过坏块的方法来解决。因为是system undo系统块,想法就是从别的相同版本库中拷贝一个相同位置块过来试试看。

拷贝数据块

C:\oracle\product\10.2.0\db_1\BIN>bbed listfile=c:\bbed.txt
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon May 19 10:47:14 2014

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set mode edit
        MODE            Edit

BBED> copy file 2 block 405 to file 1 block 405
 File: C:\ORADATA\INTERLIB\SYSTEM01.DBF (1)
 Block: 405              Offsets:    0 to  511           Dba:0x00400195
------------------------------------------------------------------------
 02a20000 94014000 d4c50800 00000104 60890000 00000500 36000000 66004545
 0000e81f 401fc81e 201ea01d 481da01c f81b501b e81a801a d8197019 08196018
 f8179017 e8168016 1816c815 68152815 d8145814 f8137813 18139812 3812f811
 a8116811 18119810 3810b80f 580fd80e 780e380e e80da80d 580d180d c80c480c
 f00b480b e00a380a d0092809 c0087008 1008d007 80074007 f006b006 60062006
 d0052805 a8046004 2004d003 d802b401 6001e000 00000000 0a001800 0c001400
 1300ff1d 78100000 78100000 02000000 00000000 0a165f45 00002e00 0201e71e
 94014000 4c004300 03020100 2b05c000 5e15c000 0c001800 0c002000 29000100
 82230000 82230000 02000000 00000000 0a163347 00003200 02014500 94014000
 58004600 03022100 f30ec000 f50ec000 00000000 00000000 03000000 0d000e00
 0e000e00 06c52008 4b5a2705 c51e3337 5f06c520 084b5a27 06c51f4c 28393906
 c520084b 5a2706c5 273e0355 0d000000 ff165f43 0a001800 0c001400 0d004000
 82230000 82230000 02000000 00000000 0a163346 00003200 02014500 94014000
 58004500 03020100 f30ec000 f80ec000 00000000 00000000 06c52008 4b5a2705
 c4201c60 5f000000 0c001800 0c002000 2a000100 82230000 82230000 02000000
 00000000 0a163345 00003200 02014500 94014000 58004400 03022100 f30ec000

 <32 bytes per line>

BBED> sum apply
Check value for File 1, Block 405:
current = 0x8960, required = 0x8960

重新启动数据库

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

分析alert日志

Mon May 19 10:54:05 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0
  Mem# 0: C:\ORADATA\INTERLIB\REDO03.LOG
Block recovery stopped at EOT rba 13.5.16
Block recovery completed at rba 13.5.16, scn 0.316366922
Doing block recovery for file 1 block 9
Block recovery from logseq 13, block 3 to scn 316366921
Mon May 19 10:54:05 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13 Reading mem 0
  Mem# 0: C:\ORADATA\INTERLIB\REDO03.LOG
Block recovery completed at rba 13.5.16, scn 0.316366922
Mon May 19 10:54:07 2014
Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_1208.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码, 参数: [4193], [102], [58], [], [], [], [], []

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604

这里是因为我们拷贝了一个其他库的undo段过来,然后数据库启动的时候首先使用到该undo块和rollback segment header 不匹配,所以通过通过修改undo header 来修复,使用bbed修改段头信息,因为在以前的文章中描述过,在此不再重复,具体参考:使用bbed解决ORA-00607/ORA-00600[4194]故障启动数据库

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             201329808 bytes
Database Buffers          243269632 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open upgrade;

数据库已更改。

但是因为seq$有坏块,数据库启动后,如果使用非系统认证登录数据库会报如下错误

C:\oracle\product\10.2.0\db_1\BIN>sqlplus interlib/oracle

SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 5月 19 16:29:29 2014

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-00600: 内部错误代码, 参数: [6807], [AUDSES$], [144], [], [], [], [], []


请输入用户名:

C:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 5月 19 16:30:08 2014

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select object_type from dba_objects where object_name='AUDSES$';

OBJECT_TYPE
-------------------
SEQUENCE

因为seq$有坏块导致该问题,因为该数据库需要重建,使用exp导出来数据,然后重建完成相关工作