At times, some users complained about connection timeout, and you saw error messages ORA-3136 in sqlnet.log as such:
Fatal NI connect error 12170.
ns main err code: 12535
ns secondary err code: 12606
nt OS err code: 0
TNS-12535 TNS:operation timed out
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=XXX))
nt secondary err code: 0
Tracing not turned on.
WARNING: inbound connection timed out (ORA-3136)
If you got the ORA-3136 accompanied with TNS-12535 and ORA-12170, it’s possibly caused by two factors, one is network traffic, the other is database heavy loading.
Luckily, the sqlnet.log logged the client connection data containing IP address for you to trace back the network condition at that time. Then, you can compare network response time during peak windows and off-peak windows to judge whether the network condition was normal or not.
Database heavily loaded cannot be easily solved in a second, but we can mitigate the complaining by increase the inbound timeout limit (in seconds), here are the steps:
- Add or modify a parameter into listener.ora on all database servers
- Add or modify a parameter into sqlnet.ora on all database servers
# For Grid infrastructure
$ echo "INBOUND_CONNECT_TIMEOUT_LISTENER = 160" >> $GRID_HOME/network/admin/listener.ora
# For Non-Grid infrastructure
$ echo "INBOUND_CONNECT_TIMEOUT_LISTENER = 160" >> $ORACLE_HOME/network/admin/listener.ora
$ echo "SQLNET.INBOUND_CONNECT_TIMEOUT = 180" >> $ORACLE_HOME/network/admin/sqlnet.ora
The value in sqlnet.ora should be slightly larger than the value in listener.ora, because database authentication needs extra time to do.
And then restart the listener:
$ srvctl stop listener
$ srvctl start listener
Then, we can validate the new setting is applied from any client using telnet, the telnet connection will be timed out after 160 seconds:
$ date; telnet 10.10.10.10 1521; date
Thu Oct 11 11:06:20 CST 2012
Connected to primary01.example.com (10.10.10.10).
Escape character is '^]'.
Connection closed by foreign host.
Thu Oct 11 11:09:00 CST 2012
The IP address above can be replaced for your need.
Notice that, the higher value you set, the higher security risk you take, it may be taken as a possible leak to DOS (Denial of Service) attack.