TNS Tips

During a session with the database, the client uses Oracle Call Interface (OCI) to interact with the database server. OCI is a software component that provides an interface between the client application and the SQL language the database server understands.

To connect to a database, we use the a "Connection String", which consists of three parts (separated by a "/" and a "@"); username, password, and the "TNS Alias" (or "Host String"). This is a typical Connection String: scott/tiger@orcl.

The TNS Alias needs to be resolved through a number of mechanisms that may be deployed in your network.

The most common mechanism for resolving the TNS Alias is the tnsnames.ora file, which has caused an unknown number of wasted hours for an unknown number of consultants and customers, and thereby an unknown amount of money.

Configuration

On the client, two files (sqlnet.ora and tnsnames.ora) contain information about available connections. These two files can be found in the \network\admin subfolder of the Oracle home folder, e.g. C:\ora\mt\network\admin.

Typical sqlnet.ora:

# sqlnet.ora Network Configuration File: C:\ora\mt\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT, ONAMES, HOSTNAME)

Typical tnsnames.ora:

# tnsnames.ora Network Configuration File: C:\ora\mt\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.company.com)
    )
  )

BPEL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bpel.company.com)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

Sometimes, the sqlnet.ora contains a default domain, e.g.:

NAMES.DEFAULT_DOMAIN = world

The complete TNS alias name is assembled by appending the value of the "NAMES.DEFAULT_DOMAIN" parameter from the sqlnet.ora file to the TNS alias name in the tnsnames.ora file. So, if the "NAMES.DEFAULT_DOMAIN" parameter is set to "world", the complete TNS alias for the "orcl.company.com" database service shown in the tnsnames.ora file above would be "orcl.world". Trying to connect using "orcl" would not work.

Tips

If you are having trouble finding the correct configuration files, check the value of environment variable TNS_ADMIN.

To check if a TNS Alias (e.g. orcl) is valid, open a command line window and enter the tnsping command, e.g.:

C:\>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.1.0.3.0 - Production on 26-OCT-2005 22:57:48

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
C:\ora\mt\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server.company.com)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.company.com)))
OK (30 msec)

Always make backup copies of sqlnet.ora and tnsnames.ora before you edit them
Whenever you add a TNS Alias to one of your tnsnames.ora files, be sure to copy it to the tnsnames.ora file in each of the other Oracle homes that you might have, e.g. C:\ora\im, C:\ora\mt, C:\ora\oc4j ...

To see which other Oracle homes you might have to deal with, open a command line window and enter the path command:

C:\>path
PATH=C:\ora\oc4j\jre\1.4.2\bin\client;C:\ora\oc4j\jre\1.4.2\bin;C:\ora\oc4j
\bin;C:\ora\oc4j\jlib;C:\ora\mt\bin;C:\ora\mt\jre\1.4.2\bin\client;C:\ora\m
t\jre\1.4.2\bin;C:\ora\mt\jlib;C:\ora\im\bin;C:\ora\im\jlib;C:\WINNT\system
32;C:\WINNT

(In this example, three Oracle homes are active, in this order; C:\ora\oc4j, C:\ora\mt, and C:\ora\im.)

To reduce the time it takes for a client to to connect to the database, you can comment out the following line (by prefixing it with a # sign) in the sqlnet.ora file:
```
# SQLNET.AUTHENTICATION_SERVICES= (NTS)
```
Please note that this prevents you (and programs such as installers and applications) to connect to the database without entering a password (as in "connect / as sysdba")
In a default installation of Oracle Application Server (Infrastructure and Middle-Tier), consider editing the following line so that it is identical in both copies of the sqlnet.ora file (by default, the Middle-Tier has the LDAP entry before the TNSNAMES entry, which is known to cause authentication problems for iDesk deployment in OC4J):
```
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, ONAMES, HOSTNAME)
```
... then edit tnsnames.ora in all Oracle homes to include a real host name:
```
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.company.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
    )
  )
```
To disconnect sessions that have been idle for 8 hours or more, enter the following parameter in sqlnet.ora on the database server:
```
SQLNET.EXPIRE_TIME=480
```