IT博客汇 | Oracle：Error when Database Open（ORA-03113: end-of-file on communication channel）

Oracle：Error when Database Open（ORA-03113: end-of-file on communication channel）

Adamhuan发表于 2015-08-07 01:55:36

今天，在启动数据库的时候，遇到了如下所示的这个问题：

[oracle11g@em12cdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 01:16:33 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2204696576 bytes
Fixed Size		    2255112 bytes
Variable Size		  671090424 bytes
Database Buffers	 1526726656 bytes
Redo Buffers		    4624384 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3226
Session ID: 1 Serial number: 5


SQL>

该错误发生时的alert日志状态如下：

Fri Aug 07 01:19:05 2015
alter database open
Fri Aug 07 01:19:05 2015
LGWR: STARTING ARCH PROCESSES
Fri Aug 07 01:19:05 2015
ARC0 started with pid=20, OS id=3409
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Aug 07 01:19:06 2015
ARC1 started with pid=21, OS id=3413
Fri Aug 07 01:19:06 2015
ARC2 started with pid=22, OS id=3417
Errors in file /u01/app/oracle/diag/rdbms/em12c/em12c/trace/em12c_ora_3401.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4385144832 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************

ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/EM12C/archivelog/2015_08_07/o1_mf_1_27_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/em12c/em12c/trace/em12c_ora_3401.trc:
ORA-16038: log 5 sequence# 27 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/em12c/redo05'

Fri Aug 07 01:19:06 2015
ARC3 started with pid=23, OS id=3421
USER (ospid: 3401): terminating the instance due to error 16038
Fri Aug 07 01:19:07 2015
System state dump requested by (instance=1, osid=3401), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/em12c/em12c/trace/em12c_diag_3339_20150807011907.trc
Dumping diagnostic data in directory=[cdmp_20150807011907], requested by (instance=1, osid=3401), summary=[abnormal instance termination].
Instance terminated by USER, pid = 3401

通过alert日志可以定位到问题：
“
ARCH: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/EM12C/archivelog/2015_08_07/o1_mf_1_27_%u_.arc’
Errors in file /u01/app/oracle/diag/rdbms/em12c/em12c/trace/em12c_ora_3401.trc:
ORA-16038: log 5 sequence# 27 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 5 thread 1: ‘/u01/app/oracle/oradata/em12c/redo05′
”
因为当前数据库开启了归档：

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     27
Next log sequence to archive   27
Current log sequence	       32
SQL> show parameter db_recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fast_recovery_
						 area
db_recovery_file_dest_size	     big integer 4182M
SQL>

归档路径为：/u01/app/oracle/fast_recovery_area
该路径在数据库中的阀值为：4182M

而发生问题时，该路径的空间如下：

SQL> !du -sh /u01/app/oracle/fast_recovery_area
4.1G	/u01/app/oracle/fast_recovery_area

SQL> 
SQL> !df -h       
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       11G  2.7G  7.6G  27% /
/dev/sda1              99M   25M   70M  26% /boot
tmpfs                 3.5G     0  3.5G   0% /dev/shm
/dev/mapper/vg_oracle_software-lv_oracle_software
                       40G   25G   13G  66% /u01

SQL>

虽然，4.1G远远没有达到Linux文件系统的空间上限，但是由于已经达到了数据库设定的空间阀值，故而导致数据库无法创建新的归档日志去归档第五号联机重做日志：
“ORA-16038: log 5 sequence# 27 cannot be archived”
故而，OPen数据库报错。

解决该问题的方法是增加数据库的归档路径上限，或者降低当前归档空间的使用率。
这里，我采用增加归档空间的数据库阀值上限的方式。
具体如下：

SQL> alter system set db_recovery_file_dest_size=8G scope=spfile;

System altered.

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 2204696576 bytes
Fixed Size		    2255112 bytes
Variable Size		  671090424 bytes
Database Buffers	 1526726656 bytes
Redo Buffers		    4624384 bytes
Database mounted.
SQL> 
SQL> show parameter db_recovery_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fast_recovery_
						 area
db_recovery_file_dest_size	     big integer 8G
SQL> 
SQL> alter database open;

Database altered.

SQL>

如上所示，我将归档空间的上限修改为了8G。
数据库成功被Open。
——————
Done。