13 Enabling Advanced Features of Oracle Net Services
Understand how to configure the advanced features of Oracle Net Services, including advanced connect data parameters, load balancing, failover, and connections to non-database services.
13.1 Configuring Advanced Network Address and Connect Data Information
A database service can be accessed by several routes and protocol addresses. You configure which routes to use by setting the list of protocol addresses. You configure the order addresses are used by specifying the address parameters. This section contains the following topics:
13.1.1 Creating a List of Listener Protocol Addresses
A database service may be accessed by more than one network route, or protocol address. In the following example, sales.us.example.com
can connect to the sales.us.example.com
service using listeners on either sales1-server
or sales2-server
.
sales.us.example.com= (DESCRIPTION= (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)))
To add a network protocol address to an existing network service name or database service, use one of the following procedures:
Adding a Network Protocol Using Oracle Enterprise Manager Cloud Control
The following procedure describes how to add a network protocol to an existing network service name or database service using Oracle Enterprise Manager Cloud Control:
-
Access the Directory Naming or Local Naming page in Oracle Enterprise Manager Cloud Control:
-
Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.
-
Select Local Naming or Directory Naming from the Administer list, and then select the Oracle home for the directory server or the location of the local configuration files.
-
Click Go.
The Local Naming or Directory Naming page appears.
-
-
Select the directory service or network service name.
-
For Local Naming, select a network service from the list, and then click Edit.
-
For Directory Naming, perform a search of the network service name in the Simple Search section, then select the network service or database service from the Results list, and then click Edit.
-
-
In the Addresses section, click Add.
The Add Address page appears.
-
From the Protocol list, select the protocol which the listener is configured to listen. This protocol must also be installed on the client.
-
Enter the appropriate parameter information for the selected protocol in the fields provided.
See Also:
Oracle Database Net Services Reference for protocol parameter settings
-
(Optional) In the Advanced Parameters section, specify the I/O buffer space limit for send and receive operations of sessions in the Total Send Buffer Size and Total Receive Buffer Size fields.
See Also:
"Configuring I/O Buffer Space" for additional information about buffer space
-
Click OK.
The protocol address is added to the Addresses section.
-
Click OK to update the address information.
Adding a Network Protocol Using Oracle Net Manager
The following procedure describes how to add a network protocol to an existing network service name or database service using Oracle Net Manager:
-
Start Oracle Net Manager.
-
In the navigator pane, select Service Naming from the Directory or Local menus.
-
Select either the network service name or a database service.
The right pane displays the current destination service and address list.
-
In the Address Configuration box, click the plus sign (+) to add a new address.
A new Address tab appears:
-
Select the protocol and enter appropriate address information.
See Also:
Oracle Database Net Services Reference for details about protocol address parameters
-
(Optional) On the Address tab, click Advanced to specify the I/O buffer space limit for send and receive operations of sessions in the Total Send Buffer Size and Total Receive Buffer Size fields.
See Also:
"Configuring I/O Buffer Space " for additional information about buffer space
-
Order the protocol addresses using the left-arrow and right-arrow buttons. This will order the addresses in the protocol address list. Unless multiple address options are configured, the first address in the list is contacted.
-
-
Select Save Network Configuration from the File menu.
13.1.2 About the Address List Parameters
When a database service is accessible by multiple listener protocol addresses, specify the order in which the addresses are to be used, such as chosen randomly or tried sequentially. Table 13-1 lists the parameters used with multiple protocol addresses.
Table 13-1 Address List Parameters in the tnsnames.ora File
Parameter | Description |
---|---|
At connect time, this parameter instructs Oracle Net to fail over to a different listener if the first listener fails when set to Connect-time failover is turned |
|
When set to Client load balancing is turned |
|
When set to |
Note:
You cannot set source routing (SOURCE_ROUTE) at the same level as connect-time failover (FAILOVER) or client load balancing (LOAD_BALANCE). Source routing connects to each address in the list sequentially whereas connect-time failover and client load balancing select a single address from a list.
When a connect descriptor in a tnsnames.ora
file contains at least two protocol addresses for an Oracle Connection Manager hop, then parameters for connect-time failover and load balancing within the hop can be included in the file.
Table 13-2 describes the address list options.
Table 13-2 Address List Options Dialog Box
Option | Parameter Setting |
---|---|
Try each address, in order, until one succeeds. |
|
Try each address, randomly, until one succeeds. |
LOAD_BALANCE=on FAILOVER=on |
Try one address, selected at random. |
LOAD_BALANCE=on |
Use each address in order until destination reached. |
SOURCE_ROUTE=on |
Use only the first address. |
LOAD_BALANCE=off FAILOVER=off SOURCE_ROUTE=off |
The following example shows a tnsnames.ora
file configured for client load balancing:
sales.us.example.com=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)))
The following example shows a tnsnames.ora
file configured for connect-time failover:
sales.us.example.com= (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=off) (FAILOVER=on) (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)))
The following example shows a tnsnames.ora file configured for Oracle Connection Manager and load balancing:
sales.us.example.com= (DESCRIPTION= (SOURCE_ROUTE=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc1)(PORT=1630)) (ADDRESS= (LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc2)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc3)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)))
See Also:
"Configuring Clients for Oracle Connection Manager" for additional information about configuring clients for source routing
13.1.2.1 Configuring Address List Parameters
The following procedure describes how to configure address list parameters:
-
Perform the procedure in "Creating a List of Listener Protocol Addresses".
-
Use Oracle Enterprise Manager Cloud Control or Oracle Net Manager to configure address list options.
-
For Oracle Enterprise Manager Cloud Control, select the appropriate option in the Connect-time Failover and Client Load Balancing section.
-
For Oracle Net Manager, click Advanced in the Address Configuration box. The Address List Options dialog box appears. Select the appropriate option.
-
13.1.3 About the Advanced Connect Data Parameters
Starting with Oracle Database 12c release 2 (12.2), data compression can be set in the sqlnet.ora
file. The parameters that set compression are SQLNET.COMPRESSION and SQLNET.COMPRESSION_LEVELS. Setting these parameters in the sqlnet.ora
file affects all the connections using the sqlnet.ora
file, except for Oracle Data Guard streaming redo and SecureFiles LOBs (Large Objects). The following example shows how to set compression:
SQLNET.COMPRESSION = yes SQLNET.COMPRESSION_LEVELS =(low,high)
The CONNECT_DATA section of a connect descriptor in the tnsnames.ora
file defines the destination database service. In the following example, SERVICE_NAME defines a service called sales.us.example.com
:
sales.us.example.com= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)))
In addition to the service name, you can optionally configure the connect data information with the parameters described in Table 13-3.
Table 13-3 Advanced Connect Data Settings in the tnsnames.ora File
Oracle Enterprise Manager Cloud Control/Oracle Net Manager Option | tnsnames.ora File Parameter | Description |
---|---|---|
Instance Name |
The database instance to access. The instance name can be obtained from the INSTANCE_NAME parameter in the initialization parameter file. |
|
Session Data Unit Size |
The transfer rate of data packets being sent across the network. You can specify the session data unit (SDU) size to change the performance characteristics having to do with the packets sent across the network. The SDU size limit is 2 MB. |
|
Use for Heterogeneous Services |
If you want an Oracle database server to access a third-party system through Heterogeneous Services, then set this option to |
|
The file name of the Oracle Rdb database. |
||
The type of service to use for the Oracle Rdb database. |
||
Oracle Rdb database identifier. |
In the following example, the transfer rate for data packets is set:
sales.us.example.com=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SDU=8192)
))
Use Oracle Enterprise Manager Cloud Control or Oracle Net Manager to configure advanced CONNECT_DATA parameters for either a network service name or a database service.
Configuring Advanced Connect Descriptor Parameters Using Oracle Enterprise Manager Cloud Control
The following procedure describes how to configure advanced connect descriptor parameters using Oracle Enterprise Manager Cloud Control:
-
Access the Directory Naming or Local Naming page in Oracle Enterprise Manager Cloud Control, as follows:
-
Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.
-
Select Local Naming or Directory Naming from the Administer list, and then select the Oracle home for the directory server or the location of the local configuration files.
-
Click Go.
The Directory Naming or Local Naming pages appear.
-
-
Select the directory service or network service name.
-
For Local Naming, select a network service from the list, and then click Edit.
-
For Directory Naming, search the network service name in the Simple Search section by selecting the network service or database service from the Results list, and then clicking Edit.
-
-
Click the Advanced tab.
-
Enter fields or select options as appropriate, and then click OK.
-
Click OK to update the connect data information.
Configuring Advanced Connect Descriptor Parameters Using Oracle Net Manager
The following procedure describes how to configure advanced connect descriptor parameters using Oracle Net Manager:
-
Start Oracle Net Manager.
-
In the navigator pane, select Service Naming from Directory or Local menus.
-
Select either the network service name or a database service.
The right pane displays the current destination service and address list.
-
In the Service Identification box, click Advanced.
The Advanced Service Options dialog box appears.
-
Enter fields or select options as appropriate, and then click OK.
-
If you are making these changes to the Local folder, then select Save Network Configuration from the File menu. Changes to the Directory folder are saved automatically.
13.2 Understanding Connection Load Balancing
The connection load balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers. In an Oracle Real Application Clusters (Oracle RAC) environment, connection load balancing can also balance the number of active connections among multiple instances.
Because the Listener Registration (LREG) process can register with remote listeners, a listener can always be aware of all instances and dispatchers, regardless of their location. Depending on the load information, a listener decides which instance and, if shared server is configured, which dispatcher to send the incoming client request.
In a shared server configuration, a listener selects a dispatcher in the following order:
-
Least loaded node.
-
Least loaded instance.
-
Least loaded dispatcher for that instance.
In a dedicated server configuration, a listener selects an instance in the following order:
-
Least loaded node.
-
Least loaded instance.
Load Balancing across nodes for HTTP presentation is introduced in this release. The remote listener can load balance across instances that are on different nodes for HTTP presentation using HTTP redirect.
An Oracle RAC environment requires that the dispatchers on each instance be cross-registered with the other listeners on the other nodes. This is achieved by the use of the LISTENER
attribute of the DISPATCHERS parameter.
Note:
For optimum connection load balancing results, the instances that belong to the same database service should be on equivalent hardware and software configurations.
See Also:
-
"Registering Information with a Remote Listener" for additional information about cross-registration
-
Oracle Database Reference for complete information about the SERVICE_NAMES and INSTANCE_NAME parameters
-
Configuring a Shared Server Architecture for additional information about the
LISTENER
attribute -
Oracle Database Global Data Services Concepts and Administration Guide for information about management of global services
13.2.1 Example of Connection Load Balancing for Shared Server Configuration
Figure 13-1 shows an Oracle RAC shared server database with two instances, sales1
and sales2
, of the same service, sales.us.example.com
. The instances sales1
and sales2
reside on computers sales1-server
and sales2-server
, respectively. Instance sales1
has one dispatcher and instance sales2
has two dispatchers. Listeners named listener
run on nodes 1 and 2. The listener
attribute in the DISPATCHERS parameter has been configured to allow for service registration of information to both listeners.
Figure 13-1 Load Balancing Environment for a Shared Server Configuration
Description of "Figure 13-1 Load Balancing Environment for a Shared Server Configuration"
In this example, sales2-server
is the least loaded node, sales2
is the least loaded instance, and dispatcher2
is the least loaded dispatcher. The following load information is registered.
-
The one minute load average for each instance is 600 for
sales1
and 400 forsales2
. This can happen if more processing is required onsales1-server
. -
The number of connections to each instance is 200 for
sales1
and 300 forsales2
. -
The number of dispatcher connections to each instance is 200 for
dispatcher1
, 100 fordispatcher2
, and 200 fordispatcher3
. -
The number of connections to
sales1
(200) is the same as that of its only dispatcher,dispatcher1
. -
The number of connections on
sales2
(300) is the sum of the connections on its two dispatchers,dispatcher2
(100) anddispatcher3
(200).
The listeners_sales
value in (LISTENER=listeners_sales)
is resolved through a local tnsnames.ora
file on both servers as follows:
listeners_sales= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))))
Based on the environment, the following actions occur. The numbered actions correspond to the arrows shown in Figure 13-2:
-
LREG processes for instances
sales1
andsales2
register with both listeners. The listeners are updated dynamically on the load of the instances and dispatchers. -
The client sends a connect request. A connect descriptor is configured to try each protocol address randomly until one succeeds:
sales.us.example.com= (DESCRIPTION= (
LOAD_BALANCE=on
) (FAILOVER=on
) (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))The listener on
sales1-server
was randomly chosen to receive the client connect request.The listener on
sales1-server
compares the load of the instancessales1
andsales2
. The comparison takes into account the load on nodessales1-server
andsales2-server
, respectively. Becausesales2-server
is less loaded thansales1-server
, the listener selectssales2-server
oversales1-server
. -
The listener compares the load on dispatchers
dispatcher2
anddispatcher3
. Becausedispatcher2
is less loaded thandispatcher3
, the listener redirects the client connect request todispatcher2
. -
The client connects directly to
dispatcher2
.
Figure 13-2 Load Balancing Example for a Shared Server Configuration
Description of "Figure 13-2 Load Balancing Example for a Shared Server Configuration"
13.2.2 Example of Connection Load Balancing for Dedicated Server Configuration
Figure 13-3 shows an Oracle RAC dedicated server database with two instances, sales1
and sales2
, of the same service, sales.us.example.com
. The instances sales1
and sales2
reside on computers sales1-server
and sales2-server
, respectively. Listeners named listener
run on nodes 1 and 2. The REMOTE_LISTENER initialization parameter has been configured to allow for service registration of information to both listeners.
Figure 13-3 Load Balancing Environment for a Dedicated Server Configuration
Description of "Figure 13-3 Load Balancing Environment for a Dedicated Server Configuration"
In this example, the following load information is registered:
-
sales1-server
has a node load average of 450 per minute. -
sales2-server
has a node load average of 200 per minute. -
sales1
has 200 connections. -
sales2
has 150 connections.
The listener_sales1
value in (REMOTE_LISTENER=listener_sales1)
is resolved through a local tnsnames.ora
file on the sales2-server
as follows:
listener_sales1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)))
The listener_sales2
value in (REMOTE_LISTENER=listener_sales2)
is resolved through a local tnsnames.ora
file on the sales1-server
as follows:
listener_sales2= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
Based on the environment, the following actions occur. The numbered actions correspond to the arrows shown in Figure 13-4:
-
LREG processes for instances
sales1
andsales2
register with both listeners. The listeners are dynamically updated on the load of the instances.Based on the preceding information,
sales2-server
is the least loaded node andsales2
is the least loaded instance. -
The client sends a connect request.
A connect descriptor is configured to try each protocol address randomly until one succeeds:
sales.us.example.com= (DESCRIPTION= (ADDRESS_LIST= (
LOAD_BALANCE=on
)(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))The listener on
sales1-server
was randomly chosen to receive the client connect request.The listener on
sales1-server
compares the load of the instancessales1
andsales2
. The comparison takes into account the load on nodessales1-server
andsales2-server
, respectively. Becausesales2-server
is less loaded thansales1-server
, the listener selectssales2-server
oversales1-server
. -
The listener on
sales1-server
redirects the client connect request to the listener onsales2-server
. -
The client connects to the listener on
sale2-server
. The listener starts a dedicated server process, and the dedicated server process inherits the connection request from the listener.
Figure 13-4 Load Balancing Example for a Dedicated Server Configuration
Description of "Figure 13-4 Load Balancing Example for a Dedicated Server Configuration"
13.3 Configuring Transparent Application Failover
Transparent Application Failover (TAF) instructs Oracle Net to fail over a failed connection to a different listener. This enables the user to continue to work using the new connection as if the original connection had never failed.
TAF involves manual configuration of a network service name that includes the FAILOVER_MODE parameter included in the CONNECT_DATA section of the connect descriptor.
This section contains the following topics:
13.3.1 About Transparent Application Failover
TAF is a client-side feature that allows clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side.
TAF is configured using either client-side specified TNS (Transparent Network Substrate) connect string or using server-side service attributes. If both methods are used to configure TAF, then the server-side service attributes supersede the client-side settings. Server-side service attributes are the preferred way to set up TAF.
TAF can operate in one of two modes, Session Failover and Select Failover. Session Failover re-creates lost connections and sessions. Select Failover replays queries that were in progress.
When there is a failure, callback functions are initiated on the client-side using Oracle Call Interface (OCI) callbacks. This works with standard OCI connections as well as connection pool and session pool connections.
TAF operates with Oracle Data Guard to provide automatic failover. TAF works with the following database configurations to effectively mask a database failure:
-
Oracle Real Application Clusters
-
Replicated systems
-
Standby databases
-
Single instance Oracle database
See Also:
-
Oracle Real Application Clusters Administration and Deployment Guide
-
Oracle Call Interface Programmer's Guide for more details on callbacks, and session pools
13.3.2 What Transparent Application Failover Restores
TAF automatically restores some or all of the following elements associated with active database connections. Other elements may need to be embedded in the application code to enable TAF to recover the connection.
-
Client-server database connections: TAF automatically reestablishes the connection using the same connect string or an alternate connect string that you specify when configuring failover.
-
Users' database sessions: TAF automatically logs a user in with the same user ID as was used before the failure. If multiple users were using the connection, then TAF automatically logs them in as they attempt to process database commands. Unfortunately, TAF cannot automatically restore other session properties. These properties can be restored by invoking a callback function.
-
Completed commands: If a command was completed at the time of connection failure, and it changed the state of the database, then TAF does not resend the command. If TAF reconnects in response to a command that may have changed the database, then TAF issues an error message to the application.
-
Open cursors used for fetching: TAF allows applications that began fetching rows from a cursor before failover to continue fetching rows after failover. This is called select failover. It is accomplished by re-running a
SELECT
statement using the same snapshot, discarding those rows already fetched and retrieving those rows that were not fetched initially. TAF verifies that the discarded rows are those that were returned initially, or it returns an error message. -
Active transactions: Any active transactions are rolled back at the time of failure because TAF cannot preserve active transactions after failover. The application instead receives an error message until a
ROLLBACK
command is submitted. -
Server-side program variables: Server-side program variables, such as PL/SQL package states, are lost during failures, and TAF cannot recover them. They can be initialized by making a call from the failover callback.
See Also:
13.3.3 About the FAILOVER_MODE Parameters
The FAILOVER_MODE parameter must be included in the CONNECT_DATA section of a connect descriptor. The FAILOVER_MODE parameter can contain the parameters described in Table 13-4.
Table 13-4 Additional Parameters of the FAILOVER_MODE Parameter
FAILOVER_MODE Parameters | Description |
---|---|
A different network service name for backup connections. A backup should be specified when using |
|
DELAY |
The amount of time in seconds to wait between connect attempts. If RETRIES is specified, then DELAY defaults to one second. If a callback function is registered, then this parameter is ignored. |
The setting for fast failover from the primary node to the backup node:
|
|
RETRIES |
The number of times to attempt to connect after a failover. If DELAY is specified, then RETRIES defaults to five retry attempts. If a callback function is registered, then this parameter is ignored. |
TRANSACTION |
The ability for a transaction to complete after a recoverable database error. This parameter is set using the SRVCTL utility or the DBMS_SERVICE package. This parameter is used with |
The type of failover. Three types of Oracle Net failover functionality are available by default to Oracle Call Interface (OCI) applications:
|
Note:
Oracle Net Manager does not provide support for TAF parameters. These parameters must be set manually.
13.3.4 Implementing Transparent Application Failover
Depending on the FAILOVER_MODE parameters, you can implement TAF in several ways. Oracle recommends the following methods:
13.3.4.1 TAF with Connect-Time Failover and Client Load Balancing
Implement TAF with connect-time failover and client load balancing for multiple addresses. In the following example, Oracle Net connects randomly to one of the protocol addresses on sales1-server
or sales2-server
. If the instance fails after the connection, then the TAF application fails over to the other node's listener, reserving any SELECT
statements in progress.
sales.us.example.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic))))
Although the preceding example has multiple addresses, the ADDRESS_LIST
parameter was not used. This is because the ADDRESS_LIST
parameter is not mandatory.
13.3.4.2 TAF Retrying a Connection
TAF also provides the ability to automatically retry connecting if the first connection attempt fails with the RETRIES and DELAY parameters. In the following example, Oracle Net tries to reconnect to the listener on sales1-server
. If the failover connection fails, then Oracle Net waits 15 seconds before trying to reconnect again. Oracle Net attempts to reconnect up to 20 times.
sales.us.example.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic) (RETRIES=20) (DELAY=15))))
13.3.4.3 TAF Pre-establishing a Connection
A backup connection can be pre-established. The initial and backup connections must be explicitly specified. In the following example, clients that use network service name sales1.us.example.com
to connect to the listener on sales1-server
are also preconnected to sales2-server
. If sales1-server
fails after the connection, then Oracle Net fails over to sales2-server
, preserving any SELECT
statements in progress. Similarly, Oracle Net preconnects to sales1-server
for those clients that use sales2.us.example.com
to connect to the listener on sales2-server
.
sales1.us.example.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (INSTANCE_NAME=sales1) (FAILOVER_MODE= (BACKUP=sales2.us.example.com) (TYPE=select) (METHOD=preconnect)))) sales2.us.example.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (INSTANCE_NAME=sales2) (FAILOVER_MODE= (BACKUP=sales1.us.example.com) (TYPE=select) (METHOD=preconnect))))
13.3.5 Verifying Transparent Application Failover
You can query the FAILOVER_TYPE, FAILOVER_METHOD, and FAILED_OVER columns in the V$SESSION view to verify that TAF is correctly configured. To view the columns, use a query similar to the following:
SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;
The output before failover looks similar to the following:
MACHINE FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER COUNT(*) -------------------- ------------- ---------- --- ---------- sales1 NONE NONE NO 11 sales2 SELECT PRECONNECT NO 1
The output after failover looks similar to the following:
MACHINE FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER COUNT(*) -------------------- ------------- ---------- --- ---------- sales2 NONE NONE NO 10 sales2 SELECT PRECONNECT YES 1
Note:
You can monitor each step of TAF using an appropriately configured OCI TAF CALLBACK
function.
See Also:
-
Oracle Database Reference for additional information about the V$SESSION view
13.4 Specifying the Instance Role for Primary and Secondary Instance Configurations
The INSTANCE_ROLE parameter is an optional parameter for the CONNECT_DATA section of a connect descriptor. It enables you to specify a connection to the primary or secondary instance of Oracle RAC configurations.
This parameter is useful when:
-
You want to explicitly connect to a primary or secondary instance. The default is the primary instance.
-
You want to use TAF to preconnect to a secondary instance.
Table 13-5 describes the INSTANCE_ROLE parameters.
Table 13-5 INSTANCE_ROLE Parameters
INSTANCE_ROLE Parameter | Description |
---|---|
PRIMARY |
Specifies a connection to the primary instance. |
SECONDARY |
Specifies a connection to the secondary instance. |
ANY |
Specifies a connection to whichever instance has the lowest load, regardless of primary or secondary instance role. |
Connection to Instance Role Type
In the following example of the tnsnames.ora
file, network service name sales_primary
enables connections to the primary instance, and network service name sales_secondary
enables connections to the secondary instance.
sales_primary= (DESCRIPTION= (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) (INSTANCE_ROLE=primary))) sales_secondary= (DESCRIPTION= (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) (INSTANCE_ROLE=secondary)))
Connection to a Specific Instance
There are times when Oracle Enterprise Manager Cloud Control and other system management products need to connect to a specific instance regardless of its role to perform administrative tasks. For these types of connections, configure (INSTANCE_NAME=
instance_name
)
and (INSTANCE_ROLE=any)
to connect to the instance regardless of its role.
In the following example, network service name sales1
enables connections to the instance on sales1-server
and sales2
enables connections to the instance on sales2-server
. (SERVER=dedicated)
is specified to force a dedicated server connection.
sales1= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (INSTANCE_ROLE=any) (INSTANCE_NAME=sales1)(SERVER=dedicated)
)) sales2= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (INSTANCE_ROLE=any) (INSTANCE_NAME=sales2)(SERVER=dedicated)
))
Note:
Failover is incompatible with the preceding settings.
TAF Pre-establishing a Connection
If TAF is configured, then a backup connection can be pre-established to the secondary instance. The initial and backup connections must be explicitly specified. In the following example, Oracle Net connects to the listener on sales1-server
and preconnects to sales2-server
, the secondary instance. If sales1-server
fails after the connection, then the TAF application fails over to sales2-server
, the secondary instance, preserving any SELECT
statements in progress.
sales1.example.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (INSTANCE_ROLE=primary)(FAILOVER_MODE=
(BACKUP=sales2.example.com)
(TYPE=select)
(METHOD=preconnect))
)) sales2.example.com= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (INSTANCE_ROLE=secondary)))
13.5 Understanding Static Service Registration
The listener uses the dynamic service information about the database and instance before using statically configured information in the listener.ora
file. Configuration of static service information is necessary in the following cases:
-
Use of external procedure calls
-
Use of Oracle Heterogeneous Services
-
Use of Oracle Data Guard
-
Remote database startup from a tool other than Oracle Enterprise Manager Cloud Control
-
Connections to Oracle databases earlier than Oracle8i release 2 (8.1)
Example 13-1 shows a listener.ora
file configured for static service registration. The LISTENER entry defines the listening protocol address for a listener named Listener, and the SID_LIST_LISTENER entry provides information about the external services statically supported by the Listener listener.
Example 13-1 Example listener.ora File
LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc) (queuesize=50)))) SID_LIST_listener= (SID_LIST= (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/oracle8) (PROGRAM=extproc)))
The SID_LIST_listener_name parameter setting in the listener.ora
file specifies information about the databases served by the listener. When services are configured statically, a listener starts a dedicated server process when it receives a client request. If the instance is not up, then the server returns an Oracle not available
error message.
If a database cannot find the listener, then configure the listener.ora
file with the GLOBAL_DBNAME parameter, as shown in the following example
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME
=sales.example.com)
(SID_NAME=sales)
(ORACLE_HOME=/u01/app/oracle))
Note:
A statically-configured global database name disables TAF. To use TAF, do not set the GLOBAL_DBNAME parameter in the SID_LIST_listener_name section of the listener.ora
file.
Table 13-6 describes static service settings in the listener.ora
file.
Table 13-6 Static Service Settings in listener.ora
Oracle Net Manager Field | listener.ora File Parameter | Description |
---|---|---|
The Oracle system identifier (SID) of the instance. You can obtain the SID value from the INSTANCE_NAME parameter in the initialization parameter file. |
||
The database service. While processing a client connection request, the listener tries to match the value of this parameter with the value of the SERVICE_NAME parameter in the client connect descriptor. If the client connect descriptor uses the SID parameter, then the listener does not attempt to map the values. This parameter is primarily intended for configurations with Oracle8 databases (where dynamic service registration is not supported for dedicated servers). This parameter may also be required for use with Oracle8i and later database services by some configurations. The value for this parameter is typically obtained from the combination of the DB_NAME and DB_DOMAIN parameters (DB_NAME.DB_DOMAIN) in the initialization parameter file, but the value can also contain any valid name used by clients to identify the service. When using a connect descriptor with a SERVICE_NAME parameter, ensure that any SID_DESC entry does not have the value |
||
The Oracle home location of the instance. Without this setting, the listener assumes its Oracle home for the instance. On Linux and UNIX, this setting is optional. On Microsoft Windows, this setting is ignored. The Oracle home specified by the ORACLE_HOME parameter in |
||
Not applicable |
SID_LIST_listener_name |
The section of the |
13.5.1 Configuring Static Registration for the Listener
The following procedure describes how to statically configure the listener:
Note:
If you are using connect-time failover or TAF, such as in an Oracle Real Application Clusters environment, then do not set the GLOBAL_DBNAME parameter.
-
Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.
-
Select Listeners from the Administer list, and then select the Oracle home that contains the configuration files.
-
Click Go. You may be prompted to log in to the database server.
The Listeners page appears.
-
Select a listener, and then click Edit.
The Edit Listener page appears.
-
Click the Static Database Registration tab, and then click Add.
The Add Database Service page appears. Enter the required information in the fields.
-
Click OK.
Note:
You can also configure static service information with Oracle Net Manager. See Statically Configure Database Service Information in the online help for additional information.
See Also:
"Configuring Dynamic Service Registration" for additional information about configuring dynamic service registration Oracle Databases
13.6 Configuring Connections to Third-Party Database Services
The following topics describe how to configure connections to third-party database services:
13.6.1 Default Configuration for External Procedures
An external procedure is a procedure called from another program, written in a different language. An example would be a PL/SQL program calling one or more C routines that are required to perform special-purpose processing.
When an application calls an external procedure, Oracle Database starts an external procedure agent named extproc. Using the network connection established by Oracle Database, the application passes the following information to the agent:
-
DLL or shared library name
-
External procedure name
-
Any parameters
The agent then loads the DLL or the shared library, and runs the external procedure and passes back to the application any values returned by the external procedure. The agent must reside on the same computer as the application making the external procedure call.
When you use the default configuration for external procedures, the extproc
agent is spawned directly by Oracle Database. There are no configuration changes required for either the listener.ora
or tnsnames.ora
file. However, you must define the environment variables to be used by external procedures in the extproc.ora
file located in the ORACLE_HOME/hs/admin
directory. If the default configuration for external procedures is not used, then the parameters listed in Table 13-7 must be set.
Table 13-7 External Procedures Settings in listener.ora
Oracle Enterprise Manager Cloud Control Field | listener.ora Parameter | Description |
---|---|---|
The name of the external procedure agent executable. Note: On Microsoft Windows, the executable must reside in the |
||
The environment variables to be used by external procedures in the Note: When Syntax: Example: Specify the EXTPROC_DLLS environment variable to restrict the DLLs that the extproc agent is allowed to load. Without the EXTPROC_DLLS environment variable, the extproc agent loads DLLs from the Set EXTPROC_DLLS to one of the following values:
Examples: "EXTPROC_DLLS=/home/xyz/mylib.so:/home/abc/urlib.so, LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs, MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt" "EXTPROC_DLLS=ONLY:/home/xyz/mylib.so:/home/abc/urlib.so, LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs, MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt" "EXTPROC_DLLS=ANY,LD_LIBRARY_PATH=/private/xpm/lib:/private/mylibs, MYPATH=/usr/ucb:/usr/local/packages,APL_ENV_FILE=/apl/conf/env.txt" |
||
The Oracle home location of the agent. |
||
A system identifier for the external procedure agent by any name. |
Footnote 1
The DLLs are separated by semi-colons (;) on Microsoft Windows.
Note:
The default configuration for external procedures does not require a network listener to work with Oracle Database and the extproc
agent. The extproc
agent is spawned directly by Oracle Database and eliminates the risks that the extproc
agent might be spawned by Oracle Listener unexpectedly. This default configuration is recommended for maximum security.
You can change the default configuration for external procedures and have the extproc
agent spawned by Oracle Listener. To do this, you must perform additional network configuration steps.
Having the extproc
agent spawned by Oracle Listener is necessary if you use:
-
Multi-threaded agent
-
Oracle Database in MTS mode on Microsoft Windows
-
The AGENT clause of the LIBRARY specification or the AGENT IN clause of the PROCEDURE specification such that you can redirect external procedures to a different
extproc
agent.
See Also:
Oracle Database Security Guide for additional information about securing external procedures
13.6.1.1 Configuring Oracle Net Services for External Procedures
You can change the default configuration for external procedures and have the extproc agent spawned by the listener similar to earlier releases of Oracle Database.
Example 13-2 shows a sample configuration in the listener.ora
file.
Example 13-2 listener.ora File with an External Procedure
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
Example 13-3 shows a sample configuration in the tnsnames.ora
file.
Example 13-3 tnsnames.ora File with an External Procedure
EXTPROC_CONNECTION_DATA= (DESCRIPTION= (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)
) (CONNECT_DATA=(SID=plsextproc)
))
The listener for external procedures should have a user account that does not have general access to the files owned by the oracle
user. Specifically, this user should not have permission to read or write to database files or to the Oracle server address space. In addition, this user should have read access to the listener.ora
file, but must not have write access to it.
Running the listener with lower privileges also prevents using the Listener Control SET
commands to alter the configuration of the listener in the listener.ora
file. For this reason, Oracle recommends that you complete listener.ora
file configuration before running the listener.
13.6.1.1.1 Modifying the Default Configuration for External Procedures
To modify the default configuration for external procedures, configure and run a separate or existing listener to serve external procedures. The following procedure describes how to modify the default configuration:
-
Configure an existing listener to serve external procedures using Oracle Net Configuration Assistant as follows. For most installation types, this listener is named
LISTENER
.-
Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.
-
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
-
Click Go.
The Listeners page appears.
-
Select the existing listener created by Oracle Net Configuration Assistant, and then click Edit.
The Edit Listeners page appears.
-
In the Addresses section, select the protocol address for external procedures, and then click Add.
-
Click the Other Services tab.
-
Select the row representing the service information for external procedures, and then click Add.
-
-
Add service information about the extproc agent in the
listener.ora
file, including the parameters described in Table 13-7.
13.6.1.1.2 Creating a New Listener to Run External Procedures
To configure and run a separate listener to serve external procedures, create the external procedure entries for a different listener using Oracle Net Configuration Assistant. The following procedure describes how to create a new listener:
-
Create a listener to exclusively handle external procedures, as follows:
-
Navigate to the Listeners page.
-
Click Create.
The Create Listener page appears.
-
In the Listener Name field, enter a unique listener name, such as
LISTENEREXTPROC
, in the Listener Name field.
-
-
In the Addresses section, configure an IPC protocol address, as follows:
-
Click Add.
The Add Address page appears.
-
From the Protocol list, select IPC.
-
In the Key field, enter a key value of the extproc agent.
-
Click OK.
See Also:
"Configuring Listening Protocol Addresses" for additional information about configuring listener protocol addresses
-
-
Add service information about the extproc agent in the
listener.ora
file, including the parameters described in Table 13-7, as follows:-
Click the Other Services tab.
-
Click Add.
The Create Other Service page appears.
-
Enter the following values in the fields:
-
extproc
in the Program Name field. -
The Oracle home where the
extproc
executable resides in the Oracle Home Directory field. -
System identifier, such as
extproc
, in the SID field.
-
-
In the Environment Variables section, click Add Another Row.
-
Enter the EXTPROC_DLLS environment variable in the Name field, and the directory path and file name of the DLLs in the Value field.
-
Click OK.
The Create Listener page appears.
-
Click OK to add the listener.
The listener is added to the Listeners page.
The
listener.ora
file updates with information for external procedures, as shown in the following output:LISTENEREXTPROC= (DESCRIPTION= (ADDRESS= (PROTOCOL=ipc)(KEY=extproc)))
-
-
Start the listener for external procedures from a user account with lower privileges than the
oracle
user.
See Also:
-
"Starting Oracle Net Listener and the Oracle Database Server" for instructions on using the Listener Control utility
START
command to start the listener -
Oracle Database Development Guide for instruction on enabling external procedure calls
13.6.2 About Oracle Net Services for Oracle Heterogeneous Services
Heterogeneous Services is an integrated component within the Oracle database server, and provides the generic technology for accessing third-party systems from the Oracle database server. Heterogeneous Services enables you to:
-
Use Oracle SQL to transparently access data stored in third-party systems as if the data resides within an Oracle database server.
-
Use Oracle procedure calls to transparently access third-party systems, services, or application programming interfaces (APIs) from your Oracle distributed environment.
While Heterogeneous Services provides the generic technology in the Oracle database server, a Heterogeneous Services agent is required to access a particular third-party system.
13.6.2.1 Configuring Oracle Database to Connect to Agents
To initiate a connection to the third-party system, the Oracle database server starts an agent process through the listener on the gateway. The following procedure describes how to configure the Oracle database server to be able to connect to the agents:
-
Configure the listener on the gateway to listen for incoming requests from the Oracle database server and spawn Heterogeneous Services agents by configuring the following parameters in the
listener.ora
file: -
Configure the PROGRAM, ORACLE_HOME, and SID parameters in Oracle Enterprise Manager Cloud Control.
-
Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.
-
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
-
Click Go.
The Listeners page appears.
-
Select the listener created by Oracle Net Configuration Assistant, and then click Edit.
The Edit Listeners page appears.
-
Click the Other Services tab.
-
Click Add.
The Create Other Service page appears.
-
Enter the program name in the Program Name field that will be run to create a gateway, the Oracle home where the agent executable resides in the Oracle Home Directory field, and the Oracle System Identifier (SID) or service name of the third-party system in the SID field.
-
Click OK.
The Edit Listener page appears.
-
Click OK to modify the listener.
The Listeners page appears.
The
listener.ora
file updates information about the Heterogeneous Services, as shown in the following:SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=sybasegw) (ORACLE_HOME=/oracle12c) (PROGRAM=tg4sybs)))
-
-
On the computer where the Oracle database resides, set up a network service name to connect to the listener on the gateway. The connect descriptor must include the
HS=ok
clause to ensure the connection uses Heterogeneous Services, as follows:-
Create a network service name that can be used for connections from the Oracle database server to a third-party system.
See Also:
Task 1, "Configure Net Services Names" for local naming instructions and Task 2, "Create Net Service Names in the Directory" for directory naming instructions
-
Use either Oracle Enterprise Manager Cloud Control or Oracle Net Manager to configure
HS=ok
.-
For Oracle Enterprise Manager Cloud Control, access the Net Services Administration page, select Local Naming for the listener, and then click the Advanced tab in the Create Net Service Name page. Next, click Use for Heterogeneous Services.
-
For Oracle Net Manager, click Advanced in the Service Identification box. The Advanced Service Options dialog box appears. Click Use for Heterogeneous Services.
-
-
Click OK to confirm the change.
The
tnsnames.ora
file updates with the new network service name configured for Heterogeneous Services, as shown in the following:sybase_gtw= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=gate-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sybasegw) ) (HS=ok))) )
-
13.6.3 Configuring Oracle Net Services for an Oracle Rdb Database
Oracle Rdb is a database for Digital 64-bit operating systems. Because Oracle Rdb has its own listener, the client interacts with Rdb in the same manner as it does with an Oracle database.
To initiate a connection to an Oracle Rdb, set up a network service name to connect to the Oracle Rdb database using the parameters described in Table 13-8.
Table 13-8 Oracle RDB Database Settings in a Connect Descriptor
Oracle Enterprise Manager Cloud Control Field | tnsnames.ora Parameter | Description |
---|---|---|
The file name of an Oracle Rdb database. |
||
The type of service to use for an Oracle Rdb database. It is used by Rdb interface tools. This feature should only be used if the application supports both Oracle Rdb and Oracle database services, and you want the application to load balance between the two. |
||
The Oracle Rdb database. Optional. |
The following procedure describes how to configure a client for an Oracle Rdb database:
-
Create a network service name that can be used for connections from the Oracle server to a third-party system.
See Also:
Task 1, "Configure Net Services Names" for local naming instructions and Task 2, "Create Net Service Names in the Directory" for directory naming instructions
-
Use either Oracle Enterprise Manager Cloud Control or Oracle Net Manager to set the Oracle Rdb parameters.
-
For Oracle Enterprise Manager Cloud Control, access the Net Services Administration page, select Local Naming for the listener, and then click the Advanced tab in the Create Net Service Name page.
-
For Oracle Net Manager, click Advanced in the Service Identification section. The Advanced Service Options dialog box appears.
-
-
Enter the file name of an Oracle Rdb database in the Rdb Database field.
-
(Optional) Specify the type of service in the Type of Service field, if needed, and enter the global database name in the Global Database Name field, and then click OK.
The
tnsnames.ora
file updates with the new network service name configured for the Oracle Rdb database, similar to the following:alpha5= (DESCRIPTION= (ADDRESS=...) (CONNECT_DATA= (SERVICE_NAME=generic) (RDB_DATABASE=[.mf]mf_personnel.rdb) (GLOBAL_NAME=alpha5)))
In the following example, the TYPE_OF_SERVICE parameter is used to load balance between an Oracle Rdb database service and an Oracle database service:
alpha5= (DESCRIPTION_LIST= (DESCRIPTION= (ADDRESS=...) (CONNECT_DATA= (SERVICE_NAME=generic) (RDB_DATABASE=[.mf]mf_personnel.rdb) (GLOBAL_NAME=alpha5))) (DESCRIPTION= (ADDRESS=...) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)) (TYPE_OF_SERVICE=oracle_database))