Sunday, January 20, 2013

WARNING: inbound connection timed out ORA-3136

Hi,

Recently I was troubleshootiong for this error,finally resolved.First,let me explain what exactly it mean, the cause of this error,possible method of diagnosis and finally the resolution.

INBOUND_CONNECT_TIMEOUT:
 The INBOUND_CONNECT_TIMEOUT is the parameter used to specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.
i.e The default timeout for a new connection to be established. This setting is called SQLNET.INBOUND_CONNECT_TIMEOUT.

 If the listener does not receive the client request in the time specified, then it terminates the connection.Check the listener.log for details.

In Oracle 9i,the value is set to unlimited and the new value in 10g is 60 seconds.In Oracle 11g also the default value is 60 seconds.

Causes:

1) Some Malicious client connections. 
2)  Connection takes a long time.
3)Database is heavily loaded and cannot process request in allotted time.

Diagnosis:

1)Check the alert log file and check from where the connection comes.
2)Check the listener is up & running.
3)Ping the server,make sure tnsping is working.

Resolution:

To identify the listener name and ORACLE_HOME,we can use the below command.

$ ps -eaf|grep tns
oracle    1643     1  0  2012 ?        04:51:49 /data01/home/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
oracle   31682 16935  0 04:54 pts/0    00:00:00 grep tns


Check if the sqlnet.ora file is existing in $ORACLE_HOME/network/admin path,if not create one.

Adjusted the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora and reloaded the the listener configuration:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=60
listener.ora: INBOUND_CONNECT_TIMEOUT_LISTENER=60
where:Listener_name=>LISTENER
Default value for these is 60 seconds.


Below are the details:

LSNRCTL>set  INBOUND_CONNECT_TIMEOUT_LISTENER=60
[oracle@hostname admin]$ cat sqlnet.ora
#SQLNET.ORA Network Configuration File: /u01/home/oracle/product/10.2.0/network/admin/sqlnet.ora
#To eliminate inbound connection timeout
SQLNET.INBOUND_CONNECT_TIMEOUT=60

#sqlnet.authentication_services = (NONE)

#SQLNET.EXPIRE_TIME = 0

#SQLNET.ENCRYPTION_SERVER = requested

#SQLNET.ENCRYPTION_CLIENT = requested

NAMES.DIRECTORY_PATH= (TNSNAMES,hostname)
[oracle@s259722ch3el19 admin]$

[oracle@s259722ch3el19 admin]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JAN-2013 05:01:25

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.1.511)(PORT=1521)))
The command completed successfully


Keep  monitoring - alerts have stopped for now.

If the alerts continous to come,than you have to increase the value of  INBOUND_CONNECT_TIMEOUT as follows:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=300
listener.ora: INBOUND_CONNECT_TIMEOUT_LISTENER=300
 where:Listener_name=>LISTENER

 Hope it help.

Happy DBA tasks and troubleshooting.


Best regards,

Rafi.