遇到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.