Understanding SQL*Net

Identifying Destinations: TNSNAMES.ORA

The TNSNAMES.ORA file is used by clients and distributed database servers to identify potential destinations, both servers and, optionally, Interchanges. (If Oracle Names is used in the network, the TNSNAMES.ORA files are not necessary; the Names Servers get the needed information from the network definition stored on a database. Similarly, if an Oracle Native Naming Adapter such as NIS or DCE's CDS is used in the network, then service names can be resolved by one or more native naming services.) Unless you are using Oracle Names or an Oracle Native Naming Adapter, Network Manager generates the TNSNAMES.ORA file. Each entry in the TNSNAMES.ORA file includes two elements:

a service name

a connect descriptor

These elements are described in the following sections.

Service Names

All connect descriptors are assigned service names in the TNSNAMES.ORA file. The user specifies the service name, a single word rather than the lengthier connect descriptor, to identify the service to which the user wants to connect. (These are comparable to the aliases used for connect_strings in SQL*Net version 1.) The contents of a TNSNAMES.ORA file consists of a series of service names mapped to TNS connect descriptors.

The service name for a database must be exactly the same as the global database name defined by the system administrator. SQL*Net limits the total length of a global database name to 64 characters. Of these, up to eight are the DB_NAME as defined by the database administrator, and the remainder show the service's place in the domain hierarchy (DB_DOMAIN). The name part of the service name can be longer than eight characters only if the DBA changes the name of the database with a RENAME GLOBAL_NAME parameter. The total global database name, or service name, must remain at or below 64 characters. See "Global Naming Issues" in Chapter 2 of Oracle7 Server Distributed Systems, Volume I for more information on creating a global database name.

Alternate service names, or aliases, may be assigned to a database service through the TNSNAMES.ORA file. The alternate service names may be any convenient, easy to remember names you choose. For example, if a database were used by two different divisions of a company, Human Resources and Finance, you might want to map two different service name aliases, HR and FINANCE, to the database. The TNSNAMES.ORA file would then have three separate entries: a service name that is the same as the global database name, and two aliases, mapped to the same connect descriptor.

The service name for an Interchange is the name of the Interchange or its Connection Manager component. Typically, the Interchange and the Connection Manager are referred to by the same name.

Connect Descriptors

Every service requires a connect descriptor. For a database, a connect descriptor describes the location of the network listener and the system identifier (SID) of the database to which to connect. Database connect descriptors commonly consist of two sections:

the listener ADDRESS

the database SID passed as application CONNECT_DATA

ADDRESS Section

The application address is the information required to reach the application within a given protocol environment. It includes the community in which the destination resides, the protocol it uses, and protocol-specific parameters. Oracle Network Manager automatically provides the correct protocol specific parameters for any protocol you use, but you must provide the appropriate values. For information about the values for the parameters for a given protocol, see the Oracle operating system-specific documentation for your platform.

Note: If you specify a TCP/IP address prefixed with a "0", it is assumed to be an octal number, not a decimal number. For example, 39.223.72.44 is a decimal number, but 039.223.72.44 is an octal number.

CONNECT_DATA Section

SQL*Net uses the CONNECT_DATA keyword to denote the system identifier (SID) of the remote database. When SQL*Net on the server side receives the connection request, TNS passes the CONNECT_DATA contents to the listener, which identifies the desired database. For SQL*Net use, sample CONNECT_DATA might look like:

(CONNECT_DATA= 
	(SID=V7PROD)
)

CONNECT_DATA is a protocol independent keyword indicating that application-specific data will be supplied at connect time, and SID specifies the Oracle System ID of the database server. You must specify the SID in the CONNECT DATA section of the connect descriptor.

With this release of SQL*Net, the CONNECT_DATA section must also include the global database name of the database. In most instances the global database name is the same as the service name. However, if you have a replicated database, you may have assigned a single service name that maps to more than one database name.

Interchange Addresses

A connect descriptor for an Interchange consists of only one section, an ADDRESS_LIST section. Within the ADDRESS_LIST section all the addresses of the Interchange are listed, including the required protocol specific keywords.

There is no CONNECT_DATA section in the connect descriptor of an Interchange.

Example of the Use of TNSNAMES.ORA

Figure A - 2 shows a simple network in which client applications access a database on the server NY_VAX. In this example, the SID of the database is DB1. Its service name and connect descriptor are found in the TNSNAMES.ORA entry that follows.

Figure A - 2. SQL*Net Connect Descriptor

The following TNSNAMES.ORA entry maps the service name NY_FIN to the connect descriptor:

NY_FIN.FIN.HQ.ACME = (DESCRIPTION=
					(ADDRESS=
					  (COMMUNITY=DECCOM.FIN.HQ.ACME)
					  (PROTOCOL=DECNET)
					  (NODE=NY_VAX.FIN.HQ.ACME)
					  (OBJECT=LSNR)
					)
					(CONNECT_DATA=
					  (SID=DB1)
					  (GLOBAL_NAME=NY_FIN.FIN.HQ.ACME)
					)
				   )

A user who wished to access the database would use NY_FIN to identify the appropriate connect descriptor. For example:

SQLPLUS SCOTT/TIGER@NY_FIN

This example assumes that NY_FIN is in the user's default domain. If it is not, the whole service name (NY_FIN.FIN.HQ.ACME) would need to be used.

Updating connect descriptors

Whenever you add a new database to the network, you must add a new service name and connect descriptor to the TNSNAMES.ORA file. Use Oracle Network Manager to update TNSNAMES.ORA.

System and User TNSNAMES.ORA Files

On most platforms, there can be two versions of the TNSNAMES.ORA file, one at the system level (all users) and an optional private one at the user level. If a private TNSNAMES.ORA file exists, its contents take precedence over the system-level file. That is, if both files have the same service name mapped to different connect descriptors, the connect descriptor in the user's local file will be used.

A local TNSNAMES.ORA file does not replace the system file, but exists in addition to it. For example, if a developer creates a database which is not generally accessible to other users, he might want to create a private TNSNAMES.ORA file with a simple service name mapped to its connect descriptor. By creating his own TNSNAMES.ORA file, the developer can have the convenience of using a service name without having to go through the system administrator.

Note: For more specific information about the name, location, and use of the TNSNAMES.ORA file on your operating system, refer to your Oracle operating system-specific manual.

Configuring Listener Load Balancing

To configure listener load balancing, the administrator must configure multiple listeners for each database. There can be multiple listeners on the same platform as the database, or, for multi-threaded servers, the listeners can be on different nodes. For multi-threaded servers, the administrator must add some parameters to the database parameter file. (On UNIX systems, this is INIT.ORA.)

Note: The administrator does not need to add or change any parameters in the database parameter file for dedicated servers.

Database Parameter File

For multiple listeners to be enabled for multi-threaded servers, the database initialization parameter file must include the following parameter:

MTS_MULTIPLE_LISTENERS=TRUE

Note: Unless MTS_MULTIPLE_LISTENERS=TRUE is included, listener load balancing is not enabled.

If there is a single listener for each multi-threaded server, each address of the single listener is listed on a separate line, as follows:

MTS_LISTENER_ADDRESS= address

MTS_LISTENER_ADDRESS= address

[MTS_LISTENER_ADDRESS= address]

...

If multiple listeners are enabled, each listener must be on a separate line. If a listener has multiple addresses, each one is listed within an address list, as follows:

MTS_LISTENER_ADDRESS=(ADDRESS_LIST=(ADDRESS=(address)
		(ADDRESS=(address)))

MTS_LISTENER_ADDRESS=(ADDRESS_LIST=(ADDRESS=(address)
		(ADDRESS=(address)))

Note: If you decide to change back so that each dispatcher registers with only one listener, you must remove the ADDRESS_LIST keyword and place alternative addresses for the same listener on separate lines:

MTS_LISTENER_ADDRESS=address MTS_LISTENER_ADDRESS=address

TNSNAMES.ORA

For clients to be able to randomize connections between listeners, a new address format is needed in TNSNAMES.ORA, as follows:

service_name = 
  (DESCRIPTION_LIST=
    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=address)
                  (ADDRESS=address))
                   (CONNECT_DATA=(SID=sid)
                       (GLOBAL_NAME=global_dbname)))
    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=address)
                   	(ADDRESS=address))
			      (CONNECT_DATA=(SID=sid)
					(GLOBAL_NAME=global_dbname)))
    [(CONNECT_DATA =(SID=sid)(GLOBAL_NAME=global_dbname))])

In this syntax, each description in the description list is for a different listener. If the connect data is the same for all the descriptions in a description list, it need be entered only once, in the last line.

The global name is a concatenation of the db_name and the db_domain from the INIT.ORA file.

Note: The old format for TNSNAMES.ORA entries continues to work only for those databases that have only one listener.

TNSNAMES.ORA Example for a Single Database Instance with Two Listeners

emp=(DESCRIPTION_LIST=
     (DESCRIPTION=(ADDRESS_LIST=
       (ADDRESS=(COMMUNITY=DECCOM.WORLD)(PROTOCOL=DECNET)
                 (NODE=NY_VAX)(OBJECT=LSNR))
       (ADDRESS=(COMMUNITY=TCPCOM.WORLD)(PROTOCOL=TCP)
                 (HOST=NY_VAX)(PORT=1526))))
    (DESCRIPTION=(ADDRESS_LIST=
       (ADDRESS=(COMMUNITY=DECCOM.WORLD)(PROTOCOL=DECNET)
                (NODE=NY_SEQ)(OBJECT=LSNR))
       (ADDRESS=(COMMUNITY=TCPCOM.WORLD)(PROTOCOL=TCP)
                (HOST=NY_SEQ)(PORT=1526))))
   (CONNECT_DATA=(SID=db1)(GLOBAL_NAME=emp.world)))

In this example, there are two listeners for the database, on two different nodes, each listening on two different protocols. This example matches the example illustrated in Figure 2 - 4.

Many-to-Many Relationships

If equivalent databases use listener load balancing, a single service name may have descriptions with different SIDs and global database names. There may be several listeners, each listening for several database instances. In this case, there is Connect Data in every description, and no final Connect Data without an associated address.

If there are replicated databases that are equivalent, create a service name that maps to more than one SID and global database name. For example, the following portion of a TNSNAMES.ORA file is for a replicated server with two listeners. The example matches that shown in Figure 2 - 5.

TNSNAMES.ORA Example for Equivalent Databases

twinemp=(DESCRIPTION_LIST=
          (DESCRIPTION=(ADDRESS_LIST=
             (ADDRESS=(COMMUNITY=DECCOM.WORLD)
                      (PROTOCOL=DECNET)
  				   (NODE=NY_VAX)(OBJECT=LSNR))
             (ADDRESS=(COMMUNITY=TCPCOM.WORLD)
                      (PROTOCOL=TCP)
                      (HOST=NY_VAX)(PORT=1526)))
          (CONNECT_DATA=(SID=db1)(GLOBAL_NAME=emp.world)))
          (DESCRIPTION=(ADDRESS_LIST=
             (ADDRESS=(COMMUNITY=DECCOM.WORLD)
                      (PROTOCOL=DECNET)(NODE=NY_SEQ)
                      (OBJECT=LSNR))
             (ADDRESS=(COMMUNITY=TCPCOM.WORLD)
                      (PROTOCOL=TCP)(HOST=NY_SEQ)
                      (PORT=1526)))
          (CONNECT_DATA=(SID=db2)(GLOBAL_NAME=hr.world))))