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

[root@testwms orawms]# ps -ef |grep smon

orawms    6818     1  0 15:49 ?        00:00:00 ora_smon_AAD

oradev    6930     1  0 Oct19 ?        00:03:09 ora_smon_ADV

oradev    7500     1  0 Oct19 ?        00:02:08 ora_smon_AAD4

root      8068  3227  0 15:55 pts/2    00:00:00 grep smon

orawms    9300     1  0 Oct19 ?        00:03:13 ora_smon_AAD3

Case:

While trying to open db 11g on linux machine we encounter the error ORA-03113: end-of-file on communication channel.



Investigation and solution:

Returning back to the alert log file we can see the following lines:

*************************************************************************************

ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 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.

************************************************************************

Errors in file /u01/app/oracle/diag/rdbms/mostprod/mostprod/trace/mostprod_ora_11853.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 42960384 bytes disk space from 5368709120 limit

ARCH: Error 19809 Creating archive log file to ‘/u01/flash_rec_area/MOSTPROD/archivelog/2012_11_21/o1_mf_1_177_%u_.arc’

Errors in file /u01/app/oracle/diag/rdbms/mostprod/mostprod/trace/mostprod_ora_11853.trc:

ORA-16038: log 3 sequence# 177 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/mostprod/redo03.log’

*************************************************************************************

As you can see the probles is regarding the archive log area is ran out of space.

To solve this issue:
We can follow any of the given suggestions but what we do in our case is to archive some old archived logs and delete then from the archive log area.
Optionally you may change the archive log retention policy to a suitable period of time.
As we remove the archived log files through the OS command we need to tell the database about what we did that is using rman otherwise the database will still failed to open. So…

SQL> startup — Failed to startup, the same ORA-03113 Error

SQL> startup nomount — ORACLE Instance Started

SQL> alter database mount — Database altered

SQL> exit

$ cd $ORACLE_HOME/bin

$ ./rman target /

RMAN> crosscheck archivelog all — Here you will see all the names of archivelogs still exist

RMAN> delete expired archivelog all

RMAN> exit

$ sqlplus / as sysdba

SQL> alter database open — Database altered