Pavan DBA's Blog

The DBA Knowledge Store

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.

8 Responses to “how to handle inbound connection timed out (ORA-3136)?”

  1. sai said

    hi sir, can we immediately start troubleshooting this issue or shall we wait to see same error in alertlog repeatedly..??

  2. 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

  3. 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?

  4. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: