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)
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.
Solution to ORA-3136
Heavy loading of a database cannot be easily solved in one second, but we can mitigate the complaining by increase INBOUND_CONNECT_TIMEOUT_LISTENER, the inbound timeout limit (in seconds).
Here are the steps:
1. Add Parameter to listener.ora
The default value of INBOUND_CONNECT_TIMEOUT_LISTENER is 60 seconds, we can raise the value to 160 seconds.
We should add the parameter at oracle-level.
$ echo "INBOUND_CONNECT_TIMEOUT_LISTENER = 160" >> $ORACLE_HOME/network/admin/listener.ora
We should add the parameter at grid-level on all nodes.
$ echo "INBOUND_CONNECT_TIMEOUT_LISTENER = 160" >> $GRID_HOME/network/admin/listener.ora
2. Add Parameter to sqlnet.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.
3. Restart the listener
$ srvctl stop listener
$ srvctl start listener
4. Validate the Result
Then, we can validate the new setting by using telnet, the telnet connection will test open port 1521 and be timed out after 160 seconds, which is 2 minutes and 40 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 with yours.
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.