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

Comments

Post a Comment

Popular posts from this blog

Administering an Oracle Database Instance Using ORADIM

ORADIM is a command-line tool that is available with Oracle Database. You are required to use ORADIM only if you are manually creating, deleting, or modifying databases. Oracle Database Configuration Assistant is an easier tool to use for this purpose. Starting with Oracle Database 12 c  Release 1 (12.1), ORADIM creates Oracle Database service, Oracle VSS Writer service, and Oracle Scheduler service to run under the Oracle Home User account. If this account is a Windows Local User Account or Windows Domain User Account, then ORADIM prompts for password for that account and accepts the same through  stdin . It is possible to specify both the Oracle Home User and its password using the  -RUNAS osusr[/ospass]  option to  oradim . If the given  osusr  is different from the Oracle Home User, then the Oracle Home User is used instead of  osusr  along with the given  ospass . The following sections describe ORADIM commands and paramet...
Read more

How to resolve "`/root/.gvfs':Permission denied" when running df command from non-root user

When root user is logging in to GNOME, error "`/root/.gvfs':Permission denied" occurs if a normal user runs df. Solution Verified - Updated May 6 2014 at 12:16 AM - English English Environment Red Hat Enterprise Linux (RHEL) 6 GNOME Desktop Environment Issue When root user is logging in to GNOME, the following error occurs if a normal user runs df. $ df -kl Filesystem 1K-blocks Used Available Use% Mounted on /dev/sda2 24670076 3751464 19665444 17% / tmpfs 1030908 100 1030808 1% /dev/shm /dev/sda1 99150 25491 68539 28% /boot df: `/root/.gvfs': Permission denied Resolution To disable gvfs-fuse-daemon , the GVFS_DISABLE_FUSE environment needs to be set. Create the file /etc/X11/xinit/xinitrc.d/00-gvfs-disable-fuse.sh with the follwing contents: GVFS_DISABLE_FUSE=1 export GVFS_DISABLE_FUSE Log out and log in again. If vncserver is running, restart ...
Read more