5 Introduction to Automatic Workload Management

Using Oracle Services

To manage workloads or a group of applications, you can define services that you assign to a particular application or to a subset of an application's operations. You can also group work by type under services. For example, online users can use one service, while batch processing can use another and reporting can use yet another service to connect to the database.

Oracle recommends that all users who share a service have the same service level requirements. You can define specific characteristics for services and each service can represent a separate unit of work. There are many options that you can take advantage of when using services. Although you do not have to implement these options, using them helps optimize application performance.

Service Characteristics

When you create new services for your database, you should define the automatic workload management characteristics for each service. The characteristics of a service include:

• Service Name

• Net Service Name

• Service Edition

• Service Management Policy

• Database Role for a Service

• Instance Preference

• Server Pool Assignment

• Load Balancing Advisory Goal for Runtime Connection Load Balancing

• Connection Load Balancing Goal

• Distributed Transaction Processing

• Subscription to High Availability Events

• Transparent Application Failover Policy

$ srvctl modify service -d crm -s tafconn.example.com -P BASIC
   -e SELECT -z 5 -w 120

Service Usage in an Oracle RAC Database

A service name can identify multiple database instances, and an instance can belong to multiple services. Services for an Oracle RAC database are used by several database features, as described in the following topics:

• Resource Profile for a Service

• Database Resource Manager Consumer Group Mappings for Services

• Performance Monitoring by Service with AWR

• Parallel Operations and Services

Default Service Connections

A special Oracle database service is created by default for your Oracle RAC database. This default service is always available on all instances in an Oracle RAC environment, unless an instance is in restricted mode. You cannot alter this service or its properties. The database also supports the following two internal services:

• SYS$BACKGROUND is used by the background processes only

• SYS$USERS is the default service for user sessions that are not associated with any application service

Both of these internal services support all of the automatic workload management features. You cannot stop or disable either of these internal services.

Client-Side Load Balancing

Client-side load balancing is defined in your client connection definition (tnsnames.ora file, for example) by setting the parameter LOAD_BALANCE=ON. When you set this parameter to ON, Oracle Database randomly selects an address in the address list, and connects to that node's listener. This balances client connections across the available SCAN listeners in the cluster.

The SCAN listener redirects the connection request to the local listener of the instance that is least loaded and provides the requested service. When the listener receives the connection request, the listener connects the user to an instance that the listener knows provides the requested service. To see what services a listener supports, run the lsnrctl services command.

When clients connect using SCAN, Oracle Net automatically load balances client connection requests across the three IP addresses you defined for the SCAN, unless you are using EZConnect.

(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)

Other Client-Side Connection Features

In addition to client-side load balancing, Oracle Net Services include connection failover. If an error is returned from the chosen address in the list, Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list. For SCAN, Oracle Net Services tries all three addresses before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.

To increase availability, you can specify a timeout that specifies how long Oracle Net waits for a response from the listener before returning an error. The method of setting this timeout parameter depends on the type of client access.

Properties prop = new Properties ();
prop.put (oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR,
"" + (1 * 1000)); // 1 second
dbPools[ poolIndex ].setConnectionProperties ( prop );

sqlnet.outbound_connect_timeout = 1

Server-Side Load Balancing

When you create an Oracle RAC database with DBCA, it automatically:

• Configures and enables server-side load balancing

• Sets the remote listener parameter to the SCAN listener (Note: If you do not use DBCA, you should set the REMOTE_LISTENER database parameter to scan_name:scan_port.)

• Creates a sample client-side load balancing connection definition in the tnsnames.ora file on the server

FAN, Fast Connection Failover, and the load balancing advisory depend on an accurate connection load balancing configuration that includes setting the connection load balancing goal for the service. You can use a goal of either LONG or SHORT for connection load balancing. These goals have the following characteristics:

• LONG: Use the LONG connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. LONG is the default connection load balancing goal. The following is an example of modifying a service, batchconn, with the srvctl utility to define the connection load balancing goal for long-lived sessions:

  srvctl modify service -d db_unique_name -s batchconn -j LONG 
  

• SHORT: Use the SHORT connection load balancing method for applications that have short-lived connections. When using connection pools that are integrated with FAN, set the CLB_GOAL to SHORT. The following example modifies the service known as oltpapp, using SRVTCL to set the connection load balancing goal to SHORT:

  srvctl modify service -d db_unique_name -s oltpapp -j SHORT 
  

Overview of Fast Application Notification

FAN is a notification mechanism that Oracle RAC uses to notify other processes about configuration and service level information that includes service status changes, such as UP or DOWN events. Applications can respond to FAN events and take immediate action. FAN UP and DOWN events can apply to instances, services, and nodes.

For cluster configuration changes, the Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster. Instead of waiting for the application to poll the database and detect a problem, applications can receive FAN events and react immediately. With FAN, in-flight transactions can be immediately terminated and the client notified when the instance fails.

FAN also publishes load balancing advisory events. Applications can take advantage of the load balancing advisory FAN events to direct work requests to the instance in the cluster that is currently providing the best service quality. You can take advantage of FAN events in the following three ways:

1. Your application can use FAN without programmatic changes if you use an integrated Oracle client. The integrated clients for FAN events include Oracle Database JDBC, Oracle Database ODP.NET, and Oracle Database Oracle Call Interface. This includes applications that use TAF. The integrated Oracle clients must be Oracle Database 10g release 2 (10.2) or later to take advantage of the load balancing advisory FAN events. (See the Oracle Database Net Services Administrator's Guide for more information about configuring TAF.)

2. Applications can use FAN programmatically by using the JDBC and Oracle RAC FAN application programming interface (API) or by using callbacks with Oracle Call Interface to subscribe to FAN events and to execute event handling actions upon the receipt of an event.

3. You can implement FAN with server-side callouts on your database tier.

For DOWN events, the disruption to the application can be minimized because sessions to the failed instance or node can be terminated. Incomplete transactions can be terminated and the application user is immediately notified. Application users who request connections are directed to available instances only. For UP events, when services and instances are started, new connections can be created so that the application can immediately take advantage of the extra resources. Through server-side callouts, you can also use FAN to:

• Log status information

• Page DBAs or to open support tickets when resources fail to start

• Automatically start dependent external applications that must be co-located with a service

• Change resource plans or shut down services when the number of available instances for a policy-managed database decreases, for example, if nodes fail

• Automate the fail back of a service to preferred instances for administrator-managed databases, if needed

FAN events are published using ONS and Oracle Streams Advanced Queuing. The publication mechanisms are automatically configured as part of your Oracle RAC installation.

Connection Manager (CMAN) and Oracle Net Services listeners are integrated with FAN events, enabling the listener and CMAN to immediately de-register services provided by the failed instance and to avoid erroneously sending connection requests to failed instances.

If you specify the connection load balancing goal CLB_GOAL_SHORT for the service, then the listener uses the load balancing advisory when the listener balances the connection loads. When load balancing advisory is enabled, the metrics used for the listener are finer grained.

Application High Availability with Services and FAN

Oracle Database focuses on maintaining service availability. In Oracle RAC, Oracle services are designed to be continuously available with loads shared across one or more instances. The Oracle RAC high availability framework maintains service availability by using Oracle Clusterware and resource profiles.

The Oracle RAC high availability framework monitors the database and its services and sends event notifications using FAN. Oracle Clusterware recovers and balances services according to business rules and the service attributes.

Managing Unplanned Outages

You can assign services to one or more instances in an administrator-managed Oracle RAC database or to server pools in a policy-managed database. If Oracle RAC detects an outage, then Oracle Clusterware isolates the failed component and recovers the dependent components. For services, if the failed component is an instance, then Oracle Clusterware relocates the service to an available instance in the cluster. FAN events can occur at various levels within the Oracle Database architecture and are published through ONS and Oracle Streams AQ. You can also program notification using FAN callouts.

Notification occurs from a surviving node when the failed node is out of service. The location and number of instances in an Oracle RAC environment that provide a service are transparent to applications. Restart and recovery are automatic, including the restarting of the subsystems, such as the listener and the Oracle Automatic Storage Management (Oracle ASM) processes, not just the database. You can use FAN callouts to report faults to your fault management system and to initiate repair jobs.

Managing Planned Outages

For repairs, upgrades, and changes that require you to isolate one or more instances or nodes, Oracle RAC provides interfaces that relocate, disable, and enable services to minimize service disruption to application users. When you relocate a service, you indicate the service should run on another instance temporarily. If you disable a service, then the service is stopped on all database instances and is no longer available. Disabled services are not restarted automatically. Once you complete the operation, you can return the service to normal operation or enable the service and then restart it.

Due to dependencies, if you manually shutdown your database, then all of your services for that database automatically stop. If you want your services to automatically start when you manually restart the database, then you must set the management policy of the service to automatic. If you want to shut down only one instance of the database, but not the service, you can use the srvctl stop instance command with the -f option. When you use the -f option with this command, the database services that were running on that instance are failed over to another instance if possible.

Fast Application Notification High Availability Events

Table 5-1 lists the FAN event types and Table 5-2 describes name-value pairs for the event parameters. The event type is always the first entry and the timestamp is always the last entry, as in the following example:

FAN event type: SERVICEMEMBER VERSION=1.0
service=fantest database=ractest instance=rac1host=node01 status=up
reason=FAILURE card=1 timestamp=2010-07-02 22:06:02

SERVICEMEMBER VERSION=1.0 service=myServ.us.oracle.com
database=prod instance=PROD1 host=stru09 status=up
reason=USER card=1 timestamp=2010-07-27 14:43:03

NODE VERSION=1.0 host=stru09 incarn=175615351 status=down
reason=member_leave timestamp=27-Jul-2010 14:49:32

Some of the FAN event record parameters have values that correspond to values returned by the SYS_CONTEXT function using the default namespace USERENV, as shown in Table 5-3.

Using Fast Application Notification Callouts

FAN callouts are server-side executables that Oracle RAC executes immediately when high availability events occur. You can use FAN callouts to automate activities when events occur in a cluster configuration, such as:

• Opening fault tracking tickets

• Sending messages to pagers

• Sending e-mail

• Starting and stopping server-side applications

• Maintaining an uptime log by logging each event as it occurs

• Relocating low-priority services when high priority services come online

To use FAN callouts, place an executable in the Grid_home/racg/usrco directory on every node that runs Oracle Clusterware. The executable must be able to run stand-alone when called, with optional arguments, from another program. The following is an example file for the Grid_home/racg/usrco/callout.sh callout:

#! /bin/ksh
FAN_LOGFILE= [your path name]/admin/log/'hostname`_uptime'.log
echo $* "reported="'date' >> $FAN_LOGFILE &

The previous example produces output similar to the following:

NODE VERSION=1.0 host=sun880-2 incarn=23 status=nodedown reason=
timestamp=08-Oct-2004 04:02:14 reported=Fri Oct 8 04:02:14 PDT 2004

Example callout scripts are available in the Oracle RAC Sample Code section on Oracle Technology Network at http://www.oracle.com/technology/sample_code/products/rac/.

The contents of a FAN event record matches the current session of the user logged on to the database, as shown in Table 5-3. The user environment (USERENV) information is also available using Oracle Call Interface connection handle and descriptor attributes (using OCIAttrGet()). Use this information to take actions on sessions that match the FAN event data.

Overview of the Load Balancing Advisory

Load balancing distributes work across all of the available Oracle RAC database instances. Oracle recommends that applications use persistent connections that span the instances that offer a particular service, such as when using connection pools. When using persistent connections, connections are created infrequently and exist for a long duration. Work comes into the system with high frequency, borrows these connections, and exists for a relatively short duration. The load balancing advisory provides advice about how to direct incoming work to the instances that provide the optimal quality of service for that work. This minimizes the need to relocate the work later.

By using the Load Balancing Advisory or runtime connection load balancing goals, feedback is built in to the system. Work is routed to provide the best service times globally, and routing responds gracefully to changing system conditions. In a steady state, the system approaches equilibrium with improved throughput across all of the Oracle RAC instances.

Standard architectures that can use the load balancing advisory include connection load balancing, transaction processing monitors, application servers, connection concentrators, hardware and software load balancers, job schedulers, batch schedulers, and message queuing systems. All of these applications can allocate work.

The load balancing advisory is deployed with key Oracle clients, such as a listener, the JDBC universal connection pool, and the ODP.NET Connection Pool. Third-party applications can also subscribe to load balancing advisory events by using JDBC and Oracle RAC FAN API or by using callbacks with Oracle Call Interface.

Configuring Your Environment to Use the Load Balancing Advisory

You can configure your environment to use the load balancing advisory by defining service-level goals for each service for which you want to enable load balancing. Configuring a service-level goal enables the load balancing advisory and the publishing of FAN load balancing events for that service.

There are two types of service-level goals for runtime connection load balancing:

• SERVICE_TIME: Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE_TIME is for workloads such as internet shopping where the rate of demand changes. The following example shows how to set the goal to SERVICE_TIME for connections using the online service:

  srvctl modify service -d db_unique_name -s online -B SERVICE_TIME -j SHORT
  

• THROUGHPUT: Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. An example for the use of THROUGHPUT is for workloads such as batch processes, where the next job starts when the last job completes. The following example shows how to set the goal to THROUGHPUT for connections using the sjob service:

  srvctl modify service -d db_unique_name -s sjob -B THROUGHPUT -j LONG
  

Setting the runtime connection load balancing goal to NONE disables load balancing for the service. You can see the goal settings for a service in the data dictionary by querying the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views. You can also review the load balancing settings for a service using Oracle Enterprise Manager.

Load Balancing Advisory FAN Events

The load balancing advisory FAN events provide metrics for load balancing algorithms. The easiest way to take advantage of these events is to use the runtime connection load balancing feature of an Oracle integrated client such as JDBC, ODP.NET, or Oracle Call Interface. Other client applications can take advantage of FAN programatically by using the Oracle Notification Service application programming interface (ONS API) to subscribe to FAN events and execute event-handling actions upon receipt. Table 5-4 describes the load balancing advisory FAN event parameters.

Monitoring Load Balancing Advisory FAN Events

You can use the following query against the internal queue table for load balancing advisory FAN events to monitor load balancing advisory events generated for an instance:

SET PAGES 60 COLSEP '|' LINES 132 NUM 8 VERIFY OFF FEEDBACK OFF
COLUMN user_data HEADING "AQ Service Metrics" FORMAT A60 WRAP
BREAK ON service_name SKIP 1
SELECT
 TO_CHAR(enq_time, 'HH:MI:SS') Enq_time, user_data
 FROM sys.sys$service_metrics_tab
 ORDER BY 1 ;

The results of this query contain rows similar to the following:

02:56:05|SYS$RLBTYP('hr', 'VERSION=1.0 database=sales service=hr { {in
        |stance=sales_4 percent=38 flag=GOOD aff=TRUE}{instance=sales_1
        | percent=62 flag=GOOD aff=TRUE} } timestamp=2010-07-16 07:56
        |:05')

Overview of Oracle Integrated Clients and FAN

The overall goal when using FAN is to enable applications to consistently obtain connections to available instances that provide the best service. The Oracle integrated clients you can use are Oracle Call Interface Session Pools, CMAN session pools, and JDBC and ODP.NET connection pools. The Fast Connection Failover (FCF) feature is a FAN client implemented through a connection pool.

Due to the integration with FAN, Oracle integrated clients are more aware of the current status of an Oracle RAC cluster. This prevents client connections from waiting or trying to connect to an instance that is no longer available. When instances start, Oracle RAC uses FAN to notify the connection pool so that the connection pool can create connections to the recently started instance and take advantage of the additional resources that this instance provides.

Oracle connection pools that are integrated with FAN can:

• Balance connections across all of the Oracle RAC instances when a service starts; this is preferable to directing the sessions that are defined for the connection pool to the first Oracle RAC instance that supports the service

• Remove terminated connections immediately when a service is declared DOWN at an instance, and immediately when nodes are declared DOWN

• Report errors to clients immediately when Oracle Database detects the NOT RESTARTING state, instead of making the client wait while the service repeatedly attempts to restart

• Balance work requests at runtime using load balancing advisory events

The use of connection pools and FAN requires that you have properly configured database connection load balancing across all of the instances that provide the services used by the connection pool. Oracle recommends that you configure both client-side and server-side load balancing with Oracle Net Services. If you use DBCA to create your database, then both client-side and server-side load balancing are configured by default.

Enabling JDBC Clients for Fast Connection Failover

Enabling Fast Connection Failover (FCF) for the Oracle JDBC Universal Connection Pool (UCP) enables the use of FAN high availability and load balancing advisory events. To use FAN, your application can use the JDBC development environment for either thick or thin JDBC clients. The Java Database Connectivity Oracle Call Interface (JDBC/OCI) driver connection pooling functionality is part of the JDBC client. This functionality is provided by the OracleOCIConnectionPool class.

The UCP is integrated to take advantage of Load Balancing Advisory information. Oracle introduced the Universal Connection Pool for JDBC in Oracle Database 11g release 11.1.0.7.0. Consequently, Oracle deprecated the existing JDBC connection pool, the Implicit Connection Cache, that was introduced in Oracle Database 10g release 1, for use with Oracle RAC databases. You can use the UCP with Oracle Database 10g or Oracle Database 11g.

To enable FCF for the JDBC client, set the fastConnectionFailoverEnabled property of the OracleDataSource class in the oracle.jdbc.pool package before making the first getConnection() request. When you enable FCF for the JDBC client, the failover property applies to every connection in the connection pool. Enabling FCF with JDBC Thin Driver (Thin driver) or JDBC/OCI clients enables the connection pool to receive and react to all FAN events.

JDBC application developers can now programatically integrate with FAN by using a set of APIs introduced in Oracle Database 11g release 2 (11.2). The Oracle RAC FAN APIs enable application code to receive and respond to FAN event notifications sent by Oracle RAC in the following ways:

• Listening for Oracle RAC service down, service up, and node down events

• Listening for load balancing advisory events and responding to them

Enabling JDBC Clients for Runtime Connection Load Balancing

UCP JDBC connection pools leverage the load balancing functionality provided by an Oracle RAC database. Runtime connection load balancing requires the use of an Oracle JDBC driver and an Oracle RAC database.

Runtime connection load balancing requires that FCF is enabled and configured properly. See "Configuring FCF for JDBC/OCI and JDBC Thin Driver Clients". In addition, the Oracle RAC load balancing advisory must be configured with service-level goals for each service used by the connection pool. The connection load balancing goal should be set to SHORT, for example:

srvctl modify service -d db_unique_name -s service_name -B SERVICE_TIME -j SHORT

Enabling Oracle Call Interface Clients for Fast Connection Failover

Oracle Call Interface (OCI) clients can enable FCF by registering to receive notifications for Oracle RAC high availability FAN events and responding when events occur. Using FCF improves the session failover response time in OCI applications and also removes connections to nonfunctioning instances from connection and session pools. FCF can be used in OCI applications that also use TAF, connection pools, or session pools.

To use FCF, you must use a service with the load balancing advisory goal and the connection load balancing goal configured. The service metrics received from the Oracle RAC load balancing advisory through FAN events for the service are automatically placed in the Oracle Streams AQ queue table, ALERT_QUEUE. Client applications can register callbacks that are used whenever an event occurs. This reduces the time that it takes to detect a connection failure.

During DOWN event processing, OCI:

• Terminates affected connections at the client and returns an error

• Removes connections from the OCI connection pool and the OCI session pool—the session pool maps each session to a physical connection in the connection pool, and there can be multiple sessions for each connection

• Fails over the connection if you have configured TAF. If TAF is not configured, then the client only receives an error if the instance it is connected to fails.

If your application is using TAF, then you must enable the TAF properties for the service using SRVCTL or Oracle Enterprise Manager. Configure your OCI client applications to connect to an Oracle RAC database using the configured service.

Configuring FCF for OCI Clients

OCI applications must connect to an Oracle RAC instance to enable HA event notification. Furthermore, these applications must perform the following steps to configure FCF for an OCI client:

1. Configure the service for your OCI connection pool to enable connection load balancing and runtime connection load balancing. Also configure the service to have Advanced Queuing notifications enabled, as shown in the following example:

   $ srvctl modify service -d crm -s ociapp.example.com -q TRUE 
      -B THROUGHPUT -j LONG
   

2. Set the context of the environment for OCI calls on the client to enable subscriptions by using the OCIEnvCreate() call, setting the MODE parameter value to OCI_EVENTS, as follows:

   (void) OCIEnvCreate(&myenvhp, OCI_EVENTS|OCI_OBJECT, ...);
   

3. Link the application with a thread library.

4. After linking with the thread library, the applications can register a callback that is invoked whenever a FAN event occurs.

Enabling Oracle Call Interface Clients for Runtime Connection Load Balancing

As of Oracle Database 11g release 2 (11.2), OCI session pooling enables multiple threads of an application to use a dynamically managed set of pre-created database sessions. In connection pooling, the pool element is a connection, but in session pooling, the pool element is a session. Oracle Database continually reuses the sessions in the session pool to form nearly permanent channels to the instances, thus saving the overhead of creating and closing sessions every time applications need them.

Runtime connection load balancing is enabled by default in a release 11.1 or higher client talking to a server of 10.2 or higher. For Oracle RAC environments, session pools use service metrics received from the Oracle RAC load balancing advisory^Foot 1  through Fast Application Notification (FAN) events to balance application session requests. The work requests coming into the session pool can be distributed across the instances of Oracle RAC offering a service, using the current service performance.

Runtime connection load balancing is basically routing work requests to sessions in a session pool that best serve the work. It comes into effect when selecting a session from an existing session pool and thus is a very frequent activity. For session pools that support services at one instance only, the first available session in the pool is adequate. When the pool supports services that span multiple instances, there is a need to distribute the work requests across instances so that the instances that are providing better service or have greater capacity get more requests.

Connect time load balancing occurs when a session is first created by the application. It is necessary that the sessions that are part of the pool be well distributed across Oracle RAC instances, at the time they are first created. This ensures that sessions on each of the instances get a chance to execute work.

Configuring OCI Clients to Receive Load Balancing Advisory FAN Events

For Oracle RAC environments, session pools use service metrics received from the Oracle RAC load balancing advisory through Fast Application Notification (FAN) events to balance application session requests. To enable your application to receive the service metrics based on the service time, ensure you have met the following conditions:

1. Link the application with the threads library.

2. Create the OCI environment in OCI_EVENTS and OCI_THREADED mode.

3. Configure the load balancing advisory goal (-B option) and connection load balancing goal (-j option) for a service that is used by the session pool, as shown in the following example:

   srvctl modify service -d crm -s ociapps -B SERVICE_TIME -j SHORT
   

Enabling ODP.NET Clients to Receive FAN High Availability Events

ODP.NET connection pools can subscribe to notifications that indicate when nodes, services, and service members are down. After a DOWN event, Oracle Database cleans up sessions in the connection pool that go to the instance and ODP.NET proactively removes connections that are no longer valid. ODP.NET establishes additional connections to existing Oracle RAC instances if the removal of invalid connections reduces the total number of connections to below the value for the MIN_POOL_SIZE parameter.

Perform the following steps to enable FAN for ODP.NET clients:

1. Enable Advanced Queuing notifications for a service by using SRVCTL as shown in the following example:

   srvctl modify service -d crm -s odpnet.example.com -q TRUE
   

2. Grant permissions on the internal event queue table by executing the following command for the users that will be connecting by way of the ODP.NET application, where user_name is the database user name:

   EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','SYS.SYS$SERVICE_METR
   ICS', user_name); 
   

3. Enable Fast Connection Failover for ODP.NET connection pools by subscribing to FAN high availability events. To enable FCF, include "HA Events=true" and "pooling=true" (the default value) in the connection string, as shown in the following example where user_name is the name of the database user and password is the password for that user:

   con.ConnectionString =
      "User Id=user_name;Password=password;Data Source=odpnet;" +
      "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
      "HA Events=true;Incr Pool Size=5;Decr Pool Size=2";
   

   See Also:

   Oracle Data Provider for .NET Developer's Guide for more information about using FAN events in ODP.NET applications

Enabling ODP.NET Clients to Receive FAN Load Balancing Advisory Events

Use the following procedures to enable ODP.NET clients or applications to receive FAN load balancing advisory events:

1. Enable Advanced Queuing notifications by using SRVCTL, and set the connection load balancing goal as shown in the following example:

   srvctl modify service -d crm -s odpapp.example.com -q TRUE -j LONG
   

2. Ensure Oracle Net Services is configured for connection load balancing.

3. Grant permissions on the internal event queue table by executing the following command for the users that will be connecting by way of the ODP.NET application, where user_name is the name of the database user:

   EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','SYS.SYS$SERVICE_METR
   ICS', user_name);
   

4. To take advantage of load balancing events with ODP.NET connection pools, set the load balancing attribute in the ConnectionString to TRUE (the default is FALSE). You can do this at connect time. This only works if you are using connection pools, or when the pooling attribute is set to TRUE which is the default.

   The following example demonstrates how to configure the ConnectionString to enable load balancing, where user_name is the name of the user and password is the password:

   con.ConnectionString =
     "User Id=user_name;Password=password;Data Source=odpapp;" +
     "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
     "Load Balancing=true;Incr Pool Size=5;Decr Pool Size=2";
    
   

Overview of XA Transactions and DTP Services

An XA transaction can span Oracle RAC instances by default, allowing any application that uses the Oracle XA library to take full advantage of the Oracle RAC environment to enhance the availability and scalability of the application.

GTXn background processes support global (XA) transactions in an Oracle RAC environment. The GLOBAL_TXN_PROCESSES initialization parameter, which is set to 1 by default, specifies the initial number of GTXn background processes for each Oracle RAC instance. Use the default value for this parameter clusterwide to allow distributed transactions to span multiple Oracle RAC instances. Using the default value allows the units of work performed across these Oracle RAC instances to share resources and act as a single transaction (that is, the units of work are tightly coupled). It also allows 2PC requests to be sent to any node in the cluster.

Before Release 11.1, the way to achieve tight coupling in Oracle RAC was to use Distributed Transaction Processing (DTP) services, that is, services whose cardinality (one) ensured that all tightly-coupled branches landed on the same instance—regardless of whether load balancing was enabled. Tightly coupled XA transactions no longer require the special type of singleton services to be deployed on Oracle RAC databases if the XA application does not join or resume XA transaction branches. XA transactions are transparently supported on Oracle RAC databases with any type of service configuration.

An external transaction manager, such as Oracle Services for Microsoft Transaction Server (OraMTS), coordinates DTP/XA transactions. However, an internal Oracle transaction manager coordinates distributed SQL transactions. Both DTP/XA and distributed SQL transactions must use the DTP service in Oracle RAC.

Benefits of DTP Services for XA Transactions

To provide improved application performance with distributed transaction processing in Oracle RAC, you may want to take advantage of DTP services. Using DTP services, you can direct all branches of a distributed transaction to a single instance in the cluster. To load balance across the cluster, it is better to have several groups of smaller application servers with each group directing its transactions to a single service, or set of services, than to have one or two larger application servers.

In addition, connection pools at the application server tier that load balance across multiple connections to an Oracle RAC database can use this method to ensure that all tightly-coupled branches of a global distributed transaction run on only one Oracle RAC instance. This is also true in distributed transaction environments using protocols such as X/Open Distributed Transaction Processing (DTP) or the Microsoft Distributed Transaction Coordinator (DTC).

To enhance the performance of distributed transactions, you can use services to manage DTP environments. By defining the DTP property of a service, the service is guaranteed to run on one instance at a time in an Oracle RAC database. All global distributed transactions performed through the DTP service are ensured to have their tightly-coupled branches running on a single Oracle RAC instance. This has the following benefits:

• The changes are available locally within one Oracle RAC instance when tightly coupled branches need information about changes made by each other

• Relocation and failover of services are fully supported for DTP

• By using more DTP services than there are Oracle RAC instances, Oracle Database can balance the load by services across all of the Oracle RAC database instances

Using DTP Services with Oracle RAC

To leverage all of the instances in a cluster, create one or more DTP services for each Oracle RAC instance that hosts distributed transactions. Choose one DTP service for one distributed transaction. Choose different DTP services for different distributed transactions to balance the workload among the Oracle RAC database instances. Because all of the branches of a distributed transaction are on one instance, you can leverage all of the instances to balance the load of many DTP transactions through multiple singleton services, thereby maximizing application throughput.

If you add or delete nodes from your cluster database, then you may have to identify and relocate services that you are using for DTP transactions to ensure that you maintain optimum performance levels.

Configuring DTP Services

To create DTP services for distributed transaction processing, perform the following steps:

1. Create a singleton service using Oracle Enterprise Manager or SRVCTL.

   For an administrator-managed database, define only one instance as the preferred instance. You can have as many available instances as you want, for example:

   srvctl add service -d crm -s xa_01.example.com -r RAC01 -a RAC02,RAC03
   

   For a policy-managed database, specify the server pool to use, and set the cardinality of the service to SINGLETON, for example:

   srvctl add service -d crm -s xa_01.example.com -g dtp_pool -c SINGLETON
   

2. Set the DTP option (-x) for the service to TRUE (the default value is FALSE). You can use Oracle Enterprise Manager or SRVCTL to modify the DTP property of the singleton service. The following example shows how to modify the xa_01.example.com service using SRVCTL:

   srvctl modify service -d crm -s xa_01.example.com -x TRUE
   

Relocating DTP Services in Administrator-Managed Databases

If the instance that provides a DTP service, for example XA_01, fails, then the singleton service fails over to an available instance, such as RAC02 or RAC03.

If services migrate to other instances, then you might have to force the relocation of the service back to the preferred instance after it is restarted to evenly re-balance the load on all of the available hardware. You can use data from the GV$ACTIVE_SERVICES view to determine whether you need to relocate the STP service.

Overview of Service Administration

When you create and administer services, you are dividing the work that your database performs into manageable units. The goal of using services is to achieve optimal utilization of your database infrastructure. You can create and deploy services based on business requirements. Oracle Database can measure the performance for each service. Using the DBMS_MONITOR package, you can define both the application modules within a service and the individual actions for a module and monitor thresholds for these actions, enabling you to manage workloads to deliver capacity on demand.

To create a service, you can use either Oracle Enterprise Manager, or SRVCTL. When you create new services for your database, you should define the automatic workload management characteristics for each service, as described in "Service Characteristics".

In addition to creating services, you can:

• Delete a service. You can delete services that you created. However, you cannot delete or modify the properties of the default database service that Oracle Database created.

• Check the status of a service. A service can be assigned different roles among the available instances. In a complex database with many services, you may not remember the details of every service. Therefore, you may have to check the status on an instance or service basis. For example, you may have to know the status of a service for a particular instance before you make modifications to that instance or to the Oracle home from which it runs.

• Start or stop a service for a database or an instance. A service must be started before it can be used for client connections to that instance. If you shut down your database, for example, by running the SRVCTL command srvctl stop database -d db_unique_name where db_unique_name is the name of the database you want to stop, then Oracle Database stops all services to that database. Depending on the service management policy, you may have to manually restart the services when you start the database.

  Note:

  If Oracle Database QoS Management is enabled for the Oracle RAC database, then the services are automatically restarted after they are stopped.

• Map a service to a consumer group. Oracle Database can automatically map services to Resource Manager Consumer groups to limit the amount of resources that services can use in an instance. You must create the consumer group and then map the service to the consumer group.

  See Also:

  Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI procedure

• Enable or disable a service for a database or an instance. By default, Oracle Clusterware attempts to restart a service automatically after failures. You can prevent this behavior by disabling a service. Disabling a service is useful when you must perform database or instance maintenance, for example, if you are performing an upgrade and you want to prevent connection requests.

• Relocate a service to a different instance. You can move a service from one instance to another instance to re-balance workloads, for example, after adding or deleting cluster nodes.

Administering Services with Oracle Enterprise Manager

The Cluster Managed Database Services page is the master page for beginning all tasks related to services. To access this page, go to the Cluster Database Availability page, then click Cluster Managed Database Services in the Services section. You can use this page and drill down from this page to perform the following tasks:

• View a list of services for the cluster

• View the instances on which each service is currently running

• View the status for each service

• Create or edit a service

• Start or stop a service

• Enable or disable a service

• Perform instance-level tasks for a service

• Delete a service

Administering Services with SRVCTL

When you create a service by using SRVCTL, you must start the service with a separate SRVCTL command. However, you may later have to manually stop or restart the service. You may also have to disable the service to prevent automatic restarts, to manually relocate the service, or obtain status information about the service. The following sections explain how to use SRVCTL to perform the following administrative tasks:

• Creating Services with SRVCTL

• Starting and Stopping Services with SRVCTL

• Enabling and Disabling Services with SRVCTL

• Relocating Services with SRVCTL

• Obtaining the Statuses of Services with SRVCTL

• Obtaining the Configuration of Services with SRVCTL

srvctl add service -d db_unique_name -s service_name -t edition_name
{-r preferred_list [-a available_list]} | {-g server_pool
[-c {UNIFORM | SINGLETON}] [-k net_number]}
[-P {BASIC | NONE}] [-l {[PRIMARY] | [PHYSICAL_STANDBY] | [LOGICAL_STANDBY] |
[SNAPSHOT_STANDBY]}] [-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}]
[-x {TRUE | FALSE}] [-j {SHORT | LONG}] [-B {NONE | SERVICE_TIME | THROUGHPUT}]
 [-e {NONE |SESSION | SELECT}] [-m {NONE | BASIC}] [-z failover_retries]
 [-w failover_delay]

srvctl start service -d database_unique_name [-s service_name_list] [-i inst_name]
[-o start_options]

srvctl stop service -d database_unique_name -s service_name_list [-i inst_name]
[-o start_options]

srvctl enable service -d database_unique_name -s service_name_list [-i inst_name]

srvctl disable service -d database_unique_name -s service_name_list [-i inst_name]

srvctl relocate service -d apps -s crm -i apps1 -t apps3

srvctl status service -d apps

Service erp is running on nodes: apps02,apps03
Service hr is running on nodes: apps02,apps03
Service sales is running on nodes: apps01,apps04

srvctl config service -d apps -s erp

Service name: erp
Service is enabled
Server pool: sp1
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notificaitons: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: SERVICE_TIME
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:

Example of Services and Thresholds Alerts

In this scenario, you need to check the thresholds for the payroll service. You can use the AWR report to get this information. You should compare the results from reports run over several successive intervals during which time the system is running optimally. For example, assume that for servers accessed by a payroll application, the AWR report runs each Thursday during the peak usage times of 1:00 p.m. to 5:00 p.m. The AWR report contains the response time, or elapsed database time, and the CPU consumption time, or CPU time, for calls for each server, including the payroll service. The AWR report also provides a breakdown of the work done and the wait times that are contributing to the response times.

Using DBMS_MONITOR, you set a warning threshold for the elapsed time per call for the payroll service at 0.5 seconds (500000 microseconds). You also set a critical threshold for the elapsed time per call for the payroll service at 0.75 seconds (750000 microseconds).

In this example, thresholds are added for the payroll service as follows:

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD( 
METRICS_ID => DBMS_SERVER_ALERT.ELAPSED_TIME_PER_CALL 
, warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE 
, warning_value => '500000' 
, critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE 
, critical_value => '750000' 
, observation_period => 30 
, consecutive_occurrences => 5 
, instance_name => NULL 
, object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE 
, object_name => 'payroll');

You can verify the threshold configuration is set on all the instances using the following SELECT statement:

SELECT METRICS_NAME, INSTANCE_NAME, WARNING_VALUE, CRITICAL_VALUE, 
OBSERVATION_PERIOD FROM dba_thresholds ;

Enable Service, Module, and Action Monitoring

You can enable performance data tracing for important modules and actions within each service. The performance statistics are available in the V$SERV_MOD_ACT_STATS view. For example, you might decide to set the following:

• For the ERP service, enable monitoring for the exceptions pay action in the payroll module.

• For the ERP service, enable monitoring for the all actions in the payroll module.

• For the HOT_BATCH service, enable monitoring for all actions in the posting module.

The following commands show how to enable the module and action monitoring for the services:

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'erp', module_name=>
 'payroll', action_name => 'exceptions pay');
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'erp', module_name=>
 'payroll', action_name => NULL);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'hot_batch', 
module_name =>'posting', action_name => NULL); 

To verify monitoring is enabled for the service, module, and actions, use the following SELECT statement:

COLUMN AGGREGATION_TYPE FORMAT A21 TRUNCATED HEADING 'AGGREGATION'
COLUMN PRIMARY_ID FORMAT A20 TRUNCATED HEADING 'SERVICE'
COLUMN QUALIFIER_ID1 FORMAT A20 TRUNCATED HEADING 'MODULE'
COLUMN QUALIFIER_ID2 FORMAT A20 TRUNCATED HEADING 'ACTION'
SELECT * FROM DBA_ENABLED_AGGREGATIONS ; 

The output might appear as follows:

AGGREGATION           SERVICE                MODULE        ACTION
------------           --------------------   ----------    -------------
SERVICE_MODULE_ACTION  ERP                    PAYROLL       EXCEPTIONS PAY
SERVICE_MODULE_ACTION  ERP                    PAYROLL
SERVICE_MODULE_ACTION  HOT_BATCH              POSTING