SQLNET.ORA Parameters: SQLNET.EXPIRE_TIME & SQLNET.INBOUND_CONNECT_TIMEOUT

Finally after a long long time posting on this blog. 

Hi All, 

Oracle documentation states SQLNET.EXPIRE_TIME parameter is used to avoid unused sessions to be kept open in database and locking resources.

For SQLNET.INBOUND_CONNECT_TIMEOUT, it states: 

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 tnslsnr diag destination ($ORACLE_HOME/diag/tnslsnr/<listener_name>/trace/<listener.log>. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.and also records in alert log if audit is enabled for IPs. 

In our environment both parameters are set for default value which I feel were too low SQLNET.EXPIRE_TIME (0 - disabled) & SQLNET.INBOUND_CONNECT_TIMEOUT (60 seconds), potentially causing some connectivity issue when users trying to run some batch jobs which establishes 60-70 connections. 

   After 2-3 weeks of discussions and other workarounds (server, network and storage side) we finally decided to set these parameters as user were still facing same connection problems all at once (ORA-12547: TNS:lost contact). So we defined the following values: 

SQLNET.INBOUND_CONNECT_TIMEOUT=300  (default is 60 & 0 means indefinite)

SQLNET.EXPIRE_TIME=5  (default is 0(disabled) & 10 is recommended).

   Haven't received any complains after setting that  but fingers crossed. 😉

Oracle Doc Says: 

SQLNET.EXPIRE_TIME

Purpose

To specify a time interval, in minutes, to send a check to verify that client/server connections are active.

Usage Notes

Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the system supports TCP keepalive tuning, then Oracle Net Services automatically uses the enhanced detection model, and tunes the TCP keepalive parameters

If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.

This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are:


It is not allowed on bequeathed connections.


Though very small, a probe packet generates additional traffic that may downgrade network performance.


Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.



Default - 0

Minimum Value - 0

Recommended Value - 10
SQLNET.INBOUND_CONNECT_TIMEOUT


Purpose

To specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

Usage Notes

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.

The default value of this parameter is appropriate for typical usage scenarios. However, if you need to explicitly set a different value, then Oracle recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying the values for these parameters, note 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 SQLNET.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.

Default - 60 seconds

Link Referred: https://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF211