ORA-03113 is a quite a common error.Let us take a deep dive into it
Issue ORA-03113: end-of-file on communication channel
Cause: The connection between Client and Server process was broken. It may also happen if the external agent extproc crashes for some reason.
Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time. There may be some system calls in the .NET function which might terminate the process. Remove such calls.
An ORA-3113 “end of file on communication channel” error is a general error usually reported by a client process connected to an Oracle database. The error basically means ‘I cannot communicate with the Oracle shadow process’. For some reason, your client machine and the database server have stopped talking to each other.As it is such a general error more information must be collected to help determine what has happened – this error by itself does not indicate the cause of the problem.
For example, ORA-3113 could be signalled for any of these following scenarios:
•Server machine crashed
•Your server process was killed at the O/S level
•Network problems
•Oracle internal errors (ORA-600 / ORA-7445) / aborts on the server
•Client incorrectly handling multiple connections
• etc.. etc.. etc.. – a lot of possible causes !!
It is common for this error to be accompanied by other errors such as:
• ORA-01041 internal error. hostdef extension doesn’t exist
•ORA-03114 not connected to ORACLE
• ORA-01012 not logged on
This error is sometimes caused by the simplest of things. If, however, it is caused by an Oracle internal error, look to your alert log for further information.
Lets take different scenerion where ORA-03113
1) ORA-3113 during startup of Oracle database
It can occur in all the stage of startup of Oracle database
| startup nomount | 1) Could be problem with init.ora/spfile. Check that for accuracy
2) Proper environment variable are not set. Check all the variable. Specially LD_LIBRARY_PATH 3) Some time oracle executable could be corrupt. So relink can be done to fix it 4) Rebooting the entire server also helps sometimes 5) ensure enough space is present in bdump,audit directories |
| Startup mount | It could be problem with datafiles and controlfile.Do check on it |
| Alter database open | Oracle perform lot of check at this point. Check the alertlog. If the wrong undo tablespace is specified in init,ora,it will fail with this error. Specify the correct undo tablespace.
You can set below events to debug more event=”10046 trace name context forever, level 12″ event=”10015 trace name context forever, level 1″ event=”10228 trace name context forever, level 1″
|
2) Client sees ORA-3113 running SQL / PLSQL
If the ORA-3113 error occurs AFTER you have connected to Oracle then,it is most likely that the ‘oracle’ executable has terminated unexpectedly. The server process could be died of many reasons.
a) System Administrator killed the process deliberately by killing the process id as it may be consuming more CPU and memory
b) It could happens because of some bug, We should look for the trace file for this session in diagonistic directory and check for the solution in metalink
c) For UNIX only: If there is no trace file, check for a ‘core’ dump in the CORE_DUMP_DEST. Check as follows:
cd $ORACLE_HOME/dbs # Or your CORE_DUMP_DEST
ls -l core*
If there is a file called ‘core’ check that it’s time matches the time of the problem. If there are directories called
‘core_<PID>’, check for core files in each of these. It is IMPORTANT to get the correct core file. Now obtain a stack
trace from this ‘core’ file. Check each of the sequences below to see how to do this – one of these should work for your platform.
| If you have dbx: % script /tmp/core.stack % dbx $ORACLE_HOME/bin/oracle core (dbx) where … (dbx) quit % exit If you have sdb: If you have xdb: |
d) It may be possible a particular SQL statement or PL/SQL block is causing the error .In many cases this will be listed in the trace file produced under the heading “Current SQL statement”, or near the middle of the trace file under the cursor referred to by the “Current cursor NN” line.
If the trace file does not show the failing statement then SQL_TRACE may be used to help determine this, provided the problem reproduces. SQL_TRACE can be enabled in most client tools
We should always refer to the master Metalink note on ORA-03113
Master Note: Troubleshooting ORA-03113 (Doc ID 1506805.1)