6 Local Naming Parameters in the tnsnames.ora File
Learn about the Oracle Net service names local naming parameters that you specify in the tnsnames.ora
configuration file.
- Overview of Local Naming Parameters
Thetnsnames.ora
file is a configuration file that contains network service names that are mapped to connect descriptors for the Local Naming method. - General Syntax of tnsnames.ora
In this example, you can see the generaltnsnames.ora
file syntax. - Using Multiple Descriptions in tnsnames.ora Files
In this example, you can see two connect descriptors with multiple addresses. - Multiple Address Lists in tnsnames.ora Files
Thetnsnames.ora
file supports connect descriptors with multiple lists of addresses, each with its own characteristics. Learn how to configure multiple address lists intnsnames.ora
files. - Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
Whentnsnames.ora
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 theDESCRIPTION
parameter. Identify multiple connect descriptors with theDESCRIPTION_LIST
parameter. - Protocol Addresses
The protocol address section of atnsnames.ora
file specifies listener protocol addresses. - 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 thetnsnames.ora
file specifies these security-related parameters for use with Oracle security features. - Timeout Parameters
The timeout section of thetnsnames.or
a file provides the ability to specify timeout and retry configuration through the TNS connect string. - Compression Parameters
The compression section of thetnsnames.ora
file provides the ability to enable compression and specify compression levels. These parameters can be set at theDESCRIPTION
level of a connect string.
6.1 Overview of 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.
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.
Typically, tools such as Oracle Database Configuration Assistant (DBCA) and Oracle Net Configuration Assistant (NETCA) create the tnsnames.ora
file in the ORACLE_HOME/network/admin
directory for Oracle Database installations, the GRID_HOME/network/admin
directory for Oracle Grid Infrastructure installations, or the corresponding ORACLE_BASE_HOME/network/admin
directory for a read-only Oracle home. Note that if you have installed multiple databases, then the file is created in the Oracle home or Grid home where DBCA or NETCA is run (or the Oracle base home for read-only instances).
The order for checking the tnsnames.ora
file is:
-
The directory specified by the
TNS_ADMIN
environment variable -
If the
TNS_ADMIN
environment variable is not set or the file is not found in theTNS_ADMIN
directory:-
On Linux and UNIX: The
ORACLE_HOME/network/admin
directory (or itsORACLE_BASE_HOME/network/admin
directory for a read-only Oracle home) -
On Windows: The
ORACLE_HOME\network\admin
directory (or itsORACLE_BASE_HOME\network\admin
directory for a read-only Oracle home)
-
-
For a read-only Oracle home, if the file is not found in the Oracle base home:
-
On Linux and UNIX: The
ORACLE_HOME/network/admin
directory -
On Windows: The
ORACLE_HOME\network\admin
directory
-
Note:
-
On Windows, the
ORACLE_HOME
location is determined by theORACLE_HOME\bin\oracle.key
file (which contains the name of the Windows Registry key whereORACLE_HOME
is defined). Also, theTNS_ADMIN
environment variable is used if it is set in the environment of the process. If you do not define theTNS_ADMIN
environment variable in the environment or if the process is a service that does not have an environment, then Windows scans the registry for aTNS_ADMIN
parameter. -
With Oracle Instant Client,
tnsnames.ora
is located in the subdirectory of the Oracle Instant Client software. For example, in the/opt/oracle/instantclient_release_number/network/admin
directory.
Related Topics
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.2 General Syntax of tnsnames.ora
In this example, you can see the general tnsnames.ora
file syntax.
Here, DESCRIPTION
contains the connect descriptor, ADDRESS
contains the protocol address, and CONNECT_DATA
contains database service identification information.
Example 6-1 Basic Format of tnsnames.ora File
net_service_name= (DESCRIPTION=(ADDRESS=(
protocol_address_information
))
(CONNECT_DATA= (SERVICE_NAME=service_name
) ) )
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.3 Using Multiple Descriptions in tnsnames.ora Files
In this example, you can see two connect descriptors with multiple addresses.
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.
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.
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.4 Multiple Address Lists in tnsnames.ora Files
The tnsnames.ora
file supports connect descriptors with multiple lists of addresses, each with its own characteristics. Learn how to configure multiple address lists in tnsnames.ora
files.
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
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.5 Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
When tnsnames.ora
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:
-
The client connects to the protocol address of the first Oracle Connection Manager as indicated by:
(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))
-
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)))
-
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:
-
The client selects an
ADDRESS_LIST
at random and fails over to the other address if the chosenADDRESS_LIST
fails. This is indicated if you set theLOAD_BALANCE
andFAILOVER
parameters toon
. -
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 theADDRESS_LIST
. -
Oracle Connection Manager then connects to the database service using the protocol address that is indicated for the
ADDRESS_LIST
.
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.6 Connect Descriptor Descriptions
Specify connect descriptors using the DESCRIPTION
parameter. Identify multiple connect descriptors with the DESCRIPTION_LIST
parameter.
- DESCRIPTION_LIST
TheDESCRIPTION_LIST
parameter of thetnsnames.ora
file defines a list of connect descriptors for a particular net service name. - DESCRIPTION
Use thetnsnames.ora
fileDESCRIPTION
parameter to specify connect descriptor containers.
Parent topic: Local Naming Parameters in the tnsnames.ora File
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=
Parent topic: Connect Descriptor Descriptions
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)))
Parent topic: Connect Descriptor Descriptions
6.7 Protocol Addresses
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
Thetnsnames.ora
parameterADDRESS
specifies protocol addresses with theADDRESS_LIST
for multiple addresses or with theDESCRIPTION
parameter for one listener. - HTTPS_PROXY
Learn to use thetnsnames.ora
parameterHTTPS_PROXY
to specify HTTP proxy host names to tunnel Transport Layer Security (TLS) client connections. - HTTPS_PROXY_PORT
Learn how to use thetnsnames.ora
parameterHTTPS_PROXY_PORT
to specify forward HTTP proxy host ports for tunneling Transport Layer Security (TLS) client connections. - ADDRESS_LIST
TheADDRESS_LIST
networking parameter specifies the number of protocol addresses.
Parent topic: Local Naming Parameters in the tnsnames.ora File
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))
Parent topic: Protocol Addresses
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
Parent topic: Protocol Addresses
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
Parent topic: Protocol Addresses
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)))
Parent topic: Protocol Addresses
6.8 Optional Parameters for Address Lists
For multiple addresses, you can use the optional parameters to configure address lists.
- ENABLE
Use theENABLE
parameter to enable the keepalive feature on the supported TCP transports. - EXPIRE_TIME
Use theEXPIRE_TIME
parameter to specify how often, in minutes, to verify that the remote server connection is active. - FAILOVER
Use theFAILOVER
parameter to enable or disable connect-time failover for multiple protocol addresses. - LOAD_BALANCE
Use theLOAD_BALANCE
parameter to enable or disable client load balancing for multiple protocol addresses. - RECV_BUF_SIZE
Use theRECV_BUF_SIZE
parameter to specify buffer space for session receive operations. - SDU
Use theSDU
parameter to configure the session data unit (SDU) size. - SEND_BUF_SIZE
Use theSEND_BUF_SIZE
parameter to specify buffer space for session send operations. - SOURCE_ROUTE
Use theSOURCE_ROUTE
parameter to enable routing through multiple protocol addresses. - TYPE_OF_SERVICE
Use theTYPE_OF_SERVICE
parameter to specify the type of service to use for an Oracle Rdb database.
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.8.1 ENABLE
Use the ENABLE
parameter to enable the keepalive feature on the supported TCP transports.
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.
Parent topic: Optional Parameters for Address Lists
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))
Parent topic: Optional Parameters for Address Lists
6.8.3 FAILOVER
Use the FAILOVER
parameter to enable or disable connect-time failover for multiple protocol addresses.
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)))
Parent topic: Optional Parameters for Address Lists
6.8.4 LOAD_BALANCE
Use the LOAD_BALANCE
parameter to enable or disable client load balancing for multiple protocol addresses.
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))
Parent topic: Optional Parameters for Address Lists
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)))
Related Topics
Parent topic: Optional Parameters for Address Lists
6.8.6 SDU
Use the SDU
parameter to configure the session data unit (SDU) size.
Purpose
To instruct Oracle Net to optimize the transfer rate of data packets being sent across the network with a specified 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))
Related Topics
Parent topic: Optional Parameters for Address Lists
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)))
Related Topics
Parent topic: Optional Parameters for Address Lists
6.8.8 SOURCE_ROUTE
Use the SOURCE_ROUTE
parameter to enable routing through multiple protocol addresses.
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))
Related Topics
Parent topic: Optional Parameters for Address Lists
6.8.9 TYPE_OF_SERVICE
Use the TYPE_OF_SERVICE
parameter to specify the type of service to use for an Oracle Rdb database.
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)))
Related Topics
Parent topic: Optional Parameters for Address Lists
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 theCONNECT_DATA
parameter to define the connection service. - COLOCATION_TAG
- CONNECTION_ID_PREFIX
Use this parameter to add application specific ID to connection identifier. - FAILOVER_MODE
- GLOBAL_NAME
- HS
- INSTANCE_NAME
- POOL_BOUNDARY
Use thePOOL_BOUNDARY
parameter to enable implicit connection pooling with either Database Resident Connection Pooling (DRCP) or Proxy Resident Connection Pooling (PRCP). - POOL_CONNECTION_CLASS
Use this parameter to explicitly name the connection class for Database Resident Connection Pooling (DRCP) connections. - POOL_NAME
Use thePOOL_NAME
parameter to specify a pool name for multi-pool 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 theSHARDING_KEY
parameter to route the database connection request to an appropriate shard. - SHARDING_KEY_ID
Use this parameter to route the database connection request to a shard using the unique SHA-256 ID of a sharding key (instead of a sharding key value). - SUPER_SHARDING_KEY
Use theSUPER_SHARDING_KEY
parameter in the case of composite sharding to route the database request to a collection of shards (shardspace). - SERVER
- SERVICE_NAME
- TUNNEL_SERVICE_NAME
Set this parameter to identify the client CMAN.
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.9.1 CONNECT_DATA
Use the CONNECT_DATA
parameter to define the connection service.
Purpose
To define the service to which you want to connect, such as SERVICE_NAME
.
Usage Notes
Put this parameter under the DESCRIPTION
parameter. CONNECT_DATA
permits additional parameters as listed in Connection Data Section.
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)))
Parent topic: Connection Data Section
6.9.2 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 samecolocation_tag
to the same database instance may not be consistent.
Parent topic: Connection Data Section
6.9.3 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:
TheCONNECTION_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.
Related Topics
Parent topic: Connection Data Section
6.9.4 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 theCOMMIT_OUTCOME=TRUE
parameter. -
RETRIES
: Specifies the number of times to attempt to connect after a failover. IfDELAY
is specified, thenRETRIES
defaults to five retry attempts. -
DELAY
: Specifies the amount of time in seconds to wait between connect attempts. IfRETRIES
is specified, thenDELAY
defaults to one second.
Note:
If a callback function is registered, thenRETRIES
and DELAY
parameters are ignored.
Related Topics
Parent topic: Connection Data Section
6.9.5 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)))
Parent topic: Connection Data Section
6.9.6 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))
Related Topics
Parent topic: Connection Data Section
6.9.7 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
Parent topic: Connection Data Section
6.9.8 POOL_BOUNDARY
Use the POOL_BOUNDARY
parameter to enable implicit connection pooling with either Database Resident Connection Pooling (DRCP) or Proxy Resident Connection Pooling (PRCP).
Purpose
To enable implicit connection pooling with either DRCP (on the database server side) or PRCP (for Oracle Connection Manager in Traffic Director Mode). This setting specifies a time boundary to release an application session back to the DRCP or PRCP pool. You can set implicit connection pooling at a statement or transaction boundary.
If you omit this parameter setting, then implicit connection pooling is disabled.
Usage Notes
-
Specify this parameter under the
CONNECT_DATA
section of the connect string in thetnsnames.ora
file, Easy Connect syntax, or directly as part of the command-line connect string. -
This parameter is applicable to pooled connections only. In the application tier, to configure DRCP or PRCP, the client must specify the server type as
POOLED
using theSERVER=POOLED
setting. -
When set to
STATEMENT
, a session is released back to the connection pool when the session is implicitly stateless. A session is implicitly stateless when all open cursors in a session have been fetched through to completion and there are no active transactions, temporary tables, or temporary LOBs.Use the
STATEMENT
value for applications that create minimal session states from partially fetched cursors, temporary LOBs, and global or private temporary tables. -
When set to
TRANSACTION
, a session is released back to the connection pool when a transaction ends implicitly or explicitly, or when a transaction is not available and the session is stateless. The release to the pool closes any active cursors, temporary tables, and temporary LOBs.Use the
TRANSACTION
value for applications that create many session states from partially fetched cursors, temporary LOBs, and global or private temporary tables and for applications that have occasional commits or rollbacks of transactions. -
After executing a statement or creating a temporary LOB, if you perform a commit or rollback within the
TRANSACTION
boundary, then:-
Subsequent fetch operations may encounter an invalid cursor-related error, such as
ORA-01001
. -
Subsequent use of a temporary LOB may encounter an
ORA-22922
error. -
Working with a persistent LOB can continue even after an implicit release. However, if an implicit operation lands on a different instance, then an
ORA-43887
error may occur. In such cases, you must attempt the operation again.
-
-
If an application uses session pooling APIs along with the
POOL_BOUNDARY=STATEMENT
orPOOL_BOUNDARY=TRANSACTION
attribute in the connect string, then the connect string setting takes precedence over the pooling APIs. The session is released back to the connection pool using the statement or transaction boundary directive, overriding the session release API call.
Default
None
Values and Examples
Value | Example |
---|---|
|
Easy connect string:
Connect descriptor in
tnsnames.ora :
|
|
Easy connect string:
Connect descriptor in
tnsnames.ora :
|
tnsnames.ora
shows how to set implicit connection pooling in different modes:inst1s=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SERVER=POOLED)
(POOL_BOUNDARY=STATEMENT))
)
inst1t=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=proxy-server)(PORT=1522))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SERVER=POOLED)
(POOL_BOUNDARY=TRANSACTION))
)
For the inst1s
application instance, POOL_BOUNDARY=STATEMENT
specifies that implicit connection pooling applies at a statement boundary. HOST=sales-server
and PORT=1521
specify a DRCP connection through the database server.
For the inst1t
application instance, POOL_BOUNDARY=TRANSACTION
specifies that implicit connection pooling applies at a transaction boundary. HOST=proxy-server
and PORT=1522
specify a PRCP connection through Oracle Connection Manager in Traffic Director Mode.
Parent topic: Connection Data Section
6.9.9 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)))
Related Topics
Parent topic: Connection Data Section
6.9.10 POOL_NAME
Use the POOL_NAME
parameter to specify a pool name for multi-pool Database Resident Connection Pooling (DRCP) connections.
Purpose
To specify a pool name for each connection in multi-pool DRCP. This enables client applications or services to establish connections from specific DRCP pools.
With this setting, DRCP marks the connection against the appropriate pooled server. If you do not set this parameter, then the connection is established from the default pool if it is active.
Usage Notes
Use this parameter under the CONNECT_DATA
section of the tnsnames.ora
file or directly as part of the command-line connect string.
POOL_NAME=pool_name
along with the SERVER=POOLED
setting, DRCP checks whether a DRCP pool with the given pool name is added in the database:
-
If the pool does not exist, then an error is returned.
-
If the pool with the given pool name exists and is active, then the connection is established from the specified pooled server.
-
If the pool with the given pool name exists but is inactive, then an error is returned for any new connections attempted. If connections are already established and the pool is made inactive, then those connections are handled based on the
drainTime
parameter used in thestop_pool()
procedure (called to stop the DRCP pool).
DBA_CPOOL_INFO
contains information about existing DRCP pools. For example, this query lists all the available active pools: SELECT * FROM DBA_CPOOL_INFO WHERE status='ACTIVE';
Value
DRCP pool name
Default
None
Example
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=sales-svr)
(PORT=1522)
)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SERVER=POOLED)
(POOL_NAME=mypool)
)
)
Related Topics
Parent topic: Connection Data Section
6.9.11 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)))
Related Topics
Parent topic: Connection Data Section
6.9.12 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)))
Parent topic: Connection Data Section
6.9.13 SHARDING_KEY
Use the SHARDING_KEY
parameter to route the database connection request to an appropriate shard.
Purpose
To specify the value of a sharding key. Based on the value specified during a database connection request, the request is directly routed to the appropriate shard.
Usage Notes
You specify this parameter under the CONNECT_DATA
section of a connect string or tnsnames.ora
file.
Use the SHARDING_KEY
parameter to specify a sharding key in simplified text format. This parameter supports only ASCII character set and not special characters. The following data types are supported for a 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 a sharding key. This parameter supports these special characters: "
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],...))...
-
Parts of the compound key are separated with a comma.
-
version
specifies the version number of base64 representation. Currently, only version 1 is supported, and thus the supported version value is1
. -
type
specifies the character set string and its encoding information. The supportedtype
values are:Value Character Set String Encoding Scheme 0
String contains hash value.
Character values are encoded in
AL32UTF8
(forVARCHAR
) andAL16UTF16
(forNVARCHAR
).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 byte12
DATE
23
RAW
180
TIMESTAMP
-
The header is terminated by a 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
SHARDING_KEY
parameter value is specified in simplified text format: net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(host=sales-east1)(port=1522))
(ADDRESS=(host=sales-east2)(port=1522))
)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SHARDING_KEY=40598230)
)
)
Example 6-9
SHARDING_KEY_B64
parameter value is encoded to base64 binary representation: net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(host=sales-east1)(port=1522))
(ADDRESS=(host=sales-east2)(port=1522))
)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SHARDING_KEY_B64=1 1 2,VVM=,OTQwMDI=)
)
)
Related Topics
Parent topic: Connection Data Section
6.9.14 SHARDING_KEY_ID
Use this parameter to route the database connection request to a shard using the unique SHA-256 ID of a sharding key (instead of a sharding key value).
Purpose
If you are using hashing to encrypt sharding keys, then use this parameter to specify the unique SHA256 hash value assigned to a sharding key. Based on the value specified during the database connection request, the request is directly routed to the appropriate shard.
Usage Notes
You use this parameter for directory-based sharding.
Set this parameter under the CONNECT_DATA
section of a connect string or tnsnames.ora
file.
Value
SHA256 hash value of a sharding key in simplified text format. This value supports only the RAW
data type.
You must enclose the value in single quotation marks.
Default
None
Example
net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(host=sales-east1)(port=1522))
(ADDRESS=(host=sales-east2)(port=1522))
)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SHARDING_KEY_ID='7E01C6D3F5AF3116668AFB6B2376DAA457165A34020617884C216F1ADAA25C7B')
)
)
Parent topic: Connection Data Section
6.9.15 SUPER_SHARDING_KEY
Use the SUPER_SHARDING_KEY
parameter in the case of composite sharding to route the database request to a collection of shards (shardspace).
Purpose
To specify a 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
You specify this parameter under the CONNECT_DATA
section of a connect string or tnsnames.ora
file.
Use the SUPER_SHARDING_KEY
parameter to specify a 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 a super sharding key are the same as those for a sharding key.
Use the SUPER_SHARDING_KEY_B64
parameter to specify the base64-encoded binary representation of a shardspace key. This parameter supports special characters (such as " quotation mark , comma ( ) close parenthesis + plus sign).
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
SHARDING_KEY
and SUPER_SHARDING_KEY
parameter values are specified in simplified text format: net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(host=sales-east1)(port=1522))
(ADDRESS=(host=sales-east2)(port=1522))
)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
((SHARDING_KEY=40598230)(SUPER_SHARDING_KEY=gold))
)
)
Example 6-11
SHARDING_KEY_B64
and SUPER_SHARDING_KEY_B64
parameter values are encoded to base64 binary representation: net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(host=sales-east1)(port=1522))
(ADDRESS=(host=sales-east2)(port=1522))
)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
((SHARDING_KEY_B64=1 1 2,VVM=,OTQwMDI=)(SUPER_SHARDING_KEY_B64=1 1,BBWEPGRBBDOEMGQW))
)
)
Parent topic: Connection Data Section
6.9.16 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)))
Parent topic: Connection Data Section
6.9.17 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)))
Related Topics
Parent topic: Connection Data Section
6.9.18 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 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)
(TUNNEL_SERVICE_NAME=south)))
Related Topics
Parent topic: Connection Data Section
6.10 Security Section
The security section of the tnsnames.ora
file specifies these security-related parameters for use with Oracle security features.
- AUTHENTICATION_SERVICE
Use thetnsnames.ora
parameterAUTHENTICATION_SERVICE
to enable an authentication service. - AZURE_DB_APP_ID_URI
Use theAZURE_DB_APP_ID_URI
parameter to specify the application ID URI of the Oracle Database instance, registered with Microsoft Entra ID (previously called Microsoft Azure Active Directory). - CLIENT_CERTIFICATE
Use theCLIENT_CERTIFICATE
parameter to specify the file system path to a client certificate that authenticates your database client application. - CLIENT_ID
Use theCLIENT_ID
parameter to specify the ID of the database client Microsoft Entra ID app registration. - IGNORE_ANO_ENCRYPTION_FOR_TCPS
TheIGNORE_ANO_ENCRYPTION_FOR_TCPS
parameter specifies if theSQLNET.ENCRYPTION_CLIENT
parameter should be ignored for this specific TNS alias. - KERBEROS5_CC_NAME
Use thetnsnames.ora
parameterKERBEROS5_CC_NAME
to specify the complete path name to the Kerberos credentials cache (CC) file. - KERBEROS5_PRINCIPAL
Use theKERBEROS5_PRINCIPAL
parameter to set the Kerberos principal name associated with the Kerberos credentials cache (CC) file. - OCI_COMPARTMENT
Use theOCI_COMPARTMENT
parameter to specify Oracle Cloud Identifier (OCID) of the compartment that holds database instances for client connections. - OCI_CONFIG_FILE
Use theOCI_CONFIG_FILE
parameter to specify the directory location where the Oracle Cloud Infrastructure (OCI) configuration file is stored. - OCI_DATABASE
Use theOCI_DATABASE
parameter to specify Oracle Cloud Identifier (OCID) of the database that you want to access for the client connection. - OCI_IAM_URL
Use theOCI_IAM_URL
parameter to specify an endpoint URL that the database client must connect with to get the database token for authenticating Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users on OCI Database as a Service (DBaaS). - OCI_PROFILE
Use theOCI_PROFILE
parameter to specify the profile name for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users. - OCI_TENANCY
Use theOCI_TENANCY
parameter to specify Oracle Cloud Identifier (OCID) of the user’s tenancy. - PASSWORD_AUTH
With this setting, client connections use the IAM user name and IAM database password for logging in users to the database. - REDIRECT_URI
Use theREDIRECT_URI
parameter to specify the redirect URI, registered for your Microsoft Entra ID client application. - SECURITY
Use theSECURITY
parameter to change the security properties of a connection. - SSL_CERTIFICATE_ALIAS
Use thesqlnet.ora
ortnsnames.ora
parameterSSL_CERTIFICATE_ALIAS
to specify the certificate alias to use in Transport Layer Security (TLS) connections. - SSL_CERTIFICATE_THUMBPRINT
Use thesqlnet.ora
ortnsnames.ora
parameterSSL_CERTIFICATE_THUMBPRINT
to specify the certificate thumbprint to use in Transport Layer Security (TLS) connections. - SSL_CLIENT_AUTHENTICATION
Use theSSL_CLIENT_AUTHENTICATION
parameter to specify whether the database client is authenticated using Transport Layer Security (TLS). - SSL_DISABLE_WEAK_EC_CURVES
Use theSSL_DISABLE_WEAK_EC_CURVES
parameter to disable the use of weak Elliptic Curve Cryptography (ECC) curves. - SSL_SERVER_CERT_DN
Use theSSL_SERVER_CERT_DN
parameter to specify the distinguished name (DN) of the database server. - SSL_SERVER_DN_MATCH
Use theSSL_SERVER_DN_MATCH
parameter to enforce server-side certificate validation through distinguished name (DN) matching. - SSL_VERSION
Use theSSL_VERSION
parameter to define valid Transport Layer Security (TLS) versions to be used for connections. - TENANT_ID
Use theTENANT_ID
parameter to specify the ID of your Microsoft Entra ID tenant. - TOKEN_AUTH
Use theTOKEN_AUTH
parameter to configure token-based authentication for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) or Microsoft Azure users of Microsoft Entra ID (previously called Microsoft Azure Active Directory). - TOKEN_LOCATION
Use theTOKEN_LOCATION
parameter to specify the directory location where token file is stored for token-based authentication. - WALLET_LOCATION
Use theWALLET_LOCATION
parameter in thetnsnames.ora
file to specify different locations where Oracle wallets are stored.
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.10.1 AUTHENTICATION_SERVICE
Use the tnsnames.ora
parameter AUTHENTICATION_SERVICE
to enable an authentication service.
Purpose
To enable an authentication service. If you have installed authentication, then Oracle recommends that you set AUTHENTICATION_SERVICE
to either NONE
or to one of the listed authentication methods.
Usage Notes
-
Use this parameter in the
SECURITY
section of thetnsnames.ora
file or directly as part of the connect string.You can also set this value in the
sqlnet.ora
file. TheAUTHENTICATION_SERVICE
parameter is equivalent to thesqlnet.ora
parameterSQLNET.AUTHENTICATION_SERVICES
. The parameter value specified in the connect string takes precedence.Note that the
AUTHENTICATION_SERVICE
parameter can take only a single authentication service, unlike theSQLNET.AUTHENTICATION_SERVICES
parameter that can take multiple values. -
When using the
AUTHENTICATION_SERVICE=ALL
, the server attempts to authenticate using each of the following methods:-
Authentication based on a service external to the database, such as a service on the network layer, Kerberos, or RADIUS.
-
Authentication based on the operating system user's membership in an administrative operating system group. Group names are platform-specific. This authentication applies to administrative connections only.
-
Authentication performed by the database.
-
Authentication based on credentials stored in a directory server.
The server falls back to the authentication methods that appear further down on the list if attempts to use the authentication methods appearing higher on the list were unsuccessful.
-
-
When using local database password authentication (no external authentication), set
AUTHENTICATION_SERVICE=NONE
for better client performance. -
Operating system authentication enables access to the database using any user name and any password when an administrative connection is attempted to the CDB root, such as using the
AS SYSDBA
clause when connecting using SQL*Plus.An example of a connection to the CDB root is as follows.
sqlplus ignored_username/ignored_password AS SYSDBA
When the operating-system user who issued the preceding command is already a member of the appropriate administrative operating system group, then the connection is successful. This is because Oracle checks the group membership first, and thus the user name and password are ignored by the server.
Default
ALL
Note:
When installing Oracle Database with Database Configuration Assistant (DBCA), you can set this parameter to NTS
in the sqlnet.ora
file.
Values
Authentication methods that are available with Oracle Net Services:
-
NONE
for no authentication methods, including Microsoft Windows native operating system authentication. When you setAUTHENTICATION_SERVICE
toNONE
, then the user can use a valid user name and password to access the database. -
ALL
for all authentication methods. -
BEQ
for native operating system authentication for operating systems other than Microsoft Windows. -
KERBEROS5
for Kerberos authentication. -
RADIUS
for Remote Authentication Dial-In User Service (RADIUS) authentication. -
TCPS
for TLS authentication. -
NTS
for Microsoft Windows native operating system authentication. In this case, the user must authenticate to the database (CDB root) with OS credentials using Windows native authentication. No external password is needed. NTS checks the group membership for an OS user. For example, if an OS user is a member of theORA_DBA
group, then the user can log in to the database asSYSDBA
.Note:
With the
AUTHENTICATION_SERVICE=NTS
setting, if you try to connect through SQL*Plus using NTS authentication and specify an external password (for example,SQL*Plus SYSTEM/password
), then the connection fails with anORA-12638, 00000, "Failed to retrieve credentials for adapter_name.
error message. For regular user name and password based authentication, set the value toNONE
.
Example
net_service_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=(AUTHENTICATION_SERVICE=KERBEROS5))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
Related Topics
Parent topic: Security Section
6.10.2 AZURE_DB_APP_ID_URI
Use the AZURE_DB_APP_ID_URI
parameter to specify the application ID URI of the Oracle Database instance, registered with Microsoft Entra ID (previously called Microsoft Azure Active Directory).
Purpose
To specify the application ID URI that uniquely identifies your database instance in Entra ID.
$Scope = "database_app_id_uri/scope"
$Scope = "https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3/session:scope:connect"
Here, the app ID URI https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3
is part of the scope.
Usage Notes
This parameter is mandatory. You must set it along with the TOKEN_AUTH
parameter for the AZURE_INTERACTIVE
, AZURE_SERVICE_PRINCIPAL
, AZURE_MANAGED_IDENTITY
, and AZURE_DEVICE_CODE
authentication flows.
For the JDBC-thin clients, you can specify this parameter in the connect string, Easy Connect syntax, tnsnames.ora
file, or properties. For the thick clients (OCI and Instant Client) and ODP.NET core and managed database clients, you can specify this parameter in the connect string, sqlnet.ora
file, Easy Connect syntax, or tnsnames.ora
file. The parameter value specified in the connect string takes precedence.
Default
None
Value
You can get the application ID URI value by logging in to the Azure portal. This is listed as the Application ID URI value on the App registrations - Overview page.
Note that this is the value that you specified while registering your Oracle Database instance with the Entra ID tenancy, as shown in Oracle Database Security Guide.
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=AZURE_INTERACTIVE)
(AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
TOKEN_AUTH=AZURE_INTERACTIVE
AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=AZURE_INTERACTIVE&AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3
In these examples, the CLIENT_ID
, TENANT_ID
, and REDIRECT_URI
parameters are not specified. CLIENT_ID
and TENANT_ID
are required parameters when using the thick clients (OCI and Instant Client). These parameters are optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get these values from the Azure SDK configuration.
Related Topics
Parent topic: Security Section
6.10.3 CLIENT_CERTIFICATE
Use the CLIENT_CERTIFICATE
parameter to specify the file system path to a client certificate that authenticates your database client application.
Purpose
File system path to a client certificate that authenticates your database client application in Microsoft Entra ID. A client certificate is the digital certificate of an Azure cloud resource, and the client uses this certificate as a credential to prove its identity when requesting an Entra ID access token. This is used for the AZURE_SERVICE_PRINCIPAL
token-based authentication flow.
Note:
Only the JDBC-thin clients and ODP.NET core and managed database clients (and not the thick clients, such as OCI and Instant Client) support certificate-based authentication.Usage Notes
-
The Entra ID client uses a client ID and a client secret to retrieve the Entra ID
OAuth2
database access token. If a client secret is not configured, then the client driver automatically reads the file system path of a client certificate from theAZURE_CLIENT_CERTIFICATE_PATH
environment variable in the Azure SDK configuration. If the application client is public, then it uses only a client ID.For more information about using certificates with service principals, review Microsoft documentation.
-
This parameter is optional. You can set it if you have not configured the SDKs.
Note that this parameter is ignored if the client driver is configured with a client secret.
-
You can specify this parameter along with the
TOKEN_AUTH=AZURE_SERVICE_PRINCIPAL
setting in the connect string, Easy Connect syntax, ortnsnames.ora
file. The parameter value specified in the connect string takes precedence. -
The supported formats for a certificate file are:
-
.pem
(Privacy Enhanced Mail) -
.pfx
(Personal Information Exchange)This format is password-protected. If the file is in a
.pfx
format, then you must also set the correspondingAZURE_CLIENT_CERTIFICATE_PASSWORD
parameter.
-
-
You cannot store the certificate in an Oracle Wallet or Azure Key Vault.
Because this certificate is a credential for accessing the database, you must protect it on your file system.
Default
None
Value
Full path (including a file name) to the Azure certificate file
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=AZURE_SERVICE_PRINCIPAL)
(AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3)
(CLIENT_CERTIFICATE=ORACLE_HOME/.azure/certificates/my-app.pem))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=AZURE_SERVICE_PRINCIPAL&AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3&CLIENT_CERTIFICATE=ORACLE_HOME/.azure/certificates/my-app.pem
In these examples, the CLIENT_ID
and TENANT_ID
parameters are not specified. These parameters are optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get these values from the Azure SDK configuration.
Related Topics
Parent topic: Security Section
6.10.4 CLIENT_ID
Use the CLIENT_ID
parameter to specify the ID of the database client Microsoft Entra ID app registration.
Purpose
To specify the client ID assigned to your database client during Entra ID app registration. Note that this is not the client ID for the database server. This application is your database client that requests to get an access token for the user during Azure token-based authentication.
Usage Notes
You use this parameter along with the TOKEN_AUTH
parameter for the AZURE_INTERACTIVE
, AZURE_SERVICE_PRINCIPAL
, AZURE_MANAGED_IDENTITY
, and AZURE_DEVICE_CODE
authentication flows, as follows:
-
The
AZURE_MANAGED_IDENTITY
authentication flow is applicable to client-side or server-side applications hosted on Azure environments, such as Azure App Service or Azure virtual machine.When using the JDBC-thin clients and ODP.NET core and managed database clients, the client driver uses a system-assigned managed identity. A system-assigned managed identity is an implicit identity assigned by Entra ID to your application, and is configured in the Azure SDK by default. Optionally, you can set this parameter to explicitly assign the client ID of a user-assigned managed identity to your application.
When using the thick clients (OCI and Instant Client), which do not use the Azure SDKs, you must set this parameter to assign a user-assigned managed identity to your application.
-
For other authentication flows, when using the JDBC-thin clients and ODP.NET core and managed database clients, the client driver searches for the client ID in the Azure SDK configuration. In this case, this parameter is optional.
When using the OCI and Instant Clients, you must set this parameter (along with other required parameters, such as
TENANT_ID
). Otherwise, an error message appears prompting you to configure the required parameters.
Note that this parameter is mandatory for the OCI and Instant Clients. It is optional only when using the JDBC-thin clients and ODP.NET core and managed database clients.
For the JDBC-thin clients, you can specify this parameter in the connect string, Easy Connect syntax, tnsnames.ora
file, or properties. For the thick clients (OCI and Instant Client) and ODP.NET core and managed database clients, you can specify this parameter in the connect string, sqlnet.ora
file, Easy Connect syntax, or tnsnames.ora
file. The parameter value specified in the connect string takes precedence.
Default
None
Value
You can get the client ID value by logging in to the Azure portal. This is listed as the Application (client) ID value on the App registrations - Overview page.
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=AZURE_INTERACTIVE)
(AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3)
(CLIENT_ID=123ab4cd-1a2b-1234-a12b-aa00123b2cd3)
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
TOKEN_AUTH=AZURE_INTERACTIVE
AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3
CLIENT_ID=123ab4cd-1a2b-1234-a12b-aa00123b2cd3
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=AZURE_INTERACTIVE&AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3&CLIENT_ID=123ab4cd-1a2b-1234-a12b-aa00123b2cd3
In these examples, the TENANT_ID
and REDIRECT_URI
parameters are not specified. TENANT_ID
is required when using the thick clients (OCI and Instant Client). This parameter is optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get this value from the Azure SDK configuration.
Related Topics
Parent topic: Security Section
6.10.5 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))
)
Parent topic: Security Section
6.10.6 KERBEROS5_CC_NAME
Use the tnsnames.ora
parameter KERBEROS5_CC_NAME
to specify the complete path name to the Kerberos credentials cache (CC) file.
Purpose
To specify the complete path name to the Kerberos CC file.
Usage Notes
-
Use this parameter in the
SECURITY
section of thetnsnames.ora
file or directly as part of the connect string.You can also set this value in the
sqlnet.ora
file. TheKERBEROS5_CC_NAME
parameter is equivalent to thesqlnet.ora
parameterSQLNET.KERBEROS5_CC_NAME
. The parameter value specified in the connect string takes precedence. -
This parameter supports multiple principals for the storage of credentials that are returned by KDC in an encrypted format.
You can use the
okinit
,oklist
, andokdstry
utilities to configure encrypted cache files for all Kerberos principals. These utilities work with encrypted cache files only if you specify the cache path usingKERBEROS5_CC_NAME
. -
KERBEROS5_CC_NAME
is mandatory for all additional Kerberos users and principals. Optionally, you can set theKERBEROS5_PRINCIPAL
parameter to specify the Kerberos principal name associated with the credential cache (specified throughKERBEROS5_CC_NAME
). You can setKERBEROS5_PRINCIPAL
in the connect string,sqlnet.ora
file, ortnsnames.ora
file.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.
Values and Examples
KERBEROS5_CC_NAME
:
-
If the Oracle database is using a directory cache:
-
KERBEROS5_CC_NAME=complete_path_to_cc_file
For example:
KERBEROS5_CC_NAME=/tmp/kcache
KERBEROS5_CC_NAME=D:\tmp\kcache
-
KERBEROS5_CC_NAME=FILE:complete_path_to_cc_ file
For example:
KERBEROS5_CC_NAME=FILE:/tmp/kcache
-
-
If the Oracle database is using the native Windows cache:
-
KERBEROS5_CC_NAME=OSMSFT://
-
KERBEROS5_CC_NAME=MSLSA:
The
OSMSFT
andMSLSA
options specify that the file is on Microsoft Windows and is running Microsoft Kerberos Key Distribution Center (KDC). -
Default
-
On Linux and UNIX operating systems:
/tmp/krb5cc_userid
-
On Microsoft Windows operating systems:
c:\tmp\krbcache
Parent topic: Security Section
6.10.7 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 KERBEROS5_CC_NAME
) matches the parameter value.
Usage Notes
Use this parameter in the SECURITY
section of the tnsnames.ora
file, or set it in the sqlnet.ora
file. Alternatively, you can set KERBEROS5_PRINCIPAL
in the connect string along with the KERBEROS5_CC_NAME
parameter to connect as a different Kerberos principal.
The parameter value specified in the connect string takes precedence over the value specified in the sqlnet.ora
or tnsnames.ora
file.
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.
Examples
-
For a user
krbuser1
, who is externally authenticated using the Kerberos principalkrbprinc1@example.com
and the credential cache for this principal is located at/tmp/krbuser1/krb.cc
, the connect descriptor in thetnsnames.ora
file 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=krbprinc1@example.com)))
In thesqlnet.ora
file:SQLNET.KERBEROS5_CC_NAME=/tmp/krbuser1/krb.cc KERBEROS5_PRINCIPAL=krbprinc1@example.com
-
For a user
krbuser2
, who is externally authenticated using the Kerberos principalkrbprinc2@example.com
and the credential cache for this principal is located at/tmp/krbuser2/krb.cc
, the connect descriptor in thetnsnames.ora
file 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=krbprinc2@example.com)))
Insqlnet.ora
file:SQLNET.KERBEROS5_CC_NAME=/tmp/krbuser2/krb.cc KERBEROS5_PRINCIPAL=krbprinc2@example.com
Note:
The connection fails if the principal in the/tmp/krbuser1/krb.cc
file does not contain the krbprinc1@example.com
value.
Parent topic: Security Section
6.10.8 OCI_COMPARTMENT
Use the OCI_COMPARTMENT
parameter to specify Oracle Cloud Identifier (OCID) of the compartment that holds database instances for client connections.
Purpose
To define the scope of your database token request. This value instructs the database client to initiate a token request to databases within the specified compartment only. You use this parameter while configuring token-based authentication for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users on OCI Database as a Service (DBaaS).
Usage Notes
The OCI_COMPARTMENT
parameter is optional if you have not specified the OCI_DATABASE
parameter. If you choose to set OCI_DATABASE
, then you must also set OCI_COMPARTMENT
to limit your token request to the specified database within that compartment.
If you do not set both the OCI_COMPARTMENT
and OCI_DATABASE
parameters, then the entire tenancy is the scope of your token request.
PASSWORD_AUTH
and TOKEN_AUTH
authentication settings:
-
With the
PASSWORD_AUTH
configuration, the database client can only request an IAM database token using the IAM user name and IAM database password. - With the
TOKEN_AUTH
configuration, the database client can request an IAM database token using the API-key, delegation token, security token, resource principal, or instance principal credentials. You can also enable the database client to directly retrieve thedb-token
with IAM Single-Sign On (SSO) credentials by using theOCI_INTERACTIVE
,OCI_API_KEY
,OCI_INSTANCE_PRINCIPAL
,OCI_DELEGATION_TOKEN
, andOCI_RESOURCE_PRINCIPAL
authentication flows.
Use this parameter under the SECURITY
section of the tnsnames.ora
file, sqlnet.ora
file, Easy Connect syntax, or directly as part of the command-line connect string. The parameter value specified in the connect string takes precedence over the other specified values.
Default
None
Value
OCID for the IAM compartment to allow access for the database token. You can get the OCID value for your compartment from the Compartments information page in the OCI console.
The compartment OCID uses this syntax:
OCI_COMPARTMENT=compartment_OCID
For details on the syntax options, see Oracle Cloud IDs (OCIDs).
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(PASSWORD_AUTH=OCI_TOKEN)
(OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken)
(OCI_TENANCY=ocid1.tenancy..12345)
(OCI_COMPARTMENT=ocid1.compartment..12345)
(OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
PASSWORD_AUTH=OCI_TOKEN
OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken
OCI_TENANCY=ocid1.tenancy..12345
OCI_COMPARTMENT=ocid1.compartment..12345
OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=OCI_INTERACTIVE&OCI_COMPARTMENT=ocid1.compartment..12345&OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345
Related Topics
Parent topic: Security Section
6.10.9 OCI_CONFIG_FILE
Use the OCI_CONFIG_FILE
parameter to specify the directory location where the Oracle Cloud Infrastructure (OCI) configuration file is stored.
Purpose
To specify the directory location of the OCI configuration file. This file stores the client connection information for OCI Identity and Access Management (IAM) users as part of their profile. The SDK, CLI, and other OCI tools use this file to access the IAM user credentials during IAM token-based authentication.
Usage Notes
This is an optional parameter. If you do not set this parameter, then the database client gets the user's profile from the default configuration file located at C:/user-profile/.oci/config
. You can use this parameter to override the default configuration file location. In this case, the database client searches for the profile in the location specified by OCI_CONFIG_FILE
.
TOKEN_AUTH
parameter for the OCI_API_KEY
and OCI_INTERACTIVE
authentication flows:
-
When using the
OCI_INTERACTIVE
authentication flow, if this parameter is not set and the configuration file is also not present in the default location, then Oracle Database prompts the user for a region ID, presenting a list of region IDs from which the user can choose. -
When using the
OCI_API_KEY
authentication flow, if this parameter is not set and the default configuration file is also not present, then an ORA-50109 error message is returned. In this case, you must set this parameter to include the configuration file location.
For JDBC-thin clients, you can specify this parameter in the Easy Connect syntax or tnsnames.ora
connect string. For ODP.NET Core classes and ODP.NET Managed Driver classes, you can specify this parameter in the sqlnet.ora
file, Easy Connect syntax, or tnsnames.ora
connect string. The parameter value specified in the connect string takes precedence.
Default
None
Value
Full path (including a file name) to the OCI configuration file
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=OCI_INTERACTIVE)
(OCI_CONFIG_FILE=/home/dbuser1/config))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
TOKEN_AUTH=OCI_INTERACTIVE
OCI_CONFIG_FILE=/home/dbuser1/config
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=OCI_INTERACTIVE&OCI_CONFIG_FILE=/home/dbuser1/config
In these examples, the optional OCI_PROFILE
parameter is not specified. Thus, the client automatically gets the DEFAULT
profile from the specified configuration file directory.
Related Topics
Parent topic: Security Section
6.10.10 OCI_DATABASE
Use the OCI_DATABASE
parameter to specify Oracle Cloud Identifier (OCID) of the database that you want to access for the client connection.
Purpose
To define the scope of your database token request. The database OCID value instructs the database client to initiate a token request to the specified database within your compartment. You use this parameter while configuring token-based authentication for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users on OCI Database as a Service (DBaaS).
Usage Notes
This is an optional parameter. You can set this parameter to limit the access to only a particular database. If you set OCI_DATABASE
, then you must also provide specific compartment identifier using the OCI_COMPARTMENT
parameter.
PASSWORD_AUTH
and TOKEN_AUTH
authentication settings:
-
With the
PASSWORD_AUTH
configuration, the database client can only request an IAM database token using the IAM user name and IAM database password. - With the
TOKEN_AUTH
configuration, the database client can request an IAM database token using the API-key, delegation token, security token, resource principal, or instance principal credentials. You can also enable the database client to directly retrieve thedb-token
with IAM Single-Sign On (SSO) credentials by using theOCI_INTERACTIVE
,OCI_API_KEY
,OCI_INSTANCE_PRINCIPAL
,OCI_DELEGATION_TOKEN
, andOCI_RESOURCE_PRINCIPAL
authentication flows.
Specify this parameter under the SECURITY
section of the tnsnames.ora
file, sqlnet.ora
file, Easy Connect syntax, or directly as part of the command-line connect string. The parameter value specified in the connect string takes precedence.
Default
None
Value
OCID of the database that you want to access for the client connection. You can get the OCID value for your database from the Database details page in the OCI console.
The database OCID uses this syntax:
OCI_DATABASE=database_OCID
For details on the syntax options, see Oracle Cloud IDs (OCIDs).
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(PASSWORD_AUTH=OCI_TOKEN)
(OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken)
(OCI_TENANCY=ocid1.tenancy..12345)
(OCI_COMPARTMENT=ocid1.compartment..12345)
(OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
PASSWORD_AUTH=OCI_TOKEN
OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken
OCI_TENANCY=ocid1.tenancy..12345
OCI_COMPARTMENT=ocid1.compartment..12345
OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=OCI_INTERACTIVE&OCI_COMPARTMENT=ocid1.compartment..12345&OCI_DATABASE=ocid1.autonomousdatabase.oc1.12345
Related Topics
Parent topic: Security Section
6.10.11 OCI_IAM_URL
Use the OCI_IAM_URL
parameter to specify an endpoint URL that the database client must connect with to get the database token for authenticating Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users on OCI Database as a Service (DBaaS).
Purpose
To specify the IAM URL for your REST API requests. The database client connects to this URL to retrieve the database token from IAM.
Usage Notes
You set the OCI_IAM_URL
parameter along with the PASSWORD_AUTH
and OCI_TENANCY
parameters while configuring IAM token-based authentication (using the IAM user name and IAM database password to retrieve the database token). These parameters are mandatory.
With this configuration, the database client can only request an IAM database token using the IAM user name and IAM database password. The client cannot request an IAM database token for an API-key, delegation token, security token, resource principal, service principal, or instance principal.
You can also set the optional OCI_COMPARTMENT
and OCI_DATABASE
parameters to specify the scope of your token request.
Use this parameter under the SECURITY
section of the tnsnames.ora
file, sqlnet.ora
file, or directly as part of the command-line connect string. The parameter value specified in the connect string takes precedence over the other specified values.
Default
None
Value
<authentication_regional_endpoint>/v1/actions/generateScopedAccessBearerToken
You can derive this value by replacing <authentication_regional_endpoint> with the API endpoint URL for your region. To obtain the appropriate API endpoint URL, see Identity and Access Management Data Plane API.
https://auth.us-region-1.example.com
, then your OCI_IAM_URL value is:https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(PASSWORD_AUTH=OCI_TOKEN)
(OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken)
(OCI_TENANCY=ocid1.tenancy..12345))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
PASSWORD_AUTH=OCI_TOKEN
OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken
OCI_TENANCY=ocid1.tenancy..12345
In these examples, the optional OCI_COMPARTMENT
and OCI_DATABASE
parameters are not specified and thus the entire tenancy is set as the scope of the token request.
Related Topics
Parent topic: Security Section
6.10.12 OCI_PROFILE
Use the OCI_PROFILE
parameter to specify the profile name for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users.
Purpose
To specify the profile name for IAM users. This profile is the client connection information stored in the OCI configuration file, and is used during IAM token-based authentication.
Usage Notes
-
This is an optional parameter. A profile named
DEFAULT
is already set in the configuration file. The database client gets theDEFAULT
profile from the OCI configuration file (from either the defaultC:/user-profile/.oci/config
directory location or the location specified byOCI_CONFIG_FILE
). -
You can specify this parameter to override the
DEFAULT
profile set in the configuration file and assign a new profile name for the IAM user.When this parameter is not set and the profile is also not present in the configuration file, then an error message appears indicating that token-based authentication has failed due to the profile not existing.
-
You can use this parameter along with the
TOKEN_AUTH
parameter for theOCI_API_KEY
andOCI_INTERACTIVE
authentication flows.For JDBC-thin clients, you can specify this parameter in the Easy Connect syntax or
tnsnames.ora
connect string. For ODP.NET Core classes and ODP.NET Managed Driver classes, you can specify this parameter in thesqlnet.ora
file, Easy Connect syntax, ortnsnames.ora
connect string. The parameter value specified in the connect string takes precedence.
Values
-
DEFAULT
: This means that no value is explicitly defined for a given profile, and the profile is inherited from the default profile set in the configuration file. -
profile_name
: Specify a new configuration profile name (for example,ADMIN_USER
) to override theDEFAULT
profile set in the configuration file.
Default
DEFAULT
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=OCI_INTERACTIVE)
(OCI_CONFIG_FILE=/home/dbuser1/config))
(OCI_PROFILE=ADMIN_USER))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
TOKEN_AUTH=OCI_INTERACTIVE
OCI_CONFIG_FILE=/home/dbuser1/config
OCI_PROFILE=ADMIN_USER
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=OCI_INTERACTIVE&OCI_CONFIG_FILE=/home/dbuser1/config&OCI_PROFILE=ADMIN_USER
Related Topics
Parent topic: Security Section
6.10.13 OCI_TENANCY
Use the OCI_TENANCY
parameter to specify Oracle Cloud Identifier (OCID) of the user’s tenancy.
Purpose
To specify OCID of the user’s tenancy (root compartment).
Usage Notes
You set this parameter along with the mandatory PASSWORD_AUTH
and OCI_IAM_URL
parameters while configuring token-based authentication for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users on OCI Database as a Service (DBaaS).
With this configuration, the database client can only request an IAM database token using the IAM user name and IAM database password. The client cannot request an IAM database token for an API-key, delegation token, security token, resource principal, service principal, or instance principal.
You can also set the optional OCI_COMPARTMENT
and OCI_DATABASE
parameters to specify the scope of your token request. If you do not set the OCI_COMPARTMENT
and OCI_DATABASE
parameter values, then the entire tenancy is the scope of your token request.
Use this parameter under the SECURITY
section of the tnsnames.ora
file, sqlnet.ora
file, or directly as part of the command-line connect string. The parameter value specified in the connect string takes precedence over the other specified values.
Default
None
Value
OCID of the user’s tenancy. You can get the OCID value for your tenancy from the Tenancy information page in the OCI console.
The tenancy OCID uses this syntax:
OCI_TENANCY=tenancy_OCID
For details on the syntax options, see Oracle Cloud IDs (OCIDs).
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(PASSWORD_AUTH=OCI_TOKEN)
(OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken)
(OCI_TENANCY=ocid1.tenancy..12345))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
PASSWORD_AUTH=OCI_TOKEN
OCI_IAM_URL=https://auth.us-region-1.example.com/v1/actions/generateScopedAccessBearerToken
OCI_TENANCY=ocid1.tenancy..12345
In these examples, the optional OCI_COMPARTMENT
and OCI_DATABASE
parameters are not specified and thus the entire tenancy is set as the scope of the token request.
Related Topics
Parent topic: Security Section
6.10.14 PASSWORD_AUTH
PASSWORD_AUTH
parameter to configure an authentication method for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users on OCI Database as a Service (DBaaS). With this setting, client connections use the IAM user name and IAM database password for logging in users to the database. Purpose
To configure either IAM database password verifier authentication or IAM token-based authentication, using the IAM user name and IAM database password for the access.
For password verifier authentication, the database server retrieves an IAM database password verifier from IAM. For token-based authentication, the database client requests a database token (db-token
) from IAM.
Usage Notes
-
Use this parameter under the
SECURITY
section of thetnsnames.ora
file,sqlnet.ora
file, or directly as part of the command-line connect string. The parameter value specified in the connect string takes precedence over the other specified values. -
This setting instructs the database client to either use the existing password login process with the database server (password verifier authentication) or to get a token with the IAM user name and IAM database password (token-based authentication). This IAM database password is different from the OCI console password. An IAM user can set this password from the OCI console.
See Create an OCI IAM password to use for Autonomous Databases User Authentication and Authorization.
-
By default, this parameter is set to
PASSWORD_VERIFIER
. ThePASSWORD_AUTH=PASSWORD_VERIFIER
setting configures IAM database password verifier authentication. The database server retrieves an IAM database password verifier (an encrypted hash of password) from IAM to authenticate users.When an IAM user logs in with the IAM user name and IAM database password using
@connect_identifier
, thePASSWORD_AUTH=PASSWORD_VERIFIER
setting along with@connect_identifier
instructs the database client to follow the existing user name and password login process with the database server.You can use the
PASSWORD_AUTH
parameter to override thetnsnames.ora
orsqlnet.ora
setting by specifying a different value in the connect string. -
To configure IAM token-based authentication with the IAM user name and IAM database password, set
PASSWORD_AUTH=OCI_TOKEN
. The database client requests a database token (db-token
) from IAM for the user to access the database.This
db-token
obtained by the client is a bearer token with an expiration time and scope, and does not come with a private key. These tokens are transmitted over secure channels. You must use only the TCP/IP with Transport Layer Security (TLS) protocol, otherwise an error message appears indicating that non-TLS connections are disallowed.When an IAM user logs in with the IAM user name and IAM database password using
/@connect_identifier
, thePASSWORD_AUTH=OCI_TOKEN
setting along with/@connect_identifier
instructs the database client to get the token directly from an OCI IAM endpoint using a REST API request. If the IAM user is mapped to a database schema (exclusively or shared), then the login is completed.For the database client to retrieve the token from IAM, you must set additional parameters so that the database client can find the IAM endpoint along with additional meta-data. The additional parameters are
OCI_IAM_URL
andOCI_TENANCY
along with the optionalOCI_COMPARTMENT
andOCI_DATABASE
. These values enable the database client to make appropriate calls to the specified endpoint.The
OCI_IAM_URL
parameter specifies the API endpoint URL that the database client must connect with. TheOCI_TENANCY
parameter specifies the OCID (Oracle Cloud Identifier) of the user’s tenancy. The optionalOCI_COMPARTMENT
andOCI_DATABASE
parameters limit the scope of your request.This authentication method is more secure than using a password verifier because a password verifier is considered sensitive. Also, only the database client can retrieve the database token. Applications or tools cannot pass these types of tokens through the database client API.
Note:
You can also use other IAM user credentials (such as API-key, security token, resource principal, service principal, instance principal, or delegation token) to get the db-token
. This db-token
is a proof-of-possession (PoP) token. In this case, you use a different parameter setting (TOKEN_AUTH=OCI_TOKEN
).
Unlike the IAM database password that can only be used by the database client to retrieve the token, these credentials require an application or tool to retrieve the token. See TOKEN_AUTH.
Default
PASSWORD_VERIFIER
Values and Examples
Value | Example |
---|---|
For IAM database password verifier authentication:
Note: Use of IAM user name and IAM database password with the IAM database password verifier is the default configuration, and you do not need to set any additional parameters for the client. However, if |
In the
tnsnames.ora file:
In the
sqlnet.ora file:
|
For IAM token-based authentication with the IAM user name and IAM database password:
Note: You must configure the TCPS protocol ( |
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the optional |
Parent topic: Security Section
6.10.15 REDIRECT_URI
Use the REDIRECT_URI
parameter to specify the redirect URI, registered for your Microsoft Entra ID client application.
Purpose
To specify the redirect URI (or reply URL), registered for your Entra ID client application. This is used for the AZURE_INTERACTIVE
token-based authentication flow. This URL obtains the authorization code from the Entra authentication endpoint and determines which port to use to receive the authorization code.
Usage Notes
This is an optional parameter. If you do not specify this parameter, then it uses the default value of http://localhost
, which is the most common redirect URL.
Specify this parameter only if necessary for your use case. The authorization server redirects the user to your specified address only if you have registered the redirect URI for the client application in the Azure portal, as shown in Oracle Database Security Guide.
You can specify this parameter along with the TOKEN_AUTH=AZURE_INTERACTIVE
setting in the connect string, Easy Connect syntax, or tnsnames.ora
file. The parameter value specified in the connect string takes precedence.
Default
http://localhost
Value
You can get a redirect URI value by logging in to the Azure portal. All URI values are listed as Redirect URIs on the App registrations - Authentication page of your Entra ID service.
Note that this is the value that you specified while registering your database client application with Entra ID.
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=AZURE_INTERACTIVE)
(AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3)
(REDIRECT_URI=http://localhost:1575))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=AZURE_INTERACTIVE&AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3&REDIRECT_URI=http://localhost:1575
In these examples, the CLIENT_ID
and TENANT_ID
parameters are not specified. CLIENT_ID
and TENANT_ID
are required parameters when using the thick clients (OCI and Instant Client). These parameters are optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get these values from the Azure SDK configuration.
Related Topics
Parent topic: Security Section
6.10.16 SECURITY
Use the SECURITY
parameter to change the security properties of a connection.
Purpose
To change the security properties of a connection.
Usage Notes
Put this parameter under the DESCRIPTION
parameter. SECURITY
permits additional parameters as listed in Security Section.
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")))
Parent topic: Security Section
6.10.17 SSL_CERTIFICATE_ALIAS
Use the sqlnet.ora
or tnsnames.ora
parameter SSL_CERTIFICATE_ALIAS
to specify the certificate alias to use in Transport Layer Security (TLS) connections.
Purpose
To specify the alias that you provided when storing the client or server certificate in an Oracle Database wallet.
When encrypting TLS connections, both the database client and database server need to provide a signed certificate. You can store this certificate in an Oracle Database wallet or Microsoft Certificate Store (MCS). If there is more than one certificate that can be used, the user or application settings can specify the particular certificate to connect with. This choice can be made manually by the user via graphical user interface (GUI) or automatically by the application using a thumbprint or alias name. A thumbprint or alias name can uniquely identify the certificate.
This parameter instructs the client or server to automatically select a particular certificate using the specified alias name. Thus, the user does not need to manually select the correct client certificate from the list available in a wallet.
Usage Notes
Use this parameter in the tnsnames.ora
file, sqlnet.ora
file, or directly as part of the command-line connect string. The parameter values specified in the connect string take precedence over the other specified values.
orapki
helps you manage certificates and wallets for Oracle Database. To get the alias name value, run the following command: orapki wallet display -wallet <wallet directory> -pwd <wallet password> -complete
Value
Certificate alias name
Default
None
Examples
-
In the
tnsnames.ora
file:net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521)) (SECURITY=(SSL_CERTIFICATE_ALIAS=my_cert)) )
-
In the Easy Connect string:
tcps://salesserver:1521/sales.us.example.com?SSL_CERTIFICATE_ALIAS=my_cert
-
In the
sqlnet.ora
file:SSL_CERTIFICATE_ALIAS=my_cert
Related Topics
Parent topic: Security Section
6.10.18 SSL_CERTIFICATE_THUMBPRINT
Use the sqlnet.ora
or tnsnames.ora
parameter SSL_CERTIFICATE_THUMBPRINT
to specify the certificate thumbprint to use in Transport Layer Security (TLS) connections.
Purpose
To specify the thumbprint signature for an X509 certificate. These thumbprints are automatically generated for certificates.
When encrypting TLS connections, both the database client and database server need to provide a signed certificate. You can store this certificate in an Oracle Database wallet or Microsoft Certificate Store (MCS). If there is more than one certificate that can be used, the user or application settings can specify the particular certificate to connect with. This choice can be made manually by the user via graphical user interface (GUI) or automatically by the application using a thumbprint or alias name. A thumbprint or alias name can uniquely identify the certificate.
This parameter instructs the client or server to automatically select a particular certificate using the specified thumbprint. Thus, the user does not need to manually select the correct certificate from the list available in a certificate store.
Usage Notes
Use this parameter in the tnsnames.ora
file, sqlnet.ora
file, or directly as part of the command-line connect string. The parameter values specified in the connect string take precedence over the other specified values.
You can specify both the SHA-1 and SHA-256 thumbprint information for the client certificate.
orapki
helps you manage certificates and wallets for Oracle Database. To get the thumbprint value, run the following command: orapki wallet display -wallet <wallet directory> -pwd <wallet password> -complete
Value
SHA-1 or SHA-256 thumbprint of the client certificate, in the <Algorithm>:<Hash>
format
For example:
SHA1:1B:11:01:5A:B1:2C:20:B2:12:34:3E:04:7B:83:47:DE:70:2E:4E:11
SHA256:B3:8A:5B:1A:03:63:83:92:2B:5D:E1:53:61:EE:03:94:0A:56:B4:56:41:7E:41:24:41:9B:88:EB:C6:1E:11:23
or
SHA1:1B11015AB12C20B212343E047B8347DE702E4E11
SHA256:B38A5B1A036383922B5DE15361EE03940A56B456417E4124419B88EBC61E1123
Default
None
Examples
-
In the
tnsnames.ora
file:net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521)) (SECURITY=(SSL_CERTIFICATE_THUMBPRINT=SHA1:1B:11:01:5A:B1:2C:20:B2:12:34:3E:04:7B:83:47:DE:70:2E:4E:11)) )
net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521)) (SECURITY=(SSL_CERTIFICATE_THUMBPRINT=SHA1:1B11015AB12C20B212343E047B8347DE702E4E11)) )
-
In the Easy Connect string:
tcps://salesserver:1521/sales.us.example.com?SSL_CERTIFICATE_THUMBPRINT=SHA1:1B11015AB12C20B212343E047B8347DE702E4E11
-
In the
sqlnet.ora
file:SSL_CERTIFICATE_THUMBPRINT=SHA256:B38A5B1A036383922B5DE15361EE03940A56B456417E4124419B88EBC61E1123
Related Topics
Parent topic: Security Section
6.10.19 SSL_CLIENT_AUTHENTICATION
Use the SSL_CLIENT_AUTHENTICATION
parameter to specify whether the database client is authenticated using Transport Layer Security (TLS).
Purpose
To enable client authentication in a TLS connection. The connection can be one-way or two-way (mutual TLS or mTLS).
Usage Notes
When set to TRUE
, a two-way TLS connection is initiated. Both the client and server (including the listener) authenticate each other. For example, if you set this parameter to TRUE
in the server configuration (server-side sqlnet.ora
), then the server attempts to authenticate the client. If you set it to TRUE
in the listener configuration (listener.ora
), then the listener attempts to authenticate the client.
When set to FALSE
, only the client authenticates the server and listener as a one-way TLS connection. For example, if you set this parameter to FALSE
in the server configuration, then the server does not authenticate the client. If you set it to FALSE
in the listener configuration, then the listener does not authenticate the client.
OPTIONAL
, the server behaves as follows:
-
If the client sends a certificate, then the connection is completed as a two-way TLS connection after authenticating the client.
-
If the client does not send a certificate, then the connection is completed as a one-way TLS connection.
Ensure that this parameter setting is consistent for the server or listener (on one side) and the client (on the other). Otherwise, the connection may fail. For example, if you enable client authentication in the server or listener configuration, then you must enable it in the client configuration.
Default
TRUE
Values
-
TRUE
|ON
|YES
|1
: To enable mTLS -
FALSE
|OFF
|NO
|0
: To enable one-way TLS -
OPTIONAL
: To enable both TLS and mTLS
Example
SSL_CLIENT_AUTHENTICATION=FALSE
Related Topics
Parent topic: Security Section
6.10.20 SSL_DISABLE_WEAK_EC_CURVES
Use the SSL_DISABLE_WEAK_EC_CURVES
parameter to disable the use of weak Elliptic Curve Cryptography (ECC) curves.
Purpose
To disable the use of weak ECC curves with key length less than 256 bits. You can set this parameter in the database server (sqlnet.ora
), client (sqlnet.ora
or tnsnames.ora
connect string), or the listener (listener.ora
).
Usage Notes
By default, this parameter is set to FALSE
to enable the use of all ECC curves. If you want to enable the use of only Oracle approved curves with ECC curve key size of 256 bits or higher, then set this parameter to TRUE
.
TRUE
, you can use only the following ECC curves:
-
secp256r1
-
secp384r1
-
secp521r1
-
x25519
-
x448
Values
-
TRUE
|ON
|YES
|1
: To enable only the Oracle approved ECC curves with minimum ECC curve key length of 256 bits -
FALSE
|OFF
|NO
|0
: To enable all ECC curves
Default
FALSE
Examples
-
In the
tnsnames.ora
file:net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521)) (SECURITY=(SSL_DISABLE_WEAK_EC_CURVES=TRUE)) )
-
In the
sqlnet.ora
file or thelistener.ora
file:SSL_DISABLE_WEAK_EC_CURVES=TRUE
Parent topic: Security Section
6.10.21 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 for DN matching. DN matching adds another client-side check on the listener and server certificates to ensure that the certificates are the correct ones that the client expects.
Usage Notes
-
Set this parameter in the
tnsnames.ora
file or connect string to enforce full DN matching. For partial DN matching, do not include this parameter. -
The value in the
SSL_SERVER_CERT_DN
parameter is matched to the full DN in the listener and server certificates only when theSSL_SERVER_DN_MATCH
parameter is set toON
.The client must know the server DN ahead of time to specify the value in
SSL_SERVER_CERT_DN
. The client usesSSL_SERVER_CERT_DN
to check the certificate DN for both the listener and database server that the client wants to connect to. -
Oracle recommends that you use the same certificate for both the listener and server. If you use different certificates with different DNs for the listener and server, then full DN matching fails. In this case, you need to get new certificates with the same DN (for full DN matching) or you need to change your DN matching strategy.
-
Starting with Oracle Database 23ai, the DN matching behavior is enhanced for better security. Full DN matching checks the complete DN in
SSL_SERVER_CERT_DN
against the DN in both the listener and server certificates. In earlier releases, full DN matching checked the complete DN only in the server certificate.If you want to revert to the earlier weaker DN matching behavior (that is, checking only the server certificate), then set
SSL_ALLOW_WEAK_DN_MATCH=TRUE
. However, note that theSSL_ALLOW_WEAK_DN_MATCH
parameter is deprecated and will be removed in a future release. Oracle recommends that you get new certificates or change your DN matching strategy.
Value
Database server DN
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")))
Parent topic: Security Section
6.10.22 SSL_SERVER_DN_MATCH
Use the SSL_SERVER_DN_MATCH
parameter to enforce server-side certificate validation through distinguished name (DN) matching.
Purpose
To enforce server-side certificate validation through DN matching.
The purpose of adding this DN matching parameter for the client is to further improve security on a Transport Layer Security (TLS) connection. A TLS connection relies on the client to verify if the database server certificate is valid and signed by a trusted root certificate. The listener and server certificate DN matching adds another client-side check on the listener and server certificates to ensure that the certificates are the correct ones that the client expects.
Usage Notes
-
If you set this parameter to
TRUE
, then in addition to verifying the server's certificate chain, the client enforces another check against the listener and server through DN matching. -
You can configure either partial DN matching or full DN matching.
Through partial DN matching, the client checks theHOSTNAME
parameter (in the clientsqlnet.ora
file or connect string) against a host name in the certificate DN or certificate Subject Alternative Name (SAN) field. The client checksHOSTNAME
against both the listener and server certificates in this order:-
The client first compares
HOSTNAME
with a host name in the listener certificate’s DN. For example, CN part of DN:"c=us,o=examplecorporation,cn=sales.us.example.com"
-
If no match is found in the listener certificate’s DN, then the client compares
HOSTNAME
with a host name in the listener certificate’s SAN field. For example:"DNS:sales.us.example.com"
If no match is found in the listener certificate’s SAN field, then the client does not try connecting to the server and the connection fails.
-
If the listener certificate check succeeds, then the client performs similar checks on the server certificate. That is, the client first compares
HOSTNAME
with a host name in the server certificate’s DN. -
If no match is found in the server certificate’s DN, then the client compares
HOSTNAME
with a host name in the server certificate’s SAN field.
Through full DN matching, the client checks the complete DN in
SSL_SERVER_CERT_DN
against the certificate DN of both the listener and server certificates. To enforce a full DN match, specify the complete DN using theSSL_SERVER_CERT_DN
parameter in thetnsnames.ora
file or connect string. -
-
Oracle recommends that you use the same certificate for both the listener and server.
If you use different certificates with different DNs for the listener and server, then full DN matching fails. In this case, you need to get new certificates with the same DN (for full DN matching) or you need to change your DN matching strategy. If you have configured partial DN matching, then it may also fail if
HOSTNAME
is not found in the certificate DN or SAN fields of both the listener and server certificates. -
Prior to Oracle Database 23ai, partial DN matching checked against host name and SAN only in the server certificate. If a match was not found, then along with the host name and SAN, it also checked the
SERVICE_NAME
parameter. Similarly, full DN matching checked against the complete DN only in the server certificate.If you want to revert to the earlier weaker DN matching behavior (that is, checking only the server certificate and allowing a service name check for partial DN matching), then set
SSL_ALLOW_WEAK_DN_MATCH=TRUE
. However, note that theSSL_ALLOW_WEAK_DN_MATCH
parameter is deprecated and will be removed in a future release. Oracle recommends that you get new certificates or change your DN matching strategy.
Default
NO
Values
-
YES
|ON
|TRUE
|1
:To enforce partial or full DN matching. If the DN matches the host name or SAN in both the listener and server certificates, then the connection succeeds. If the DN does not match the host name or SAN in the server or listener certificate, then the connection fails.
-
NO
|OFF
|FALSE
|0
:To not enforce DN matching. If the DN does not match the host name or SAN in the sever or listener certificate, then the connection is successful, but an error is logged to the
sqlnet.log
file.
Example
SSL_SERVER_DN_MATCH=YES
Parent topic: Security Section
6.10.23 SSL_VERSION
Use the SSL_VERSION
parameter to define valid Transport Layer Security (TLS) versions to be used for connections.
Purpose
To define the version of TLS that must run on the systems with which the database server communicates. By default, the database server and client negotiate the strongest security protocol. Oracle does not recommend modifying this parameter, unless your security requirements mandate the usage of certain protocol versions.
Usage Notes
-
Clients, listeners, and database servers must use compatible versions. Modify this parameter only when necessary to enforce the use of the more secure TLS protocol and not allow clients that only work with the older TLS protocols. The current default uses TLS 1.3, which is the version required for multiple security compliance requirements. If you need to specify TLS 1.2, then also include TLS 1.3 to allow more secure connections.
-
In addition to
sqlnet.ora
,listener.ora
, andcman.ora
, you can specify this parameter under theSECURITY
section oftnsnames.ora
or directly as part of the connect string. The parameter value specified in the connect string takes precedence over the other specified values. -
Starting with Database 23ai, the use of Transport Layer Security protocol versions 1.0 and 1.1 are desupported.
In most cases, this change will not have any impact, because the database client and server will negotiate the use of the most secure protocol and cipher algorithm. However, if TLS 1.0 or 1.1 has been specified, then you must either remove it to allow the database server and client to pick the most secure protocol, or you must specify either TLS 1.2, or TLS 1.3, or both, for the protocol. Oracle recommends using the latest, most secure protocol. That protocol is TLS 1.3, which is introduced with Oracle Database 23ai.
-
Starting with Oracle Database 23ai, the Secure Socket Layer v3 protocol (SSLv3) is no longer supported for database server-client connections, and the
sqlnet.ora
parameterADD_SSLV3_TO_DEFAULT
has been removed.SSLv3 is a much less secure protocol to secure the database server-to-client connection. Instead of using SSLv3, allow the database server and client to negotiate the most secure protocol that is common between the server and the client. Oracle Database 23ai provides TLS 1.2 and TLS 1.3 protocols for certificate-based network encryption.
-
If you set
SSL_VERSION
toundetermined
, then the most secure TLS protocol version is used. You can also use theSSL_VERSION=undetermined
setting in the connect string for a specific connection to override theSSL_VERSION
value configured in thesqlnet.ora
,listener.ora
, orcman.ora
file. -
If you do not set
SSL_VERSION
to any value, then all the supported TLS protocol versions are tried starting with the most secure version. This is typically the most common configuration, ensuring that the strongest protocol is chosen during TLS negotiation.
Values
undetermined
| TLSv1.2
| TLSv1.3
Default
undetermined
Syntax and Examples
-
To specify a single protocol version:
SSL_VERSION=TLS_protocol_version
For example:SSL_VERSION=TLSv1.3
-
To specify multiple protocol versions, use a comma-separated string of values, enclosed in parenthesis:
SSL_VERSION=(TLS_protocol_version1,TLS_protocol_version2)
For example:SSL_VERSION=(TLSv1.2,TLSv1.3)
Note:
Do not enclose protocol versions in parenthesis while specifying this parameter in the
tnsnames.ora
file or as part of the connect string, otherwise the setting will not parse correctly. For example:net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcps)(HOST=salesserver)(PORT=1522)) (SECURITY=(SSL_VERSION=TLSv1.2,TLSv1.3)) )
Related Topics
Parent topic: Security Section
6.10.24 TENANT_ID
Use the TENANT_ID
parameter to specify the ID of your Microsoft Entra ID tenant.
Purpose
To specify the ID of the Entra ID tenant in which your Entra ID application is registered. This is the Azure tenancy ID that uniquely identifies your database instance in Entra ID.
Usage Notes
-
You use this parameter along with the
TOKEN_AUTH
parameter for theAZURE_INTERACTIVE
,AZURE_SERVICE_PRINCIPAL
,AZURE_MANAGED_IDENTITY
, andAZURE_DEVICE_CODE
token-based authentication flows. -
This parameter is mandatory for the thick clients (OCI and Instant Client). It is optional when using the JDBC-thin clients and ODP.NET core and managed database clients.
When using the JDBC-thin clients and ODP.NET core and managed database clients, if you have configured the Azure SDKs, then the client driver automatically searches for the tenant ID in the SDK configuration. If you have not configured the SDKs, then you must set this parameter.
When using the OCI and Instant Clients (which do not use the Azure SDKs), you must set this parameter (along with other required parameters, such as
CLIENT_ID
). Otherwise, an error message appears prompting you to configure the required parameters. -
For the JDBC-thin clients, you can specify this parameter in the connect string, Easy Connect syntax,
tnsnames.ora
file, or properties. For the thick clients (OCI and Instant Client) and ODP.NET core and managed database clients, you can specify this parameter in the connect string,sqlnet.ora
file, Easy Connect syntax, ortnsnames.ora
file. The parameter value specified in the connect string takes precedence.
Default
None
Value
You can get the tenant ID value by logging in to the Azure portal. This is listed as Tenant ID on the Tenant Properties page.
Examples
tnsnames.ora
file:net_service_name=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcps)(HOST=sales-svr)(PORT=1521))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,O=example,CN=OracleContext")
(TOKEN_AUTH=AZURE_INTERACTIVE)
(AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3)
(TENANT_ID=1a123ab1-a1b1-1a2b-a1b2-a12bcdab0123)
(REDIRECT_URI=http://localhost:1575))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
sqlnet.ora
file:SSL_SERVER_DN_MATCH=TRUE
TOKEN_AUTH=AZURE_INTERACTIVE
AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3
TENANT_ID=1a123ab1-a1b1-1a2b-a1b2-a12bcdab0123
REDIRECT_URI=http://localhost:1575
tcps:sales-svr:1521/sales.us.example.com?TOKEN_AUTH=AZURE_INTERACTIVE&AZURE_DB_APP_ID_URI=https://application.example.com/123ab4cd-1a2b-1234-a12b-aa00123b2cd3&TENANT_ID=1a123ab1-a1b1-1a2b-a1b2-a12bcdab0123&REDIRECT_URI=http://localhost:1575
In these examples, the CLIENT_ID
parameter is not specified. CLIENT_ID
is required when using the thick clients (OCI and Instant Client). This parameter is optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get this value from the Azure SDK configuration.
Related Topics
Parent topic: Security Section
6.10.25 TOKEN_AUTH
Use the TOKEN_AUTH
parameter to configure token-based authentication for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) or Microsoft Azure users of Microsoft Entra ID (previously called Microsoft Azure Active Directory).
Purpose
Token-based access enforces strong authentication, which enables a more secure access to the database. IAM users can connect to OCI Database as a Service (DBaaS) databases, and Azure users can connect to Oracle Databases (cloud or on-premises).
With this setting, when a /
(slash) login is used, the Oracle Database client either looks for a token file or directly gets the token using single-sign on (SSO) credentials.
Use this parameter under the SECURITY
section of the tnsnames.ora
file, sqlnet.ora
file, or directly as part of the command-line connect string. The parameter value specified in the connect string takes precedence over the other specified values.
Usage Notes for IAM
-
OCI IAM token:
An OCI IAM token (
db-token
), which is obtained from IAM using Oracle Cloud Infrastructure (OCI) Command Line Interface (CLI) or programmatically from the OCI Software Development Kit (SDK), is a proof-of-possession (PoP) token with an expiration time and scope.You can use one of the IAM user credentials, such as API-key, security token, resource principal, instance principal, or delegation token to retrieve the
db-token
and private key from IAM.These tokens are transmitted over secure channels. You must use only the TCP/IP with Transport Layer Security (TLS) protocol, otherwise an error message appears indicating that non-TLS connections are disallowed.
-
Required setting for token-based authentication:
You must configure the TCPS protocol (
PROTOCOL=tcps
) and set theSSL_SERVER_DN_MATCH
parameter toTRUE
for token-based authentication. -
Use a file location to send the token to Oracle Database:
When an IAM user logs in using
/@connect_identifier
(andTOKEN_AUTH
is set toOCI_TOKEN
), theTOKEN_AUTH=OCI_TOKEN
setting along with/@connect_identifier
instructs the database client to get thedb-token
and private key from either the default directory or the location specified byTOKEN_LOCATION
. -
Use the client API to send the token to Oracle Database:
If your client application is updated to retrieve tokens from IAM, then you can override the
TOKEN_AUTH=OCI_TOKEN
setting. The client application gets thedb-token
and private key from IAM and sends as attributes to the database client using the client API. In this case, you do not need to specify theTOKEN_AUTH
andTOKEN_LOCATION
parameters. -
General IAM token-based authentication process:
-
An IAM user or application in OCI first requests the
db-token
from IAM by using API-key, security token, resource principal, service principal, instance principal, or delegation token (delegation token is available only in the Cloud Shell).To use a security token, you need to generate it by completing the browser authentication process and then request the
db-token
using that security token. If the IAM policy that authorizes you to be issued thedb-token
exists, then thedb-token
is returned.You request the
db-token
using OCI CLI (or OCI SDK for applications). For example, run the following OCI CLI command to request thedb-token
by using an API-key (apikey
):$ oci iam db-token get --profile scott
The
profile
option specifies the profile for which you want to access the IAM user credentials and retrieve thedb-token
.For more information on using OCI CLI, see the
get
command details in Oracle Cloud Infrastructure CLI Command Reference. -
OCI CLI references the
config
file (that stores your IAM user credentials as part of the profile) and makes a call to IAM to get thedb-token
. Thedb-token
and private key files are written at the default or specified token location. -
You can specify the
TOKEN_LOCATION
parameter to override the default directory where thedb-token
and private key files are stored.The database client gets the
db-token
and private key from the default token location or the location specified byTOKEN_LOCATION
, signs thedb-token
with the private key and sends it to the database server. The database server verifies thedb-token
and gets the group membership information for the user. If the IAM user is mapped to a database schema (exclusively or shared), then the login is completed.
-
-
Use an Oracle Database client to directly send the token to Oracle Database:
This feature is available in environments that use the JDBC-thin clients and ODP.NET core and managed database clients. For the JDBC-thin clients, you can set this in the
tnsnames.ora
or Easy Connect connect string. For the ODP.NET core and managed database clients, you can set this in thesqlnet.ora
,tnsnames.ora
, or Easy Connect connect string. The parameter value specified in the connect string takes precedence.To configure this feature for the JDBC-thin clients, see Oracle Database JDBC Developer's Guide and for the ODP.NET clients, see Oracle Data Provider for .NET Developer's Guide.
The following authentication flows enable the database client to directly retrieve the
db-token
with IAM SSO credentials:-
OCI Interactive:
TOKEN_AUTH=OCI_INTERACTIVE
specifies the OCI Interactive flow. This authenticates the token request interactively using a web browser, and is useful for client-side web applications or desktop applications.The database client gets a default profile (named
DEFAULT
) from the OCI configuration file, which is stored either in the default directory or at the location specified by theOCI_CONFIG_FILE
parameter. After validating the user's region against a list of valid regions, the client launches an authentication request to the user in a web browser, prompting to log in using the IAM user name and password along with any additional factors required by IAM.Optionally, you can override the
DEFAULT
profile set in the configuration file by specifying theOCI_PROFILE
parameter. -
OCI API Key:
TOKEN_AUTH=OCI_API_KEY
specifies the OCI API Key flow. This authenticates the token request with IAM using an IAM-recognized API-key.The database client reads the file system location of the API-key from the user's
DEFAULT
profile in the OCI configuration file, from either the default configuration file directory or the location specified byOCI_CONFIG_FILE
.Optionally, you can override the user's
DEFAULT
profile set in the configuration file by specifying theOCI_PROFILE
parameter. -
OCI Instance Principal:
TOKEN_AUTH=OCI_INSTANCE_PRINCIPAL
specifies the OCI Instance Principal flow. This authenticates the token request with IAM as an OCI instance principal for applications running on OCI compute instances. -
OCI Delegation Token:
TOKEN_AUTH=OCI_DELEGATION_TOKEN
specifies the OCI Delegation Token flow. This authenticates the token request with IAM using a delegation token for applications running in an OCI Cloud Shell. -
OCI Resource Principal:
TOKEN_AUTH=OCI_RESOURCE_PRINCIPAL
specifies the OCI Resource Principal flow. This authenticates the token request with IAM as an OCI resource principal for applications running in a container (as an OCI function). -
Default:
TOKEN_AUTH=OCI_DEFAULT
specifies the Default flow. With this setting, the client driver reads the predefined environment variables from the SDK configuration, evaluates each authentication flow in a sequence, and then assigns the most appropriate flow based on the environment where the application is running.Sequence in which the driver evaluates each authentication flow withOCI_DEFAULT
:-
OCI API Key: The driver first checks if a configuration file is present at the location specified by the
OCI_CONFIG_FILE
parameter or at the default location ($HOME/.oci/config
). The driver then checks if the file contains a profile matching the name configured by theOCI_PROFILE
parameter or the default name (DEFAULT
). Finally, the driver checks if the profile is configured with an entry namedkey_file
. If all of these checks succeed, then authentication with an API key is used. If any of these checks fail, then the driver proceeds to the next step. -
OCI Delegation Token: The driver first checks if the
OCI_CONFIG_FILE
environment variable is set. The driver then checks if a file is present at the location configured by theOCI_CONFIG_FILE
environment variable. The driver then checks if the file contains a profile namedDEFAULT
. Finally, the driver checks if the profile is configured with an entry nameddelegation_token_file
. If all of these checks succeed, then authentication with a delegation token is used. If any of these checks fail, then the driver proceeds to the next step. -
OCI Resource Principal: The driver first checks if the
OCI_RESOURCE_PRINCIPAL_VERSION
environment variable is set. The driver then checks if the variable is set to version2.2
or1.1
. If the variable is set to2.2
, the driver then checks if theOCI_RESOURCE_PRINCIPAL_PRIVATE_PEM
,OCI_RESOURCE_PRINCIPAL_RPST
, andOCI_RESOURCE_PRINCIPAL_REGION
environment variables are also set. Otherwise, if the variable is set to1.1
, then the driver checks if theOCI_RESOURCE_PRINCIPAL_RPT_ENDPOINT
environment variable is also set. If the required variables for a version are set, then authentication as a resource principal is used. If any variable is not set, then the driver proceeds to the next step. -
OCI Instance Principal: The driver requests a certificate from the instance metadata service. The base URL of the service is
http://169.254.169.254/opc/v2/
. However, a fallback URL ofhttp://169.254.169.254/opc/v1/
is used if thev2
service request fails. If a request to thev2
orv1
service succeeds, then authentication as an instance principal is used. If the request fails, then the driver proceeds to the next step. -
The driver reports an error indicating that authentication is not possible using any of the authentication flows.
-
You also need to specify the
OCI_DATABASE
andOCI_COMPARTMENT
parameters for all these authentication flows, if the OCI database token policy limits you to access only a particular database or databases within a compartment. -
Note:
You can also use another IAM credential, IAM database password, to request the db-token
from IAM. This db-token
is a bearer token and does not come with a private key. You can configure the database client to request this token using your IAM user name and IAM database password. An application cannot pass this type of db-token
to the client. In this case, you use a different parameter setting (PASSWORD_AUTH=OCI_TOKEN
).
Unlike the API-key, security token, resource principal, service principal, instance principal, and delegation token that require an application or tool to get a token, the IAM database password can only be used by the database client to retrieve the token. See PASSWORD_AUTH.
Default Setting for IAM
None
Table 6-1 Values and Examples for IAM
Value | Example |
---|---|
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the optional |
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the optional |
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the optional |
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
|
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
|
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
|
Usage Notes for Entra ID
-
Entra ID access token:
An Entra ID OAuth2 access token is a bearer token with an expiration time and scope. This token follows the OAuth2.0 standard with Entra ID extensions. You can request these tokens from tools and scripts run on Linux, Microsoft PowerShell, or other environments. You can also request these tokens programmatically using the Microsoft SDKs.
These tokens are transmitted over secure channels. You must use only the TCP/IP with Transport Layer Security (TLS) protocol, otherwise an error message appears indicating that non-TLS connections are disallowed.
-
Required setting for token-based authentication:
You must configure the TCPS protocol (
PROTOCOL=tcps
) and set theSSL_SERVER_DN_MATCH
parameter toTRUE
for token-based authentication. -
Use a file location to send the token to Oracle Database:
When an Azure user logs in using
/@connect_identifier
(andTOKEN_AUTH
is set toOAUTH
), theTOKEN_AUTH=OAUTH
setting instructs the database client to get the access token from the directory location specified byTOKEN_LOCATION
if the token file is namedtoken
. If the token file name is different fromtoken
, then you must use the file name along with the directory location while specifying theTOKEN_LOCATION
parameter.The
TOKEN_LOCATION
parameter is mandatory for Azure token-based authentication. The database client gets the token from this location and sends it to the database server. -
Use the client API to send the token to Oracle Database:
If your client application is updated to retrieve tokens from Entra ID, then you can override the
TOKEN_AUTH=OAUTH
setting. Entra ID directly passes thedb-token
as an attribute to the database client using the client API. When the client receives this request, the client sends it to the database server.In this case, you do no need to specify the
TOKEN_AUTH
andTOKEN_LOCATION
parameters. -
General Azure token-based authentication process:
-
An Azure user or application first requests the access token from Entra ID using one of the supported authentication flows (resource owner password credentials, authorization code, on-behalf-of (OBO) flow, or client credentials).
An Azure user can connect using any supported utility to retrieve the token and store it in a local file directory.
You can request the token from tools and scripts run on Linux, Microsoft PowerShell, or other environments. You can also request programmatically using the Microsoft SDKs.For detailed examples on how to retrieve an Entra ID OAuth2 access token, see Oracle Database Security Guide.
-
The database client then sends the token to the database server. The database server verifies the token (using the Entra ID public key) and extracts various claims from the token, including user name, app roles, and audience. If the Entra ID principal is mapped to a database schema (exclusively or shared), then the login is completed.
-
-
Use an Oracle Database client to directly send the token to Oracle Database:
This feature is available in environments that use the JDBC-thin clients, thick clients (Oracle Call Interface (OCI) and Oracle Database Instant Client, JDBC-thick, ODP.NET unmanaged, or Python-thick), ODP.NET Core classes, or ODP.NET Managed Driver classes.
For the thick clients, only the interactive flow is supported (
TOKEN_AUTH=AZURE_INTERACTIVE
).For the JDBC-thin clients, you can set this in the
tnsnames.ora
or Easy Connect connect string. For the thick clients and ODP.NET core and managed database clients, you can set this in thetnsnames.ora
, connect string, orsqlnet.ora
file (except forREDIRECT_URI
andCLIENT_CERTIFICATE
). The parameter value specified in the connect string takes precedence.To configure this feature for the JDBC clients, see Oracle Database JDBC Developer's Guide. For the ODP.NET clients, see Oracle Data Provider for .NET Developer's Guide. For the OCI and Instant Clients, see Oracle Call Interface Developer's Guide.
The following authentication flows enable the database client to directly retrieve an access token with Azure SSO credentials:
-
Azure OAuth2 Interactive:
TOKEN_AUTH=AZURE_INTERACTIVE
specifies the Azure OAuth2 Interactive flow. This authenticates the token request interactively using a web browser, and is useful for client-side web applications or desktop applications.When an Azure user logs in using
/@connect_identifier
(andTOKEN_AUTH
is set toAZURE_INTERACTIVE
), theTOKEN_AUTH=AZURE_INTERACTIVE
setting along with/@connect_identifier
instructs the database client driver to directly get an access token from Entra ID. This is for human users who are logging in to tools (such as SQLcl) and can also open a browser window in their environment for authentication.If the user has not already logged in, then the database client launches an authentication request to the user (either in a dialog box if the user is using a web application or as a prompt if the user is working in a command line shell), prompting to log in using the Azure user name and password. After logging in to the Azure account, the user is redirected back to the client application (to its registered redirect URI).
Optionally, you can set the
REDIRECT_URI
parameter if you want to override the default redirect URI value (http://localhost
).You must set the
AZURE_DB_APP_ID_URI
parameter to compose the authorization scope of your token request.The
CLIENT_ID
andTENANT_ID
parameters are optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get these values from the Azure SDK configuration.CLIENT_ID
andTENANT_ID
are required parameters for the OCI and Instant Clients. -
Azure Service Principal:
TOKEN_AUTH=AZURE_SERVICE_PRINCIPAL
specifies the Azure Service Principal flow. This authenticates the token request as a service principal by using either a client secret or a client certificate, and is useful for server-side applications (for example, microservices or back-end apps).You must set the
AZURE_DB_APP_ID_URI
parameter to compose the authorization scope of your token request.The
CLIENT_ID
,TENANT_ID
, andCLIENT_CERTIFICATE
parameters are optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get these values from the Azure SDK configuration.CLIENT_ID
andTENANT_ID
are required parameters for the OCI and Instant Clients. -
Azure Managed Identity:
TOKEN_AUTH=AZURE_MANAGED_IDENTITY
specifies the Azure Managed Identity flow. This authenticates the token request with Entra ID as an Azure managed identity, and is useful for client-side or server-side applications hosted on Azure environments (for example, Azure App Service or Azure virtual machine).You must set the
AZURE_DB_APP_ID_URI
parameter to compose the authorization scope of your token request.You can set the
CLIENT_ID
parameter to configure a user-assigned managed identity for authenticating the token request. This parameter is optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get this value from the Azure SDK configuration.CLIENT_ID
is required for the OCI and Instant Clients. -
Azure Device Code:
TOKEN_AUTH=AZURE_DEVICE_CODE
specifies the Azure Device Code flow. This authenticates the token request interactively, and is for human users or client-side applications running on platforms with limited or no browser support (for example, command line environments such as SQLcl).The database client displays a device code and an Entra ID login URL through the standard output of the tool, and prompts the user to enter the device code, Azure user name, and Azure password on any browser-supporting device (for example, cellphone or laptop). After completing the login in a web browser, the Azure SDK returns an access token to the client. The client sends the access token to the database to authorize the database user session.
You must set the
AZURE_DB_APP_ID_URI
parameter to compose the authorization scope of your token request.The
CLIENT_ID
andTENANT_ID
parameters are optional for the JDBC-thin and ODP.NET core and managed database clients, which can automatically get these values from the Azure SDK configuration.CLIENT_ID
andTENANT_ID
are required parameters for the OCI and Instant Clients.Note:
You must explicitly enable the Azure OAuth2 Interactive and Azure Device Code flows for your Entra ID app in the Azure portal. To do so, on the App registrations - Authentication page, under Advanced Settings, set Allow public client flows to Yes. -
Default:
TOKEN_AUTH=AZURE_DEFAULT
specifies the Default flow. With this setting, the client driver reads the predefined environment variables from the SDK configuration, evaluates each authentication flow in a sequence, and then assigns the most appropriate flow based on the environment where the application is running.Sequence in which the driver evaluates each authentication flow withAZURE_DEFAULT
:-
Azure Service Principal with Client Secret Credentials: The driver checks if client ID and client secret are configured as parameters to the driver or as SDK environment variables. If both are configured, then the driver authenticates as a service principal using a client secret. Otherwise, the driver proceeds to the next step.
-
Azure Service Principal with Client Certificate Credentials: The driver checks if client ID and client certificate are configured as parameters to the driver or SDK environment variables. If both are configured, then the driver authenticates as a service principal using a client certificate. Otherwise, the driver proceeds to the next step.
-
Azure Username Credentials: The driver checks if client ID, username, and password are configured as parameters to the driver or SDK environment variables. If all are configured, then the driver authenticates as a service principal using the username and password. Otherwise, the driver proceeds to the next step.
-
Azure Managed Identity: The driver checks if the
MSI_ENDPOINT
orIDENTITY_ENDPOINT
environment variable is set. If either is set, then the driver authenticates as a managed identity using the configured endpoint. If neither is set, then the driver checks if theAZURE_TENANT_ID
andAZURE_FEDERATED_TOKEN_FILE
environment variables are set. If both are set, then the driver authenticates as a managed identity using the configured token file. If both are not set, then the driver requests an access token from the Azure Instance Metadata Service (IMDS) endpoint. If the request succeeds, then the driver authenticates as a managed identity. Otherwise, the driver proceeds to the next step. -
Visual Studio Credentials: For ODP.NET Core classes and ODP.NET Managed Driver classes, the driver additionally evaluates the Azure user through Visual Studio Credentials authentication flow. The driver checks if the
TENANT_ID
parameter or theAZURE_TENANT_ID
environment variable is set and if the Azure user is logged in to Visual Studio. If both the checks succeed, then authentication with the Visual Studio credentials is used. Otherwise, the driver proceeds to the next step. -
The driver reports an error indicating that authentication is not possible using any of the authentication flows.
-
-
Default Setting for Entra ID
None
Table 6-2 Values and Examples for Entra ID
Value | Example |
---|---|
|
|
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the |
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the |
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the |
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the |
Related Topics
Parent topic: Security Section
6.10.26 TOKEN_LOCATION
Use the TOKEN_LOCATION
parameter to specify the directory location where token file is stored for token-based authentication.
Purpose
To specify the token file directory location. You use this parameter while configuring token-based authentication for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) or Microsoft Azure users of Microsoft Entra ID. The database client gets the token from this location and sends it to the database server. For Entra ID, you can also specify the token file name along with the directory location.
Use this parameter along with the TOKEN_AUTH
parameter in the tnsnames.ora
file, sqlnet.ora
file, or directly as part of the command-line connect string. The parameter values specified in the connect string take precedence over the other specified values.
Usage Notes for IAM
The TOKEN_LOCATION
parameter is optional for IAM token-based authentication. You can use this parameter along with the TOKEN_AUTH
parameter to override the default directory where the db-token
and private key are stored. This location is used by the database client to retrieve the db-token
and private key.
When an IAM user initiates a connection using /@connect_identifier
(and TOKEN_AUTH
is set to OCI_TOKEN
), the database client retrieves the db-token
and private key from either the default directory or the location specified by TOKEN_LOCATION
. The client then signs the db-token
using the private key and sends the db-token
to the database server.
Default Setting for IAM
-
On Linux:
/home/username/.oci/db-token
-
On Windows:
The database client searches for the default directory in this order:
If the
USERPROFILE
environment variable is set, then the client searches in theUSERPROFILE
directory (for example,C:\Users\username
).If
USERPROFILE
is not set, then the client searches inHOMEDRIVE
directory (for example,C:
) withHOMEPATH
(for example,\Users\username
).For example, the default token location directory on Windows is:
C:\Users\username\.oci\db-token
Values and Examples for IAM
Value | Example |
---|---|
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
|
Usage Notes for Entra ID
The TOKEN_LOCATION
parameter is mandatory for Azure token-based authentication. You must use this parameter along with the TOKEN_AUTH
parameter to specify the directory location where the Entra ID OAuth2 access token is stored. This location is used by the database client to get the access token.
If your token file is named token
, then specify only the directory path. If the token file name is different from token
, then you must use the file name along with the directory path.
When an Azure user initiates a connection using /@connect_identifier
, the database client retrieves the access token from the location specified by TOKEN_LOCATION
and sends the token to the database server.
Default Setting for Entra ID
None
Values and Examples for Entra ID
Value | Example |
---|---|
If the token file is named
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the token file name is |
If the token file name is different from
|
In the
tnsnames.ora file:
In the
sqlnet.ora file:
In these examples, the token file name is |
6.10.27 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
-
The parameter
WALLET_LOCATION
is deprecated for use with Oracle Database 23ai for the Oracle Database server. It is not deprecated for use with the Oracle Database client or listener.For Oracle Database server, Oracle recommends that you use the
WALLET_ROOT
system parameter instead of usingWALLET_LOCATION
. -
The connect string parameter
MY_WALLET_DIRECTORY
has been deprecated with Oracle Database 23ai.Oracle recommends that you use
WALLET_LOCATION
in the connect string to override thesqlnet.ora
WALLET_LOCATION
setting.WALLET_LOCATION
has been updated for connect strings so that the same parameter can be used insqlnet.ora
and intnsnames.ora
This change simplifies the parameters that you need to remember. Oracle recommends that you change your client connect strings to useWALLET_LOCATION
instead ofMY_WALLET_DIRECTORY
.Use of
WALLET_LOCATION
intnsnames.ora
overrides theWALLET_LOCATION
insqlnet.ora
for the specifictnsnames.ora
service. Thetnsnames.ora
WALLET_LOCATION
setting enables a client connection to have distinct TLS connections that use certificates. This means that the client initiates 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.
Default
None
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))
)
Parent topic: Security Section
6.11 Timeout Parameters
The timeout section of the tnsnames.or
a 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
Use thetnsnames.ora
parameterCONNECT_TIMEOUT
to specify the amount of time, in milliseconds, seconds, or minutes, in which clients must establish Oracle Net connections to database instances. - RETRY_COUNT
Use thetnsnames.ora
parameterRETRY_COUNT
to specify the number of times anADDRESS
list is traversed before terminating the connection attempt. - RETRY_DELAY
Use thetnsnames.ora
parameterRETRY_DELAY
to specify the delay between connection retries. - TRANSPORT_CONNECT_TIMEOUT
Use thetnsnames.ora
parameterTRANSPORT_CONNECT_TIMEOUT
to specify the transport connect timeout duration, in milliseconds, seconds, or minutes. - RECV_TIMEOUT
Use thetnsnames.ora
parameterRECV_TIMEOUT
to specify the duration of time that a database client or server should wait for data from a peer after establishing a connection.
Parent topic: Local Naming Parameters in the tnsnames.ora File
6.11.1 CONNECT_TIMEOUT
Use the tnsnames.ora
parameter CONNECT_TIMEOUT
to specify the amount of time, in milliseconds, seconds, or minutes, in which clients must establish Oracle Net connections to database instances.
Purpose
To specify the timeout duration in ms
or msec
(milliseconds), sec
(seconds), or min
(minutes) for a client to establish an Oracle Net connection to Oracle Database.
Usage Notes
-
Put this parameter under the
DESCRIPTION
parameter. -
In case, no unit is mentioned, the default unit is
sec
.It accepts different timeouts with or without space between the value and the unit. For example:
CONNECT_TIMEOUT=10 ms
or
CONNECT_TIMEOUT=10ms
-
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.The timeout interval is applicable to each
ADDRESS
in anADDRESS_LIST
, and each IP address to which a host name is mapped. -
The
CONNECT_TIMEOUT
parameter is equivalent to thesqlnet.ora
parameterSQLNET.OUTBOUND_CONNECT_TIMEOUT
, and overrides it.
Examples
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)))
Related Topics
Parent topic: Timeout Parameters
6.11.2 RETRY_COUNT
Use the tnsnames.ora
parameter RETRY_COUNT
to specify the number of times an ADDRESS
list is traversed before terminating the connection attempt.
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))))
Parent topic: Timeout Parameters
6.11.3 RETRY_DELAY
Use the tnsnames.ora
parameter RETRY_DELAY
to specify the delay between connection retries.
Purpose
To specify the delay between subsequent retries for a connection in units of ms
or msec
(milliseconds), sec
(seconds), or min
(minutes). This parameter works in conjunction with the RETRY_COUNT
parameter.
Usage Notes
-
Put this parameter under the
DESCRIPTION
parameter.When a
DESCRIPTION_LIST
is specified, eachDESCRIPTION
is traversed multiple times based on the specified number of retries, and the specific delay for the description. -
In case, no unit is mentioned, the default unit is
sec
.You can configure it with or without space between the value and the unit. For example:
RETRY_DELAY=800 ms
or
RETRY_DELAY=800ms
Example
net_service_name=
(DESCRIPTION_LIST=
(DESCRIPTION=
(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(RETRY_DELAY=800ms)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost2)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=example1.com)))
(DESCRIPTION=
(CONNECT_TIMEOUT=60)(RETRY_COUNT=1)(RETRY_DELAY=5sec)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost3)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost4)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=example2.com))))
Related Topics
Parent topic: Timeout Parameters
6.11.4 TRANSPORT_CONNECT_TIMEOUT
Use the tnsnames.ora
parameter TRANSPORT_CONNECT_TIMEOUT
to specify the transport connect timeout duration, in milliseconds, seconds, or minutes.
Purpose
To specify the transport connect timeout duration in ms
or msec
(milliseconds), sec
(seconds), or min
(minutes) for a client to establish an Oracle Net connection to Oracle Database.
Usage Notes
-
This parameter is put under the
DESCRIPTION
parameter. -
The default value is 60
sec
. In case, no unit is mentioned, the default unit issec
.It accepts different timeouts with or without space between the value and the unit.
TRANSPORT_CONNECT_TIMEOUT=10 ms
or
TRANSPORT_CONNECT_TIMEOUT=10ms
-
The timeout interval is applicable for each
ADDRESS
in anADDRESS_LIST
description, and each IP address that a host name is mapped. TheTRANSPORT_CONNECT_TIMEOUT
parameter is equivalent to thesqlnet.ora
parameterTCP.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)))
Related Topics
Parent topic: Timeout Parameters
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 duration in ms
or msec
(milliseconds), sec
(seconds), min
(minutes), or hr
(hours) 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.
Usage Notes
-
This parameter is put under the
DESCRIPTION
parameter. -
If you do not specify a unit of measurement, then the default unit is
sec
.It accepts time duration with or without space between the value and the unit. For example:
RECV_TIMEOUT=10 ms
or
RECV_TIMEOUT=10ms
-
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
andORA-12609: TNS: Receive timeout occurred
messages to thesqlnet.log
file.
Default Value
None
Minimum Value
1 ms
Allowed Range
Any number greater than the minimum value of 1 ms
up to 4294967295 ms
.
Examples
net_service_name= (DESCRIPTION= (CONNECT_TIMEOUT=10ms)(RETRY_COUNT=3)(RECV_TIMEOUT=10ms) (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)))
Related Topics
Parent topic: Timeout Parameters
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
Thetnsnames.ora
file’s compression parameter enables or disables the data compression. - COMPRESSION_LEVELS
TheCOMPRESSION_LEVELS
parameter of thetnsnames.ora
file specifies the compression level.
Parent topic: Local Naming Parameters in the tnsnames.ora File
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)))
Related Topics
Parent topic: Compression Parameters
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)))
Related Topics
Parent topic: Compression Parameters