Thursday, July 2, 2009

ORA-12170 & SQLNET.INBOUND_CONNECT_TIMEOUT

SQLNET.INBOUND_CONNECT_TIMEOUT has been introduced in version 9i.This has to be configured in sqlnet.ora file specifies the time,in seconds, for a client to connect with the database server and provide the necessary authentication information.

If there are network latencies and the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file.
The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message. In version 10g and higher, the ORA-3136 errors may appear in the alert.log.


To protect both the database server and the listener for denial of service attacks
Oracle Corporation recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file.

TIPS

In 10gR2, the default setting for these parameters is 60 seconds.
So set the parameters to at least that value in pre-10gR2 releases because there is no default setting for this versions.

E.g

Set on sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=60

Set on listener.ora for each listener
INBOUND_CONNECT_TIMEOUT_[LISTENER_NAME]=60

2 comments:

  1. hi - how do you explain the fact that we get errors in sqlnet.log ORA-12170: TNS:Connect timeout, although inbound_connect_timeout in both listener and sqlnet files is not set. version is 10gr1

    thanks!

    ReplyDelete
  2. Check your alert.log for WARNING: inbound connection timed out (ORA-3136). If you found these errors then maybe the database is the
    problem for these timeouts not the network.

    When a database session is in the authentication phase, it will issue SQL statements that must parsed, executed and fetched.

    It follows that any problem encountered during these phases which appears as a hang (the issue here is that the authenticating session is blocked waiting to get a shared resource which is held by another session inside the database) or severe slow performance may result in a timeout.

    Symptoms of such hangs will be seen by the authenticating session as waits for:

    cursor: pin S wait on X
    latch: row cache objects
    row cache lock

    So if your case is similar a solution might be to increase your shared pool for a start. If error continues to happen the system dumps or queries on internal views may needed. In any such case call Oracle Support.

    ReplyDelete