Apps Developer

WARNING | inbound connection timed out (ORA-3136)-R12

By Jag - March 15, 2014
WARNING: inbound connection timed out (ORA-3136)-R12
ORA-3136 Connection Timeout

Causes of the ORA-3136 Error Message

There are many changes in the default behaviour from Oracle 9i and Oracle 10g databases. One of the changes to SQLNet was the default timeout for a new connection to be established. This setting is called SQLNET.INBOUND_CONNECT_TIMEOUT.
The value in 9i was unlimited and the new value in 10g is 60 seconds. In reality, I don’t think that many applications should take 60 seconds to establish a connection to the database – I think you’d have complaints before you received this error from a genuine application.
There are several reasons why you may hit this timeout:
  • Malicious client connections – DOS attack
  • Connection takes a long time
  • DB is heavily loaded and cannot process request in allotted time

Checks to Diagnose your ORA-3136 Error Message

You can check in one of the log file directories, Oracle_home/sysman/log if I remember correctly, for a log file which will show you where the connections are coming from. You may also be able to check in the alert log for the database as to where those connections are coming from. It’s always good to check through the alert log as a starting point.

Are Connections Working?

In order to try and diagnose if there are issues, you can do the following:
1. Check local connections are quick. To do this log onto the DB server and run the following checks:
    • Check the listener is up and responding quickly using the TNSPING utility:
                        TNSPING <sid> – where <sid> is the connection alias as specified in the TNSNames file
    • Create a connection to the database next, because TNSPING only checks that the listener is up and available:
                        SQLPLUS user/password@sid
2. Create a connection to the database but without the listener (these commands are for Windows):
    • SET ORACLE_SID=SID
    • SQLPLUS user/password
If there are errors somewhere here then you need to resolve these issues first. If the checks are all OK, you can move onto the next step.

Client Connectivity Status

You now need to check that connections coming from the client can reach the database server. You are happy that the listener is up and running, as you tested it in the previous step, so the client should be able to connect in the same way. From the client machine run the following checks:
    • Check that you can PING the DB server by opening up a command line and running:
                        PING <DB Server IP>
    • Check that you can reach the port of the listener. The default port is 1521, but check your listener.ora file on the DB server to make sure it is the correct port and then use TELNET. The screen on Windows just shows an arrow when it’s connected, you have to press CTRL + ] to get out and then type “quit”.
                        TELNET <DB Server IP> <port> - add <DB server IP> and <port>, default port is 1521
    • Now check that you can make a SQL connection:
                        SQLPLUS user/password@sid
You will need to troubleshoot any issues with the above steps if they do not work properly before moving on.

The Next Step in Resolving your ORA-3136 Error Message

If you are able to make connections without any issues then it’s clearly nothing obvious such as a configuration problem so you are going to have to do a little investigative work.
To be on the safe-side it’s worth checking database performance as a whole at this stage, by checking an AWR or STATSPACK report. If you are using AWR then you should compare the period you are investigating against a previously known good period. You can use the awrddrpt.sql script to do this in the ORACLE_HOME\rdbms\admin\ directory

Related Parameters…

- Server side SQLNet -
SQLNET.INBOUND_CONNECT_TIMEOUT
- Listener -
INBOUND_CONNECT_TIMEOUT_<listenername> - default is 60 from 10.2.0.3 onwards, prior to that it was unlimited

Are They Enabled…

    • To check INBOUND_CONNECT_TIMEOUT_<listenername>
                        TELNET <db server ip> <port>
Connection should timeout after set time for the above parameter
    • To check SQLNET.INBOUND_CONNECT_TIMEOUT = > 60
For dedicated server connection mode, enable level 16 sqlnet server tracing
Example Output:
niotns: Enabling CTO, value=60000 (milliseconds) <== 60 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life…
    • For shared server connection mode:
                        TELNET <db server ip> <dispatcher port>
should disconnect after specified time

Other Resources…

Please refer to Metalink Note: 465043.1 for further information. If you are still experiencing issues I would recommend opening a service request with Oracle support.

TAG:

WARNING: inbound connection timed out (ORA-3136)
warning inbound connection timed out (ora-3136) 11g
warning inbound connection timed out ora-3136 oracle 10g
tns-12535: tns:operation timed out
warning inbound connection timed out ora 3136 10g
warning inbound connection timed out ora 3136 alert log
ora 3136 inbound connection timeout
  • Share:

You Might Also Like

0 comments