Solved

ORA-03113: end-of-file on communication channel - client connection lost after several minutes

Posted on 2003-12-01
12
118,826 Views
Last Modified: 2012-08-14
Hello,

I have a strange problem with my client connection to an oracle database.  I can connect to the database fine and work with the database ok, but if I leave the connection alone then after a couple of minutes I get an "ORA-03113: end-of-file on communication channel" error.  

If I open a sqlplus session on the server where the database resides I do not lose my connection when I leave it open for over 10 minutes.

There are several databases on the server and I seem to be having the same problem with all of them.

I suspect that because I can connect ok initially, but then lose my connection, that I have a network issue, but I am not sure what would cause this problem to happen.  It is occurring with all my colleagues as well.

Unfortunately I do not have a metalink account as we do not hold the oracle licenses ourselves so I cannot check this problem out there.  The database is oracle 8.1.7 and resides on a Windows 2000 server.  If any one has any suggestions then I'd appreciate your input?

0
Comment
Question by:mtae
  • 2
  • 2
  • 2
  • +5
12 Comments
 
LVL 6

Assisted Solution

by:morphman
morphman earned 100 total points
ID: 9851459
"V3.5.6 2002-12-04 17:51:26 [ERROR] Open loglog:select * from logLog where tried=0 and ( (whentosent<=sysdate) or (whentosent is null) )
Exception:ORA-03113: end-of-file on communication channel."

The exception is "Exception:ORA-03113: end-of-file on communication channel".

ORA-03113 is a catch-all error message, similar to ORA-00600 but
less useful. It means that something went wrong, and Oracle
doesn't know what.

Following are some steps on how to check the reason behind the problem.

1. As it turns out, the most common reason is that the Oracle shadow process
on the server died unexpectedly. So, if a running process were to
suddenly encounter an ORA-03113 and/or 3114, the first place to check is
the alert.log on the server to see if any other Oracle errors occurred.

2. Next most common cause of ORA-03113 is that the SQL*Net driver was not
linked into the Oracle executable on Server. While the listener
successfully received the connection and passed it to the Oracle shadow
process, the shadow process would fail to respond in any way because it
would not know how to. So the client will see an ORA-03113 at connect
time.

3. Third most common cause is a machine crash or network failure at the
server side.

4. ORA-03113 has also been noticed where the a TOKEN RING card has the
Shared RAM size set to 8KB rather than 16KB. If you are using a TOKEN
RING card check the shared buffer size and try increasing it.

5. ORA-03113 also occurs when INIT.ORA parameters CONTEXT_AREA and
CONTEXT_INCR are set to a value of 4096. Increasing the value to 8192
resolves the 3113. (RDVMS V6 only)

6. ORA-03113 also occurs when there are duplicate IP addresses on the
network. To find the duplicate addresses turn off the unit that is getting
the 03113 and ping its IP address. If the ping responds then you have to
find the offending unit.

7. If an ORA-03113 error occurs intermittentlly on comparatively large select
statements, try setting the default buffer size to 4096, especially if
issuing an order by causes no problems but going through Q&E or SQR hangs
the machine.

ORA-03113 is just a symptom of a larger problem that will require more
diagnosis to track down. Hopefully, the above information will lead you
in the right direction to find the solution.

To debug an ORA-03113, it is a good idea to attempt the same operation
while doing a loopback, i.e can any tool on the server connect using the
same connect string as they specify from the Desktop client? If the same
problem occurs doing a loopback, then you know the problem resides on the
server side and not on the Desktop client side.

To perform a loopback invoke SQLPLUS or SQLDBA from the server, and at the
SQLPLUS or SQLDBA prompt on the server type:

CONNECT USERNAME/PASSWORD@t:/:
0
 
LVL 10

Expert Comment

by:SDutta
ID: 9851655

Since you are able to make the connection initially but get dropped after some inactivity, I am of the opinion that this is being caused by the firewall on your network. Firewall settings are notorious for this if not configured properly. They can be set to disconnect an idle user after a period of time.

Other than the firewall, Oracle also has a setting in user's profile called idle_time which disconnects users after a given amount of inactivity. By default this parameter is set to unlimited and will not disconnect users after a period of time. You should check and make sure this parameter has not been set in the default profile.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9851746
If the message ORA-03113 occurs after a  connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error.

Problem Explanation: ====================  
The TNSLISTENER is configured incorrectly to launch an undesired program  automatically when any connection attempt references a particular SID or  Service_name.    

Solution Description: =====================  
Remove the (PROGRAM = xxxxx) line from the SID_DESC portion of the  "listener.ora" if connection is to an actual database instance, and not psuedo  instance or external procedure call.  
Example:
From Listener.ora  
(SID_LIST =    
      (SID_DESC =      
            (SID_NAME = TEST)     #<----Actual SID for an Oracle Database      
            (ORACLE_HOME = /usr/users/oracle/app/oracle/product/8.1.5)      
            (PROGRAM = extproc)   #<---Invalid entry for a "database" connection    
      )  
)  

If this does not solve your problem, can you post up your Server side LISTENER.ORA file and client side SQLNET.ORA file?
0
 
LVL 6

Expert Comment

by:morphman
ID: 9852898
Also, check the alert.log for more info
0
 
LVL 47

Expert Comment

by:schwertner
ID: 9856504
Have you recently installed other products on your computer? This error is common in unix/linux and often caused by kernel changes. In Windows  - changed DLLs by the new installation. Sometimes a new installation of the Oracle client will help.
0
 
LVL 1

Author Comment

by:mtae
ID: 9927052
It seems we have two ip addresses on the server.  When the second IP address was removed the problem was resolved.  However we need this other IP address to gain access to another server on another network.  So we have put the second IP address back in place and now the problem has returned.  I don't see any simple way of resolving this issue, so may have to live with it until we can remove the second IP address permanently.  I will see if there is any other resolution and if not I will close the call.  Thanks for all your help so far.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Accepted Solution

by:
seazodiac earned 150 total points
ID: 9928119
Ok,how about this?

in your client TNSNAMES.ora file, if you have (HOST=<your_host>) in below section,
Changing it to (HOST=<the first IP address) , IMHO, will fix the problem

take a shot, it's worth a try.

<SID>=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <your_IP_address>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <SID>)
    )
  )

0
 
LVL 1

Author Comment

by:mtae
ID: 9956971
Originally I had the host name for the server in the HOST=<your_host> line in the tnsnames.ora file.  I changed this to the first ip address on the server and was still having the problem.  I then changed it to the second ip address on the server and this seems to have resolved the problem, I am no longer getting disconnected.  I'm not sure why this is the case and will investigate further when I get a chance.  For the meantime it seems the problem is solved.  Thank you very much for you help.  I will close this question now (I'm not sure how to post a closing comment whilst closing the question?!) and split the points.
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13929246
I know you closed this, but for others who may be interested in the thread I suggest checking the sqlnet.expire_time in the sqlnet.ora.  If it is set to a non-zero value, try commenting it out and seeing what happens.  Normally I would leave it on, but I've seen some nasty side effects in very specific instances.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 13953631
I disagree!
It sends probes and closes dead connections.
The only disadvantage is that it loads the networks sending probes.
So this is an excellent solution to close dead connections.
It works perfect in all my instances.
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 14517247
I am getting the same error, when my client want to compile a procedure pointing to my ORACLE 9.2.0.1 Server. The Error is 'ORA-03113-End-of-file on communication channel'. When i go through the Alert Log file, this error is seen
ORA-00600: internal error code, arguments: [qerrmOFBu1], [3113], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kpnugd-5], [4294967295], [põÔK‘a\3ÞÈq‘aTõÔdõÔTõÔŽ‘aT], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ddfnetDPartial-6], [4294967295], [], [], [], [], [], []

Any help

Fayyaz
0
 

Expert Comment

by:dalbello
ID: 21572517
Under Kerio Winroute Firewall this error occours because "Enable connection limit" is checked.
It's a default setting with value=600.
Disable this check in Advanced Options / Security Settings.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Cross Outer Join 4 52
Generate HTML charts from SQL 4 51
Need help with Oracle syntax 4 42
Parametric query in oracle 6 42
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now