遇到ORA-03113 end-of-file on communication channel

  遇到ORA-03113: end-of-file on communication channel
  打开数据库的时候报ORA-03113。
关于ORA-03113，ORACLE官方文档是这样描述的：

ORA-03113: end-of-file on communication channel
Cause: The connection between Client and Server process was broken.
Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.

并没有明确的解决方法。

查看日志文件：
Wed May 13 21:22:55 2009
Errors in file /u01/admin/YSP/bdump/ysp_lgwr_2920.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/oradata/YSP/redo01B.log'

关于ORA-00322，ORACLE官方是这样描述的：

ORA-00322: log string of thread string is not current copy
Cause: Check of log file header at database open found that an online log appears to be an incorrectly restored backup.
Action: Restore correct file or reset logs.

 

此时重建日志文件将会报错。
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-00350: log 1 of instance YSP (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u01/oradata/YSP/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/oradata/YSP/redo01B.log'

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
*
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/oradata/YSP/redo01B.log'
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/oradata/YSP/redo01.log'

SQL> SELECT * FROM V$LOGFILE;
rows will be truncated

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -----------------------------------------------------
         1         ONLINE  /u01/oradata/YSP/redo01.log
         3         ONLINE  /u01/oradata/YSP/redo03.log
         2         ONLINE  /u01/oradata/YSP/redo02.log
         1         ONLINE  /u01/oradata/YSP/redo01B.log
         2         ONLINE  /u01/oradata/YSP/redo02B.log
         3         ONLINE  /u01/oradata/YSP/redo03B.log

6 rows selected.

SQL> SELECT * FROM V$LOG;
truncating (as requested) before column FIRST_CHANGE#

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          0   15728640          2 NO  CLEARING_CURRENT 13-M
         3          1         13   15728640          2 YES INACTIVE         13-M
         2          1          0   15728640          2 YES UNUSED           13-M

由于数据处于MOUNT状态，因此日志切换不行。
SQL> ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE
*
ERROR at line 1:
ORA-01109: database not open

测试打开数据库就会报ORA-03113

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
而且数据库INSTANCE将会意外终止。

此时可以用基于CANCEL的不完全恢复。

 

SQL> CONN / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              79694068 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select * from v$log;
truncating (as requested) before column FIRST_CHANGE#

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          0   15728640          2 NO  CLEARING_CURRENT 13-M
         3          1         13   15728640          2 YES INACTIVE         13-M
         2          1          0   15728640          2 YES UNUSED           13-M

SQL> recover database until cancel;
Media recovery complete.
SQL> select * from v$log;
truncating (as requested) before column FIRST_CHANGE#

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          0   15728640          2 NO  CLEARING_CURRENT 13-M
         3          1         13   15728640          2 YES INACTIVE         13-M
         2          1          0   15728640          2 YES UNUSED           13-M

SQL> alter database open resetlogs;

Database altered.

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16162908/viewspace-596535/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16162908/viewspace-596535/