how to handle inbound connection timed out (Fatal NI connect error 12170, ORA-3136)

how to handle inbound connection timed out (ORA-3136)?

Today when going through regular activities like alert log checking, i found the following warning in one of database

Tue Mar 16 10:44:55 2014
WARNING: inbound connection timed out (ORA-3136)

Also, following entry was there in sqlnet.log file

Fatal NI connect error 12170.

  VERSION INFORMATION:
 TNS for 64-bit Windows: Version 11.1.0.7.0 – Production
 Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 – Production
 Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 – Production
  Time: 15-JUN-2010 12:58:20
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.1.1)(PORT=1521))

To my surprise, in next 2 min i got a ticket stating user is facing problem in connecting to database. After waiting for long time, user is getting error in connection establishment.

After analysis, i found following

user connectivity time bound is 60 sec from 10.2 which means oracle will try to establish connection for 60 seconds, once it crosses that limit, it will send error to user process.

This error you will not see in 10.1 version, because in that version time bound limit in infinite.

I did following which resolved my problem

1) Add following entires in listener.ora file on database server

INBOUND_CONNECT_TIMEOUT_<LISTENER_NAME>=0
DIRECT_HANDOFF_TTC_<LISTENER_NAME>=OFF

alternatively you can also do this

lsnrctl> set inbound_connect_timeout=0

2) add following entries in sqlnet.ora of database server

SQLNET.INBOUND_CONNECT_TIMEOUT=0

we are setting time bound limit to infinite using above parameters. you need to reload the listener after performing above changes using

$ lsnrctl reload

Note : Always take backup of listener.ora or sqlnet.ora files before modifying anything inside

More about this error, you can check in metalink docs 465043.1 and 345197.1

Sometimes, this problem will also occur if you have firewall restrictions. So please check from that end too.