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

My one of the client reported me ORA-03113 oracle error message. He is facing problem while starting up database. Usually this error occurs when connection between Client and Server process was broken OR It can be any big problem.

SQL> startup
ORACLE instance started.
Total System Global Area 8584982528 bytes
Fixed Size 2262088 bytes
Variable Size 4462742456 bytes
Database Buffers 4093640704 bytes
Redo Buffers 26337280 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2588
Session ID: 1705 Serial number: 5

On further diagnosis of ADRCI logs, I found disk space size assigned to Flash recovery area falling short. ADRCI logs are as follows:

2016-09-12 12:46:00.960000 +05:30
Successful mount of redo thread 1, with mount id 3346245764
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=564
2016-09-12 12:46:02.333000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\trace\mnsbdb_ora_2588.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 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 'D:\APP\ADMINISTRATOR\FAST_RECOVE
RY_AREA\MNSBDB\ARCHIVELOG\2016_09_12\O1_MF_1_454_%U_.ARC'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\trace\mnsbdb_ora_25
88.trc:
ORA-16038: log 8 sequence# 454 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 8 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\MNSBDB\REDO08.LO
G'
USER (ospid: 2588): terminating the instance due to error 16038
ARC0: STARTING ARCH PROCESSES
2016-09-12 12:46:03.347000 +05:30
Logins disabled; aborting ARCH process startup (1092)
ARC0: Archival disabled due to shutdown: 1092
Shutting down archive processes
Archiving is disabled
System state dump requested by (instance=1, osid=2588), summary=[abnormal instan
ce termination].
System State dumped to trace file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\
trace\mnsbdb_diag_4064.trc
Dumping diagnostic data in directory=[cdmp_20160912124603], requested by (instan
ce=1, osid=2588), summary=[abnormal instance termination].
2016-09-12 12:46:05.266000 +05:30
Instance terminated by USER, pid = 2588

So, I finally assigned more size to Flash recovery area using following command, and database opened successfully.

 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 8584982528 bytes
 Fixed Size 2262088 bytes
 Variable Size 4462742456 bytes
 Database Buffers 4093640704 bytes
 Redo Buffers 26337280 bytes
 Database mounted.
SQL> alter system set db_recovery_file_dest_size=100G scope=both;
SQL> alter database open;

I strongly recommend you to monitor your every database move from ADRCI, this will easy for your to understand problem area and act accordingly.

Your comments highly appreciated.

Stay Tune. 🙂

Leave a Reply

%d bloggers like this: