Configuring Advanced Network Address and Connect Data Information

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.acme.com can connect to sales.us.acme.com using listeners on either sales1-server or sales2-server.

Oracle Enterprise Manager

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 net service name.

For Directory Naming, perform a search of the net service name in the Simple Search section, select the net service or database service from the Results list, and then click Edit. For Local Naming, select a net service from the list, and then click Edit.

In the Addresses section, click Add.

The Add Address page appears.

From the Protocol list, select the protocol on 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.

Order the protocol addresses according to where they should be in the protocol address list with the left-arrow and right-arrow buttons. Unless multiple address options are configured, the first address in the list is contacted.

If you are making these changes to the Local folder, then choose File > Save Network Configuration.

Configuring 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. The addresses can be chosen randomly or tried sequentially.

When multiple protocol addresses have been configured for a net service name or database service, you can configure the parameters described in Table 13-1.

Table 13-1 Address List Parameters

Parameter

Description

SOURCE_ROUTE (Source Routing)

When set to on, instructs Oracle Net to use each address in the order presented until the destination is reached. This parameter is required for reaching the destination using a specific route, that is, by specific machines. This parameter is used to enable connections to Oracle Connection Manager.

At connect time, instructs Oracle Net to fail over to a different listener if the first listener fails when set to on. The number of addresses in the list determines how many addresses are tried. When set to off, instructs Oracle Net to try one address.

Important: Oracle Corporation recommends not setting the GLOBAL_DBNAME parameter in the SID_LIST_listener_name section of the listener.ora file. A statically configured global database name disables connect-time failover.

LOAD_BALANCE (Client Load Balancing)

When set to on, instructs Oracle Net to progress through the list of protocol addresses in a random sequence, balancing the load on the various listeners. When set to off, instructs Oracle Net to try the addresses sequentially until one succeeds.

Client load balancing is turned on by default for multiple connect descriptors (DESCRIPTION_LIST).

Note:

It is not possible to set client load balancing or connect-time failover with source routing. While connect-time failover and client load balancing select an address from a list, source routing connects to each address in the list sequentially.

Source routing involves other configuration that goes beyond the scope of this section.

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

Oracle Enterprise Manager

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 net service name.

For Directory Naming, perform a search of the net service name in the Simple Search section, select the net service or database service from the Results list, and then click Edit. For Local Naming, select a net service from the list, and then click Edit.

Because the PMON 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 to.

In a shared server configuration, a listener selects a dispatcher in the following order: 1) least loaded node, 2) least loaded instance, and 3) least loaded dispatcher for that instance. In a dedicated server configuration, a listener selects an instance in the following order: 1) least loaded node, and 2) least loaded instance.

If a database service has multiple instances on multiple nodes, the listener selects the least loaded instance on the least loaded node. If shared server is configured, then the least loaded dispatcher of the selected instance is chosen.

An Oracle9i Real Application Clusters 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.

For optimum connection load balancing results, the instances that belong to the same database service should be on equivalent hardware and software configurations.

Example: Connection Load Balancing for Shared Server Configuration

Figure 13-1 shows an Oracle9i Real Application Clusters shared server database with two instances, sales1 and sales2, of the same service, sales.us.acme.com. The instances sales1 and sales2 reside on computers sales1-server and sales2-server, respectively. sales1 has one dispatcher and sales2 has two dispatchers. Listeners named listener run on nodes 1 and 2, respectively. The listener attribute in the DISPATCHERS parameter has been configured to allow for service registration of information to both listeners.

Based on the environment, the following actions occur. The numbered actions correspond to the arrows shown in Figure 13-2:

PMON processes for instances sales1 and sales2 register with both listeners. The listeners are updated on the load of the instances and dispatchers dynamically. The following load information is registered:

sales1-server

sales2-server

1 Minute Node Load Average

600

400

sales1

sales2

Number of Connections to Instance

200

300

dispatcher1

dispatcher2

dispatcher 3

Number of Connections to Dispatchers

200

100

200

The load average on sales2-server (400) is less than the load average on sales1-server (600). This can happen if more processing is required on sales1-server. 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) and dispatcher 3 (200). Therefore, sales2 has more connections than sales1. In this example, sales2-server is the least loaded node, sales2 is the least loaded instance, and dispatcher2 is the least loaded dispatcher.

The client sends a connect request.

A connect descriptor is configured to try each protocol address randomly until one succeeds:

The listener on sales1-server was randomly chosen to receive the client connect request.

The listener on sales1-server compares the load of the instances sales1 and sales2. The comparison takes into account the load on nodes sales1-server and sales2-server, respectively. Since sales2-server is less loaded than sales1-server, the listener selects sales2-server over sales1-server.

The listener compares the load on dispatchers dispatcher2 and dispatcher3. Because dispatcher2 is less loaded than dispatcher3, the listener redirects the client connect request to dispatcher2.

The client connects directly to dispatcher2.

Figure 13-2 Load Balancing Example for a Shared Server Configuration

Example: Connection Load Balancing for Dedicated Server Configuration

Figure 13-3 shows an Oracle9i Real Application Clusters dedicated server database with two instances, sales1 and sales2, of the same service, sales.us.acme.com. The instances sales1 and sales2 reside on computers sales1-server and sales2-server, respectively. Listeners named listener run on nodes 1 and 2, respectively. The REMOTE_LISTENER parameter has been configured to allow for service registration of information to both listeners.

The listener on sales1-server was randomly chosen to receive the client connect request.

The listener on sales1-server compares the load of the instances sales1 and sales2. The comparison takes into account the load on nodes sales1-server and sales2-server, respectively. Since sales2-server is less loaded than sales1-server, the listener selects sales2-server over sales1-server.

The listener on sales1-server redirects the client connect request to the listener on sales2-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.

What TAF Restores

TAF automatically restores some or all of the following elements associated with active database connections. Other elements, however, 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 prior to 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, however, be restored by invoking a callback function.

Executed Commands

If a command was completely executed upon connection failure, and it changed the state of the database, TAF does not resend the command. If TAF reconnects in response to a command that may have changed the database, 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-executing 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 is submitted.

Serverside Program Variables

Serverside program variables, such as PL/SQL package states, are lost during failures; TAF cannot recover them. They can be initialized by making a call from the failover callback.

Example: 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, the TAF application fails over to the other node's listener, reserving any SELECT statements in progress.

Example: 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, Oracle Net waits 15 seconds before trying to reconnect again. Oracle Net attempts to reconnect up to 20 times.

Example: 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 net service name sales1.us.acme.com to connect to the listener on sales1-server are also preconnected to sales2-server. If sales1-server fails after the connection, Oracle Net fails over to sales2-server, preserving any SELECT statements in progress. Likewise, Oracle Net preconnects to sales1-server for those clients that use sales2.us.acme.com to connect to the listener on sales2-server.

TAF Verification

You can query FAILOVER_TYPE, FAILOVER_METHOD, and FAILED_OVER columns in the V$SESSION view to verify that TAF is correctly configured.

Use the V$SESSION view to obtain information about the connected clients and their TAF status. For example, query the FAILOVER_TYPE, FAILOVER_METHOD, and FAILED_OVER columns to verify that you have correctly configured TAF as in the following SQL statement:

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 Oracle9i Real Application Clusters 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.

INSTANCE_ROLE supports the following values:

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

Example: Connection to Instance Role Type

In the following example, net service name sales_primary enables connections to the primary instance, and net service name sales_secondary enables connections to the secondary instance.

There are times when Oracle Enterprise Manager 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, net 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.

If Transparent Application Failover (TAF) is configured, 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, the TAF application fails over to sales2-server, the secondary instance, preserving any SELECT statements in progress.

An external procedure is a procedure called from another program, but 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.

You can configure the listener to listen for external procedure calls. When an application calls an external procedure, the listener starts an external procedure agent named extproc. Using the network connection established by the listener, 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.

Default Configuration for External Procedures

Oracle Net Configuration Assistant configures a listener to accept connections for both the database and external procedures during a database server installation. In addition, Oracle Net Configuration Assistant configures a net service name for the external procedures in tnsnames.ora file on the database server. The external procedure agent will only be able to load DLLS from $ORACLE_HOME/lib on UNIX operating systems and ORACLE_HOME\bin on Windows.

Example 13-1 shows the default configuration in the listener.ora file.

Modifying Configuration of External Procedures for Higher Security

To achieve a higher level of security in a production environment, modify the default configuration by performing the following tasks:

Configure and run a separate listener dedicated to servicing external procedure requests.

The extproc agent spawned by the listener inherits the operating system privileges of the listener. Therefore, configure this listener to run with operating system privileges lower than those of the listener for the database.

Restrict the DLLs that the extproc agent can load by listing them explicitly in the listener.ora file.

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.

In the Addresses section, select the protocol address for external procedures, and then click Remove.

Click the Other Services tab.

Select the row representing the service information for external procedures, and then click Remove.

Create another listener to exclusively handle external procedures:

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

Click Add.

The Add Address page appears.

From the Protocol list, select IPC.

In the Key field, enter a key value of extproc.

Note:

If the computer has more than one Oracle home or more than one listener, each listener must specify a unique KEY. For example, you can use extproc1 for the first listener, extproc2 for the second listener, and so on.

Add service information about extproc in the listener.ora file, including the parameters described in Table 13-5.

Table 13-5 External Procedures Settings in listener.ora

Oracle Enterprise Manager Field

listener.ora Parameter

Description

Program Name

PROGRAM

Specify the name of the external procedure agent executable.

Note: On Windows, the executable must reside in the ORACLE_HOME\bin directory.

Environment Variables

ENVS

Specify the EXTPROC_DLLS environment variable to restrict the DLLs that extproc is allowed to load. Without the EXTPROC_DLLS environment variable, extproc loads DLLs from $ORACLE_HOME/lib on UNIX operating systems and ORACLE_HOME\bin on Windows.

Set EXTPROC_DLLS to one of the following values:

Colon-separated list of the DLLs

Syntax:"DLL:DLL"

Description: This value allows extproc to load the specified DLLs and the DLLs from $ORACLE_HOME/lib on UNIX operating systems and ORACLE_HOME\bin on Windows. You must enter the complete directory path and file name of the DLLs.

ONLY (Recommended for maximum security)

Syntax:"ONLY:DLL:DLL"

Description: This value allows extproc to load only the specified DLLs. You must enter the complete directory path and file name of the DLLs.

ANY

Syntax:"ANY"

Description: This value allows extproc to load any DLL. ANY disables DLL checking.

Start the listener for external procedures from a user account with lower privileges than the oracle user.

Ensure that this user account does not have general access to oracle-owned files. 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 you from using Listener Control utility SET commands to alter the configuration of this listener in the listener.ora file. For this reason, Oracle Corporation recommends that you complete listener.ora file configuration prior to running the listener.

Heterogeneous Services are an integrated component within the Oracle database server, and provides the generic technology for accessing non-Oracle systems from the Oracle database server. Heterogeneous Services enable you to:

Use Oracle SQL to transparently access data stored in non-Oracle systems as if the data resides within an Oracle database server

While Heterogeneous Services provides the generic technology in the Oracle database server, a Heterogeneous Service agent is required to access a particular non-Oracle system.

To initiate a connection to the non-Oracle system, the Oracle database server starts an agent process through the listener on the gateway. For the Oracle database server to be able to connect to the agent, perform the following steps:

Configure the listener on the gateway to listen for incoming requests from the Oracle database server and spawn Heterogeneous Services agents by configuring the parameters described in Table 13-6 in the listener.ora file.

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.

In the Addresses section, select the protocol address for external procedures, and then click Remove.

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 executed to create a gateway, the Oracle home where the agent executable resides in the Oracle Home Directory field, and the SID or service name of the non-Oracle 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:

On the computer where the Oracle database resides, set up a net service name to connect to the listener on the gateway. The connect descriptor must also include the HS=ok clause to make sure the connection uses Heterogeneous Services:

Create a net service name that can be used for connections from the Oracle database server to a non-Oracle system.

Configuring Oracle Net Services for an Oracle Rdb Database

Oracle Rdb is a database for Digital's 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 net service name to connect to the Oracle Rdb database using the parameters described in Table 13-7.

Table 13-7 Oracle RDB Database Settings in a Connect Descriptor

Oracle Enterprise Manager Field

tnsnames.ora Parameter

Description

Rdb Database

RDB_DATABASE

Specify the file name of an Oracle Rdb database.

Type of Service

TYPE_OF_SERVICE

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

Global Database Name

GLOBAL_NAME

(Optional) Specify the Oracle Rdb database.

See Also:

Oracle Rdb documentation

To configure a client for an Oracle Rdb database, use Oracle Net Manager:

Create a net service name that can be used for connections from the Oracle server to a non-Oracle system.