ORA-03135: connection lost contact


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

2 comments:

  1. OP reported it was due to a firewall change. You probably need to enable TCP keepalive:

    add the parameter in tnsnames.ora "ENABLE=BROKEN" :

    XXXX =
    (DESCRIPTION =
    (ENABLE = BROKEN)
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = 1521))
    (CONNECT_DATA =
    (SID = XXXX)
    )
    )

    On the client PC add these registry items to the TCPIP parameters:

    KeepAliveTime
    KeepAliveInterval

    for instance:

    [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesTcpipParameters]
    "KeepAliveTime"=dword:001b7740
    "KeepAliveInterval"=dword:00001000

    The SQLNET.EXPIRE_TIME in sqlnet.ora should be implemented on the server side, but this will only remove dead connections.

    ReplyDelete
  2. You should be a part of a contest for one of the most
    useful blogs on the internet. I will highly recommend this blog!

    ReplyDelete