6 Local Naming Parameters in the tnsnames.ora File

This chapter describes the tnsnames.ora file configuration parameters.

• Overview of Local Naming Parameters
  This section provides an overview of Oracle Net service names local naming parameters.
• General Syntax of tnsnames.ora
  This section explains the general tnsnames.ora file syntax.
• Using Multiple Descriptions in tnsnames.ora Files
  Learn about tnsnames.ora file connect descriptors.
• Multiple Address Lists in tnsnames.ora Files
  Learn how to configure multiple address lists in tnsnames.ora files.
• Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
  When tnsnames.ora file connect descriptors have at least two protocol addresses for Oracle Connection Manager, you can also include parameters for connect-time failover and load balancing in the file.
• Connect Descriptor Descriptions
  Specify connect descriptors using the DESCRIPTION parameter. Identify multiple connect descriptors with the DESCRIPTION_LIST parameter.
• Protocol Addresses
  Learn about Oracle Net Services protocol address parameters.
• Optional Parameters for Address Lists
  For multiple addresses, you can use the optional parameters to configure address lists.
• Connection Data Section
  Learn how to configure network connections with protocol addresses.
• Security Section
  The security section of the tnsnames.ora file specifies these security-related parameters for use with Oracle security features.
• Timeout Parameters
  The timeout section of the tnsnames.ora file provides the ability to specify timeout and retry configuration through the TNS connect string.
• Compression Parameters
  The compression section of the tnsnames.ora file provides the ability to enable compression and specify compression levels. These parameters can be set at the DESCRIPTION level of a connect string.

6.1 Overview of Local Naming Parameters

This section provides an overview of Oracle Net service names local naming parameters.

The tnsnames.ora file is a configuration file that contains network service names that are mapped to connect descriptors for the local naming method or net service names that are mapped to listener protocol addresses.

A net service name is an alias that is mapped to a database network address that is contained in a connect descriptor. A connect descriptor contains the location of the listener that is accessed 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 connecting with applications.

By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net checks the other directories for the configuration file. For example, the order of 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 you do not set the TNS_ADMIN environment variable, then Oracle Net first checks the ORACLE_BASE_HOME/network/admin directory.
• If the file is not found in the ORACLE_BASE_HOME/network/admin directory, then Oracle Net checks for the file in the ORACLE_HOME/network/admin directory.

Note:

• On Microsoft Windows, the TNS_ADMIN environment variable is used if it is set in the environment of the process. If you do not define the TNS_ADMIN environment variable in the environment, or if the process is a service that does not have an environment, then Microsoft Windows scans the registry for a TNS_ADMIN parameter.
• With Oracle Instant Client, the tnsnames.ora file is located in the subdirectory of the Oracle Instant Client software. For example, in the /opt/oracle/instantclient_release_number/network/admin directory.

6.2 General Syntax of tnsnames.ora

This section explains the general tnsnames.ora file syntax.

The basic syntax for a tnsnames.ora file is shown in Example 6-1.

Example 6-1 Basic Format of tnsnames.ora File

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS=(protocol_address_information))
   (CONNECT_DATA= 
     (SERVICE_NAME=service_name))) 

In the preceding example, DESCRIPTION contains the connect descriptor, ADDRESS contains the protocol address, and CONNECT_DATA contains database service identification information.

6.3 Using Multiple Descriptions in tnsnames.ora Files

Learn about tnsnames.ora file connect descriptors.

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. The following example shows two connect descriptors with multiple addresses. Use the tnsnames.ora parameter DESCRIPTION_LIST to define the list of connect descriptors.

Example 6-2 Net Service Name with Multiple Connect Descriptors in tnsnames.ora

net_service_name= 
 (DESCRIPTION_LIST=
  (DESCRIPTION= 

   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)))
  (DESCRIPTION= 

   (ADDRESS=(PROTOCOL=tcp)(HOST=hr1-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=hr2-svr)(PORT=1521))
   (CONNECT_DATA=
     (SERVICE_NAME=hr.us.example.com))))

Note:

Oracle Net Manager does not support multiple connect descriptors for a net service name if you use Oracle Connection Manager.

6.4 Multiple Address Lists in tnsnames.ora Files

Learn how to configure multiple address lists in tnsnames.ora files.

The tnsnames.ora file supports connect descriptors with multiple lists of addresses, each with its own characteristics. The following example shows two address lists. The first address list features client load balancing and no connect-time failover. These setting apply only to protocol addresses that are within its ADDRESS_LIST. The second protocol address list does not enable client load loading balancing, but the list does enable connect-time failover. These settings affect only protocol addresses that are included in its ADDRESS_LIST. The client first tries the first or second protocol address at random, then it tries protocol addresses number three and four, in that order, and so on.

Example 6-3 Multiple Address Lists in tnsnames.ora Files

net_service_name= 
 (DESCRIPTION= 
  (ADDRESS_LIST= 
   (LOAD_BALANCE=on)
   (FAILOVER=off)
   (ADDRESS=(protocol_address_information))
   (ADDRESS=(protocol_address_information)))
  (ADDRESS_LIST= 
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(protocol_address_information))
   (ADDRESS=(protocol_address_information)))
  (CONNECT_DATA=
   (SERVICE_NAME=service_name)))

Note:

• 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. You must add the parameter manually to the file. The following is an example of the syntax:

  IFILE=/tmp/listener_em.ora
  IFILE=/tmp/listener_cust1.ora
  IFILE=/tmp/listener_cust2.ora 
  

6.5 Connect-Time Failover and Client Load Balancing with Oracle Connection Managers

When tnsnames.ora file connect descriptors have at least two protocol addresses for Oracle Connection Manager, you can also include parameters for connect-time failover and load balancing in the file.

Example 6-4 Multiple Oracle Connection Manager Addresses in tnsnames.ora

This example illustrates the failover of multiple Oracle Connection Manager protocol addresses.

sample1=
 (DESCRIPTION= 
   (SOURCE_ROUTE=yes) 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))    # 1 
     (ADDRESS_LIST=  
       (FAILOVER=on) 
       (LOAD_BALANCE=off)                               #  2 
       (ADDRESS=(PROTOCOL=tcp)(HOST=host2a)(PORT=1630)) 
       (ADDRESS=(PROTOCOL=tcp)(HOST=host2b)(PORT=1630)))
     (ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=1521)))    #  3
   (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))

The previous syntax does the following:

1. The client connects to the protocol address of the first Oracle Connection Manager as indicated by:

   (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))

2. Oracle Connection Manager connects to the first protocol address of another Oracle Connection Manager. If the first protocol address fails, then it tries to connect to the second protocol address. This sequence is specified with the following configuration:

   (ADDRESS_LIST=  
     (FAILOVER=on) 
     (LOAD_BALANCE=off)
     (ADDRESS=(PROTOCOL=tcp)(HOST=host2a)(PORT=1630)) 
     (ADDRESS=(PROTOCOL=tcp)(HOST=host2b)(PORT=1630))) 
   

3. Oracle Connection Manager connects to the database service using the following protocol address:

   (ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=1521))

Example 6-5 Client Load Balancing in tnsnames.ora

This example illustrates client load balancing among two Oracle Connection Managers and two protocol addresses:

sample2=
 (DESCRIPTION= 
   (LOAD_BALANCE=on)                                    # 1
   (FAILOVER=on)
   (ADDRESS_LIST= 
     (SOURCE_ROUTE=yes) 
     (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))    # 2
     (ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521)))
  (ADDRESS_LIST= 
     (SOURCE_ROUTE=yes) 
     (ADDRESS=(PROTOCOL=tcp)(HOST=host3)(port=1630)) 
     (ADDRESS=(PROTOCOL=tcp)(HOST=host4)(port=1521)))
   (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))  # 3

The previous syntax does the following:

1. The client selects an ADDRESS_LIST at random and fails over to the other address if the chosen ADDRESS_LIST fails. This is indicated if you set the LOAD_BALANCE and FAILOVER parameters to on.

2. 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 as is indicated for the ADDRESS_LIST.

3. Oracle Connection Manager then connects to the database service using the protocol address that is indicated for the ADDRESS_LIST.

6.6 Connect Descriptor Descriptions

Specify connect descriptors using the DESCRIPTION parameter. Identify multiple connect descriptors with the DESCRIPTION_LIST parameter.

• DESCRIPTION_LIST
  The DESCRIPTION_LIST parameter of the tnsnames.ora file defines a list of connect descriptors for a particular net service name.
• DESCRIPTION
  Use the tnsnames.ora file DESCRIPTION parameter to specify connect descriptor containers.

6.6.1 DESCRIPTION_LIST

The DESCRIPTION_LIST parameter of the tnsnames.ora file defines a list of connect descriptors for a particular net service name.

Purpose

To define a list of connect descriptors for a particular net service name.

Example 6-6 Example

net_service_name= 
(DESCRIPTION_LIST=
 (DESCRIPTION=
  (ADDRESS=...)
  (CONNECT_DATA=(SERVICE_NAME=sales.example.com)))
 (DESCRIPTION=

6.6.2 DESCRIPTION

Use the tnsnames.ora file DESCRIPTION parameter to specify connect descriptor containers.

Purpose

To specify a container for a connect descriptor.

Usage Notes

When using more than one DESCRIPTION parameter, place the parameters under the DESCRIPTION_LIST parameter.

Example 6-7 DESCRIPTION Parameter Example

net_service_name=
(DESCRIPTION=
  (ADDRESS=...)
  (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))

6.7 Protocol Addresses

Learn about Oracle Net Services protocol address parameters.

The protocol address section of a tnsnames.ora file specifies listener protocol addresses. 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
  The tnsnames.ora parameter ADDRESS specifies protocol addresses with the ADDRESS_LIST for multiple addresses or with the DESCRIPTION parameter for one listener.
• HTTPS_PROXY
  Learn to use the tnsnames.ora parameter HTTPS_PROXY to specify HTTP proxy host names to tunnel Transport Layer Security (TLS) client connections.
• HTTPS_PROXY_PORT
  Learn how to use the tnsnames.ora parameter HTTPS_PROXY_PORT to specify forward HTTP proxy host ports for tunneling Transport Layer Security (TLS) client connections.
• ADDRESS_LIST
  The ADDRESS_LIST networking parameter specifies the number of protocol addresses.

6.7.1 ADDRESS

The tnsnames.ora parameter ADDRESS specifies protocol addresses with the ADDRESS_LIST for multiple addresses or with the DESCRIPTION parameter for one listener.

Purpose

To specify one listener protocol address.

Usage Notes

Put this parameter under either the ADDRESS_LIST parameter or the DESCRIPTION parameter.

ADDRESS Parameter Example

net_service_name=
(DESCRIPTION= 
 (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
 (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))

6.7.2 HTTPS_PROXY

Learn to use the tnsnames.ora parameter HTTPS_PROXY to specify HTTP proxy host names to tunnel Transport Layer Security (TLS) client connections.

Purpose

To specify HTTP proxy host names for tunneling your TLS client connections.

Usage Notes

Your clients can tunnel secure connections over HTTP proxy using the HTTP CONNECT method. This helps access the public cloud database service because it eliminates the need to open an outbound port on a client-side firewall. This parameter is applicable only to the connect descriptors where PROTOCOL=TCPS. This is similar to the web browser setting for intranet users who want to connect to internet hosts. You can increase the forward web proxy read timeout for requests to a higher value depending on client queries. Otherwise, the forward web proxy closes the connection assuming that no requests are made from the client.

A successful connection depends on your specific proxy configurations. The performance of your data transfers depend on the proxy capacity. Oracle recommends against using this feature in production environments where performance is critical.

Configuring tnsnames.ora for an HTTP proxy may not be secure enough, depending your organization’s network configuration and security policies. For example, some networks require a user name and password for the HTTP proxy.

Oracle Client versions earlier than 18c does not support connections through HTTP proxy.

Contact your network administrator to open outbound connections to hosts that are in the oraclecloud.com domain by using the relevant port, without going through an HTTP proxy. For example, port 1522.

Default

None

Values

An HTTP proxy host name that can make an outbound connection to internet hosts.

Example

HTTPS_PROXY=www-proxy.example.com

6.7.3 HTTPS_PROXY_PORT

Learn how to use the tnsnames.ora parameter HTTPS_PROXY_PORT to specify forward HTTP proxy host ports for tunneling Transport Layer Security (TLS) client connections.

Purpose

To specify forward HTTP proxy host port for tunneling TLS client connections.

Usage Notes

It forwards the HTTP proxy host port that receives the HTTP CONNECT method. Use this parameter with HTTPS_PROXY_PORT. The value for the HTTPS_PROXY_PORT parameter takes effect only when you set SQLNET.USE_HTTPS_PROXY=1 set in your sqlnet.ora file.

Default

none

Values

port number

Example

HTTPS_PROXY_PORT=80

6.7.4 ADDRESS_LIST

The ADDRESS_LIST networking parameter specifies the number of protocol addresses.

Purpose

To define a list of protocol addresses.

Usage Notes

If there is only one listener protocol address, then ADDRESS_LIST is not necessary.

Put this parameter either under the DESCRIPTION parameter or the DESCRIPTION_LIST parameter.

Example

net_service_name=
 (DESCRIPTION= 
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))

6.8 Optional Parameters for Address Lists

For multiple addresses, you can use the optional parameters to configure address lists.

• ENABLE
  
• EXPIRE_TIME
  Use the EXPIRE_TIME parameter to specify how often, in minutes, to verify that the remote server connection is active.
• FAILOVER
  
• LOAD_BALANCE
  
• RECV_BUF_SIZE
  Use the RECV_BUF_SIZE parameter to specify buffer space for session receive operations.
• SDU
  
• SEND_BUF_SIZE
  Use the SEND_BUF_SIZE parameter to specify buffer space for session send operations.
• SOURCE_ROUTE
  
• TYPE_OF_SERVICE
  

6.8.1 ENABLE

Purpose

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

Usage Notes

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.

Values

broken

Example

net_service_name=
 (DESCRIPTION= 
  (ENABLE=broken)
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))

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

6.8.2 EXPIRE_TIME

Use the EXPIRE_TIME parameter to specify how often, in minutes, to verify that the remote server connection is active.

Purpose

To specify time intervals, in minutes, for how often to verify that the remote server connection is active.

Usage Notes

Oracle Net Services tunes the TCP keepalive parameters so that probes are sent after an idle activity.

Limitations on using the terminated connection detection feature are:

• You cannot use it on bequeathed connections.
• Though very small, a probe packet generates additional traffic that may degrade your network performance.
• Depending on your operating system, the server may need to perform additional processing to distinguish the connection probing event from other events. This can also result in a degraded network performance.

Default

0

Minimum Value

0

Recommended Value

10

Example

net_service_name=
   (DESCRIPTION=
     (EXPIRE_TIME=10)
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521)))
     (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))

6.8.3 FAILOVER

Purpose

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

Usage Notes

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.

Note:

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.

Default

on for the DESCRIPTION_LIST, DESCRIPTION, and ADDRESS_LIST parameters

Values

• yes | on | true

• no | off | false

Example

net_service_name=
 (DESCRIPTION= 
  (FAILOVER=on)
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))

6.8.4 LOAD_BALANCE

Purpose

To enable or disable client load balancing for multiple protocol addresses.

Usage Notes

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.

Default

on for DESCRIPTION_LIST

Values

• yes | on | true

• no | off | false

Example

net_service_name=
 (DESCRIPTION= 
  (LOAD_BALANCE=on)
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))

6.8.5 RECV_BUF_SIZE

Use the RECV_BUF_SIZE parameter to specify buffer space for session receive operations.

Purpose

To specify, in bytes, the buffer space for receive operations of sessions.

Usage Notes

This parameter is supported by the TCP/IP, TCP/IP with TLS, 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.

Note:

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

Default

The default value for this parameter is specific to the operating system. The default for the Linux 2.6 operating system is 87380 bytes.

Example

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)
        (RECV_BUF_SIZE=11784))
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)
        (RECV_BUF_SIZE=11784))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)))
net_service_name= 
 (DESCRIPTION= 
   (RECV_BUF_SIZE=11784)
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=hr1-server)(PORT=1521))
     (ADDRESS=(PROTOCOL=tcp)(HOST=hr2-server)(PORT=1521)))
   (CONNECT_DATA=
     (SERVICE_NAME=hr.us.example.com)))

6.8.6 SDU

Purpose

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.

Usage Notes

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.

Default

8192 bytes (8 KB)

Values

512 to 2097152 bytes.

Example

net_service_name= 
 (DESCRIPTION=
  (SDU=8192)
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com))

6.8.7 SEND_BUF_SIZE

Use the SEND_BUF_SIZE parameter to specify buffer space for session send operations.

Purpose

To specify, in bytes, the buffer space for send operations of sessions.

Usage Notes

This parameter is supported by the TCP/IP, TCP/IP with TLS, 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.

Note:

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

Default

The default value for this parameter is operating system specific. The default for the Linux 2.6 operating system is 16 KB.

Example

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)
        (SEND_BUF_SIZE=11784))
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)
        (SEND_BUF_SIZE=11784)))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)))
net_service_name= 
 (DESCRIPTION= 
   (SEND_BUF_SIZE=11784)
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=hr1-server)(PORT=1521)
     (ADDRESS=(PROTOCOL=tcp)(HOST=hr2-server)(PORT=1521)))
   (CONNECT_DATA=
     (SERVICE_NAME=hr.us.example.com)))

6.8.8 SOURCE_ROUTE

Purpose

To enable routing through multiple protocol addresses.

Usage Notes

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.

Default

off

Values

• yes | on

• no | off

Example

net_service_name=
 (DESCRIPTION= 
  (SOURCE_ROUTE=on)
  (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc)(PORT=1630))
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))

See Also:

Oracle Database Net Services Administrator's Guide for complete configuration information

6.8.9 TYPE_OF_SERVICE

Purpose

To specify the type of service to use for an Oracle Rdb database.

Usage Notes

This parameter should only be used if the application supports both an Oracle Rdb and Oracle database service, and you want the application to load balance between the two.

Put this parameter under the DESCRIPTION parameter.

Example

net_service_name=
 (DESCRIPTION_LIST=
  (DESCRIPTION=
   (ADDRESS=...)
   (CONNECT_DATA= 
    (SERVICE_NAME=generic)
    (RDB_DATABASE=[.mf]mf_personal.rdb)
    (GLOBAL_NAME=alpha5))
   (TYPE_OF_SERVICE=rdb_database))
  (DESCRIPTION=
   (ADDRESS=...)
   (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com))
   (TYPE_OF_SERVICE=oracle11_database)))

6.9 Connection Data Section

Learn how to configure network connections with protocol addresses.

A network object is identified by a protocol address. When a connection is made, the client and the receiver of the request (listener or Oracle Connection Manager) are configured with identical protocol addresses. The client uses this address to send the connection request to a particular network object location, and the recipient "listens" for requests on this address, and grants a connection based on its address information matching the client information.

• CONNECT_DATA
  Use the CONNECT_DATA parameter to define the network service to which you want to connect.
• FAILOVER_MODE
  
• GLOBAL_NAME
  
• HS
  
• INSTANCE_NAME
  
• POOL_CONNECTION_CLASS
  Use this parameter to explicitly name the connection class for Database Resident Connection Pooling (DRCP) connections.
• POOL_PURITY
  Use this parameter to specify if an application needs a new session that is not tainted with any prior session state or to reuse a previous session.
• RDB_DATABASE
  
• SHARDING_KEY
  Use this parameter to route the database connection request to an appropriate shard. Put this parameter under the CONNECT_DATA section of a connect string.
• SUPER_SHARDING_KEY
  Use this parameter in the case of composite sharding to route the database request to a collection of shards (shardspace). Put this parameter under the CONNECT_DATA section of a connect string.
• SERVER
  
• SERVICE_NAME
  
• COLOCATION_TAG
  
• CONNECTION_ID_PREFIX
  Use this parameter to add application specific ID to connection identifier.
• TUNNEL_SERVICE_NAME
  Set this parameter to identify the client CMAN.
• KERBEROS5_PRINCIPAL
  Use the KERBEROS5_PRINCIPAL parameter to set the Kerberos principal name associated with the Kerberos credentials cache (CC) file.

6.9.1 CONNECT_DATA

Use the CONNECT_DATA parameter to define the network service to which you want to connect.

Purpose

To define the service to which to connect, such as SERVICE_NAME.

Usage Notes

Put this parameter under the DESCRIPTION parameter.

CONNECT_DATA permits the following additional parameters:

• COLOCATION_TAG

• CONNECTION_ID_PREFIX

• FAILOVER_MODE

• GLOBAL_NAME

• HS

• INSTANCE_NAME

• KERBEROS5_PRINCIPAL

• RDB_DATABASE

• SHARDING_KEY

• SUPER_SHARDING_KEY

• SERVER

• SERVICE_NAME

• TUNNEL_SERVICE_NAME

• WALLET_LOCATION

Example

net_service_name=
 (DESCRIPTION= 
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)))

6.9.2 FAILOVER_MODE

Purpose

To instruct Oracle Net to fail over to a different listener if the first listener fails during run time.

Usage Notes

Depending upon the configuration, the session or any SELECT statements which were in progress are automatically failed over.

This type of failover is called Transparent Application Failover (TAF) and should not be confused with the connect-time failover FAILOVER parameter.

Put this parameter under the CONNECT_DATA parameter.

Additional Parameters

FAILOVER_MODE supports the following parameters:

• BACKUP: Specifies the failover node by its net service name. A separate net service name must be created for the failover node.

• TYPE: Specifies the type of failover. Three types of Oracle Net failover functionality are available by default to Oracle Call Interface (OCI) applications:

  • SESSION: Fails over the session. For example, if a user's connection is lost, then a new session is automatically created for the user on the backup. This type of failover does not attempt to recover selects.

  • SELECT: Allows users with open cursors to continue fetching them after failure. However, this mode involves overhead on the client side in normal select operations.

  • NONE: This is the default, in which no failover functionality is used. This can also be explicitly specified to prevent failover from happening.

• METHOD: Specifies how fast failover is to occur from the primary node to the backup node:

  • BASIC: Establishes connections at failover time. This option requires almost no work on the backup database server until failover time.

  • PRECONNECT: Pre-establishes connections. This provides faster failover but requires that the backup instance be able to support all connections from every supported instance.

• TRANSACTION: Allows the database to complete the current database transaction following a recoverable error. This parameter is used with the COMMIT_OUTCOME=TRUE parameter.

• RETRIES: Specifies the number of times to attempt to connect after a failover. If DELAY is specified, then RETRIES defaults to five retry attempts.

• DELAY: Specifies the amount of time in seconds to wait between connect attempts. If RETRIES is specified, then DELAY defaults to one second.

Note:

If a callback function is registered, then RETRIES and DELAY parameters are ignored.

6.9.3 GLOBAL_NAME

Purpose

To identify the Oracle Rdb database.

Usage Notes

Put this parameter under the CONNECT_DATA parameter.

Example

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS_LIST=
    (ADDRESS=...)
    (ADDRESS=...))
   (CONNECT_DATA=
    (SERVICE_NAME=generic)
    (RDB_DATABASE=[.mf]mf_personal.rdb)
    (GLOBAL_NAME=alpha5)))

6.9.4 HS

Purpose

To direct Oracle Net to connect to a non-Oracle system through Heterogeneous Services.

Usage Notes

Put this parameter under the CONNECT_DATA parameter.

Default

None

Values

ok

Example

net_service_name= 
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
    (SID=sales6)
    )
(HS=ok))

6.9.5 INSTANCE_NAME

Purpose

To identify the database instance to access.

Usage Notes

Set the value to the value specified by the INSTANCE_NAME parameter in the initialization parameter file.

Put this parameter under the CONNECT_DATA parameter.

Example

net_service_name= 
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales1)))

See Also:

Oracle Database Net Services Administrator's Guide for additional information about the use of INSTANCE_NAME

6.9.6 POOL_CONNECTION_CLASS

Use this parameter to explicitly name the connection class for Database Resident Connection Pooling (DRCP) connections.

Usage Notes

Add this parameter in the connect string under CONNECT_DATA section of the connect identifier. This parameter takes precedence and overrides the properties programmatically set by the application using this connect string.

Example

ServerPool =
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp) (HOST=sales-svr) (PORT=1521))
   (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)(SERVER=POOLED)(POOL_CONNECTION_CLASS=value)))

6.9.7 POOL_PURITY

Use this parameter to specify if an application needs a new session that is not tainted with any prior session state or to reuse a previous session.

Usage Notes

Starting with Oracle Database 21c, you can configure Database Resident Connection Pooling (DRCP) for specific pluggable databases (PDBs). Add this parameter in the connect string under CONNECT_DATA section of the connect identifier to set purity attributes to a DRCP connection request.

This parameter takes precedence and overrides the properties programmatically set by the application using this connect string.

Values

NEW/SELF

Example

ServerPool =
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp) (HOST=sales-svr) (PORT=1521))
   (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)(SERVER=POOLED)(POOL_PURITY=value)))

6.9.8 RDB_DATABASE

Purpose

To specify the file name of an Oracle Rdb database.

Usage Notes

Put this parameter under the CONNECT_DATA parameter.

Example

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (RDB_DATABASE= [.mf]mf_personal.rdb)))

6.9.9 SHARDING_KEY

Use this parameter to route the database connection request to an appropriate shard. Put this parameter under the CONNECT_DATA section of a connect string.

Purpose

To specify the value of sharding key. Based on the value specified during a database connection request, the request is directly routed to an appropriate shard.

Usage Notes

Use the SHARDING_KEY parameter to specify sharding key in simplified text format. This parameter supports only ASCII character set and not special characters. The following data types are supported for sharding key:

• NUMBER

• INTEGER

• SMALLINT

• RAW

• NVARCHAR

• NVARCHAR2

• NCHAR

• DATE

• TIMESTAMP

Use the SHARDING_KEY_B64 parameter to specify the base64-encoded binary representation of sharding key. This parameter supports special characters (such as " quotation mark , comma ( ) close parenthesis + plus sign).

Values

The fields for base64-encoded values (*_B64) start with a header, which is a sequence of space-separated integer values:

(CONNECT_DATA=(SHARDING_KEY_B64=[version] [type] [key column 1 type identifier] [key column 2 type identifier] ... ,[base64 string],[base64 string],[base64 string],...))... 

In the above syntax:

• Parts of the compound key are separated with comma.

• version specifies the version number of base64 representation. Currently, only version 1 is supported and thus the supported version value is 1.

• type specifies the character set string and its encoding information. The supported type values are:

  Value	Character Set String	Encoding Scheme
  0	String contains hash value.	Character values are encoded in AL32UTF8 (for VARCHAR) and AL16UTF16 (for NVARCHAR).
  1	String does not contain hash value.
  2	String does not contain hash value.	Character values are encoded in database encoding, which may be specific for each column.
  3	String contains hash value.
  4	String contains only hash value.

• key column type identifier specifies the data types. The supported key column type identifier values are:

  Value	Data Type
  1	VARCHAR, NVARCHAR, CHAR, NCHAR
  2	NUMBER
  6	NUMBER with length in first byte
  12	DATE
  23	RAW
  180	TIMESTAMP

• The header is terminated by comma and is followed by base64 string. base64 string is a comma-separated list of the base64-encoded value string. The hash value, if available, is the last value in the list.

Example 6-8

In the following sample connect string, the SHARDING_KEY parameter value is specified in simplified text format:

net_service_name
(DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     ((SHARDING_KEY=40598230))))

Example 6-9

In the following sample connect string, the SHARDING_KEY_B64 parameter value is encoded to base64 binary representation:

net_service_name
(DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     ((SHARDING_KEY_B64=1 1 2,VVM=,OTQwMDI=))))

6.9.10 SUPER_SHARDING_KEY

Use this parameter in the case of composite sharding to route the database request to a collection of shards (shardspace). Put this parameter under the CONNECT_DATA section of a connect string.

Purpose

To specify shardspace key for a collection of shards. A shardspace is set of shards that store data that corresponds to a range or list of key values. Based on the value specified during a database connection request, the request is directly routed to an appropriate shardspace.

Usage Notes

Use the SUPER_SHARDING_KEY parameter to specify shardspace key for a collection of shards in simplified text format. This parameter supports only ASCII character set and not special characters. The supported data types for super sharding key are the same as those for sharding key.

Use the SUPER_SHARDING_KEY_B64 parameter to specify the base64-encoded binary representation of shardspace key. This parameter supports special characters (such as " quotation mark , comma ( ) close parenthesis + plus sign).

Values

The fields for base64-encoded values (*_B64) start with a header, which is a sequence of space-separated integer values:

(CONNECT_DATA=(SUPER_SHARDING_KEY_B64=[version] [type] [integer literal] [integer literal] ... ,[base64 binary],[base64 binary],[base64 binary],...))... 

For details on each of the base64-encoded header fields, see SHARDING_KEY.

Example 6-10

In the following sample connect string, the SHARDING_KEY and SUPER_SHARDING_KEY parameter values are specified in simplified text format:

net_service_name=
(DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     ((SHARDING_KEY=40598230)(SUPER_SHARDING_KEY=gold)))

Example 6-11

In the following sample connect string, the SHARDING_KEY_B64 and SUPER_SHARDING_KEY_B64 parameter values are encoded to base64 binary representation:

net_service_name
(DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     ((SHARDING_KEY_B64=1 1 2,VVM=,OTQwMDI=)(SUPER_SHARDING_KEY_B64=1 1,BBWEPGRBBDOEMGQW)))

6.9.11 SERVER

Purpose

To direct the listener to connect the client to a specific type of service handler.

Usage Notes

Put this parameter under the CONNECT_DATA parameter.

Values

• dedicated to specify whether client requests be served by dedicated server.

• shared to specify whether client requests be served by a dispatcher or shared server.

• pooled to get a connection from the connection pool if database resident connection pooling is enabled on the server.

Note:

• Shared server must be configured in the database initialization file in order for the client to connect to the database with a shared server process.

• The USE_DEDICATED_SERVER parameter in the sqlnet.ora file overrides this parameter.

Example

net_service_name= 
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)
    (SERVER=dedicated)))

6.9.12 SERVICE_NAME

Purpose

To identify the Oracle Database database service to access.

Usage Notes

Set the value to a value specified by the SERVICE_NAMES parameter in the initialization parameter file.

Put this parameter under the CONNECT_DATA parameter.

Example

net_service_name= 
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)))

6.9.13 COLOCATION_TAG

Purpose

To direct the listener to route all connections with the same colocation_tag to the same database instance.

Usage Notes

Use this parameter with the CONNECT_DATA parameter.

The parameter value must be an alphanumeric string.

Example

net_service_name=
 (DESCRIPTION=
   (ADDRESS_LIST=
    (ADDRESS=...)
    (ADDRESS=...))
 (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)
    (COLOCATION_TAG=abc)))

Note:

Under certain conditions, such as, when maximum load of an instance is reached or when new instances are added or deleted for a service, the colocation of client connections that have the same colocation_tag to the same database instance may not be consistent.

6.9.14 CONNECTION_ID_PREFIX

Use this parameter to add application specific ID to connection identifier.

Usage Notes

Put this parameter under the CONNECT_DATA parameter.

Example

net_service_name=
(DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     ((CONNECTION_ID_PREFIX=value)))

Note:

The CONNECTION_ID_PREFIX value is appended internally to a system generated connection ID value and sent as CONNECTION_ID in connect string. The CONNECTION_ID_PREFIX must be an 8-byte alphanumeric identifier limited to the following [a...z] [A...Z] [0...9] _ character set.

6.9.15 TUNNEL_SERVICE_NAME

Set this parameter to identify the client CMAN.

Purpose

The server CMAN listener will route the connection to a gateway that has a tunnel connection to the requested client ID.

Usage

Put this parameter under the CONNECT_DATA parameter.

Example

net_service_name=
(DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=...)
     (ADDRESS=...))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (TUNNEL_SERVICE_NAME=south)))

6.9.16 KERBEROS5_PRINCIPAL

Use the KERBEROS5_PRINCIPAL parameter to set the Kerberos principal name associated with the Kerberos credentials cache (CC) file.

Purpose

When you configure Kerberos authentication for an Oracle Database client, you can specify multiple Kerberos principals with a single Oracle Database client.

This is an optional parameter. When specified, it is used to verify if the principal name in the credential cache (specified using SQLNET.KERBEROS5_CC_NAME) matches the parameter value.

Usage Notes

Use this parameter with the CONNECT_DATA parameter in the tnsnames.ora file. Alternatively, you can specify KERBEROS5_CC_NAME in the connect string along with the optional KERBEROS5_PRINCIPAL parameter to connect as a different Kerberos principal.

Each Kerberos principal must have a valid credential cache. Oracle Database checks KERBEROS5_PRINCIPAL against the value that is retrieved from the credential cache. If the two values do not match, then the user is not authenticated.

Example

For a user krbuser1, who is externally authenticated using the Kerberos principal krbprinc1.example.com and the credential cache for this principal is located at /tmp/krbuser1/krb.cc, the connect string is:

net_service_name=
(DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=sales.example.com)) 
      (SECURITY=
          (KERBEROS5_CC_NAME=/tmp/krbuser1/krb.cc)
          (KERBEROS5_PRINCIPAL=[e-mail])))

Note:

The connection fails if the principal in the /tmp/krbuser1/krb.cc file does not contain the [e-mail] value.

Similarly, for a user krbuser2, who is externally authenticated using the Kerberos principal krbprinc2.example.com and the credential cache for this principal is located at /tmp/krbuser2/krb.cc, the connect string is:

net_service_name=
(DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=sales.example.com)) 
      (SECURITY=
          (KERBEROS5_CC_NAME=/tmp/krbuser2/krb.cc)
          (KERBEROS5_PRINCIPAL=[e-mail])))

6.10 Security Section

The security section of the tnsnames.ora file specifies these security-related parameters for use with Oracle security features.

• SECURITY
  Use the SECURITY parameter to change the security properties of a connection.
• SSL_SERVER_CERT_DN
  Use the SSL_SERVER_CERT_DN parameter to specify the distinguished name (DN) of the database server.
• IGNORE_ANO_ENCRYPTION_FOR_TCPS
  The IGNORE_ANO_ENCRYPTION_FOR_TCPS parameter specifies if the SQLNET.ENCRYPTION_CLIENT parameter should be ignored for this specific TNS alias.
• WALLET_LOCATION
  Use the WALLET_LOCATION parameter in the tnsnames.ora file to specify different locations where Oracle wallets are stored.

6.10.1 SECURITY

Use the SECURITY parameter to change the security properties of a connection.

Purpose

To change the security properties of a connection. Put this parameter under the DESCRIPTION parameter.

Example

net_service_name=
 (DESCRIPTION= 
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com))
    (SECURITY=
      (SSL_SERVER_CERT_DN="cn=sales,cn=OracleContext,dc=us,dc=acme,dc=com")))

6.10.2 SSL_SERVER_CERT_DN

Use the SSL_SERVER_CERT_DN parameter to specify the distinguished name (DN) of the database server.

Purpose

To specify the DN of the database server.

Usage Notes

The server DN must be known by the client ahead of time. Otherwise, the client cannot specify the server's DN in SSL_SERVER_CERT_DN. The client uses this information to obtain the list of DNs it expects for each of the servers, enforcing the database server DN to match its service name. This parameter must be set to the server DN (for example, SSL_SERVER_CERT_DN="finance, cn=OracleContext,c=us,o=example") to use full DN matching. For partial DN matching, do not include this parameter.

Use this parameter with the sqlnet.ora parameter SSL_SERVER_DN_MATCH to enable full DN matching.

Example

finance= 
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL = tcps) (HOST = finance)
     (PORT = 1575)))
   (CONNECT_DATA=
     (SERVICE_NAME=finance.us.example.com))
   (SECURITY=
     (SSL_SERVER_CERT_DN="cn=finance,cn=OracleContext,c=us,o=example")))

6.10.3 IGNORE_ANO_ENCRYPTION_FOR_TCPS

The IGNORE_ANO_ENCRYPTION_FOR_TCPS parameter specifies if the SQLNET.ENCRYPTION_CLIENT parameter should be ignored for this specific TNS alias.

Purpose

To specify if the SQLNET.ENCRYPTION_CLIENT parameter should be ignored for this specific TNS alias.

Usage Notes

If your requirements are that SQLNET.ENCRYPTION_SERVER be set to required, then you can set the IGNORE_ANO_ENCRYPTION_FOR_TCPS parameter in both SQLNET.ENCRYPTION_CLIENT and SQLNET.ENCRYPTION_SERVER to TRUE. This forces the client to ignore the value that is set for the SQLNET.ENCRYPTION_CLIENT parameter for all outgoing TCPS connections.

Default

FALSE

Example 6-12 Example

test_ssl=
    (DESCRIPTION =
       (ADDRESS=(PROTOCOL=tcps)(HOST=)(PORT=1750))
       (CONNECT_DATA=(SID=^ORACLE_SID^))
       (SECURITY=(IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE))
     ) 

6.10.4 WALLET_LOCATION

Use the WALLET_LOCATION parameter in the tnsnames.ora file to specify different locations where Oracle wallets are stored.

Purpose

This parameter denotes a connection specific wallet. You can use this parameter when different connections need to use different wallets on the client side.

Usage Notes

You can use WALLET_LOCATION in both the sqlnet.ora file and tnsnames.ora file. Use of WALLET_LOCATION in tnsnames.ora overrides the WALLET_LOCATION in sqlnet.ora for the specific tnsnames.ora service.

WALLET_LOCATION enables a client connection to have distinct TLS connections that use certificates. This means that the clients will initiate multiple TLS connections using different TLS certificates, in the same client process.

Use this parameter if you have a single client that must rely on more than one TLS session. An example would be for a client that requires access to multiple pluggable databases (PDBs), each with its own identity (certificate). This feature enables you to configure the client to connect to the correct identity for each PDB. After the configuration is complete, multi-threaded clients are able to access more than one wallet with different certificates in simultaneous TLS sessions.

Examples

net_service_name=
 (DESCRIPTION=
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com))
      (SECURITY=(wallet_location=/home/oracle/wallets/databases)))

ssl_certs1 = 
    (DESCRIPTION = 
       (ADDRESS=(PROTOCOL=tcps)(HOST=shobeen.us.example.com) (PORT=1750))
       (CONNECT_DATA=(SID=sales_pdb)) 
       (SECURITY=(WALLET_LOCATION=/oracle/wallets/certificates/sales_cert))
     )
ssl_certs2 = 
    (DESCRIPTION = 
       (ADDRESS=(PROTOCOL=tcps)(HOST=shobeen.us.example.com) (PORT=1750))
       (CONNECT_DATA=(SID=marketing_pdb)) 
       (SECURITY=(WALLET_LOCATION=/oracle/wallets/certificates/marketing_cert))
     )

6.11 Timeout Parameters

The timeout section of the tnsnames.ora file provides the ability to specify timeout and retry configuration through the TNS connect string.

The following parameters can be set at the DESCRIPTION level of a connect string:

• CONNECT_TIMEOUT
  
• RETRY_COUNT
  
• RETRY_DELAY
  
• TRANSPORT_CONNECT_TIMEOUT
  
• RECV_TIMEOUT
  Use the tnsnames.ora parameter RECV_TIMEOUT to specify the duration of time that a database client or server should wait for data from a peer after establishing a connection.

6.11.1 CONNECT_TIMEOUT

Purpose

To specify the timeout duration in ms, sec, or min for a client to establish an Oracle Net connection to an Oracle database.

Usage Notes

Put this parameter under the DESCRIPTION parameter.

The timeout interval specified by CONNECT_TIMEOUT is a superset of the TCP connect timeout interval. It includes the time to be connected to the database instance providing the requested service, not just the duration of the TCP connection. It accepts different timeouts with or without space between the value and the unit. In case, no unit is mentioned, the default unit is sec.

The timeout interval is applicable for each ADDRESS in an ADDRESS_LIST, and each IP address to which a host name is mapped.

The CONNECT_TIMEOUT parameter is equivalent to the sqlnet.ora parameter SQLNET.OUTBOUND_CONNECT_TIMEOUT, and overrides it.

Example

net_service_name=
 (DESCRIPTION= 
  (CONNECT_TIMEOUT=10 ms)(RETRY_COUNT=3)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=
   (SERVICE_NAME=sales.us.example.com)))

6.11.2 RETRY_COUNT

Purpose

To specify the number of times an ADDRESS list is traversed before the connection attempt is terminated.

Usage Notes

Put this parameter under the DESCRIPTION parameter.

When a DESCRIPTION_LIST is specified, each DESCRIPTION is traversed multiple times based on the specified number of retries.

Example

net_service_name= 
(DESCRIPTION_LIST=
 (DESCRIPTION=
  (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1a-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1b-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales1.example.com)))
 (DESCRIPTION=
  (CONNECT_TIMEOUT=60)(RETRY_COUNT=1)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2a-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2b-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales2.us.example.com))))

6.11.3 RETRY_DELAY

Purpose

To specify the delay in seconds between subsequent retries for a connection. This parameter works in conjunction with RETRY_COUNT parameter.

Usage Notes

Put this parameter under the DESCRIPTION parameter.

When a DESCRIPTION_LIST is specified, each DESCRIPTION is traversed multiple times based on the specified number of retries, and the specific delay for the description.

Example

net_service_name= 
(DESCRIPTION_LIST=
 (DESCRIPTION=
  (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(RETRY_DELAY=2)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1a-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1b-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales1.example.com)))
 (DESCRIPTION=
  (CONNECT_TIMEOUT=60)(RETRY_COUNT=2)(RETRY_DELAY=1)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2a-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2b-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales2.us.example.com))))

6.11.4 TRANSPORT_CONNECT_TIMEOUT

Purpose

To specify the transport connect timeout duration in ms, sec, or min for a client to establish an Oracle Net connection to an Oracle database.

Usage Notes

This parameter is put under the DESCRIPTION parameter.

The TRANSPORT_CONNECT_TIMEOUT parameter specifies the time, in ms, sec, or min, for a client to establish a TCP connection to the database server. It accepts different timeouts with or without space between the value and the unit. The default value is 60 seconds. In case, no unit is mentioned, the default unit is sec.

The timeout interval is applicable for each ADDRESS in an ADDRESS_LIST description, and each IP address that a host name is mapped. The TRANSPORT_CONNECT_TIMEOUT parameter is equivalent to the sqlnet.ora parameter TCP.CONNECT_TIMEOUT, and overrides it.

Example

net_service_name =
  (DESCRIPTION=
    (TRANSPORT_CONNECT_TIMEOUT=10 ms)
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
    (CONNECT_DATA=
      (SERVICE_NAME=sales.us.example.com)))

6.11.5 RECV_TIMEOUT

Use the tnsnames.ora parameter RECV_TIMEOUT to specify the duration of time that a database client or server should wait for data from a peer after establishing a connection.

Purpose

To specify the time, in ms, sec, or min, for a database client or server to wait for data from the peer after establishing a connection. The peer must send data within the time interval that you specify.

You can specify the time in hours, minutes, seconds, or milliseconds by using the hr, min, sec, or ms keyword respectively. If you do not specify a unit of measurement, then the default unit is sec.

Usage Notes

This parameter is put under the DESCRIPTION parameter.

Setting this parameter for clients ensures that receive operations are not left in a wait state indefinitely or for a long period due to server host being down, server busy state, or network connectivity issues. If a client does not receive response data in the time specified, then the client logs ORA-12535: TNS:operation timed out and ORA-12609: TNS: Receive timeout occurred messages to the sqlnet.log file.

Default Value

None

Minimum Value

1 ms

Recommended Value

Any number greater than the minimum value of 1 ms up to 4294967295 ms.

Example

RECV_TIMEOUT=10ms

or

RECV_TIMEOUT=10 ms

6.12 Compression Parameters

The compression section of the tnsnames.ora file provides the ability to enable compression and specify compression levels. These parameters can be set at the DESCRIPTION level of a connect string.

• COMPRESSION
  The tnsnames.ora file’s compression parameter enables or disables the data compression.
• COMPRESSION_LEVELS
  The COMPRESSION_LEVELS parameter of the tnsnames.ora file specifies the compression level.

6.12.1 COMPRESSION

The tnsnames.ora file’s compression parameter enables or disables the data compression.

Purpose

To enable or disable data compression.

Usage Notes

Put this parameter under the DESCRIPTION parameter.

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

Default

off

Values

• on to enable data compression.

• off to disable data compression.

Example

net_service_name=
 (DESCRIPTION=
   (COMPRESSION=on)
      (ADDRESS_LIST=
         (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521))
         (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)))
   (CONNECT_DATA=
       (SERVICE_NAME=sales.us.example.com)))

6.12.2 COMPRESSION_LEVELS

The COMPRESSION_LEVELS parameter of the tnsnames.ora file specifies the compression level.

Purpose

To specify the compression level.

Usage Notes

The compression levels are used at the time of negotiation to verify which levels are used at both ends, and select one level. Put this parameter under the DESCRIPTION parameter.

This parameter is used with the COMPRESSION parameter. Setting this parameter in the connect descriptor for a client overrides the SQLNET.COMPRESSION_LEVELS parameter in the client-side sqlnet.ora file.

Default

low

Values

• low for low CPU usage and a low compression ratio.

• high for high CPU usage and a high compression ratio.

Example

net_service_name=
 (DESCRIPTION=
  (COMPRESSION=on)
  (COMPRESSION_LEVELS=(LEVEL=low)(LEVEL=high))
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)))