Oracle linux tnsnames ora

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.

Источник

Simon Krenger

Oracle InstantClient: TNSNAMES.ORA location

When you install the InstantClient binaries, you often want to use your existing TNSNAMES.ORA file containing all your databases. So how do you specify the location of your TNSNAMES.ORA file?

The Oracle website has the answer:

Always set the TNS_ADMIN environment variable or registry to the location of the tnsnames.ora file (full directory path only, do not include the file name). This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.

So, for UNIX and Linux systems set the variable TNS_ADMIN like so in your .profile or .bash_profile :

On Windows systems, set the environment variables via the Advanced System properties:

• Open CMD, enter sysdm.cpl
• In the Advanced tab, select Environment Variables
• Under “System Variables”, click on New… and enter “TNS_ADMIN” as the name and the path where your TNSNAMES.ORA resides as your value.

Now, you can use your TNS names for your InstantClient, for example for SQL*Plus:

For more information on SQL*Plus configuration, please refer to the Oracle documentation for SQL*Plus.

Hello world

My name is Simon Krenger, I am a Technical Account Manager (TAM) at Red Hat. I advise our customers in using Kubernetes, Containers, Linux and Open Source.

Источник

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.

Источник