ORA-607/ORA-600 [4194]异常处理

作者 阿九【转载时请务必以超链接形式标明文章原始出处和作者信息】

今天处理一个ORA-607/ORA-600 [4194] 的异常，数据库能mount，也能open，但是在open的时候报错“ORA-03113: end-of-file on communication channel”，奇怪的是，查看数据库状态还是“READ WRITE” ，不过一会数据库自动终止。后来发现，数据库能启动到open状态是因为在参数文件中添加了隐含参数_allow_resetlogs_corruption=ture（我去处理的时候就已经加了）

$ sqlplus /nolog


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 21 17:52:43 2013


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


SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.


Total System Global Area 6442450944 bytes
Fixed Size                  2065752 bytes
Variable Size            1979714216 bytes
Database Buffers         4445962240 bytes
Redo Buffers               14708736 bytes
Database mounted.
SQL> alter database open;


ORA-03113: end-of-file on communication channel


SQL> select open_mode from v$database;


OPEN_MODE
----------
READ WRITE


SQL>

后台数据库报错如下：

Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: /oradata/NC/redo01.log
Block recovery stopped at EOT rba 10.36.16
Block recovery completed at rba 10.36.16, scn 0.975281957
Doing block recovery for file 2 block 137
Block recovery from logseq 10, block 34 to scn 975281956
Thu Feb 21 17:42:57 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: /oradata/NC/redo01.log
Block recovery completed at rba 10.36.16, scn 0.975281957
Thu Feb 21 17:42:57 2013
Errors in file /oracle/admin/NC/bdump/nc_smon_4874.trc:
ORA-00604: Message 604 not found; No message file for product=RDBMS, facility=OR
A; arguments: [1]
ORA-00607: Message 607 not found; No message file for product=RDBMS, facility=OR
A
ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=OR
A; arguments: [4194] [73] [65]
Thu Feb 21 17:42:58 2013
Errors in file /oracle/admin/NC/bdump/nc_smon_4874.trc:
ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=OR
A; arguments: [4194] [73] [66]
Doing block recovery for file 27 block 277
Block recovery from logseq 10, block 36 to scn 975281962

网上很多人说ORA-600 [4194]这个错误是由于UNDO与redo不一致造成的，我查看后台日志觉得这种说法是正确的，因此按照重建undo表空间的方式来处理这个问题，具体过程如下

1、查看原来的undo表空间信息及undo相关参数值

SQL> select sum(bytes)/1024/1024 from v$datafile where ts#=1;


SUM(BYTES)/1024/1024
--------------------
                6045


SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1




SQL>

2、将undo管理改为手动，关闭数据库，并重新启动

SQL> alter system set undo_management=manual scope=spfile;


System altered.


SQL>
SQL> shutdown immediate;
ORA-01109: database not open




Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup mount;
ORACLE instance started.


Total System Global Area 6442450944 bytes
Fixed Size                  2065752 bytes
Variable Size            1979714216 bytes
Database Buffers         4445962240 bytes
Redo Buffers               14708736 bytes
Database mounted.
SQL> 


SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>


SQL> alter database open;


Database altered.


SQL>

3、创建替换的undo表空间，并将undo管理改为自动

SQL> create undo tablespace undotbs2 datafile '/oradata/NC/UNDOTBS2_1.dbf' size 1024m;


Tablespace created.


SQL> alter system set undo_tablespace="UNDOTBS2" scope=spfile;


System altered.


SQL> alter system set undo_management=auto scope=spfile;


System altered.


SQL>

4、重启数据库，并删掉原来的undo表空间

SQL> shutdown immediate;
Database closed.
Database dismounted.
RACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.


Total System Global Area 6442450944 bytes
Fixed Size                  2065752 bytes
Variable Size            1979714216 bytes
Database Buffers         4445962240 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.
SQL> 


SQL> drop tablespace UNDOTBS1;


Tablespace dropped.


SQL>

5、重建spfile，将隐含参数_allow_resetlogs_corruption=ture去掉，然后重新启动数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


SQL> startup
ORACLE instance started.


Total System Global Area 6442450944 bytes
Fixed Size                  2065752 bytes
Variable Size            1979714216 bytes
Database Buffers         4445962240 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.
SQL>

6、测试

SQL> create table test2013(t1 number);


Table created.


SQL> insert into test2013 values(1);


1 row created.


SQL> commit;


Commit complete.


SQL> select * from test2013;


        T1
----------
         1


SQL> drop table test2013;


Table dropped.


SQL>

至此，问题解决，本文操作参考了如下帖子，非常感谢：

http://www.itpub.net/thread-1483490-1-1.html