Linux oracle tns admin

6 Local Naming Parameters in the tnsnames.ora File

This chapter provides a complete listing of the tnsnames.ora file configuration parameters. This chapter contains the following topics:

Overview of Local Naming Parameters

The tnsnames.ora file is a configuration file that contains network 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 file. 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 checks 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 its ADDRESS_LIST . The second protocol address list features no client load loading balancing, but does have connect-time failover, affecting only those protocol addresses within its 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.

Oracle Net Services supports the IFILE parameter in the tnsnames.ora file, with up to three levels of nesting. The parameter is added manually to the file. The following is an example of the syntax:

Refer to Oracle Database Reference for additional information.

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 the 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:

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 fail over 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 Oracle Connection Manager, using the Oracle Connection Manager protocol address that uses port 1630 indicated for the ADDRESS_LIST .

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.

When using more than one DESCRIPTION parameter, put the parameters 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

To allow the caller to detect a terminated remote server, typically it takes 2 hours or more to notice.

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. 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.

Although the preceding example has multiple addresses, the ADDRESS_LIST parameter was not used. This is because the ADDRESS_LIST parameter is not mandatory.

FAILOVER

To enable or disable connect-time failover for multiple protocol addresses.

When you set the parameter to on , yes , or true , Oracle Net fails over at connect time 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 the DESCRIPTION_LIST , DESCRIPTION , and ADDRESS_LIST parameters

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 goes through 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 protocol addresses sequentially until one succeeds.

Put this parameter under 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.

Setting this parameter in the connect descriptor for a client overrides the RECV_BUF_SIZE parameter at the client-side sqlnet.ora file.

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. The default for the Linux 2.6 operating system is 110592 (108 KB) bytes.

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.

Setting this parameter in the connect descriptor for a client overrides the DEFAULT_SDU_SIZE parameter at client-side sqlnet.ora file.

8192 bytes (8 KB)

512 to 2097152 bytes.

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.

Put this parameter under the DESCRIPTION parameter or at the end of the protocol address.

Setting this parameter in the connect descriptor for a client overrides the SEND_BUF_SIZE parameter at the client-side sqlnet.ora file.

Additional protocols might support this parameter on certain operating systems. Refer to the operating system-specific documentation for information about additional protocols.

The default value for this parameter is operating system specific. The default for the Linux 2.6 operating system is 110592 (108 KB) bytes.

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 this parameter 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.

Источник

TNS_ADMIN, How and Why

TNS_ADMIN in Oracle Software Registry

For those Windows platform which are running Oracle server or client(s), you should know some facts of Oracle important parameters, ORACLE_HOME , ORACLE_BASE and TNS_ADMIN are not set as environment variables. That is, you can find them nowhere in Environment Variables editor. In fact, they are registry parameters. You can call regedit to open a Registry Editor and then seek them in the following path:

For example, we have installed Oracle 18c client (not instant client), so we can check registry like below:

Oracle Software Registry — ORACLE_HOME

Basics of TNS_ADMIN

What is TNS_ADMIN?

TNS_ADMIN is a key parameter or environment variable that guides Oracle clients and tools to find all network configurations, such as tnsnames.ora and sqlnet.ora .

If you didn’t see TNS_ADMIN in your registry or environment variable editor, it might be normal as I explained below.

Why TNS_ADMIN Environment Variable is Not Defined?

Usually, the value of TNS_ADMIN can be derived from ORACLE_HOME like this:

Therefore, it’s pretty normal if you found that TNS_ADMIN is not defined as a registry parameter, neither an environment variable. So don’t worry about the derived TNS_ADMIN if you have set ORACLE_HOME correctly. That’s why TNS_ADMIN environment variable is not defined.

At times, you may want to explicitly set TNS_ADMIN instead of the derived one for some reason. You can do it by the following ways.

How to Define TNS_ADMIN

You can set TNS_ADMIN either as:

1. Set TNS_ADMIN as a Registry Parameter

In some cases, we may need to create an entry for TNS_ADMIN to overwrite the derived location. For example, reusing the older version’s network files which are fully and well configured. It’s a very useful practice to collaborate multiple versions of Oracle clients together in one Windows.

First of all, right click on the key folder and new a string value.

Oracle Software Registry — New a String Value

We created the new string value, but the value is empty so far.

Oracle Software Registry — TNS_ADMIN Created

Obviously, the value of the string has not been set yet, you have to right click on this entry to modify it.

Oracle Software Registry — Modify TNS_ADMIN

Since we’d like to reuse the old tnsnames.ora (e.g. 12.2.0) instead of the new one (18.0.0 in this case), we set it to the old network admin path.

Oracle Software Registry — Input a New Value for TNS_ADMIN

As you can see, its value is a little different from other key-value pairs. This is what we want.

Oracle Software Registry — TNS_ADMIN Modified

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 — Production on 24-MAR-2
018 18:55:52

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

Used parameter files:
C:\app\client\ed\product\ 12.2.0 \client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 10.10.10.10)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)

As you can see, we cooperate both releases (18.0.0 and 12.2.0) to tnsping a remote database. In which, tnpsing of release 18.0.0 used the old version’s network file to accomplish the test.

2. Set TNS_ADMIN as an Environment Variable

TNS_ADMIN is not defined as environment variable if there already have registry parameter been used for user’s environment.

In contrast, for those who use Oracle instant clients, there’re no registry parameters. This is because Oracle instant clients are out-of-box package, they can be used right after unzipped. So they have no way to set registry parameters.

Please note that, Oracle client and Oracle instant client are different, the former is an install-based and full-fledged software, the later is a portable and partial-functioned package.

In addition, tools like TOAD, SQL Developer or PL/SQL Developer won’t define TNS_ADMIN either. In such cases, we need to create a new environment variable for TNS_ADMIN to support all tools or utilities globally.

Right click on Computer -> Properties.

Windows 7 — Advanced system settings

In Advanced tab, we click on Environment Variables.

Windows 7 — Environment Variables

Let’s new a user variable.

Windows 7 — Environment Variables — New

After finished, click on OK.

Windows 7 — Add a New Environment Variable TNS_ADMIN

A new environment variable TNS_ADMIN is working now.

Windows 7 — TNS_ADMIN Environment Variable Entry

Let’s echo the environment variable for sure.

C:\Users\ed>echo %TNS_ADMIN%
D:\Oracle\instant_112\network\admin

It points to the network admin folder for Oracle instance client 11.2.

What About %PATH% ?

Normally, Oracle clients will add their binary path strings to the environment variable PATH automatically during installation. But if you’re using instant client, you have to add Oracle binary path string to PATH by yourself.

Find out PATH and click on Edit .

Set Environment Variable — Edit PATH

Input the binary path of Oracle software.

Set Environment Variable — Input Value for PATH

Set Environment Variable — PATH Result

Consequently, we can find out the location of utilities:

C:\Users\ed>where sqlplus
D:\Oracle\instant_112\sqlplus.exe

In the above test, we have learned the path of Oracle binary utilities. It’s from Oracle instant client 11.2.

More information about Oracle registry parameters, such as ORA_SID_AUTOSTART , ORA_SID_PFILE , SQLPATH , etc. Please follow the official documentation for 12c database on Windows:
Configuration Parameters and the Registry

Источник