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.