ORA-03113: end-of-file on communication channel – In this Blog I am going to share my experience with you all on below ORA error. To fix this error we need to first check the oracle database alert logfile to find the cause of the issue. There are many reason to get this error in database so batter practice it check first in alert logfile and find the root cause of the error.
Here I have share few scenario where we can face this error and fix it. Hopefully after reading this blog your issue will be resolved. For any input or concerns please leave a message or comment below on this post. If you like it please share it.
ORA-03113: end-of-file on communication channel
Oracle oerr Utility output:
Cause: The connection between Client and Server process was broken.
Action: There was a communication error that requires further investigation.
Solution provided to fix this error:
Reason 1:
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 25281
Session ID: 639 Serial number: 47841
Check the alert log file some time we face this error due to archive destination size is full.
ORA-19815: WARNING: db_recovery_file_dest_size of 2456687415514 bytes is 100.00% used, and has 0 remaining bytes available.
Solution 1:
Open the Database in mount state
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
Check the size of filesystem specified in db_recovery parameter.
SQL > show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +TSTSTG_ARCH
db_recovery_file_dest_size big integer 50G
SQL > alter system set db_recovery_file_dest_size = 100G scope=both;
Open the Database.
SQL > alter database open;
If you want to release space from archive diskgroup you can also delete the old archive.
RMAN> backup archivelog all delete input;
Reasons 2
Check the redo log files which having issue or not uncleaned due to size insufficient size in archive diskgroup and redo not able to archived and sometime due to power failure we face these type of issue in oracle database.
]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 20 09:30:56 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 25281
Session ID: 639 Serial number: 47841
Check the size of ASM DISKGROUP
SQL> set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number,name,state,type,total_mb/1024 "TOTAL_SIZE_inGB",free_mb/1024 "FREE_inGB",round(free_mb/total_mb*100SQL> SQL> ,2) pct_free
from v$asm_diskgroup
order by group_number;
Diskgroup Information
SQL> SQL> SQL> SQL> SQL> 2 3
GROUP_NUMBER NAME STATE TYPE TOTAL_SIZE_inGB FREE_inGB PCT_FREE
------------ --------------- ----------- ------ --------------- ---------- ----------
1 TSTSTG_ARCH MOUNTED EXTERN 535.98828 175.917969 15.03
2 TSTSTG_DATA CONNECTED EXTERN 655.79688 182.835938 17.25
3 TSTSTG_REDO CONNECTED EXTERN 63.9921875 43.7539063 68.37
Solution 2
Close the database
SQL> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
Mount the database
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
Check the redo log group member details & clear the REDO logfiles which having the issue.
SQL> set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;SQL> SQL> 2 3 4 5 6 7 8 9 10 11
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
14 1 113653 NO CURRENT +TSTSTG_REDO/TSTSTG/ONLINELOG/group_14.256.10777 5120
35573
15 1 113650 NO INACTIVE +TSTSTG_REDO/TSTSTG/ONLINELOG/group_15.257.10777 5120
35583
16 1 113651 NO INACTIVE +TSTSTG_REDO/TSTSTG/ONLINELOG/group_16.258.10777 5120
35595
17 1 113652 NO INACTIVE +TSTSTG_REDO/TSTSTG/ONLINELOG/group_17.259.10777 5120
35605
SQL> alter database clear unarchived logfile group 14;
alter database clear unarchived logfile group 14
*
ERROR at line 1:
ORA-00262: current log 14 of closed thread 1 cannot switch
ORA-00312: online log 14 thread 1: '+TSTSTG_REDO/TSTSTG/ONLINELOG/group_14.256.1077735573'
ORA-00350: log 15 of instance psgdwstg (thread 1) needs to be archived
ORA-00312: online log 15 thread 1: '+TSTSTG_REDO/TSTSTG/ONLINELOG/group_15.257.1077735583'
SQL> alter database clear unarchived logfile group 15;
Database altered.
SQL> alter database clear unarchived logfile group 16;
Database altered.
SQL> alter database clear unarchived logfile group 17;
Database altered.
Shutdown database and re-open it again.
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
Database opened.
SQL>
Reasons 3
Sometime process limit exceeded and database not functioning normal and SYS user need to fix the issue.
]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 20 09:30:56 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 25281
Session ID: 639 Serial number: 47841
SQL> show parameter process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 4
cell_offload_processing boolean TRUE
db_writer_processes integer 10
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 600
processor_group_name string
SQL> alter system set processes=1000 scope=spfile;
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.6643E+11 bytes
Fixed Size 4508088 bytes
Variable Size 1.1274E+10 bytes
Database Buffers 1.5462E+11 bytes
Redo Buffers 532361216 bytes
Database mounted.
Database opened.
SQL>
Some other reason can be for above error. please check the below oracle support document and do it accordingly as per your environment.
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.