问题描述
客户数据库异常DOWN机,在启动过程中出现了这个错误。
由于掉电导致了客户数据库出现了控制文件的不一致,尝试启动报错如下:
SQL> startup ORACLE instance started. Total System Global Area 126950956 bytes Fixed SIZE 454188 bytes Variable SIZE 92274688 bytes DATABASE Buffers 33554432 bytes Redo Buffers 667648 bytes * ORA-00214: controlfile 'D:\ORACLE\ORADATA\SXXHDTS\CONTROL03.CTL' version 2623 inconsistent WITH file 'D:\ORACLE\ORADATA\SXXHDTS\CONTROL02.CTL' version 2619
专家解答
显然这是控制文件不一致导致的,将最新的控制文件CONTROL03.CTL覆盖较旧的CONTROL02.CTL和CONTROL01.CTL,再次尝试打开,报错数据库需要恢复:
SQL> recover DATABASE; Media recovery complete. 然后尝试打开数据库碰到ORA-600[2758]错误: SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * ERROR at line 1: ORA-00600: internal error code, arguments: [2758], [1], [4294967295], [204800], [10], [], [], []
检查告警日志,可以发现更多的信息:
Mon Jan 09 12:02:08 2012 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 USING log_archive_dest parameter DEFAULT VALUE LICENSE_MAX_USERS = 0 SYS auditing IS disabled Starting up ORACLE RDBMS Version: 9.2.0.1.0. System parameters WITH non-DEFAULT VALUES: processes = 150 timed_statistics = TRUE shared_pool_size = 50331648 large_pool_size = 8388608 java_pool_size = 33554432 control_files = D:\oracle\oradata\sxxhdts\CONTROL01.CTL, D:\oracle\oradata\sxxhdts\CONTROL02.CTL, D:\oracle\oradata\sxxhdts\CONTROL03.CTL db_block_size = 8192 db_cache_size = 25165824 compatible = 9.2.0.0.0 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = sxxhdts dispatchers = (PROTOCOL=TCP) (SERVICE=sxxhdtsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = D:\oracle\admin\sxxhdts\bdump user_dump_dest = D:\oracle\admin\sxxhdts\udump core_dump_dest = D:\oracle\admin\sxxhdts\cdump sort_area_size = 524288 db_name = sxxhdts open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 25165824 aq_tm_processes = 1 PMON started WITH pid=2 DBW0 started WITH pid=3 LGWR started WITH pid=4 CKPT started WITH pid=5 SMON started WITH pid=6 RECO started WITH pid=7 CJQ0 started WITH pid=8 QMN0 started WITH pid=9 Mon Jan 09 12:02:11 2012 starting up 1 shared server(s) ... starting up 1 dispatcher(s) FOR network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Mon Jan 09 12:02:19 2012 ALTER DATABASE mount Mon Jan 09 12:02:22 2012 ORA-214 signalled during: ALTER DATABASE mount... Mon Jan 09 12:13:28 2012 ALTER DATABASE mount Mon Jan 09 12:13:31 2012 ORA-214 signalled during: ALTER DATABASE mount... Mon Jan 09 12:39:11 2012 ALTER DATABASE mount Mon Jan 09 12:39:14 2012 ORA-214 signalled during: ALTER DATABASE mount ... Mon Jan 09 12:41:17 2012 ALTER DATABASE mount Mon Jan 09 12:41:21 2012 Successful mount OF redo thread 1, WITH mount id 633859757. Mon Jan 09 12:41:21 2012 DATABASE mounted IN Exclusive Mode. Completed: ALTER DATABASE mount Mon Jan 09 12:41:27 2012 ALTER DATABASE OPEN ORA-1113 signalled during: ALTER DATABASE OPEN... Mon Jan 09 12:43:21 2012 Shutting down instance: further logons disabled Shutting down instance (immediate) License high water mark = 4 Waiting FOR dispatcher 'D000' TO shutdown ALL dispatchers AND shared servers shutdown Mon Jan 09 12:43:23 2012 ALTER DATABASE CLOSE NORMAL ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... Mon Jan 09 12:43:23 2012 ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT ARCH: Archiving IS disabled Shutting down archive processes Archiving IS disabled Archive process shutdown avoided: 0 active ARCH: Archiving IS disabled Shutting down archive processes Archiving IS disabled Archive process shutdown avoided: 0 active Mon Jan 09 12:45:57 2012 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 USING log_archive_dest parameter DEFAULT VALUE LICENSE_MAX_USERS = 0 SYS auditing IS disabled Starting up ORACLE RDBMS Version: 9.2.0.1.0. System parameters WITH non-DEFAULT VALUES: processes = 150 timed_statistics = TRUE shared_pool_size = 50331648 large_pool_size = 8388608 java_pool_size = 33554432 . . . aq_tm_processes = 1 PMON started WITH pid=2 DBW0 started WITH pid=3 LGWR started WITH pid=4 CKPT started WITH pid=5 SMON started WITH pid=6 RECO started WITH pid=7 CJQ0 started WITH pid=8 QMN0 started WITH pid=9 Mon Jan 09 12:46:00 2012 starting up 1 shared server(s) ... starting up 1 dispatcher(s) FOR network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Mon Jan 09 12:46:00 2012 ALTER DATABASE MOUNT Mon Jan 09 12:46:04 2012 Successful mount OF redo thread 1, WITH mount id 633842120. Mon Jan 09 12:46:04 2012 DATABASE mounted IN Exclusive Mode. Completed: ALTER DATABASE MOUNT Mon Jan 09 12:52:21 2012 ALTER DATABASE RECOVER DATABASE Mon Jan 09 12:52:21 2012 Media Recovery START WARNING! Recovering DATA file 2 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. WARNING! Recovering DATA file 4 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. WARNING! Recovering DATA file 5 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. WARNING! Recovering DATA file 6 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. WARNING! Recovering DATA file 8 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. WARNING! Recovering DATA file 9 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. WARNING! Recovering DATA file 10 FROM a fuzzy backup. It might be an online backup taken WITHOUT entering the BEGIN backup command. Starting datafile 2 recovery IN thread 1 SEQUENCE 317 Datafile 2: 'D:\ORACLE\ORADATA\SXXHDTS\UNDOTBS01.DBF' Starting datafile 4 recovery IN thread 1 SEQUENCE 317 Datafile 4: 'D:\ORACLE\ORADATA\SXXHDTS\DRSYS01.DBF' Starting datafile 5 recovery IN thread 1 SEQUENCE 317 Datafile 5: 'D:\ORACLE\ORADATA\SXXHDTS\EXAMPLE01.DBF' Starting datafile 6 recovery IN thread 1 SEQUENCE 317 Datafile 6: 'D:\ORACLE\ORADATA\SXXHDTS\INDX01.DBF' Starting datafile 8 recovery IN thread 1 SEQUENCE 317 Datafile 8: 'D:\ORACLE\ORADATA\SXXHDTS\TOOLS01.DBF' Starting datafile 9 recovery IN thread 1 SEQUENCE 317 Datafile 9: 'D:\ORACLE\ORADATA\SXXHDTS\USERS01.DBF' Starting datafile 10 recovery IN thread 1 SEQUENCE 317 Datafile 10: 'D:\ORACLE\ORADATA\SXXHDTS\XDB01.DBF' Media Recovery Log Recovery OF Online Redo Log: Thread 1 GROUP 1 Seq 317 Reading mem 0 Mem# 0 errs 0: D:\ORACLE\ORADATA\SXXHDTS\REDO01.LOG Media Recovery Complete Completed: ALTER DATABASE RECOVER DATABASE Mon Jan 09 12:52:32 2012 ALTER DATABASE OPEN Mon Jan 09 12:52:32 2012 Thread 1 opened at log SEQUENCE 317 CURRENT log# 1 seq# 317 mem# 0: D:\ORACLE\ORADATA\SXXHDTS\REDO01.LOG Successful OPEN OF redo thread 1. Mon Jan 09 12:52:32 2012 Errors IN file d:\oracle\admin\sxxhdts\bdump\sxxhdts_lgwr_5956.trc: ORA-00345: redo log WRITE error block 4294967295 COUNT 1 ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\SXXHDTS\REDO01.LOG' ORA-27069: skgfdisp: attempt TO do I/O beyond the range OF the file OSD-04026: 传递的参数无效。 (OS 4294967295) Mon Jan 09 12:52:33 2012 Errors IN file d:\oracle\admin\sxxhdts\bdump\sxxhdts_lgwr_5956.trc: ORA-00340: IO error processing online log 1 OF thread 1 ORA-00345: redo log WRITE error block 4294967295 COUNT 1 ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\SXXHDTS\REDO01.LOG' ORA-27069: skgfdisp: attempt TO do I/O beyond the range OF the file OSD-04026: 传递的参数无效。 (OS 4294967295) LGWR: terminating instance due TO error 340 Mon Jan 09 12:52:33 2012 Errors IN file d:\oracle\admin\sxxhdts\bdump\sxxhdts_pmon_4752.trc: ORA-00340: IO error processing online log OF thread Instance TERMINATED BY LGWR, pid = 5956 Mon Jan 09 13:02:04 2012 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 USING log_archive_dest parameter DEFAULT VALUE LICENSE_MAX_USERS = 0 SYS auditing IS disabled Starting up ORACLE RDBMS Version: 9.2.0.1.0. System parameters WITH non-DEFAULT VALUES: processes = 150 timed_statistics = TRUE shared_pool_size = 50331648 . . . aq_tm_processes = 1 PMON started WITH pid=2 DBW0 started WITH pid=3 LGWR started WITH pid=4 CKPT started WITH pid=5 SMON started WITH pid=6 RECO started WITH pid=7 CJQ0 started WITH pid=8 QMN0 started WITH pid=9 Mon Jan 09 13:02:07 2012 starting up 1 shared server(s) ... starting up 1 dispatcher(s) FOR network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Mon Jan 09 13:02:08 2012 ALTER DATABASE MOUNT Mon Jan 09 13:02:12 2012 Successful mount OF redo thread 1, WITH mount id 633838736. Mon Jan 09 13:02:12 2012 DATABASE mounted IN Exclusive Mode. Completed: ALTER DATABASE MOUNT Mon Jan 09 13:02:24 2012 ALTER DATABASE OPEN Mon Jan 09 13:02:25 2012 Beginning crash recovery OF 1 threads Mon Jan 09 13:02:25 2012 Started FIRST pass scan Mon Jan 09 13:02:25 2012 Completed FIRST pass scan 0 redo blocks READ, 0 DATA blocks need recovery Mon Jan 09 13:02:25 2012 Started recovery at Thread 1: logseq 317, block 4294967295, scn 0.22473385 Recovery OF Online Redo Log: Thread 1 GROUP 1 Seq 317 Reading mem 0 Mem# 0 errs 0: D:\ORACLE\ORADATA\SXXHDTS\REDO01.LOG Mon Jan 09 13:02:25 2012 Ended recovery at Thread 1: logseq 317, block 4294967295, scn 0.22493386 0 DATA blocks READ, 0 DATA blocks written, 0 redo blocks READ Mon Jan 09 13:02:25 2012 Errors IN file d:\oracle\admin\sxxhdts\udump\sxxhdts_ora_4844.trc: ORA-00600: 内部错误代码,参数: [2758], [1], [4294967295], [204800], [8], [], [], [] ORA-600 signalled during: ALTER DATABASE OPEN
告警日志中事实上记录了更完整的信息,在第一次尝试打开数据库的时候,报错信息更将详尽,指出Oracle尝试在写REDO01.LOG日志时,返回了操作系统错误。而Oracle尝试写的REDO的位置恰好是4G-1,如果对比第二次打开时ORA-600错误的信息,就可以看到,事实上两次报错描述的是相同的问题。2758错误的第二个参数1指的就是GROUP 1,而4294967295就是前面报错的信息。
显然导致错误的原因是由于Oracle在写日志时出现了错误。由于数据库没有打开,无法清除日志文件,所以常规手段不起作用。
除了通过BBED修改控制文件中的信息外,还可以通过更常规一点的手段,通过添加隐含参数来解决:
Mon Jan 09 14:05:43 2012 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 USING log_archive_dest parameter DEFAULT VALUE LICENSE_MAX_USERS = 0 SYS auditing IS disabled Starting up ORACLE RDBMS Version: 9.2.0.1.0. System parameters WITH non-DEFAULT VALUES: processes = 150 timed_statistics = TRUE shared_pool_size = 50331648 large_pool_size = 8388608 java_pool_size = 33554432 control_files = D:\oracle\oradata\sxxhdts\CONTROL01.CTL, D:\oracle\oradata\sxxhdts\CONTROL02.CTL, D:\oracle\oradata\sxxhdts\CONTROL03.CTL db_block_size = 8192 db_cache_size = 25165824 compatible = 9.2.0.0.0 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 _allow_resetlogs_corruption= TRUE undo_management = AUTO . . . aq_tm_processes = 1 PMON started WITH pid=2 DBW0 started WITH pid=3 LGWR started WITH pid=4 CKPT started WITH pid=5 SMON started WITH pid=6 RECO started WITH pid=7 CJQ0 started WITH pid=8 QMN0 started WITH pid=9 Mon Jan 09 14:05:46 2012 starting up 1 shared server(s) ... starting up 1 dispatcher(s) FOR network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Mon Jan 09 14:05:47 2012 ALTER DATABASE MOUNT Mon Jan 09 14:05:51 2012 Successful mount OF redo thread 1, WITH mount id 633898875. Mon Jan 09 14:05:51 2012 DATABASE mounted IN Exclusive Mode. Completed: ALTER DATABASE MOUNT Mon Jan 09 14:07:06 2012 ALTER DATABASE RECOVER DATABASE until cancel Mon Jan 09 14:07:06 2012 Media Recovery START Media Recovery NOT Required Completed: ALTER DATABASE RECOVER DATABASE until cancel Mon Jan 09 14:10:06 2012 ALTER DATABASE RECOVER DATABASE USING backup controlfile until cancel Mon Jan 09 14:10:06 2012 Media Recovery START Starting datafile 1 recovery IN thread 1 SEQUENCE 317 Datafile 1: 'D:\ORACLE\ORADATA\SXXHDTS\SYSTEM01.DBF' Starting datafile 2 recovery IN thread 1 SEQUENCE 317 Datafile 2: 'D:\ORACLE\ORADATA\SXXHDTS\UNDOTBS01.DBF' Starting datafile 3 recovery IN thread 1 SEQUENCE 317 Datafile 3: 'D:\ORACLE\ORADATA\SXXHDTS\CWMLITE01.DBF' Starting datafile 4 recovery IN thread 1 SEQUENCE 317 Datafile 4: 'D:\ORACLE\ORADATA\SXXHDTS\DRSYS01.DBF' Starting datafile 5 recovery IN thread 1 SEQUENCE 317 Datafile 5: 'D:\ORACLE\ORADATA\SXXHDTS\EXAMPLE01.DBF' Starting datafile 6 recovery IN thread 1 SEQUENCE 317 Datafile 6: 'D:\ORACLE\ORADATA\SXXHDTS\INDX01.DBF' Starting datafile 7 recovery IN thread 1 SEQUENCE 317 Datafile 7: 'D:\ORACLE\ORADATA\SXXHDTS\ODM01.DBF' Starting datafile 8 recovery IN thread 1 SEQUENCE 317 Datafile 8: 'D:\ORACLE\ORADATA\SXXHDTS\TOOLS01.DBF' Starting datafile 9 recovery IN thread 1 SEQUENCE 317 Datafile 9: 'D:\ORACLE\ORADATA\SXXHDTS\USERS01.DBF' Starting datafile 10 recovery IN thread 1 SEQUENCE 317 Datafile 10: 'D:\ORACLE\ORADATA\SXXHDTS\XDB01.DBF' Media Recovery Log ORA-279 signalled during: ALTER DATABASE RECOVER DATABASE USING backup con... Mon Jan 09 14:10:13 2012 ALTER DATABASE RECOVER CANCEL Media Recovery Cancelled Completed: ALTER DATABASE RECOVER CANCEL Mon Jan 09 14:10:27 2012 ALTER DATABASE OPEN resetlogs Mon Jan 09 14:10:27 2012 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 22473386 Resetting resetlogs activation ID 615377539 (0x24adea83) Mon Jan 09 14:10:30 2012 Assigning activation ID 633898875 (0x25c8877b) Thread 1 opened at log SEQUENCE 1 CURRENT log# 1 seq# 1 mem# 0: D:\ORACLE\ORADATA\SXXHDTS\REDO01.LOG Successful OPEN OF redo thread 1. Mon Jan 09 14:10:30 2012 SMON: enabling cache recovery Mon Jan 09 14:10:31 2012 Undo Segment 1 Onlined Undo Segment 2 Onlined Undo Segment 3 Onlined Undo Segment 4 Onlined Undo Segment 5 Onlined Undo Segment 6 Onlined Undo Segment 7 Onlined Undo Segment 8 Onlined Undo Segment 9 Onlined Undo Segment 10 Onlined Successfully onlined Undo Tablespace 1. Dictionary CHECK beginning Dictionary CHECK complete Mon Jan 09 14:10:31 2012 SMON: enabling tx recovery Mon Jan 09 14:10:31 2012 DATABASE Characterset IS ZHS16GBK replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN resetlogs
可以看到,通过设置隐含参数_allow_resetlogs_corruption为TRUE,并执行不完全恢复,成功打开了数据库。
其实这种方式并没有造成数据的损失,只是避免了数据库在打开的时候尝试在错误的偏移量上去对REDO进行写操作。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。