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

• 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 Enterprise Manager Cloud Control:

1. Access the Directory Naming or Local Naming page in Oracle Enterprise Manager Cloud Control:

   1. Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.

      See Also:

      "Using Oracle Enterprise Manager Cloud Control to Configure Oracle Net Services"

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

   3. Click Go.

      The Local Naming or Directory Naming page appears.

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

3. In the Addresses section, click Add.

   The Add Address page appears.

4. From the Protocol list, select the protocol which the listener is configured to listen. This protocol must also be installed on the client.

5. Enter the appropriate parameter information for the selected protocol in the fields provided.

   See Also:

   Oracle Database Net Services Reference for protocol parameter settings

6. (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

7. Click OK.

   The protocol address is added to the Addresses section.

8. Click OK to update the address information.

The following procedure describes how to add a network protocol to an existing network service name or database service using Oracle Net Manager:

1. Start Oracle Net Manager.

   See Also:

   "Using Oracle Net Manager to Configure Oracle Net Services"

2. In the navigator pane, select Service Naming from the Directory or Local menus.

3. Select either the network service name or a database service.

   The right pane displays the current destination service and address list.

4. In the Address Configuration box, click the plus sign (+) to add a new address.

   A new Address tab appears:

   1. Select the protocol and enter appropriate address information.

      See Also:

      Oracle Database Net Services Reference for details about protocol address parameters

   2. (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

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

5. Select Save Network Configuration from the File menu.

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-2 describes the address list options.

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

• Configuring Address List 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 = on
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.

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.

The following procedure describes how to configure advanced connect descriptor parameters using Oracle Enterprise Manager Cloud Control:

1. Access the Directory Naming or Local Naming page in Oracle Enterprise Manager Cloud Control, as follows:

   1. Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.

      See Also:

      "Using Oracle Enterprise Manager Cloud Control to Configure Oracle Net Services"

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

   3. Click Go.

      The Directory Naming or Local Naming pages appear.

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

3. Click the Advanced tab.

4. Enter fields or select options as appropriate, and then click OK.

5. Click OK to update the connect data information.

The following procedure describes how to configure advanced connect descriptor parameters using Oracle Net Manager:

1. Start Oracle Net Manager.

   See Also:

   "Using Oracle Net Manager to Configure Oracle Net Services"

2. In the navigator pane, select Service Naming from Directory or Local menus.

3. Select either the network service name or a database service.

   The right pane displays the current destination service and address list.

4. In the Service Identification box, click Advanced.

   The Advanced Service Options dialog box appears.

5. Enter fields or select options as appropriate, and then click OK.

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

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 for sales2. This can happen if more processing is required on sales1-server.

• The number of connections to each instance is 200 for sales1 and 300 for sales2.

• The number of dispatcher connections to each instance is 200 for dispatcher1, 100 for dispatcher2, and 200 for dispatcher3.

• 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 dispatcher3 (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:

1. LREG processes for instances sales1 and sales2 register with both listeners. The listeners are updated dynamically on the load of the instances and dispatchers.

2. 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 instances sales1 and sales2. The comparison takes into account the load on nodes sales1-server and sales2-server, respectively. Because 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

Description of "Figure 13-2 Load Balancing Example for a Shared 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:

1. LREG processes for instances sales1 and sales2 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 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.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 instances sales1 and sales2. The comparison takes into account the load on nodes sales1-server and sales2-server, respectively. Because 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

Description of "Figure 13-4 Load Balancing Example for a Dedicated Server Configuration"

The COLOCATION_TAG parameter is an alphanumeric string that you can use with the CONNECT_DATA parameter of the TNS connect string.

When you set the colocation_tag within the CONNECT_DATA parameter, load balancing is ignored. The listener makes an effort to send all connections that have the same colocation_tag to the same database instance. The instance selection algorithm is based on the colocation_tag, and the list of available instances for the specified service.

For example, the listener will try to route all clients to the same database instance that have the COLOCATION_TAG set to interactive in the connection descriptor.

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

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

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.

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.

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

• TAF with Connect-Time Failover and Client Load Balancing
  
• TAF Retrying a Connection
  
• TAF Pre-establishing a Connection
  

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

The following procedure describes how to statically configure the listener:

1. Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.

   See Also:

   "Using Oracle Enterprise Manager Cloud Control to Configure Oracle Net Services"

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

3. Click Go. You may be prompted to log in to the database server.

   The Listeners page appears.

4. Select a listener, and then click Edit.

   The Edit Listener page appears.

5. Click the Static Database Registration tab, and then click Add.

   The Add Database Service page appears. Enter the required information in the fields.

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

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.

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

^Footnote 1

The DLLs are separated by semi-colons (;) on Microsoft Windows.

• Configuring Oracle Net Services for External Procedures
  

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.

• Configuring Oracle Database to Connect to Agents
  

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.

The following procedure describes how to configure a client for an Oracle Rdb database:

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

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

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

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