This can be a complicated issue with many different possible causes and solutions. Start here:
http://www.dba-oracle.com/t_ora_03135_connection_lost_contact.htm
Answer: The oerr utility shows this for the ORA-03135 error:
ORA-03135: connection lost contact
Cause: 1) Server unexpectedly terminated or was forced to terminate.
or 2) Server timed out the connection.Action: 1) Check if the server session was terminated.
2) Check if the timeout parameters are set properly in
sqlnet.ora.The ORA-03135 error is common when connecting remotely when a firewall terminates the connection.
One solution for the ORA-03135 error is to increase the value of the sqlnet.ora expire_time parameter in the sqlnet.ora file or to check for a expire parameter in a SQL*Plus profile.
To diagnose the ORA-03135 error, start by checking to see if the OS PID still exists, using the ps –ef|grep syntax.
Check to see if there is a Network Address Translation (NAT) between the client and server
In Windows, check to see if a Windows firewall is checking for your local connections:
Windows XP -> Control panel -> security -> Tab «Advanced»
Also, setting the parameters sqlnet.inbound_connect_timeout and inbound_connect_timeout_listenername to 0 can stop the ORA-03135 error.
Also as above, check the database alert log for errors occurring at the same time as the connection dropping. Sometimes it can be caused by an ora-600 or ora-7445, for example. It shouldn’t appear in the listener log as you only get this error after handshaking has been completed.
In my previous articles i have given detailed idea about multiple database and oracle errors. In this article i would like to give more information about the ORA-03135: connection lost contact error in detail. With this i would also like to give the resolution of this error. The error is most common error coming in the oracle and i need to explain it with detail.The ORA-03135 is an error that stems from the latter.
ORA-03135: connection lost contact – Why this error is coming?
In this section i would like to explain the actual cause of the error with the action we require to complete.
Cause of Error :
Cause 1 : Server unexpectedly terminated
The error itself is giving you the reason or cause of the error. The error is connection lost error. It simply means the server unexpectedly terminated or was forced to terminate and due to that the connection has lost. There are so many times this error will occur when we are working with complex set of tables.
Cause 2 : Server time out :
Multiple times we are getting the server time out error. The server time out error means when connection with network unexpectedly lost or server is timed out and request-reply mechanism is not working.This kind of error will occur due to some connectivity issues or may also be caused due to firewall while connecting through the remote environment.
Real Example for this cause :
While working on the oracle database there are many times the following error will occure. The error is coming at the time of connecting through the database.
ORA-03135: connection lost contact
Resolution of the Error :
We have already discussed about the cause of this error and now its time to discuss about the resolution of this specified error.
1.Server Session check :
The first check is always to check server session was terminated or not. There are multiple times due to any network connection issue the server session terminated.
How to check Server sessions ?
To check the server sessions you require to check the firewall connection first and then check with pinging the server.
Example :
If you want to check server session for Amit_Server then you can check with following command.
Ping Amit_server
You can check communication commands examples :
If reply is coming then the server connection is proper.
2.Check for timeout Parameters sqlnet.ora :
The second cause will be the sqlnet.ora timeout parameters. Most of the times the timeout parameters was set to less value. In that case we require to check and set the the sqlnet.ora timeout parameter properly to resolve this kind of error.
How to set timeout parameter in sqlnet.ora?
Location of file : $ORACLE_HOME/network/admin/.
1.The first step is to check the timeout parameters of sqlnet.ora. we need to check the value of expire_time parameters. Most of the time the parameter value is set to 0. So it could be always greater than zero.
SQLNET.EXPIRE_TIME = 5
2.There are other parameter also. If we found that the SQLNET.EXPIRE_TIME parameter set to some value and still error is coming then try to increase the value of that parameter and check again.
SQLNET.EXPIRE_TIME = 7
3.If still issue is there then kindly set sqlnet.inbound_connect_timeout and inbound_connect_timeout_listenername parameters to value 0.
3.Other checks :
We require to do some other checks to resolve this issue.
Check 1 : We require to check the NAT or Network Address Transition between client and server.
Check 2 : Checking firewall
We can do the checks for firewall for your local connections :
Windows XP -> Control panel -> security -> Tab “Advanced”
I hope you got the clear idea about the ORA-03135: connection lost contact error in detail. If you like this article on ORA-03135: connection lost contact or if you have any issues with the same kindly comment in comments section.
ORA-03135
In today’s age, the scope of features that database systems such as Oracle can cover is so vast that it can seem a bit intimidating. Fortunately, the creative nature of the product allows every table and data set to be as complex as the user desires it to be. Still, this range of components can lend itself to a variety of errors. For the sake of simplicity, it helps to break these errors down into two sets: those that occur in the data/coding, and those that pertain to software/network settings.
The ORA-03135 is an error that stems from the latter. The negative of this kind of error is that it can happen to any user, whether working with complex table setups or not. The positive is that there are a few basic strategies that can be employed to counter this error.
The Problem
An ORA-03135 error reads as the “connection lost contact”. This means that the server connection was unexpectedly terminated or forced to terminate. This can also mean that the server simply timed out the connection. The error often occurs when connecting from a remote location and the firewall in your network terminates the connection.
While this overall cause is fairly straightforward, the unfortunate aspect of the ORA-03135 is that there are a number of approaches to the solution, each dependent on the situation. Let’s take a look at how to properly diagnose the ORA-03135’s cause and some of the methods for correcting it.
The Solution
The best place to start is by diagnosing the error. To do this, begin by checking if the OS PID (OS Process Identification) still exists. Use the ps -ef|grep syntax to do so. From there, check to make sure that the Network Address Translation between the client and the server also still exists.
If neither of these seems to be the source of the problem, it could be that a firewall is the source. If you are using Windows, check if a Windows firewall is monitoring local connections and putting a halt on your database. To do this for Windows XP (and other editions), take the following steps:
1. Go to the “Start” menu
2. Click on the “Control Panel”
3. Click on “Security”
4. Click on the “Advanced” tab
This tab will note the presence of a Windows firewall and its activity in relation to your network.
From here, it is time to implement a solution to the error. One option is to increase the value of the sqlnet.ora expire time parameter. This parameter, which is set on the server, enables the detection of connections that have ended. After a specified amount of time (expressed in minutes) the server will check to see that the client is still connected and if not connected, the server process will exit. Extending this parameter value will increase the time between process expiration.
Another solution is to check for an expire parameter in a SQL*Plus profile. This will also disconnect the client from the server after a predetermined duration of time spent idling. To edit this information, follow these steps:
1. Start the Oracle Network Manager GUI (Graphical User Interface)
2. In the GUI navigator section, expand the icons for “Local>Profile”
3. From the list on the right-hand side, select “General”
4. Click on the tab for “Advanced”
5. Enter the values for the fields that correspond to the time between session expiration to any duration of your choosing
6. Remember to go to “File”, then “Save Network Configuration” so that the changes will be written to the sqlnet.ora file
You can additionally edit the parameters for sqlnet.inbound_connect_timeout and inbound_connect_timeout_listenername to zero in order to prevent the error from recurring in the future.
Looking forward
The ORA-03135 error can be frustrating, as it will interrupt your access and function in Oracle. However, as stated above, the error is preventable if the network is not in need of timing out connections at a frequent pace. If you find yourself having difficulty with this process or perhaps require further instruction on alternative solutions that keep your timeout processes in tact, it would be recommended to speak with a licensed Oracle consultant to approach these types of complex network errors.
ORA-03135 is also a server process problem. As I said in solving ORA-03113:
No matter the database is down or session is lost, for external connections, the cause is that the server process is vanished.
On the other side, for local connections or through OS authentication, the symptom is the same, but we saw ORA-03135 instead.
SQL> conn hr/hr
Connected.
SQL> select first_name from employees where last_name = 'Chen';
select first_name from employees where last_name = 'Chen'
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 4616
Session ID: 143 Serial number: 115
As you can see, I didn’t specify the destination in the connection string, then the connection automatically went for the local database.
In the first place, the connection is fine, but the following statement failed with ORA-03135, which means the server process was vanished for some reason.
ORA-03135 means that the local connection has lost contact with the local database, you should check its health and status. Possible causes have been talked in previous post: How to Resolve ORA-03113: end-of-file on communication channel.
ORA-03135: connection lost contact
Cause: 1) Server unexpectedly terminated or was forced to terminate.
2) Server timed out the connection.
Action: 1) Check if the server session was terminated.
2) Check if the timeout parameters are set properly in sqlnet.ora.
Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e2100.htm#ORA-03135
ORA-03135 occurs when we try to connect oracle database and the connection gets time out. ORA-03135 can also be caused by the firewall when connecting remotely.
To resolve ORA-03135 exception we need to increase the value of the expire_time parameter in the SQLNET.ORA file, as mentioned in Action part. We can also set the parameters sqlnet.inbound_connect_timeout and inbound_connect_timeout_listenername to 0 can avoid the ORA-03135 error. SQLNET.ORA file is located under $ORACLE_HOME/network/admin/.
Following is the Oracle Documentation having lists and description of sqlnet.ora file parameters
— http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF182
We recently faced ORA-03135 on one of our testing environment after Server Admin team did a firewall upgrade. Users were able connect to the database, but if their session was idle for some time, the connection was getting terminated.
To resolve ORA-03135 we added following to SQLNET.ORA file
SQLNET.EXPIRE_TIME = 10
This keeps connections alive by send a check to verify that client/server connections are active in every 10 minutes. If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.
Related Posts
— SQLNET: How does Oracle Client connect with Oracle Server
— ORA-03113: end-of-file on communication channel
— ORA-12154: TNS:could not resolve the connect identifier specified
— ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
— ORA-01017: invalid username/password; logon denied