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:

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 in Figure 7-1 shows connection information in a tnsnames.ora file configured for the net service name entries, FITZGERALD.SALES and ARMSTRONG.ACCT.

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.

To retrieve connection information from an Oracle tnsnames.ora file with the Oracle driver, you must inform the driver which tnsnames.ora file (using the TNSNamesFile property) and Oracle service name entry (using the TNSServerName property) to use so that the driver can reference the correct connection information. For example, the following connection URL in a Java string:

specifies the path and file name of the tnsnames.ora file (TNSNamesFile=c:\\oracle\\NETWORK\\ADMIN\\tnsnames.ora) and the net service name entry (TNSServerName=FITZGERALD.SALES) to use for the connection.

■	The connection URL does not specify the server name and port of the database server; that information is specified in the tnsnames.ora file referenced by the TNSNamesFile property.

■	If coding a path on Windows to the tnsnames.ora file in a Java string, the backslash character (\) must be preceded by the Java escape character, a backslash. For example:

If using tnsnames.ora files with a Security Manager on a Java 2 Platform, read permission must be granted to the tnsnames.ora file. See “Granting Access to an Oracle tnsnames.ora File” for an example.

If using a tnsnames.ora file to retrieve connection information, do not specify the following connection properties to prevent connection information conflicts:

AlternateServers EncryptionMethod FailoverMode LoadBalancing ServerName	ServiceName PortNumber ServerType SID

If any of these properties are specified in addition to the TNSNamesFile and TNSServerName properties, the driver throws an exception. For example, if the net service name entry ARMSTRONG.ACCT specifies the LOAD_BALANCE parameter as shown in the following example:

and you specify the LoadBalancing property in the driver connection URL as shown in the following example, the driver throws an exception.

Table 7-1 lists the Oracle driver properties that correspond to tnsnames.ora connect descriptor parameters. To prevent connection information conflicts, do not specify the listed properties if you use a tnsnames.ora file.

Table 7-1. Oracle Driver Property Mappings to tnsnames.ora Connect Descriptor Parameters
Oracle Driver Property	tnsnames.ora Attribute
AlternateServers = servers_list	ADDRESS_LIST = servers_list The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. For example: (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) (ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521)) (ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521)) ) The first ADDRESS parameter specifies connection information for the primary server. The second and third ADDRESS parameter specifies connection information for alternate servers. When multiple servers are specified by the ADDRESS_LIST parameter, connection failover is automatically enabled. If FAILOVER=off, connection failover is disabled. You also can explicitly specify connection failover using the FAILOVER parameter.
FailoverMode = {connect \| extended \| select}	FAILOVER_MODE = {TYPE={session \| select \| none} [BACKUP=value] [METHOD=basic \| preconnect] [RETRIES=value] [DELAY=value] } The FAILOVER_MODE parameter is specified in the CONNECT_DATA section to provide failover instructions for the driver. The FAILOVER_MODE parameter requires the TYPE parameter, which specifies the type of failover to be used. Other parameters are optional. (CONNECT_DATA= (SERVICE_NAME = acct.us.yourcompany.com) (FAILOVER_MODE = (BACKUP=server2) (TYPE=select) (METHOD=preconnect) (RETRIES=20) (DELAY=3) ) The FAILOVER_MODE parameter can only be used to enable extended connection failover or select failover. When multiple servers are specified by the ADDRESS_LIST parameter, connection failover is automatically enabled. If FAILOVER=off, connection failover is disabled. You also can explicitly specify connection failover using the FAILOVER parameter.
LoadBalancing = {true \| false}	LOAD_BALANCE = {on \| off} If LOAD_BALANCE=on, enables client load balancing. For example: (ADDRESS_LIST= (LOAD_BALANCE=on) (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) (ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521)) (ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521)) ) If the LOAD_BALANCE parameter is unspecified or LOAD_BALANCE=off, client load balancing is disabled.
PortNumber = port	PORT = port The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The PORT parameter is used within the ADDRESS parameter to specify the port number for each server entry. For example: (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) ... ) A port of 1521, the default port number when installing an Oracle database, is specified for server1.
EncryptionMethod= {noEncryption \| SSL}	PROTOCOL={TCP \| TCPS} The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The PROTOCOL parameter is used within the ADDRESS parameter to specify the network protocol to be used. It also is used to specify whether data is encrypted and decrypted when transmitted over the network between the driver and the server. For example, the following entry specifies that the TCP/IP protocol will be used with no encryption: (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) ... ) A port of 1521, the default port number when installing an Oracle database, is specified for server1. The following entry specifies that the TCP/IP protocol will be used with SSL encryption: (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCPS)(HOST = server1)(PORT = 2484)) ... ) A port of 2484, the port number recommended by Oracle for SSL, is specified for server1. NOTE: Truststore information must still be specified using either the TrustStore and TrustStorePassword properties or Java system properties. Optionally, you can specify the ValidateServerCertificate and HostNameInCertificate properties.
ServerName = server_name	HOST = server_name The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The HOST parameter is used within the ADDRESS parameter to specify the server name for each server entry. The server entry can be an IP address or a server name. For example: (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) ... ) The server name server1 is specified in the first server entry.
ServerType = {shared \| dedicated}	SERVER = {shared \| dedicated}. If SERVER=shared is specified in the CONNECT_DATA parameter in the tnsnames.ora file, the server process (UNIX) or thread (Windows) to be used is retrieved from a pool. For example: (CONNECT_DATA= (SERVER=shared) ) When SERVER=shared, this setting allows there to be fewer processes than the number of connections, reducing the need for server resources. When SERVER=dedicated, a server process is created to service only that connection. When that connection ends, so does the process (UNIX) or thread (Windows).
ServiceName = service_name	SERVICE_NAME = service_name The database service name that specifies the database used for the connection. The service name is a string that is the global database name—a name that typically comprises the database name and domain name. For example: sales.us.acme.com The service name is specified in the CONNECT_DATA parameter. For example: (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) ) This parameter is mutually exclusive with the SID attribute and is useful to specify connections to an Oracle Real Application Clusters (RAC) system rather than a specific Oracle instance.
SID = SID	SID = SID The Oracle System Identifier (SID) that refers to the instance of the Oracle database running on the server. The default Oracle SID that is configured when installing your Oracle database software is ORCL. The SID is specified in the CONNECT_DATA parameter. For example: (CONNECT_DATA= (SID=ORCL) ) This parameter is mutually exclusive with the SERVICE_NAME attribute.

Table 7-1. Oracle Driver Property Mappings to tnsnames.ora Connect Descriptor Parameters

Oracle Driver Property

tnsnames.ora Attribute

AlternateServers = servers_list

ADDRESS_LIST = servers_list

The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. For example:

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

(ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))

(ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))

)

The first ADDRESS parameter specifies connection information for the primary server. The second and third ADDRESS parameter specifies connection information for alternate servers.

When multiple servers are specified by the ADDRESS_LIST parameter, connection failover is automatically enabled. If FAILOVER=off, connection failover is disabled. You also can explicitly specify connection failover using the FAILOVER parameter.

FailoverMode = {connect | extended | select}

FAILOVER_MODE = {TYPE={session | select | none} [BACKUP=value] [METHOD=basic | preconnect] [RETRIES=value] [DELAY=value] }

The FAILOVER_MODE parameter is specified in the CONNECT_DATA section to provide failover instructions for the driver. The FAILOVER_MODE parameter requires the TYPE parameter, which specifies the type of failover to be used. Other parameters are optional.

(CONNECT_DATA=

(SERVICE_NAME = acct.us.yourcompany.com)

(FAILOVER_MODE =

(BACKUP=server2)

(TYPE=select)
(METHOD=preconnect)

(RETRIES=20)

(DELAY=3)

)

The FAILOVER_MODE parameter can only be used to enable extended connection failover or select failover. When multiple servers are specified by the ADDRESS_LIST parameter, connection failover is automatically enabled. If FAILOVER=off, connection failover is disabled. You also can explicitly specify connection failover using the FAILOVER parameter.

LoadBalancing = {true | false}

LOAD_BALANCE = {on | off}

If LOAD_BALANCE=on, enables client load balancing. For example:

(ADDRESS_LIST=

(LOAD_BALANCE=on)

(ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

(ADDRESS= (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))

(ADDRESS= (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))

)

If the LOAD_BALANCE parameter is unspecified or LOAD_BALANCE=off, client load balancing is disabled.

PortNumber = port

PORT = port

The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The PORT parameter is used within the ADDRESS parameter to specify the port number for each server entry. For example:

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

...

)

A port of 1521, the default port number when installing an Oracle database, is specified for server1.

EncryptionMethod=
{noEncryption | SSL}

PROTOCOL={TCP | TCPS}

The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The PROTOCOL parameter is used within the ADDRESS parameter to specify the network protocol to be used. It also is used to specify whether data is encrypted and decrypted when transmitted over the network between the driver and the server.

For example, the following entry specifies that the TCP/IP protocol will be used with no encryption:

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

...

)

A port of 1521, the default port number when installing an Oracle database, is specified for server1.

The following entry specifies that the TCP/IP protocol will be used with SSL encryption:

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL = TCPS)(HOST = server1)(PORT = 2484))

...

)

A port of 2484, the port number recommended by Oracle for SSL, is specified for server1.

NOTE: Truststore information must still be specified using either the TrustStore and TrustStorePassword properties or Java system properties. Optionally, you can specify the ValidateServerCertificate and HostNameInCertificate properties.

ServerName = server_name

HOST = server_name

The ADDRESS_LIST parameter contains connection information for one or multiple servers, using the ADDRESS parameter to specify the primary and alternate servers. The HOST parameter is used within the ADDRESS parameter to specify the server name for each server entry. The server entry can be an IP address or a server name. For example:

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

...

)

The server name server1 is specified in the first server entry.

ServerType = {shared | dedicated}

SERVER = {shared | dedicated}.

If SERVER=shared is specified in the CONNECT_DATA parameter in the tnsnames.ora file, the server process (UNIX) or thread (Windows) to be used is retrieved from a pool. For example:

(CONNECT_DATA=

(SERVER=shared)

)

When SERVER=shared, this setting allows there to be fewer processes than the number of connections, reducing the need for server resources.

When SERVER=dedicated, a server process is created to service only that connection. When that connection ends, so does the process (UNIX) or thread (Windows).

ServiceName = service_name

SERVICE_NAME = service_name

The database service name that specifies the database used for the connection. The service name is a string that is the global database name—a name that typically comprises the database name and domain name. For example:

sales.us.acme.com

The service name is specified in the CONNECT_DATA parameter. For example:

(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)

)

This parameter is mutually exclusive with the SID attribute and is useful to specify connections to an Oracle Real Application Clusters (RAC) system rather than a specific Oracle instance.

SID = SID

The Oracle System Identifier (SID) that refers to the instance of the Oracle database running on the server. The default Oracle SID that is configured when installing your Oracle database software is ORCL. The SID is specified in the CONNECT_DATA parameter. For example:

(CONNECT_DATA=
(SID=ORCL)

)

This parameter is mutually exclusive with the SERVICE_NAME attribute.

For more information about configuring tnsnames.ora files, refer to your Oracle documentation.

An Oracle instance can run in restricted mode, which allows access only to users who have the RESTRICTED SESSION system privilege. To connect to an instance running in restricted mode, add the UR = A clause to the CONNECT_DATA parameter of the tnsnames.ora file as shown.