- 6 Local Naming Parameters (tnsnames.ora)
- Overview of Local Naming Parameters
- General Syntax of tnsnames.ora
- Multiple Descriptions in tnsnames.ora
- Multiple Address Lists in tnsnames.ora
- Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
- Connect Descriptor Descriptions
- DESCRIPTION
- DESCRIPTION_LIST
- Protocol Address Section
- ADDRESS
- ADDRESS_LIST
- Optional Parameters for Address Lists
- ENABLE
- FAILOVER
- LOAD_BALANCE
- RECV_BUF_SIZE
- SEND_BUF_SIZE
- SOURCE_ROUTE
- Oracle Windows tnsnames.ora location
6 Local Naming Parameters (tnsnames.ora)
This chapter provides a complete listing of the tnsnames.ora file configuration parameters.
This chapter contains the following topics:
Overview of Local Naming Parameters
This tnsnames.ora file is a configuration file that contains net service name s mapped to connect descriptor s for the local naming method, or net service names mapped to listener protocol addresses.
A net service name is an alias mapped to a database network address contained in a connect descriptor. A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect. Clients and database servers (that are clients of other database servers) use the net service name when making a connection with an application.
By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net will check the other directories for the configuration files. For example, the order checking the tnsnames.ora file is as follows:
The directory specified by the TNS_ADMIN environment variable. If the file is not found in the directory specified, then it is assumed that the file does not exist.
If the TNS_ADMIN environment variable is not set, then Oracle Net will check the ORACLE_HOME/network/admin directory.
On Microsoft Windows, the TNS_ADMIN environment variable is used if it is set in the environment of the process. If the TNS_ADMIN environment variable is not defined in the environment, or the process is a service which does not have an environment, then Microsoft Windows scans the registry for a TNS_ADMIN parameter.
Oracle operating system-specific documentation
General Syntax of tnsnames.ora
The basic syntax for a tnsnames.ora file is shown in Example 6-1.
Example 6-1 Basic Format of tnsnames.ora File
In the preceding example, DESCRIPTION contains the connect descriptor, ADDRESS contains the protocol address, and CONNECT_DATA contains the database service identification information.
Multiple Descriptions in tnsnames.ora
A tnsnames.ora file can contain net service names with one or more connect descriptors. Each connect descriptor can contain one or more protocol addresses. Example 6-2 shows two connect descriptors with multiple addresses. DESCRIPTION_LIST defines a list of connect descriptors.
Example 6-2 Net Service Name with Multiple Connect Descriptors in tnsnames.ora
Oracle Net Manager does not support the creation of multiple connect descriptors for a net service name when using Oracle Connection Manager.
Multiple Address Lists in tnsnames.ora
The tnsnames.ora file also supports connect descriptors with multiple lists of addresses, each with its own characteristics. In Example 6-3, two address lists are presented. The first address list features client load balancing and no connect-time failover , affecting only those protocol addresses within the ADDRESS_LIST . The second protocol address list features connect-time failover and no client load loading balancing, affecting only those protocol addresses within the ADDRESS_LIST . The client first tries the first or second protocol address at random, then tries protocol addresses three and four sequentially.
Example 6-3 Multiple Address Lists in tnsnames.ora
Oracle Net Manager supports only the creation of one protocol address list for a connect descriptor.
Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
When a connect descriptor in a tnsnames.ora file contains at least two protocol addresses for Oracle Connection Manager , parameters for connect-time failover and load balancing can be included in the file.
Example 6-4 illustrates failover of multiple Oracle Connection Manager protocol addresses.
Example 6-4 Multiple Oracle Connection Manager Addresses in tnsnames.ora
In Example 6-4, the syntax does the following:
The client is instructed to connect to an protocol address of the first Oracle Connection Manager, as indicated by:
The first Oracle Connection Manager is instructed to connect to the first protocol address of another Oracle Connection Manager. If the first protocol address fails, then it tries the second protocol address. This sequence is specified with the following configuration:
The Oracle Connection Manager connects to the database service using the following protocol address:
Example 6-5 illustrates client load balancing among two Oracle Connection Managers and two protocol addresses:
Example 6-5 Client Load Balancing in tnsnames.ora
In Example 6-5, the syntax does the following:
The client is instructed to pick an ADDRESS_LIST at random and to failover to the other if the chosen ADDRESS_LIST fails. This is indicated by the LOAD_BALANCE and FAILOVER parameters being set to on .
When an ADDRESS_LIST is chosen, the client first connects to the Oracle Connection Manager, using the Oracle Connection Manager protocol address that uses port 1630 indicated for the ADDRESS_LIST .
The Oracle Connection Manager then connects to the database service, using the protocol address indicated for the ADDRESS_LIST .
Connect Descriptor Descriptions
Each connect descriptor is contained within the DESCRIPTION parameter. Multiple connect descriptors are characterized by the DESCRIPTION_LIST parameter. These parameters are described in this section.
DESCRIPTION
To specify a container for a connect descriptor. Put this parameter under the DESCRIPTION_LIST parameter.
DESCRIPTION_LIST
To define a list of connect descriptors for a particular net service name.
Protocol Address Section
The protocol address section of the tnsnames.ora file specifies the protocol addresses of the listener. If there is only one listener protocol address, then use the ADDRESS parameter. If there is more than one address, then use the ADDRESS_LIST parameter.
ADDRESS
To define a single listener protocol address. Put this parameter under either the ADDRESS_LIST parameter or the DESCRIPTION parameter.
Chapter 4, «Protocol Address Configuration» for descriptions of the correct parameters to use for each protocol
ADDRESS_LIST
To define a list of protocol addresses. If there is only one listener protocol address, then ADDRESS_LIST is not necessary. Put this parameter under either the DESCRIPTION parameter or the DESCRIPTION_LIST parameter.
Optional Parameters for Address Lists
For multiple addresses, the following parameters are available:
ENABLE
The keepalive feature on the supported TCP transports can be enabled for a net service client by putting (ENABLE=broken) under the DESCRIPTION parameter in the connect string. The keepalive feature allows the caller to detect a terminated remote server, although typically it takes 2 hours or more to notice. On the client side, the default for tcp_keepalive is off . Operating system TCP configurables, which vary by platform, define the actual keepalive timing details.
FAILOVER
To enable or disable connect-time failover for multiple protocol addresses.
When you set the parameter to on , yes , or true , Oracle Net, at connect time, fails over to a different address if the first protocol address fails. When you set the parameter to off , no , or false , Oracle Net tries one protocol address.
Put this parameter under the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.
Do not set the GLOBAL_DBNAME parameter in the SID_LIST_ listener_name section of the listener.ora . A statically configured global database name disables connect-time failover.
on for DESCRIPTION_LIST , DESCRIPTION , and ADDRESS_LIST .
LOAD_BALANCE
To enable or disable client load balancing for multiple protocol addresses.
When you set the parameter to on , yes , or true , Oracle Net progresses the list of addresses in a random sequence, balancing the load on the various listener or Oracle Connection Manager protocol addresses. When you set the parameter to off , no , or false , Oracle Net tries the first address in the address list. If the connection fails and the failover parameter is enabled, then Oracle Net tries the addresses sequentially until one succeeds.
Put this parameter under either the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.
on for DESCRIPTION_LIST
RECV_BUF_SIZE
To specify, in bytes, the buffer space for receive operations of sessions. This parameter is supported by the TCP/IP, TCP/IP with SSL, and SDP protocols.
Put this parameter under the DESCRIPTION parameter or at the end of the protocol address.
Additional protocols might support this parameter on certain operating systems. Refer to the operating system-specific documentation for additional information about additional protocols.
The default value for this parameter is specific to the operating system.
Setting this parameter in the connect descriptor for a client overrides the RECV_BUF_SIZE parameter at the client-side sqlnet.ora file.
Oracle Database Net Services Administrator’s Guide for additional information about configuring this parameter
To instruct Oracle Net to optimize the transfer rate of data packets being sent across the network with a specified session data unit (SDU) size.
Put this parameter under the DESCRIPTION parameter.
8192 bytes (8 KB).
512 to 65535 bytes.
Setting this parameter in the connect descriptor for a client overrides the DEFAULT_SDU_SIZE parameter at client-side sqlnet.ora file.
Oracle Database Net Services Administrator’s Guide for complete SDU usage and configuration information
SEND_BUF_SIZE
To specify, in bytes, the buffer space for send operations of sessions. This parameter is supported by the TCP/IP, TCP/IP with SSL, and SDP protocols.
Additional protocols might support this parameter on certain operating systems. Refer to the operating system-specific documentation for additional information about additional protocols.
Put this parameter under the DESCRIPTION parameter or at the end of the protocol address.
The default value for this parameter is operating system-specific.
Setting this parameter in the connect descriptor for a client overrides the SEND_BUF_SIZE parameter at the client-side sqlnet.ora file.
Oracle Database Net Services Administrator’s Guide for additional information about configuring this parameter
SOURCE_ROUTE
To enable routing through multiple protocol addresses.
When you set to on or yes , Oracle Net uses each address in order until the destination is reached.
To use Oracle Connection Manager, an initial connection from the client to Oracle Connection Manager is required, and a second connection from Oracle Connection Manager to the listener is required.
Put this parameter under either the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.
Oracle Windows tnsnames.ora location
Oracle Database Tips by Donald BurlesonDecember 26, 2015
Question: I am having trouble locating my tnsnames.ora file on my Windows server. tnsnames.ora file exist in the Linux machine under
But in my windows machine there is no tnsnames.ora under \oracle\product\10.2.0\client_1\network\admin
How can I locate my tnsnames.ora file, or create a tnsnames.ora file?
Answer: Remember, there is only one tnsnames.ora per server, no matter how many instances reside on it!
When finding the tnsnames.ora location in Windows, look for the TNS_ADMIN registry entry. If TNS_ADMIN is set then you should be able to use a network alias, not the full «(DESCRIPTION. » connect string. The TNS_ADMIN parameter tells Oracle clients where to find the tnsnames.ora file.
Also, make sure that TNS_ADMIN is set in your DOS $PATH variable :
C:> set TNS_ADMIN=$PATH;ORACLE_HOME\network\admin
You looked for your tnsnames.ora file at /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora.
But, the db_1 is the $ORACLE_SID!
Traditionally, the tnsnames.ora is in $ORACLE_HOME/network/admin with a soft link to the ORACLE_SID location.
In windows, look for the tns_admin registry entry. If tns_admin is set then you should be able to use a network alias, not the full «(DESCRIPTION. » connect string. The TNS_ADMIN parm tells Oracle clients where to find the tnsnames.ora file.
Also, make sure that tns_admin is set in your DOS $PATH variable:
C:> set TNS_ADMIN=$PATH;ORACLE_HOME\network\admin
According to the docs, the precedence in which Oracle Net
Configuration files are resolved is:
- Oracle Net files in present working directory (PWD/CWD)
- TNS_ADMIN set for each session session or by a user-defined script
- TNS_ADMIN set as a global environment variable
- TNS_ADMIN as defined in the registry
- Oracle Net files in %ORACLE_HOME/network/admin
(Oracle default location)
For more details, see this related thread on managing the tnsnames.ora file on Windows, where the Oracle documentation notes how to set tns_admin in the Windows registry:
«Access the Windows registry i.e. click: Start > Run, then enter ‘Regedit.exe’ (or ‘Regedt32.exe’).
Add the TNS_ADMIN environment variable to the HKEY_LOCAL_MACHINE\Software\Oracle[\Home[X]] key by right mouse clicking, then selecting: New > Key > String Value.
Note: [X] is the Home Id for the ORACLE_HOME being modified.
If you are unsure where to set TNS_ADMIN in the registry, refer to the ORACLE_HOME\bin\oracle.key file which provides the location of the registry key used by executables within that home.»
It also notes a procedure for setting tns_admin globally in everyone’s path:.
«TNS_ADMIN may also be defined as a system-wide or global variable
within the operating system. To do so:
1. Logon as Administrator.
2. Click Start > Settings > Control Panel
3. Double click the SYSTEM icon
4. From System Properties, select Environment tab.
The Environment tab lists System environment variables defined by
Windows NT — these are the same no matter which user is logged on
to the computer. When logged as as a member of the Administrator
group, you can modify, add or delete values.
5. Highlight OS in the list of Systems Variables
In the Variable field you should see: OS and in the Value field,
the value: Windows_NT
6. Change the Variable from OS to TNS_ADMIN.
7. Change the Value from Windows_NT to the complete path were the
configuration files will reside.
For example: c:\orant\net80\admin
8. Click Set, then apply.
In the System Variable table you should now see the TNS_ADMIN
variable with the path to the location of Oracle Net configuration
files.
9. Click OK to close window.»
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .
Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:
and include the URL for the page.
