admin – 第42页 – Database SOS

记录一次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表空间解决,至此本数据库恢复完成,建议对其进行逻辑方式重建

误drop tablespace后使用flashback database闪回异常处理

有朋友夜间打电话请求技术支持,数据库表空间被删除，然后使用flashback database 无法正常恢复。通过分析alert日志发现，创建表空间(xifenfei 别名),发现已经存在,就删除了该表空间(其实是业务核心表空间,误删除了,是否是连接错了数据库？)

Sat Jul 05 17:10:06 2014
create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF'
size 50M autoextend on next 50M maxsize 1536M extent management local
Sat Jul 05 17:10:06 2014
ORA-1543 signalled during: create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF'
size 50M autoextend on next 50M maxsize 1536M extent management local
...
Sat Jul 05 17:10:59 2014
drop tablespace XIFENFEI
Sat Jul 05 17:10:59 2014
ORA-1549 signalled during: drop tablespace XIFENFEI
...
Sat Jul 05 17:11:05 2014
drop tablespace XIFENFEI
ORA-1549 signalled during: drop tablespace XIFENFEI
...
Sat Jul 05 17:11:24 2014
drop tablespace XIFENFEI including contents
Sat Jul 05 17:11:36 2014
Thread 1 advanced to log sequence 186895 (LGWR switch)
  Current log# 1 seq# 186895 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO01.LOG
Sat Jul 05 17:11:36 2014
ARC3: 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.
Sat Jul 05 17:11:43 2014
LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_4
Sat Jul 05 17:11:49 2014
LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_2
Sat Jul 05 17:12:09 2014
Starting control autobackup
Control autobackup written to DISK device
	handle 'D:\FULLBACK\C-1342406147-20140705-00'
Completed: drop tablespace XIFENFEI including contents

通过这里可以发现删除表空间时间点为2014年7月5日17:12:09

闪回数据库到删除之前时间点

Sat Jul 05 18:16:54 2014
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sat Jul 05 18:19:23 2014
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')
Sat Jul 05 18:19:25 2014
Flashback Restore Start
Sat Jul 05 18:20:52 2014
--闪回时的控制文件中无表空间XIFENFEI信息(因为已经被删除)，
--但是由于闪回的system 数据字典里面有相关文件信息,因此数据库在控制文件里面创建相关文件信息
Flashback: created tablespace #6: 'XIFENFEI' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00012' for tablespace #6 in the controlfile.
Filename was:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI4.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback: created OFFLINE file 'UNNAMED00010' for tablespace #6 in the controlfile.
Filename was:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI3.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback: created OFFLINE file 'UNNAMED00008' for tablespace #6 in the controlfile.
Filename was:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI2.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6 in the controlfile.
Filename was:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback Restore Complete
Flashback Media Recovery Start
 parallel recovery started with 15 processes
Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86891_0766797318.001
Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86892_0766797318.001
Sat Jul 05 18:21:40 2014
Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86893_0766797318.001
Sat Jul 05 18:21:47 2014
WARNING: inbound connection timed out (ORA-3136)
Sat Jul 05 18:22:11 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 186894 Reading mem 0
  Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO03.LOG
Sat Jul 05 18:22:39 2014
Incomplete Recovery applied until change 9078991241
Flashback Media Recovery Complete
ORA-38795 signalled during: FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')...
Sat Jul 05 18:30:11 2014
ALTER DATABASE OPEN RESETLOGS
Sat Jul 05 18:30:11 2014
ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...
--重命名相关UNNAMExxxxx文件名到硬盘上被删除表空间文件
Sat Jul 05 18:39:31 2014
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF'
Sat Jul 05 18:39:31 2014
Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF'
Sat Jul 05 18:39:47 2014
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF'
Sat Jul 05 18:39:47 2014
Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF'
Sat Jul 05 18:39:59 2014
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF'
Sat Jul 05 18:39:59 2014
Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF'
Sat Jul 05 18:40:12 2014
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF'
Sat Jul 05 18:40:12 2014
Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012'
to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF'
Sat Jul 05 18:41:25 2014
ALTER DATABASE OPEN RESETLOGS
Sat Jul 05 18:41:25 2014
ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...

到这里,可以看出来,因为数据库整体已经闪回,但是被drop 表空间的四个数据文件未被正常闪回,因此该四个文件的scn可能异常,通过数据库恢复检查脚本(Oracle Database Recovery Check)检查结果如下
datafile_scn
datafile_header_scn
这里很明显控制文件中的scn信息混乱不做过多参考,数据文件头信息看到只有ts# 6中的四个文件(就是被删除的表空间文件)scn过大,其他文件scn都处于正常状态(处于干净状态),到这里很明显,数据库闪回成功,但是被drop tablespace的数据文件未被闪回,因此该故障可以通过bbed修改四个文件头信息和其他文件相同即可使得数据库恢复正常
温馨提示:数据库操作需要慎重,备份重于一切

数据库恢复历史再次刷新到Oracle 7.3.2版本—redo异常恢复

有网友在QQ上找我,说Oracle 7.3的数据库,因为redo异常咨询我是否可以恢复
qq咨询

检查数据库得到以下信息

SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle7 Workgroup Server Release 7.3.2.2.1 - Production Release
PL/SQL Release 2.3.2.2.0 - Production
CORE Version 3.5.2.0.0 - Production
TNS for 32-bit Windows: Version 2.3.2.1.0 - Production
NLSRTL Version 3.2.2.0.0 - Production
已选择 5 行

数据文件信息

qq2

redo信息
qq3

跳过redo进行恢复,在resetlogs过程中报rbs表空间坏块,然后通过dul工具获得回滚段名称,然后使用隐含参数屏蔽掉

License high water mark = 2
Starting up ORACLE RDBMS Version: 7.3.2.2.1.
System parameters with non-default values:
  processes                = 800
  shared_pool_size         = 540000000
  control_files            = D:\ORANT\DATABASE\ctl1orcl.ora, D:\ORANT\DATABASE\ctl2orcl.ora
  compatible               = 7.3.0.0.0
  log_buffer               = 327680
  log_checkpoint_interval  = 1000000
  db_files                 = 40
  db_file_simultaneous_writes= 1280
  max_rollback_segments    = 12800
  _offline_rollback_segments= RB13, RB14, RB15, RB16, RB20
  _corrupted_rollback_segments= RB13, RB14, RB15, RB16, RB20
  sequence_cache_entries   = 100
  sequence_cache_hash_buckets= 100
  remote_login_passwordfile= SHARED
  mts_servers              = 0
  mts_max_servers          = 0
  mts_max_dispatchers      = 0
  audit_trail              = NONE
  sort_area_retained_size  = 65536
  sort_direct_writes       = AUTO
  db_name                  = oracle
  open_cursors             = 800
  text_enable              = TRUE
  snapshot_refresh_processes= 1
  background_dump_dest     = %RDBMS73%\trace
  user_dump_dest           = %RDBMS73%\trace
Mon Jun 16 16:46:57 2014
PMON started
Mon Jun 16 16:46:57 2014
DBWR started
Mon Jun 16 16:46:57 2014
LGWR started
Mon Jun 16 16:46:57 2014
RECO started
Mon Jun 16 16:46:57 2014
SNP0 started
Mon Jun 16 16:46:57 2014
alter database  mount exclusive
Mon Jun 16 16:46:58 2014
Successful mount of redo thread 1.
Mon Jun 16 16:46:58 2014
Completed: alter database  mount exclusive
Mon Jun 16 16:48:15 2014
alter database open
Mon Jun 16 16:48:16 2014
Beginning crash recovery of 1 threads
Crash recovery completed successfully
Mon Jun 16 16:48:17 2014
Thread 1 advanced to log sequence 9
  Current log# 1 seq# 9 mem# 0: D:\ORANT\DATABASE\LOG2ORCL.ORA
Thread 1 opened at log sequence 9
  Current log# 1 seq# 9 mem# 0: D:\ORANT\DATABASE\LOG2ORCL.ORA
Successful open of redo thread 1.
Mon Jun 16 16:48:18 2014
SMON: enabling cache recovery
Mon Jun 16 16:48:19 2014
Completed: alter database open
Mon Jun 16 16:48:20 2014
SMON: enabling tx recovery
SMON: about to recover undo segment 14
SMON: mark undo segment 14 as needs recovery
SMON: about to recover undo segment 15
SMON: mark undo segment 15 as needs recovery
SMON: about to recover undo segment 16
SMON: mark undo segment 16 as needs recovery
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as needs recovery
SMON: about to recover undo segment 18
SMON: mark undo segment 18 as needs recovery
Mon Jun 16 16:48:20 2014
Errors in file D:\ORANT\RDBMS73\trace\orclSMON.TRC:
ORA-00600: internal error code, arguments: [4306], [21], [2], [], [], [], [], []

数据库在启动过程中出现ORA-00600[4306]，导致smon异常。该错误是因为在数据库open过程中smon会清理临时段从而出现该错误,通过设置event跳过，数据库算整体打开，不过在恢复过程中还遇到了

Mon Jun 16 17:53:10 2014
Errors in file D:\ORANT\RDBMS73\trace\orclDBWR.TRC:
ORA-00600: internal error code, arguments: [3600], [3], [14], [], [], [], [], []
Mon Jun 16 18:05:12 2014
Errors in file D:\ORANT\RDBMS73\trace\ORA06880.TRC:
ORA-01578: ORACLE数据块有错(文件号12, 块号46644)
ORA-01110: 文件'12'没有联机
ORA-00600: 内部错误码, 变元: [4194], [18], [5], [], [], []
ORA-00600: 内部错误码, 变元: [4194], [18], [5], [], [], []

ORA-00600[3600]是因为在offline 回滚段所在表空间锁出现的问题
ORA-00600[4194]是因为回滚段所在的表空间数据文件出现坏块所导致

Oracle安全警示录:加错裸设备导致redo异常

最近一个朋友数据库异常了,咨询我,通过分析日志发现对方人员根本不懂aix中的裸设备和Oracle数据库然后就直接使用OEM创建新表空间,导致了数据库crash而且不能正常启动

Thread 1 advanced to log sequence 4395
  Current log# 1 seq# 4395 mem# 0: /dev/rorcl_redo01
Thu Jun 12 19:28:38 2014
/* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/orcl_redo04' SIZE 2000M EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT  AUTO
ORA-1119 signalled during: /* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/orcl_redo04'
SIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ...
Thu Jun 12 19:36:23 2014
/* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/orcl_redo03' SIZE 2000M EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT  AUTO
Thu Jun 12 19:43:56 2014
ORA-604 signalled during: /* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/orcl_redo03'
SIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ...
Thu Jun 12 19:48:11 2014
/* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/rorcl_redo03' SIZE 2000M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
Thu Jun 12 19:48:11 2014
ORA-1537 signalled during: /* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/rorcl_redo03'
 SIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ...
Thu Jun 12 19:48:20 2014
/* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/rorcl_redo04' SIZE 2000M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
ORA-1537 signalled during: /* OracleOEM */ CREATE SMALLFILE TABLESPACE "XIFENFEI" LOGGING DATAFILE '/dev/rorcl_redo04'
SIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ...
Fri Jun 13 00:50:37 2014
Trace dumping is performing id=[cdmp_20140613005032]
Fri Jun 13 00:50:40 2014
Reconfiguration started (old inc 4, new inc 6)
List of nodes:
 0
 Global Resource Directory frozen
 * dead instance detected - domain 0 invalid = TRUE
…………
Fri Jun 13 00:50:40 2014
Beginning instance recovery of 1 threads
Reconfiguration complete
Fri Jun 13 00:50:41 2014
 parallel recovery started with 7 processes
Fri Jun 13 00:50:43 2014
Started redo scan
Fri Jun 13 00:50:43 2014
Errors in file /oracle/admin/orcl/bdump/orcl1_smon_213438.trc:
ORA-00316: log 3 of thread 2, type 0 in header is not log file
ORA-00312: online log 3 thread 2: '/dev/rorcl_redo03'
Fri Jun 13 00:50:43 2014
Errors in file /oracle/admin/orcl/bdump/orcl1_smon_213438.trc:
ORA-00316: log 3 of thread 2, type 0 in header is not log file
ORA-00312: online log 3 thread 2: '/dev/rorcl_redo03'
SMON: terminating instance due to error 316
Fri Jun 13 00:50:43 2014
Errors in file /oracle/admin/orcl/bdump/orcl1_lgwr_335980.trc:
ORA-00316: log  of thread , type  in header is not log file
Instance terminated by SMON, pid = 213438

从这里可以看出来,在使用OEM创建表空间的过程中犯了两个错误
1. 未分清楚aix的块设备和字符设备的命名方式
2. 对于2节点正在使用的current redo作为不适用设备当作未使用设备来创建新表空间
由于创建表空间的使用了错误的文件和错误的设备,导致2节点的当前redo(/dev/rorcl_redo03)被损坏(因为先读redo header,所以数据库中优先反馈出来的是ORA-00316: log of thread , type in header is not log file).从而导致数据库2节点先crash,然后节点1进行实例恢复,但是由于2节点的current redo已经损坏,导致实例恢复无法完成,从而两个节点都crash.因为是rac的一个节点的当前redo损坏,数据库无法正常.
如果有备份该数据库可以使用备份还原进行恢复,如果没有备份只能使用强制拉库的方法抢救数据.希望不要发生一个大的数据丢失悲剧
介绍这个案例希望给大家以警示:对数据库的裸设备操作请谨慎,不清楚切不可乱操作,否则后果严重

记录一次ORA-00600[kdxlin:psno out of range]/ORA-00600[3020]/ORA-00600[4000]/ORA-00600[4193]的数据库恢复

尝试recover database,遭遇ORA-00600[kdxlin:psno out of range]/ORA-00600[3020]/ORA-00354错误

Media Recovery Log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5645 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\REDO01.LOG
Mon Jun 09 15:36:10 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p001_9604.trc:
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], []
Mon Jun 09 15:36:12 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p002_9592.trc:
ORA-00600: internal error code, arguments: [3020], [3], [23337], [12606249], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 23337)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\SYSAUX01.DBF'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Mon Jun 09 15:36:12 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p001_9604.trc:
ORA-10562: Error occurred while applying redo to data block (file# 3, block# 20142)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 47841
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], []
Mon Jun 09 15:36:13 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p002_9592.trc:
ORA-00600: internal error code, arguments: [3020], [3], [23337], [12606249], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 23337)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\SYSAUX01.DBF'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Errors with log
Mon Jun 09 15:36:14 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p000_9600.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2357 change 25400286 time 06/06/2014 04:00:41
ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\REDO02.LOG'
Mon Jun 09 15:36:14 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p000_9600.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [1490], [6401], [], [], [], []
Mon Jun 09 15:36:16 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_p000_9600.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 1490)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 203
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [1490], [6401], [], [], [], []
Media Recovery failed with error 12801
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

因为数据库允许少量丢失数据,且redo文件发生损坏,直接使用隐含参数屏蔽redo前滚,尝试强制拉库,报ORA-00704,ORA-00600[4000]错误

Mon Jun 09 15:57:51 2014
SMON: enabling cache recovery
Mon Jun 09 15:57:51 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\udump\gtgs_ora_8664.trc:
ORA-00600: 内部错误代码, 参数: [4000], [1], [], [], [], [], [], []
Mon Jun 09 15:57:52 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\udump\gtgs_ora_8664.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [4000], [1], [], [], [], [], [], []
Mon Jun 09 15:57:52 2014
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Mon Jun 09 15:57:52 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_pmon_9760.trc:
ORA-00704: bootstrap process failure
Mon Jun 09 15:57:52 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_reco_5244.trc:
ORA-00704: bootstrap process failure
Mon Jun 09 15:57:52 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_smon_7096.trc:
ORA-00704: bootstrap process failure
Mon Jun 09 15:57:53 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_ckpt_7924.trc:
ORA-00704: bootstrap process failure
Mon Jun 09 15:57:53 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_lgwr_708.trc:
ORA-00704: bootstrap process failure
Mon Jun 09 15:57:53 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_dbw0_7400.trc:
ORA-00704: bootstrap process failure
Mon Jun 09 15:57:53 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_mman_9836.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 8664
ORA-1092 signalled during: alter database open resetlogs...

对数据库启动过程做10046,然后使用bbed修改scn绕过该错误,然后继续尝试打开数据库,报ORA-00604/ORA-00607/ORA-00600[4193]错误

Mon Jun 09 16:01:09 2014
SMON: enabling cache recovery
Mon Jun 09 16:01:10 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\udump\gtgs_ora_7548.trc:
ORA-00600: 内部错误代码, 参数: [4193], [57], [51], [], [], [], [], []
Mon Jun 09 16:01:10 2014
Doing block recovery for file 1 block 397
Block recovery range from rba 2.3.0 to scn 0.1073741830
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\REDO02.LOG
Block recovery stopped at EOT rba 2.5.16
Block recovery completed at rba 2.5.16, scn 0.1073741830
Doing block recovery for file 1 block 9
Block recovery range from rba 2.3.0 to scn 0.1073741829
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GTGS\REDO02.LOG
Block recovery completed at rba 2.5.16, scn 0.1073741830
Mon Jun 09 16:01:11 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\udump\gtgs_ora_7548.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码, 参数: [4193], [57], [51], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Mon Jun 09 16:01:11 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_reco_9176.trc:
ORA-00604: error occurred at recursive SQL level
Mon Jun 09 16:01:11 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_smon_7932.trc:
ORA-00604: error occurred at recursive SQL level
Mon Jun 09 16:01:12 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_ckpt_7428.trc:
ORA-00604: error occurred at recursive SQL level
Mon Jun 09 16:01:12 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_lgwr_6936.trc:
ORA-00604: error occurred at recursive SQL level
Mon Jun 09 16:01:12 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_dbw0_404.trc:
ORA-00604: error occurred at recursive SQL level
Mon Jun 09 16:01:12 2014
Errors in file d:\oracle\product\10.1.0\admin\gtgs\bdump\gtgs_mman_7968.trc:
ORA-00604: error occurred at recursive SQL level
Instance terminated by USER, pid = 7548
ORA-1092 signalled during: ALTER DATABASE OPEN...

该错误的原因是因为数据库在启动的过程中,会事先利用上次数据库运行过程中system undo segment header指向的block,而该block异常,所以出现该错误,使用bbed/dul之类的工具清除掉undo seg header 指向block指针,然后数据库启动会重新分配一个block,从而实现数据库正常启动.

记录一次ORA-600 kccpb_sanity_check_2和ORA-600 kcbgtcr_13 错误恢复

晚上朋友告诉我数据库不能open,请求技术支持,检查alert日志发现ORA-00600[kccpb_sanity_check_2]错误导致数据库无法正常mount

Fri Jun  6 23:36:08 2014
alter database mount
Fri Jun  6 23:36:08 2014
This instance was first to mount
Fri Jun  6 23:36:12 2014
Errors in file /on3000/oracle/admin/on3000/udump/on30001_ora_295198.trc:
ORA-00600:内部错误代码, 参数:[kccpb_sanity_check_2], [18045], [17928], [0x000000000], [], [], [], []
ORA-600 signalled during: alter database mount...

依次替换三个控制文件依然无法解决该问题。查询MOS得到解释为[435436.1]

ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is
higher than the seq# of the control file header block. This is indication of
the lost write of the header block during commit of the previous cf transaction.

出现该故障的原因是因为写丢失导致,而解决该故障的方法有

1) restore a backup of a controlfile and recover
OR
2) recreate the controlfile
OR
3) restore the database from last good backup and recover

该数据库为无备份非归档数据库,因此只能重建控制文件来解决ORA-00600[kccpb_sanity_check_2]故障,通过重建控制文件数据库mount成功.但是在open的过程中又出现需要一个不存在的归档日志(数据库一个节点5月5日异常,另外一个节点5月23日异常,到6月6日我接手中间进行了N多操作,未细细分析原因).
Oracle Database Recovery Check Result检查结果
数据库SCN

控制文件中关于数据文件SCN

数据文件头SCN

REDO SCN

这里明显表示thread#缺少归档,导致恢复过程出现如下提示

最后没有办法只能使用_allow_resetlogs_corruption参数跳过redo,然后去open数据库,很不幸出现更加悲催的ORA-00704和ORA-00600[kcbgtcr_13]错误,导致数据库open失败

Sat Jun  7 00:28:58 2014
SMON: enabling cache recovery
Sat Jun  7 00:28:58 2014
Errors in file /on3000/oracle/admin/on3000/udump/on30001_ora_344084.trc:
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []
Sat Jun  7 00:28:59 2014
Errors in file /on3000/oracle/admin/on3000/udump/on30001_ora_344084.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []
Sat Jun  7 00:28:59 2014
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 344084
ORA-1092 signalled during: alter database open...

对启动过程做10046发现

*** 2014-06-07 00:28:58.528
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [kcbgtcr_13], [], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000004 ? 10538629C ?
ksedmp+0290          bl       ksedst               104A2CDB0 ?
ksfdmp+0018          bl       03F2735C
kgerinv+00dc         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000000 ? 10564B4CC ?
                                                   000000004 ? 70000006D3FD6F0 ?
                                                   000000000 ?
kcbassertbd+0074     bl       kgeasnmierr          110195490 ? 110486310 ?
                                                   10564BD54 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 11043DC90 ?
kcbgtcr+2a68         bl       kcbassertbd          FFFFFFFFFFE7D00 ?
                                                   FFFFFFFFFE7D28 ?
kturbk1+0258         bl       kcbgtcr              000000000 ? 104D23384 ?
                                                   104D2330C ? 000000000 ?
ktrgcm+1294          bl       kturbk1              F0000000F ? FFFFFFFFFFE84B8 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFE84D0 ? 000000000 ?
ktrgtc+0660          bl       ktrgcm               1104B7600 ?
kdsgrp+0094          bl       ktrgtc               0000006C8 ? 000000000 ?
                                                   FFFFFFFFFFE8D80 ?
                                                   28242043335E5162 ?
                                                   103A06D48 ? 70000006F6C87B8 ?
                                                   1051C3528 ?
kdsfbrcb+0298        bl       kdsgrp               1044726E4 ?
                                                   433000000000003B ?
                                                   FFFFFFFFFFE8E30 ?
qertbFetchByRowID+0  bl       03F27E18
69c
qerstFetch+00ec      bl       01F9482C
opifch2+141c         bl       03F25E6C
opifch+003c          bl       opifch2              FFFFFFFFFFEC3D8 ? 000000000 ?
                                                   FFFFFFFFFFEA360 ?
opiodr+0ae0          bl       _ptrgl
rpidrus+01bc         bl       opiodr               5FFFEC840 ? 200000000 ?
                                                   FFFFFFFFFFEC850 ? 500000000 ?
skgmstack+00c8       bl       _ptrgl
rpidru+0088          bl       skgmstack            11049A820 ? 110195490 ?
                                                   000000002 ? 000000000 ?
                                                   FFFFFFFFFFEC3E8 ?
rpiswu2+034c         bl       _ptrgl
rpidrv+095c          bl       rpiswu2              70000006F6C7250 ? 1104851E8 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   1101B8C48 ? 000000000 ?
rpifch+0050          bl       rpidrv               5FFFEC840 ? 500000000 ?
                                                   FFFFFFFFFFEC850 ? 06CD75F70 ?
kqdpts+0134          bl       rpifch               11022A3E0 ?
kqrlfc+0258          bl       kqdpts               000000000 ?
kqlbplc+00b4         bl       03F28AF0
kqlblfc+0204         bl       kqlbplc              10011A9A8 ?
adbdrv+1978          bl       01F944B4
opiexe+2c08          bl       adbdrv
opiosq0+19f0         bl       opiexe               FFFFFFFFFFF9550 ? 100000000 ?
                                                   FFFFFFFFFFF8F20 ?
kpooprx+0168         bl       opiosq0              3693644A8 ? 700000010003520 ?
                                                   700000069364428 ?
                                                   A4000110195490 ?
kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB774 ?
                                                   FFFFFFFFFFFB500 ?
                                                   1B0000001B ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   000000000 ? 11039FA18 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F971E8
opiino+0990          bl       opitsk               1E00000000 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F96034
sou2o+0090           bl       opidrv               3C02D9A29C ? 4A006E298 ?
                                                   FFFFFFFFFFFF6B0 ?
opimai_real+01bc     bl       01F939B4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0070         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
--undo cr构造记录
Dump of buffer cache at level 1 for tsn=4, rdba=16777293
BH (700000035fb77b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b4000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f969c8,70000006d3fd868] lru: [700000035fb7728,70000006d6acff0]
  ckptq: [NULL] fileq: [NULL] objq: [700000035fb7798,7000000693932c0]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fb],[sfl: 0x0]
  flags:
BH (700000035f969c8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000353d6000
  set: 9 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035ff9398,700000035fb77b8] lru: [700000035f96938,70000006d6aca98]
  ckptq: [NULL] fileq: [NULL] objq: [700000035f969a8,700000069390250]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fa],[sfl: 0x0]
  flags:
BH (700000035ff9398) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 700000035f70000
  set: 12 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035fd86b8,700000035f969c8] lru: [700000035ff9528,70000006d6adaa0]
  ckptq: [NULL] fileq: [NULL] objq: [7000000693973c8,7000000693973c8]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f9],[sfl: 0x0]
  flags:
BH (700000035fd86b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 700000035b94000
  set: 11 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035fb76a8,700000035ff9398] lru: [700000035fd8848,70000006d6ad548]
  ckptq: [NULL] fileq: [NULL] objq: [700000069396398,700000069396398]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f8],[sfl: 0x0]
  flags:
BH (700000035fb76a8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b2000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f968b8,700000035fd86b8] lru: [700000035fb7948,700000035fb7838]
  ckptq: [NULL] fileq: [NULL] objq: [7000000693932c0,700000035fb78a8]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f7],[sfl: 0x0]
  flags:
BH (700000035f968b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000353d4000
  set: 9 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [70000006d3fd868,700000035fb76a8] lru: [700000035f96b58,700000035f96a48]
  ckptq: [NULL] fileq: [NULL] objq: [700000069390250,700000035f96ab8]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5f6],[sfl: 0x0]
  flags:
WAIT #5: nam='db file sequential read' ela= 135 file#=4 block#=77 blocks=1 obj#=-1 tim=1107709395109
on-disk scn: 0x0.19af5d47
BH (700000035fb77b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b4000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f969c8,70000006d3fd868] lru: [700000035fb7728,70000006d6acff0]
  ckptq: [NULL] fileq: [NULL] objq: [700000035fb7798,7000000693932c0]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fb],[sfl: 0x0]
  flags:
Dump of buffer cache at level 10 for tsn=4, rdba=16777293
BH (700000035fb77b8) file#: 4 rdba: 0x0100004d (4/77) class: 46 ba: 7000000357b4000
  set: 10 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: -1 objn: 0 tsn: 4 afn: 4
  hash: [700000035f969c8,70000006d3fd868] lru: [700000035fb7728,70000006d6acff0]
  ckptq: [NULL] fileq: [NULL] objq: [700000035fb7798,7000000693932c0]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.19afb5fb],[sfl: 0x0]
  flags:
  buffer tsn: 4 rdba: 0x0100004d (4/77)
  scn: 0x0000.19af5d47 seq: 0x01 flg: 0x04 tail: 0x5d470201
  frmt: 0x02 chkval: 0x6d2e type: 0x02=KTU UNDO BLOCK
--obj$ block dump记录
Block header dump:  0x0040007a
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x00.19afb597  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000f.012.0002c79c  0x0100004d.6113.1c  --U-    1  fsc 0x0000.19afb598

这里可以知道,数据库在读取obj$的时候使用到了undo cr块的构造,由于某种原因导致构造cr块失败,从而出现ORA-00600[kcbgtcr_13]错误,而因为obj$又在bootstarp$里面,因此又出现ORA-704.所以解决该问题的方法就是让数据库在查询obj$表的时候不再进行cr块构造,比如使用bbed提交事务等方法解决.这里使用bbed提交事务(bbed模拟提交事务一之修改itl),数据库启动成功

SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1610612736 bytes
Fixed Size                  2084400 bytes
Variable Size             973078992 bytes
Database Buffers          620756992 bytes
Redo Buffers               14692352 bytes
数据库装载完毕。
数据库已经打开。

ORA-27086: skgfglk: unable to lock file – already in use

使用nas存储存放控制文件,数据文件的数据库服务器,因为突然断电后,数据库系统无法正常启动,报ORA-27086: skgfglk: unable to lock file – already in use错误,通过分析是因为netapp的nfs锁导致该故障.本文为同事的处理过程记录
数据库启动报错

[oraprod@erpdb dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Thu May 29 22:03:00 2014
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  581506668 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers               10629120 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

alert日志

Thu May 29 23:37:56 2014
ORA-00202: controlfile: '/erpdata/PROD/proddata/cntrl01.dbf'
ORA-27086: skgfglk: unable to lock file - already in use
Linux Error: 11: Resource temporarily unavailable
Additional information: 8
Thu May 29 23:37:56 2014
ORA-205 signalled during: ALTER DATABASE   MOUNT...

查看控制文件

[oraprod@erpdb ~]$ ls -ltr /erpdata/PROD/proddata/cntrl01.dbf
-rw-r-----  1 oraprod dba 16293888 May 30  2014 /erpdata/PROD/proddata/cntrl01.dbf
[oraprod@erpdb ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              97G  4.3G   87G   5% /
/dev/sda1             190M   12M  169M   7% /boot
none                  8.0G     0  8.0G   0% /dev/shm
/dev/sdb1             577G  169G  379G  31% /erpdata
/dev/sdb2             241G   16G  213G   7% /oracle
/dev/sda3             9.7G   55M  9.1G   1% /tmp
192.168.1.13:/vol/vol1/backup
                      1.4T  199G  1.3T  14% /erpdbbak
192.168.1.11:/vol/vol1/erpdb
                      150G   84G   67G  56% /erpdata/PROD

这里可以看出来,控制文件是存在的,但是控制文件是存放在nfs中,重试umount/mount /erpdata/PROD文件系统,问题依旧.检查nfs系统所在存储(netapp存储)锁情况

nas设备锁情况

netapp2*> priv set advanced
netapp2*> lock status -h
======== NLM host erpdb.xifenfei.com
10688 0x01b70b28:0x00b83a2c 0:0 1 GRANTED (0x000000005fc63a58)
10688 0x01b70b28:0x00b83a2b 0:0 1 GRANTED (0x000000005fc63398)
10686 0x01b70b28:0x0135d0b3 0:0 1 GRANTED (0x0000000065f73a58)
10686 0x01b70b28:0x0135d0b2 0:0 1 GRANTED (0x00000000161a3b78)
10686 0x01b70b28:0x0135d0b1 0:0 1 GRANTED (0x00000001286246f8)
…………
10686 0x01b70b28:0x01770860 0:0 1 GRANTED (0x000000007e8fb938)
10690 0x01b70b28:0x007adc2a 0:0 1 GRANTED (0x0000000133e7e818)
10690 0x01b70b28:0x007adc29 0:0 1 GRANTED (0x00000000161a3c98)
10690 0x01b70b28:0x007adc28 0:0 1 GRANTED (0x000000017a2606f8)

使用相关命令解锁

storage*> sm_mon -l erpdb.xifenfei.com
storage*> lock status -h

启动数据库恢复正常

查询mos文档,发现相关文章主要有:
NFS Locking Problems Encountered During Database Startup (Doc ID 236794.1)
ORA-1157 ORA-1110 ORA-27086 Starting up Database (Doc ID 145194.1)

数据库恢复遭遇ORA-00600[3705]

某个客户在一台机器上装3个oracle数据库，机器蓝屏后，使用pe拷贝出来所有数据文件，redo文件，控制文件等，在尝试恢复过程中，三个库都出现同样的ORA-600[3705]错误,在以前的数据库恢复中对于redo异常，使用过N次类似方法出来都未出问题，但是在ORACLE 9.2.0.1版本中确实出现诡异现象，这里记录处理过程和大家分享
尝试恢复数据库

C:\Documents and Settings\Administrator>set oracle_sid=telnet
C:\Documents and Settings\Administrator>sqlplus/nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期日 5月 25 13:04:29 2014
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn / as sysdba
已连接。
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-01113: 文件 2 需要介质恢复
ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\TELNET\UNDOTBS01.DBF'
SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00322: 日志 2 (线程 1) 不是当前副本
ORA-00312: 联机日志 2 线程 1: 'E:\ORACLE\ORADATA\TELNET\REDO02.LOG'

redo异常,尝试使用_allow_resetlogs_corruption参数启动数据库

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 66763056 (在 05/12/2014 09:50:10 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\ORACLE\ORA92\RDBMS\ARC00312.001
ORA-00280: 更改 66763056 对于线程 1 是按序列 # 312 进行的
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件1需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\TELNET\SYSTEM01.DBF'
ORA-01112: 未启动介质恢复
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束

查看alert日志

Sun May 25 15:35:02 2014
ALTER DATABASE RECOVER    CANCEL
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
Sun May 25 15:35:02 2014
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Sun May 25 15:35:09 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 66763056
Resetting resetlogs activation ID 0 (0x0)
Sun May 25 15:35:20 2014
Assigning activation ID 2117757301 (0x7e3a6975)
Sun May 25 15:35:20 2014
Errors in file e:\oracle\admin\telnet\bdump\telnet_lgwr_12140.trc:
ORA-00600: internal error code, arguments: [3705], [1], [1], [1], [1], [], [], []
Sun May 25 15:35:56 2014
Errors in file e:\oracle\admin\telnet\bdump\telnet_lgwr_12140.trc:
ORA-00600: internal error code, arguments: [3705], [1], [1], [1], [1], [], [], []
LGWR: terminating instance due to error 600
Instance terminated by LGWR, pid = 12140

创建控制文件继续恢复

SQL> recover database until cancel;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码，参数: [2130], [0], [1], [2], [], [], [], []
SQL> alter database backup controlfile to trace as 'c:\ctl.txt';
数据库已更改。
SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TELNET" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'E:\ORACLE\ORADATA\TELNET\REDO01.LOG'  SIZE 100M,
  9    GROUP 2 'E:\ORACLE\ORADATA\TELNET\REDO02.LOG'  SIZE 100M,
 10    GROUP 3 'E:\ORACLE\ORADATA\TELNET\REDO03.LOG'  SIZE 100M
 11  DATAFILE
 12    'E:\ORACLE\ORADATA\TELNET\SYSTEM01.DBF',
 13    'E:\ORACLE\ORADATA\TELNET\UNDOTBS01.DBF',
 14    'E:\ORACLE\ORADATA\TELNET\CWMLITE01.DBF',
 15    'E:\ORACLE\ORADATA\TELNET\DRSYS01.DBF',
 16    'E:\ORACLE\ORADATA\TELNET\EXAMPLE01.DBF',
 17    'E:\ORACLE\ORADATA\TELNET\INDX01.DBF',
 18    'E:\ORACLE\ORADATA\TELNET\ODM01.DBF',
 19    'E:\ORACLE\ORADATA\TELNET\TOOLS01.DBF',
 20    'E:\ORACLE\ORADATA\TELNET\USERS01.DBF',
 21    'E:\ORACLE\ORADATA\TELNET\XDB01.DBF'
 22  CHARACTER SET ZHS16GBK
 23  ;
控制文件已创建
SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
SQL> alter database open;
数据库已更改。

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

在上一篇中深入分析一次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导出来数据，然后重建完成相关工作

深入分析一次ORA-00314错误

运行在win平台上的oracle 10G数据库,因为主机蓝屏,使用pe拷贝出来相关数据库文件,redo文件,控制文件,在恢复数据库过中遇到ORA-00314错误,而无法继续下去

恢复报错

SQL> recover database
ORA-00283: 恢复会话因错误而取消
ORA-00314: 日志 3 (用于线程 1) 要求的序号 1366 与 1363 不匹配
ORA-00312: 联机日志 3 线程 1:
'DC:\ORADATA\INTERLIB\REDO03.LOG'
00314, 00000, "log %s of thread %s, expected sequence# %s doesn't match %s"
// *Cause:  The online log is corrupted or is an old version.
// *Action: Find and install correct version of log or reset logs.

这里提示可以大概看出来,数据库进行恢复的时候,需要sequence 为1366的日志,但是与现在的1363不匹配。通过上面的解释我们很可能知道是redo文件异常或者弄错了redo文件

分析Oracle Database Recovery Check结果
控制文件scn相关信息,从这里可以看到数据库的控制文件scn为47714860,checkpoint scn为47711411

控制文件中关于数据库文件是scn为47711411,而且stop_scn为null

数据文件头scn是47711411,而且fuzzy为yes

redo的相关信息,这里我们可以看出当前redo的sequence为1366,first_scn为47711411,redo的写入顺序是redo01->redo02->redo03->redo01

通过这里整体分析,我们可以知道,数据库为非正常关闭,恢复应该从redo03(sequence 1366)开始进行恢复,但是为什么出现ORA-00314呢？通过dump redo header继续分析

dump redo header 分析
这个里面我们可以看到redo01的sequence 为16进制554等于10进制的1364,scn的范围为:0x000002d6e4ab-0x000002d7455b
redo1

这个里面我们可以看到redo02的sequence 为16进制555等于10进制的1365,scn的范围为:0x000002d7455b-0x000002d804b3
redo2

这个属于异常redo,注意第一个seq: 0x00000556等于十进制的1366(也就是表示该redo的sequence为1366),”Seq# 0000001363,SCN 0x000002d5e1c6-0x000002d6e4ab”表示在redo的文件头有部分信息记录的为sequence为1633,另外这里表示该文件最大scn为0x000002d6e4ab,和redo01的最小scn(0x000002d6e4ab-0x000002d7455b)/redo01的”Prev scn: 0x0000.02d5e1c6″与现在看到的redo03中的最小scn匹配.
redo3

通过这里可以明白,在主机蓝屏的时候由于某种异常,导致redo03中的部分信息修改为了sequence为1366,但是部分信息依然保留它上次的sequence为1363的信息,导致数据库在重新恢复的时候无法正常成功.

处理方法
该故障是由于current redo异常导致,根据经验(具体参考:ORACLE REDO各种异常恢复),一般使用隐含参数屏蔽前滚,然后强制拉库,绝大部分情况能够拉库成功,如果人品不好可能需要使用其他隐含参数甚至bbed等方式处理

作者：admin