在很多情况下,特别是在使用隐含参数强制打开数据库之后,可能会在出现Ora-00600 4194错误,在alert文件中,记录主要错误日志:
Sat Jan 21 13:55:21 2006 Errors in file /opt/oracle/admin/conner/bdump/conner_smon_17113.trc: ORA-00600: internal error code, arguments: [4194], [43], [46], [], [], [], [], [] Sat Jan 21 13:55:21 2006 Errors in file /opt/oracle/admin/conner/udump/conner_ora_17121.trc: ORA-00600: internal error code, arguments: [4194], [45], [44], [], [], [], [], []
4194错误通常说明UNDO段出现问题,最好的办法是通过备份进行恢复,如果没有备份,那么可以通过特殊的初始化参数进行强制启动,本文就Oracle的隐含参数进行恢复说明(由于实际情况可能各不相同,进行测试前请先行备份),仅供参考。
首先确定当前回滚段名称,这可以从alert文件中获得:
Sat Jan 21 13:55:21 2006 Undo Segment 11 Onlined Undo Segment 12 Onlined Undo Segment 13 Onlined Successfully onlined Undo Tablespace 16.
对应的AUM (auto undo management) 下的回滚段名称为:
'_SYSSMU11$','_SYSSMU12$','_SYSSMU13$'
修改init<sid>.ora参数文件,使用Oracle隐含参数_corrupted_rollback_segments将回滚段标记为损坏,此时启动数据库,Oracle会跳过对于这些回滚段的相关操作,强制启动数据库。
._corrupted_rollback_segments='_SYSSMU11$','_SYSSMU12$','_SYSSMU13$'
使用init<sid>.ora参数文件启动数据库:
[oracle@jumper dbs]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jan 21 13:56:47 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup pfile=initconner.ora ORACLE instance started. Total System Global Area 97588504 bytes Fixed Size 451864 bytes Variable Size 33554432 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted. Database opened.
此时数据库正常Open。观察alert文件可以获得如下信息:
Sat Jan 21 13:57:03 2006 SMON: enabling tx recovery SMON: about to recover undo segment 11 SMON: mark undo segment 11 as needs recovery SMON: about to recover undo segment 12 SMON: mark undo segment 12 as needs recovery SMON: about to recover undo segment 13 SMON: mark undo segment 13 as needs recovery Sat Jan 21 13:57:03 2006 Database Characterset is ZHS16GBK Sat Jan 21 13:57:03 2006 SMON: about to recover undo segment 11 SMON: mark undo segment 11 as needs recovery SMON: about to recover undo segment 12 SMON: mark undo segment 12 as needs recovery SMON: about to recover undo segment 13 SMON: mark undo segment 13 as needs recovery Sat Jan 21 13:57:04 2006 Created Undo Segment _SYSSMU1$ Undo Segment 1 Onlined Completed: ALTER DATABASE OPEN Sat Jan 21 14:02:11 2006 SMON: about to recover undo segment 11 SMON: mark undo segment 11 as needs recovery SMON: about to recover undo segment 12 SMON: mark undo segment 12 as needs recovery SMON: about to recover undo segment 13 SMON: mark undo segment 13 as needs recovery
此时可以重新创建新的UNDO表空间,删除出现问题的表空间,修改参数文件,由参数文件生成新的spfile,重新启动数据库:
SQL> create undo tablespace undotbs1 2 datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M; Tablespace created. SQL> alter system set undo_tablespace=undotbs1; System altered. SQL> drop tablespace undotbs2; Tablespace dropped.
此时的alert文件记录的:
Sat Jan 21 14:03:29 2006 create undo tablespace undotbs1 datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M Sat Jan 21 14:03:29 2006 Created Undo Segment _SYSSMU2$ Created Undo Segment _SYSSMU3$ Created Undo Segment _SYSSMU4$ Created Undo Segment _SYSSMU5$ Created Undo Segment _SYSSMU6$ Created Undo Segment _SYSSMU7$ Created Undo Segment _SYSSMU8$ Created Undo Segment _SYSSMU9$ Created Undo Segment _SYSSMU10$ Created Undo Segment _SYSSMU14$ Starting control autobackup Control autobackup written to DISK device handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-00' Completed: create undo tablespace undotbs1 datafile '/opt/ora Sat Jan 21 14:03:43 2006 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 Undo Segment 14 Onlined Successfully onlined Undo Tablespace 1. Undo Segment 1 Offlined Undo Tablespace 16 successfully switched out. Sat Jan 21 14:03:43 2006 ALTER SYSTEM SET undo_tablespace='UNDOTBS1' SCOPE=MEMORY; Sat Jan 21 14:07:18 2006 SMON: about to recover undo segment 11 SMON: mark undo segment 11 as needs recovery SMON: about to recover undo segment 12 SMON: mark undo segment 12 as needs recovery SMON: about to recover undo segment 13 SMON: mark undo segment 13 as needs recovery Sat Jan 21 14:08:06 2006 drop tablespace undotbs2 Sat Jan 21 14:08:07 2006 Starting control autobackup Control autobackup written to DISK device handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-01' Completed: drop tablespace undotbs2
修改参数文件,变更undo表空间,并取消_corrupted_rollback_segments设置:
*.undo_tablespace='UNDOTBS1'
由参数文件创建spfile文件。
SQL> create spfile from pfile; File created. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 97588504 bytes Fixed Size 451864 bytes Variable Size 33554432 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes Database mounted. Database opened.
重起数据库,观察alert文件:
Sat Jan 21 14:08:36 2006 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 Undo Segment 14 Onlined Successfully onlined Undo Tablespace 1.
此时数据库可以正常启动,通过以上方法恢复数据库,通常会导致数据库内部存在不一致的状况,通常建议立即进行全库exp,然后重新建库,再通过imp恢复数据库。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。