13 Enabling Advanced Features of Oracle Net Services

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.

sales.us.acme.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.acme.com)))

To add a network protocol address to an existing net service name or database service, use either Oracle Enterprise Manager or Oracle Net Manager.

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.

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

To configure connect-time failover or client load balancing:

1. Perform the procedure in "Creating a List of Listener Protocol Addresses".

2. Use either Oracle Enterprise Manager or Oracle Net Manager to configure address list options.

   For Oracle Enterprise Manager, 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.

   Table 13-2 describes the address list options.

The following example shows a tnsnames.ora file configured for client load balancing:

sales.us.acme.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.acme.com))) 

The following example shows a tnsnames.ora file configured for connect-time failover:

sales.us.acme.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.acme.com)))

Configuring Advanced Connect Data Parameters

The CONNECT_DATA section of a connect descriptor defines the destination database service. In the following example, SERVICE_NAME defines a service called sales.us.acme.com:

sales.us.acme.com=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
 (SERVICE_NAME=sales.us.acme.com))) 

Besides the service name, you can optionally configure the connect data information with the parameters described in Table 13-3.

To configure advanced CONNECT_DATA parameters for either a net service name or a database service:

To add a network protocol address to an existing net service name or database service, use either Oracle Enterprise Manager or Oracle Net Manager.

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.

Figure 13-1 Load Balancing Environment for a Shared Server Configuration

The listeners_sales value in (LISTENER=listeners_sales) can be then resolved through a local tnsnames.ora file on the both servers as follows:

listeners_sales= 
 (DESCRIPTION= 
   (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:

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

2. The client sends a connect request.

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

   sales.us.acme.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.acme.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 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.

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

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

Figure 13-3 Load Balancing Environment for a Dedicated Server Configuration

The listener_sales2 value in (REMOTE_LISTENER=listener_sales2) can be then resolved through a local tnsnames.ora file on the sales1-server as follows:

listener_sales2= 
 (DESCRIPTION= 
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))

The listener_sales1 value in (REMOTE_LISTENER=listener_sales1) can be then resolved through a local tnsnames.ora file on the sales2-server as follows:

listener_sales1= 
 (DESCRIPTION= 
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)))

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

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

   	sales1-server	sales2-server
   1 Minute Node Load Average	450	200

   
   

   	sales1	sales2
   Number of Connections to Instance	200	150

   
   

   In this example, sales2-server is the least loaded node and sales2 is the least loaded instance.

2. The client sends a connect request.

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

   sales.us.acme.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.acme.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 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.

3. The listener on sales1-server redirects the client connect request to the listener on sales2-server.

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

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.

TAF Database Configurations

TAF works with the following database configurations to effectively mask a database failure:

• Oracle9i Real Application Clusters

• Replicated systems

• Standby databases

• Single instance Oracle database

  
  

  See Also: Real Application Clusters Installation and Configuration

  
  

FAILOVER_MODE Parameters

The FAILOVER_MODE parameter must be included in the CONNECT_DATA section of a connect descriptor. FAILOVER_MODE can contain the subparameters described in Table 13-4.

TAF Implementation

Depending on the FAILOVER_MODE parameters, you can implement TAF in a number of ways. Oracle recommends the following methods:

• Example: TAF with Connect-Time Failover and Client Load Balancing

• Example: TAF Retrying a Connection

• Example: TAF Pre-Establishing a Connection

sales.us.acme.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.acme.com) 
(FAILOVER_MODE=
(TYPE=select) 
(METHOD=basic))))

sales.us.acme.com=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales1-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.acme.com) 
(FAILOVER_MODE=
(TYPE=select) 
(METHOD=basic)
(RETRIES=20)
(DELAY=15))))

sales1.us.acme.com=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales1-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.acme.com) 
     (INSTANCE_NAME=sales1) 
(FAILOVER_MODE=
(BACKUP=sales2.us.acme.com) 
(TYPE=select) 
(METHOD=preconnect))))
sales2.us.acme.com=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales2-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.acme.com) 
     (INSTANCE_NAME=sales2)
(FAILOVER_MODE=
(BACKUP=sales1.us.acme.com) 
(TYPE=select) 
(METHOD=preconnect))))

SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

MACHINE              FAILOVER_TYPE FAILOVER_M FAI   COUNT(*)
-------------------- ------------- ---------- --- ----------
sales1               NONE          NONE       NO          11
sales2               SELECT        PRECONNECT NO           1

MACHINE              FAILOVER_TYPE FAILOVER_M FAI   COUNT(*)
-------------------- ------------- ---------- --- ----------
sales2               NONE          NONE       NO          10
sales2               SELECT        PRECONNECT YES          1

Configuring Oracle Net Services for External Procedures

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.

This section contains the following topics:

• Default Configuration for External Procedures

• Modifying Configuration of External Procedures for Higher Security

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=sales.us.acme.com)
      (ORACLE_HOME=/oracle)
      (SID_NAME=sales))
(SID_DESC=
   (SID_NAME=plsextproc)
   (ORACLE_HOME=/oracle)
   (PROGRAM=extproc)))

EXTPROC_CONNECTION_DATA=            
 (DESCRIPTION=                     
   (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))                      
   (CONNECT_DATA=
    (SID=plsextproc)))

"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"

Configuring Oracle Net Services for Oracle Heterogeneous Services

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

• Use Oracle procedure calls to transparently access non-Oracle 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 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:

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

   Table 13-6 Heterogeneous Services Settings in listener.ora

   Oracle Enterprise Manager Field	listener.ora Parameter	Description
   Program Name	PROGRAM	Specify the name of the agent executable.
   Oracle Home	ORACLE_HOME	Specify the Oracle home location of the agent executable.
   SID	SID_NAME	Specify the Oracle System Identifier (SID).

   
   

   To configure the SID, ORACLE_HOME, and PROGRAM parameters:

2. Access the Oracle Net Administration page in Oracle Enterprise Manager.

   
   

   See Also: "Oracle Enterprise Manager"

   
   

3. Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.

4. Click Go.

   The Listeners page appears.

5. Select the listener created by Oracle Net Configuration Assistant, and then click Edit.

   The Edit Listeners page appears.

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

7. Click the Other Services tab.

8. Click Add.

   The Create Other Service page appears.

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

10. Click OK.

    The Edit Listener page appears.

11. 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=/oracle10g)
    (PROGRAM=tg4sybs)))
    

    
    

    See Also: Oracle Heterogeneous Connectivity Administrator's Guide

    
    

12. 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:

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

       
       

       See Also: "Task 1: Configure Net Service Names " for local naming instructions and "Task 2: Create or Modify Net Entries" for directory naming instructions

       
       

    2. Use either Oracle Enterprise Manager or Oracle Net Manager to configure HS=ok.

       For Oracle Enterprise Manager, click the Advanced tab in the Create Net Service Name page, and then click the 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.

    3. Click OK to confirm the change.

       The tnsnames.ora file updates with the new net 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)))
       

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.

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

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

   
   

   See Also: "Task 1: Configure Net Service Names " for local naming instructions and "Task 2: Create or Modify Net Entries" for directory naming instructions

   
   

2. Use either Oracle Enterprise Manager or Oracle Net Manager to the Oracle Rdb parameters.

   For Oracle Enterprise Manager, click the Advanced tab in the Create Net Service Name page.

   For Oracle Net Manager, click Advanced in the Service Identification box. The Advanced Service Options dialog box appears.

3. Enter the file name of an Oracle Rdb database in the Rdb Database field.

4. Optionally, enter the global database name in the Global Database Name field, and, if needed, specify the type of service in the Type of Service field, and then click OK.

   The tnsnames.ora file updates with the new net service name configured for the Oracle Rdb database, as shown in the following:

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

   In the following example, TYPE_OF_SERVICE 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.acme.com))
   (TYPE_OF_SERVICE=oracle9_database))
   

   
   

   See Also: Oracle Rdb documentation