linux ora-00322,遇到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.