Oracle 11g 超时问题的处理办法

Oracle 11g 超时问题的处理办法

在Oracle 11G中如果一个与Oracle的通讯，长时间空闲，Oracle会将其识为Timeout，主动Kill这个Session。如何你的应用端编写的程序不能提供自动重连的功能，这样的场景你需要做以下的配置以避免应用不可用。

具体如下： 按官方文档所说，原则上INBOUND_CONNECT_TIMEOUT_listener_name应小于INBOUND_CONNECT_TIMEOUT

Oracle 11g has a problem that is session timeout problem. You can add a row to sqlnet.ora file and listener.ora file to resolve session timeout problem.

sqlnet.ora

Note : Add this line to below of sqlnet.ora file.
INBOUND_CONNECT_TIMEOUT=3600

listener.ora

Note : Add this line to below of listener.ora file.
INBOUND_CONNECT_TIMEOUT_LISTENER=2400

注意：如何你的LISTENER名字为SBSBSB的话，上面的listener.ora  就要改成 sbsbsb.ora ：INBOUND_CONNECT_TIMEOUT_SBSBSB=2400
如果你不是SB的话，上面的说明应该可以看明白的。

官方说明文档：
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:
Oracle Net Services Administrator's Guide for information about configuring these parameters

Default

60 seconds

Example

SQLNET.INBOUND_CONNECT_TIMEOUT=3