今天数据库的警告日志里面，突然出现如下错误，造成应用不能访问。
...
Tue Mar 4 13:37:53 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:37:53 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:54:14 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:54:58 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:54:58 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:56:14 2014
WARNING: inbound connection timed out (ORA-3136)
...
查看了 sqlnet.log 日志，发现里面也有不少报错
...
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
Time: 04-3月 -2014 13:56:14
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.19.82)(PORT=37034))
...
关于该报错，在MOS查了到了[ID 465043.1]，原文如下：
Applies to:
Oracle Net Services - Version 10.2.0.1.0 to 11.2.0.1 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Purpose
Troubleshooting guide for "ORA-3136 WARNING inbound connection timed out" seen in the alert log.
Troubleshooting Steps
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.
You might also see the errors ORA-12170 or TNS-12535 in the sqlnet.log that is generated on the server.
Check $ORACLE_HOME/network/log for this file. This entry should contain client address from which the timeout originated and may be helpful in determining how to troubleshoot the issue. Some applications or JDBC thin driver applications may not have these details. The sqlnet.log file is not generated by default in 11g and newer.
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.
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.
To understand what is causing this issue, following checks can be done
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.
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= Where is the value in seconds.
E.g.:
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
Edit the listener.ora file and add this parameter:
INBOUND_CONNECT_TIMEOUT_ = Again, where is the timeout value in seconds.
For example if the listener name is LISTENER then use:
INBOUND_CONNECT_TIMEOUT_LISTENER = 110
From Oracle version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_ is 60 seconds. For previous releases it is zero or OFF by default.
How to check whether inbound timeout is active for the listener and database server:
For example, INBOUND_CONNECT_TIMEOUT_ =110
You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet
for eg.
$ telnet 123.23.23.23 1521
The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.
Alternatively, check at the LSNRCTL prompt using:
LSNRCTL>set current_listener
LSNRCTL>show inbound_connect_timeout
To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.
SQLNET.INBOUND_CONNECT_TIMEOUT=120
a.For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:
niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life...
b.For shared Server setup,
$ telnet
Example.
$ telnet 123.23.23.23 51658
The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.
If you have further questions regarding this issue then please create a Service Request in My Oracle Support and provide the following information:
a.Client and matching server traces generated at support level.
Note 395525.1 How to Enable Oracle Net Client,Server,Listener,Kerberos and External procedure Tracing from Net Manager (netmgr):
Note 374116.1 How to Match Oracle Net Client and Server Trace Files
b.Upload sqlnet.ora, listener.ora Sqlnet.log, & Alert_.log from database server
如是检查相关的参数，发现服务器的该参数没问题，已经设置为零。
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
而且关于SQLNET.INBOUND_CONNECT_TIMEOUT参数,Oracle建议修改该参数,以避免denial-of-service攻击.
SQLNET.INBOUND_CONNECT_TIMEOUT
Purpose
Use the SQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.
If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server 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.
Without this parameter, a client connection to the database server can stay open indefinitely without authentication. Connections without authentication can introduce possible denial-of-service attacks, whereby malicious clients attempt to flood database servers with connect requests that consume resources.
To protect both the database server and the listener, Oracle Corporation recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying values for these parameters, consider the following recommendations:
Set both parameters to an initial low value.
Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.
For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.
See Also:
Oracle9i Net Services Administrator's Guide for information about configuring these parameters
Default
None
Example
SQLNET.INBOUND_CONNECT_TIMEOUT=3
真不知道会是什么原因，导致上面的问题，会不会是网络不稳定造成咧？
...
Tue Mar 4 13:37:53 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:37:53 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:54:14 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:54:58 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:54:58 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Mar 4 13:56:14 2014
WARNING: inbound connection timed out (ORA-3136)
...
查看了 sqlnet.log 日志，发现里面也有不少报错
...
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
Time: 04-3月 -2014 13:56:14
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.19.82)(PORT=37034))
...
关于该报错，在MOS查了到了[ID 465043.1]，原文如下：
Applies to:
Oracle Net Services - Version 10.2.0.1.0 to 11.2.0.1 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Purpose
Troubleshooting guide for "ORA-3136 WARNING inbound connection timed out" seen in the alert log.
Troubleshooting Steps
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.
You might also see the errors ORA-12170 or TNS-12535 in the sqlnet.log that is generated on the server.
Check $ORACLE_HOME/network/log for this file. This entry should contain client address from which the timeout originated and may be helpful in determining how to troubleshoot the issue. Some applications or JDBC thin driver applications may not have these details. The sqlnet.log file is not generated by default in 11g and newer.
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.
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.
To understand what is causing this issue, following checks can be done
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.
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= Where is the value in seconds.
E.g.:
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
Edit the listener.ora file and add this parameter:
INBOUND_CONNECT_TIMEOUT_ = Again, where is the timeout value in seconds.
For example if the listener name is LISTENER then use:
INBOUND_CONNECT_TIMEOUT_LISTENER = 110
From Oracle version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_ is 60 seconds. For previous releases it is zero or OFF by default.
How to check whether inbound timeout is active for the listener and database server:
For example, INBOUND_CONNECT_TIMEOUT_ =110
You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet
for eg.
$ telnet 123.23.23.23 1521
The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.
Alternatively, check at the LSNRCTL prompt using:
LSNRCTL>set current_listener
LSNRCTL>show inbound_connect_timeout
To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.
SQLNET.INBOUND_CONNECT_TIMEOUT=120
a.For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:
niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life...
b.For shared Server setup,
$ telnet
Example.
$ telnet 123.23.23.23 51658
The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.
If you have further questions regarding this issue then please create a Service Request in My Oracle Support and provide the following information:
a.Client and matching server traces generated at support level.
Note 395525.1 How to Enable Oracle Net Client,Server,Listener,Kerberos and External procedure Tracing from Net Manager (netmgr):
Note 374116.1 How to Match Oracle Net Client and Server Trace Files
b.Upload sqlnet.ora, listener.ora Sqlnet.log, & Alert_.log from database server
如是检查相关的参数，发现服务器的该参数没问题，已经设置为零。
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
而且关于SQLNET.INBOUND_CONNECT_TIMEOUT参数,Oracle建议修改该参数,以避免denial-of-service攻击.
SQLNET.INBOUND_CONNECT_TIMEOUT
Purpose
Use the SQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.
If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server 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.
Without this parameter, a client connection to the database server can stay open indefinitely without authentication. Connections without authentication can introduce possible denial-of-service attacks, whereby malicious clients attempt to flood database servers with connect requests that consume resources.
To protect both the database server and the listener, Oracle Corporation recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying values for these parameters, consider the following recommendations:
Set both parameters to an initial low value.
Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.
For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.
See Also:
Oracle9i Net Services Administrator's Guide for information about configuring these parameters
Default
None
Example
SQLNET.INBOUND_CONNECT_TIMEOUT=3
真不知道会是什么原因，导致上面的问题，会不会是网络不稳定造成咧？
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-1100925/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9399028/viewspace-1100925/
扫一扫
747
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
