how to handle inbound connection timed out (ORA-3136)?
Posted by Pavan DBA on June 16, 2010
Good day friends…
Today when going through regular activities like alert log checking, i found the following warning in one of database
Tue Jun 15 14:44:55 2010
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 10.2.0.2.0 – Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 10.2.0.2.0 – Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 10.2.0.2.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=20.60.103.141)(PORT=3741))
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.
Pavan DBA's Blog 
sai said
hi sir, can we immediately start troubleshooting this issue or shall we wait to see same error in alertlog repeatedly..??
Pavan DBA said
yes. better to wait for repeated issue. sometimes, you may get error only once. in such case, you can ignore and retry operation
Marina said
hi, we are facing similar issue. May you please let us know whether sqlnet.ora should be in DB_HOME or GRID_HOME since we have it in both locations. We have a standalone db server.
Environment:
DB Version : 12.1.0.2.0
/u01/app/oracle/product/12.1.0/grid/network/admin/sqlnet.ora — 1st one (GRID_HOME)
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora — 2nd one (DB_HOME)
Thanks
Pavan DBA said
You can have in both actually. In our env we have sqlnet.ora in grid home and we created a soft link to that in database home
naresh said
Hi Pavan ,
I have faced issue now . in my case, listener.ora file is like below .
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
There is no parameter ” INBOUND_CONNECT_TIMEOUT_”
So, how can i proceed with this .
Thanks and Regards
Naresh
Pavan DBA said
Hi Naresh, my article is for standalone env. as yours is RAC env, i suggest to add parameters in sqlnet.ora file.
may i know if you have separate listener for each instance?
murali krishna said
Hi Pavan
Hope doing good!
I am old student of Kanna tech,We have faced one issue when application user is trying to connect to database they are not able to connect to database connect last’s for 5 min and it is throwing error ORA-12516
Can you please let me know what would be the reason
listener was up and running
Pavan DBA said
hi murali, what is the value of processes parameter? usually this error will occur if u have very less value for that parameter. so try increasing that value and problem will be solved.