mtae
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/orac
(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?
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/orac
(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?
Also, check the alert.log for more info
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
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
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ddfnetDPartial-6], [4294967295], [], [], [], [], [], []
Any help
Fayyaz
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
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ddfnetDPartial-6], [4294967295], [], [], [], [], [], []
Any help
Fayyaz
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.
It's a default setting with value=600.
Disable this check in Advanced Options / Security Settings.
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.