TNS

• static and dynamic listener registration - artincarstenbach.wordpress.com
• Exploring the LOCAL_LISTENER parameter - edstevensdba.wordpress.com
• diagnosing connection issues - edstevensdba.wordpress.com
• tracking down solutions to tns error messages - edstevensdba.wordpress.com

Connections fail 10% of the time. How to fix?

There are some settings that can help...

sqlnet.ora

Setting a value greater than 0 ensures that connections are not left open indefinitely, due to, for example, an abnormal client termination.

# sqlnet.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ADR_BASE = /oracle/product

DIAG_ADR_ENABLED=OFF

sqlnet.expire_time=10
DEFAULT_SDU_SIZE=11280

tnsnames.ora

Setting the local_listener in the database to the connection string instead of the connect identifier can help

# tnsnames.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_FEUK11UD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))


FEUK11UD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FEUK11UD)
    )
  )

alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))" scope=both;

listener.ora

Setting STARTUP_WAIT_TIME_LISTENER Setting CONNECT_TIMEOUT_LISTENER

# listener.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.

STARTUP_WAIT_TIME_LISTENER_FEUK11UD = 0
CONNECT_TIMEOUT_LISTENER_FEUK11UD = 30
LISTENER_FEUK11UD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER_FEUK11UD = /oracle/product