Tag Archives: ORA-19815

ORA-03113: end-of-file on communication

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

Oerr Utility output:
Cause: The connection between Client and Server process was broken.
Action: There was a communication error that requires further investigation.

When we are going to start the Oracle database, i am getting the ORA-03113 error during the startup command. My first step is looking into the Alert log what is the cause of error and find the solution according to cause.

Error occurred with many reasons:
Reason 1 If you working on one SQLPLUS session and from another session dba shutdown the database then on executing query from your session will give the error as follows:

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 10308
Session ID: 9 Serial number: 3

Solution:
Reconnect with SQLPLUS and run the command again.

Reason 2 If you are going to start the database and your Recovery file destination is full then you will also get the following error on Db_recovery_file_Dest is full.

SQL> startup
ORACLE instance started.
Total System Global Area 23584982528 bytes
Fixed Size 2452778 bytes
Variable Size 4531678966 bytes
Database Buffers 2342356778 bytes
Redo Buffers 25876431 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2588
Session ID: 1705 Serial number: 5

Solution:
1. Checked the alert log find, we find the db_recovery_file_dest is full and alert log is giving following warning:

ORA-19815: WARNING: db_recovery_file_dest_size of 2456687415514 bytes is 100.00% used, and has 0 remaining bytes available.

2. Open the Database in mount state

startup mount

3. Check and increase the parameter current value:

Show parameter db_recovery_file_dest_size
-- add 10GB size more to this parameter
alter system set db_recovery_file_dest_size = 75G scope=both

4. Open the Database.

alter database open;

5. Fixed the issue with RMAN.

RMAN> backup archivelog all delete input;

Reasons 3: Redo log file seems inactive or corrupted.
Solution:
1. Startup the instance in nomount:

SQL> startup nomount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes

2. Open database into mount state:
alter database mount;
Database altered.

3. Clear the redo log files having issue due to power failure or unclean shutdown of database.

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.

4. Shutdown the database and open it.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.

Advertisements