ORA-3136: WARNING Inbound Connection Timed Out

 

客户遭遇后台报错:"ORA-3136: WARNING Inbound Connection Timed Out",而且连续几十条,但前段的用户似乎没有异常的状态反应,对用户到没有造成什么影响。ORA-3136的成因相对复杂,可能有很多种可能,例如在MOS中有篇文档[ID 465043.1],详细的介绍了可能性:
 
—————————————————————————————————————-
The following are the most likely reasons for this error: 
 
1.Server gets a connection request from a malicious client which is not supposed to connect to the database.  In this case the error thrown would be the expected and desirable behavior. 
You can get the client address for which the error was thrown in the sqlnet.log file that is local to the database. 
 
2.The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds. 
 
3.The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified
 
—————————————————————————————————————–
 
The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete
the authentication process within the period of time specified by the parameter SQLNET.INBOUND_CONNECT_TIMEOUT
 
同时,在sqlnet.log中可能会伴随着出现ORA-12170,TNS-12535信息,而且sqlnet.log中应该记录了"验证失败"的客户端信息,在udump中可能会有一些trace文件,trace文件的内容包含类似这样的信息:
"opiino: Attach failed! error=-1 ifvp=0"
 
自从10201开始,SQLNET.INBOUND_CONNECT_TIMEOUT参数被引入,缺省值为60秒,如果在60秒内连接数据库的client由于各种原因无法完成验证工作,在alert中就会出现ORA-3136的报错信息,同时此参数的引入也有意为防止DDOS的攻击。
 
—————————————————————————————————————————-
From 10.2.0.1 onwards the default setting for the parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds.
If the client is not able to authenticate within 60 seconds, the warning would appear in the alert log and the client connection will be terminated
—————————————————————————————————————————-
 
Note: This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.
 
影响:通常这个报错不会影响db本身的稳定性,但由于client端会无法连接,要视对client端的影响而定,如果客户端相关人员并没有任何异常的反馈,那影响有限。
 
如何解决:
 
The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it is taking longer, then it's worth checking the following items before implementing the workaround:
 
1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them  resolved first. 
These critical errors might have triggered the slowness of the database server.
 
It is often necessary to increase the values for INBOUND CONNECT TIMEOUT at  both the listener and the database in order to resolve this issue.It is usually advisable to set the database (sqlnet.ora) value slightly higher than the listener (listener.ora).    The authentication process is more demanding for the database than the listener.
(建议将sqlnet.ora中的参数值设置比listener.ora中稍微大一些)
 
将SQLNET.INBOUND_CONNECT_TIMEOUT增大,可以参考下面的步骤:
 
To set these parameters to use values higher than the default of 60 seconds, follow these instructions and restart the listener. 
 
There is no need to restart Oracle: 
 
Edit the server side sqlnet.ora file and add this parameter:
 
SQLNET.INBOUND_CONNECT_TIMEOUT=<n>  Where <n> is the value in seconds.
 
E.g.:
 
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
 
Edit the listener.ora file and add this parameter: 
 
INBOUND_CONNECT_TIMEOUT_<listenername> = <n>  Again, where <n> is the timeout value in seconds.  
 
For example if the listener name is LISTENER then use:
 
INBOUND_CONNECT_TIMEOUT_LISTENER = 110
 
 
更详细的信息可以参考mos中的文档[ID 465043.1]
 
–EOF–