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

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?

LVL 1
mtaeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

morphmanCommented:
"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
SDuttaCommented:

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
seazodiacCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

morphmanCommented:
Also, check the alert.log for more info
0
schwertnerCommented:
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
mtaeAuthor Commented:
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
seazodiacCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mtaeAuthor Commented:
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
jwittenmCommented:
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
schwertnerCommented:
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
FayyazCommented:
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
dalbelloCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.