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

One day my server died suddenly due to a power problem, this caused a problem with my Oracle database,
ORA-03113: end-of-file on communication channel.

[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 16 14:22:01 2019 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 4993982464 bytes Fixed Size            2261808 bytes Variable Size         1006636240 bytes Database Buffers     3976200192 bytes Redo Buffers            8884224 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 4903 Session ID: 237 Serial number: 26032

After searching for ways to repair the server, I finally found the solution. Here are the steps:

[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 16 14:22:01 2019 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 4993982464 bytes Fixed Size            2261808 bytes Variable Size         1006636240 bytes Database Buffers     3976200192 bytes Redo Buffers            8884224 bytes Database mounted. SQL> SELECT GROUP# FROM V$LOG;     GROUP# ----------      1      3      2 SQL> alter database clear unarchived logfile group 1; Database altered. SQL> alter database clear unarchived logfile group 2; Database altered. SQL> alter database clear unarchived logfile group 3; Database altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4993982464 bytes Fixed Size            2261808 bytes Variable Size         1006636240 bytes Database Buffers     3976200192 bytes Redo Buffers            8884224 bytes Database mounted. Database opened. SQL>

If this method still fails, then there is a second method, which is as follows
Now, go into sqlplus without opening the database, just mounting it.

[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 16 14:45:09 2019 Copyright (c) 1982, 2013, Oracle.  All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 4993982464 bytes Fixed Size            2261808 bytes Variable Size         1006636240 bytes Database Buffers     3976200192 bytes Redo Buffers            8884224 bytes Database mounted. SQL>

Now, you can increase your current db_recovery_file_dest_size, increased to 100G in my case:

SQL> alter system set db_recovery_file_dest_size = 100G scope=both; System altered.

Now, you can shutdown and startup again and that previous error should be gone. The proper fix is to get rid of the recovery files. You do that using RMAN

[oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 16 14:49:39 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: DB11G (DBID=432722608, not open) RMAN> backup archivelog all delete input; Starting backup at 16-AUG-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 16-AUG-19 RMAN> exit Recovery Manager complete.

Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your database and be back in business.

[oracle@jktdi009 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 16 14:52:16 2019 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4993982464 bytes Fixed Size            2261808 bytes Variable Size         1006636240 bytes Database Buffers     3976200192 bytes Redo Buffers            8884224 bytes Database mounted. Database opened. SQL>