Using tnsnames.ora Files

The tnsnames.ora file is used to map connection information for each Oracle service to a logical alias. The Oracle driver allows you to retrieve basic connection information from a tnsnames.ora file, including:

• Oracle server name and port
• Oracle System Identifier (SID) or Oracle service name
• Server process type (shared or dedicated)
• Failover instructions
• Client load balancing instructions
• Data encryption instructions

In a tnsnames.ora file, connection information for an Oracle service is associated with an alias, or Oracle net service name. Each net service name entry contains connect descriptors that define listener and service information. The following example shows connection information in a tnsnames.ora file configured for the net service name entries, FITZGERALD.SALES and ARMSTRONG.ACCT.

FITZGERALD.SALES =
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
       (CONNECT_DATA =
          (SID = ORCL)
       )
  )
ARMSTRONG.ACCT =
  (DESCRIPTION = 
  (ADDRESS_LIST= 
     (FAILOVER = on)
     (LOAD_BALANCE = on) 
     (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
     (ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
     (ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))
)   
  (CONNECT_DATA=
     (SERVICE_NAME = acct.us.yourcompany.com)
     (FAILOVER_MODE = 
         (BACKUP=server2)
         (TYPE=select)
         (METHOD=preconnect)
         (RETRIES=20)
         (DELAY=3)
     )
)

Using this example, if the Oracle driver referenced the Oracle net service name entry FITGERALD.SALES, the driver would connect to the Oracle database instance identified by the Oracle SID ORCL (SID=ORCL). Similarly, if the Oracle driver referenced ARMSTRONG.ACCT, the driver would connect to the Oracle database identified by the service name acct.us.yourcompany.com (SERVICE_NAME=acct.us.yourcompany.com). In addition, the driver would enable failover (FAILOVER=on) and client load balancing (LOAD_BALANCE=on).

Typically, a tnsnames.ora file is installed when you install an Oracle database. By default, the tnsnames.ora file is located in the ORACLE_HOME\network\admin directory on Windows and the $ORACLE_HOME/network/admin directory on Linux/UNIX.