5 Workload Management with Dynamic Database Services

Service Usage in an Oracle RAC Database

Services provide location transparency. A service name can identify multiple database instances, and an instance can belong to multiple services. Several database features use services for an Oracle RAC database:

This section includes the following topics:

• Oracle Clusterware Resources for a Service

• Database Resource Manager Consumer Group Mappings for Services

• Performance Monitoring by Service with AWR

• Parallel Operations and Services

• Oracle Streams and Oracle RAC

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

• Service Edition

• Service Management Policy

• Database Role for a Service

• Instance Preference

• Server Pool Assignment

• Load Balancing Advisory Goal for Run-time Connection Load Balancing

• Connection Load Balancing Goal

• Distributed Transaction Processing

• Failing Over OCI Clients with TAF

$ srvctl modify service –db hr –s crmsrv –edition e2

Default Service Connections

Your Oracle RAC database includes an Oracle database service identified by DB_UNIQUE_NAME, if set, or DB_NAME or PDB_NAME, if not. 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. Additionally, the database 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

All of these services are used for internal management. You cannot stop or disable any of these internal services to do planned outages or to failover to Oracle Data Guard. Do not use these services for client connections.

Restricted Service Registration

Security is a high priority to all enterprises, and network security and controlling access to the database is a critical component of overall security endeavours. This feature allows listener registration only from local IP addresses, by default, and provides the ability to configure and dynamically update a set of IP addresses or subnets from which registration requests are allowed by the listener. Database Instance registration with a listener succeeds only when the request originates from a valid node. The network administrator can specify a list of valid nodes, excluded nodes, or disable valid node checking. The list of valid nodes explicitly lists the nodes and subnets that can register with the database. The list of excluded nodes explicitly lists the nodes that cannot register with the database. The control of dynamic registration results in increased manageability and security of Oracle RAC deployments.

By default, valid node checking for registration (VNCR) is enabled. In the default configuration, registration requests are only allowed from nodes within the cluster, because they are redirected to the private subnet, and only nodes within the cluster can access the private subnet. Non-SCAN listeners only accept registration from instances on the local node. You must manually include remote nodes or nodes outside the subnet of the SCAN listener on the list of valid nodes by using the registration_invited_nodes_alias parameter in the listener.ora file or by modifying the SCAN listener using SRVCTL, as follows:

$ srvctl modify scan_listener -invitednodes node_list -invitedsubnets subnet_list

Server-Side Load Balancing

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

• Configures and enables server-side load balancing

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

The Oracle Clusterware Database Agent is responsible for managing the LISTENER_NETWORKS parameter.

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:

• SHORT: Use the SHORT connection load balancing method for applications that use run-time load balancing. When using connection pools that are integrated with Load Balancing Advisory, set the CLB_GOAL to SHORT. The following example modifies the service known as oltpapp, using SRVCTL to set the connection load balancing goal to SHORT:

  $ srvctl modify service -db db_unique_name -service oltpapp -clbgoal SHORT
  

• LONG: Use the LONG connection load balancing method if run-time load balancing is not required. This is typical for batch operations. LONG is the default connection load balancing goal. The following is an example of modifying a service, batchconn, using SRVCTL to define the connection load balancing goal for long-lived sessions:

  $ srvctl modify service -db db_unique_name -service batchconn -clbgoal LONG
  

Generic Database Clients

Oracle Net Services provides the ability to add the CONNECT_TIMEOUT, RETRY_COUNT, and TRANSPORT_CONNECT_TIMEOUT parameters to the tnsnames.ora connection string.

For example, when using SCAN addresses for the remote listeners at the database:

jdbc:oracle:thin:@(DESCRIPTION =(TRANSPORT_CONNECT_TIMEOUT=3)(CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(FAILOVER=ON)(ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-SCANVIP.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GOLD)))Remote_listeners=CLOUD-SCANVIP.example.com:1521

For example, when using remote listeners pointing to VIPs at the database:

jdbc:oracle:thin:@(DESCRIPTION =
(TRANSPORT_CONNECT_TIMEOUT=3)
(CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(FAILOVER=ON)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP1)(PORT=1521) )
(ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP2)(PORT=1521) )
(ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP3)(PORT=1521) ))
(CONNECT_DATA=(SERVICE_NAME=GOLD)))

The value of these parameters is expressed in seconds. In the preceding examples, Oracle Net waits for 60 seconds for each full connection to receive a response, after which it assumes that a failure occurred and retries the next address in the ADDRESS_LIST. Oracle Net retries the address list 3 times before it returns a failure message to the client. The TRANSPORT_CONNECT_TIMEOUT parameter establishes the time to wait to establish a TCP connection to the database server.

For SCAN, Oracle Net Services tries all three addresses (returned by the SCAN address) before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.

This behavior is called Oracle Net connection failover. If an error is returned from a chosen address in the list, then Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list.

Client-Side Connection Configuration for Older Clients

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. Oracle Net maintains these parameters for backward compatibility.

This section includes the following topics:

• JDBC-Thin Clients

• OCI Clients

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

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.

If you configured SCAN for connection requests, then client-side load balancing is not relevant for those clients that support SCAN access. When clients connect using SCAN, Oracle Net automatically balances the load of client connection requests across the three IP addresses you defined for the SCAN, unless you are using EZConnect.

The SCAN listener redirects the connection request to the local listener of the instance that is least loaded (if -clbgoal is set to SHORT) 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.

If you are using clients that do not support SCAN (for example, the client version is pre-Oracle Database 11g release 2 (11.2)), then, to use SCAN you must change the client tnsnames.ora to include the SCAN VIPs, and set LOAD_BALANCE=ON to balance requests across the VIPs. For example:

Sales.example.com=(DESCRIPTION=
  (ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.22.67.192)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.22.67.193)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.22.67.194)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=salesservice.example.com))
  ))

Application High Availability with Services and FAN

Oracle Database focuses on maintaining the highest possible service availability. In Oracle RAC, 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.

Overview of Fast Application Notification

FAN is a high-availability 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. The Oracle client drivers and Oracle connection pools respond to FAN events and take immediate action. FAN UP and DOWN events can apply to instances, services, and nodes.

Oracle connection pools, for example, use FAN to receive very fast detection of failures, to balance connections following failures, and to balance connections again after the failed components are repaired. So, when a service at an instance starts, the FAN event is used immediately to route work to that resource. When a service at an instance or node fails, the FAN event is used immediately to interrupt applications to recover.

Importance of FAN

Applications can waste time in many critical ways:

• Waiting for TCP/IP timeouts when a node fails without closing sockets, and for every subsequent connection while that IP address is down.

• Attempting to connect when services are down.

• Not connecting when services resume.

• Processing the last result at the client when the server goes down.

• Attempting to execute work on sub-optimal nodes.

When a node fails without closing sockets, all sessions that are blocked in an I/O wait (read or write) wait for tcp_keepalive. This wait status is the typical condition for an application connected by a socket. Sessions processing the last result are even worse off, not receiving an interrupt until the next data is requested. Using FAN events eliminates applications waiting on TCP timeouts, time wasted processing the last result at the client after a failure has occurred, and time wasted executing work on slow, hung, or dead 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 time out against the database and detect a problem, applications can receive FAN events and react immediately. With FAN, in-flight transactions are 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 JDBC Universal Connection Pool, ODP.NET connection pool, OCI session pool, Oracle WebLogic Server Active Gridlink for Oracle RAC, and OCI and ODP.NET clients. This includes applications that use Application Continuity or Transaction Guard. The integrated Oracle clients must be Oracle Database 10g release 2 (10.2) or later to take advantage of the FAN high-availability events. The pooled clients can also take advantage of the load balancing advisory FAN events.

2. Applications can use FAN programmatically by using the JDBC and Oracle RAC FAN application programming interface (API) or by using callbacks with OCI and ODP.NET 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.

If you use one of the integrated clients listed in item 1 of the preceding list, then, for DOWN events, the disruption to the application is minimized because the FAN-aware client terminates the sessions to the failed instance or node before they are reused. Incomplete transactions are 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 are created so that the application can immediately take advantage of the extra hardware resources or additional capacity.

FAN callouts are server-side scripts or executables that run whenever a FAN event is generated. You can design and build callouts to do many things. For example:

• 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 Oracle Notification Service and Oracle Streams Advanced Queuing, the latter being continued for backward compatibility to previous Oracle Database releases. The publication mechanisms are automatically configured as part of your Oracle RAC installation.

Oracle Net Services listeners and Global Data Services (GDS) are integrated with FAN events, enabling the listener and GDS 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.

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 attempts to maintain the cardinality of the service. If the service definition allows for failover and that is necessary to maintain cardinality, then failover occurs.

FAN events can occur at various levels within the Oracle Database architecture and are published through Oracle Notification Service and Oracle Streams Advanced Queuing for backward compatibility with previous OCI clients. FAN callouts can also be written to execute on the database server in response to FAN events.

FAN is published 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 forcibly disable a service, then the service is stopped on all database instances and is no longer available. Disabled services are not restarted automatically. When a service is stopped or relocated, FAN is published with a planned reason code, typically reason=user. Once you complete the operation, you can return the service to normal operation or enable the service and then restart it. When a service restarts, FAN is published with UP status codes.

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 parameter. When you use the -f parameter with this command, the database services that were running on that instance are failed over to another instance if possible.

Managing Planned Outages Without User Interruption

You may have to stop database instances for many reasons, such as upgrading the Oracle software, patching, and replacing hardware. The steps in this section result in the least impact to users and requests are not interrupted.

For planned outages, the recommended approach is to drain requests over a controlled time period from FAN-enabled Oracle connection pools. Application Continuity provides continuous service for those requests that do not complete within the allotted time.

Using any FAN-aware pool with Fast Connection Failover configured (such as OCI session pools, Universal Connection Pool, Oracle WebLogic Server Active GridLink for Oracle RAC, or ODP.NET) allows sessions to drain at request boundaries after receipt of the FAN planned DOWN event. Requests are far more important than transactions because this allows the issued work to complete.

To manage a planned outage without user interruption:

1. Use SRVCTL to relocate the service from an instance or, if you are using a UNIFORM service, then shut down the service on an instance. Do not use the -force flag with any of these commands. The connection pool automatically releases a connection at a request boundary.

2. The FAN planned DOWN event clears idle sessions from the connection pool immediately and marks active sessions to be released at the next check-in. These FAN actions drain the sessions from the instance without disrupting the users.

   Existing connections on other instances remain usable, and new connections can be opened to these instances if needed.

3. Not all sessions, in all cases, will check their connections into the pool. It is best practice to have a timeout period after which the instance is forcibly shut down, evicting any remaining client connections.

   For those pools that are configured to use Application Continuity, Universal Connection Pool, or Oracle WebLogic Server Active GridLink for Oracle RAC, an attempt is made to recover these remaining sessions, masking the outage from users and applications.

   See Also:

   "Application Continuity: Automated Replay for Masking Outages"

4. Once the upgrade, patch, or repair is complete, restart the instance and the service on the original node.

5. The FAN UP event for the service informs the connection pool that a new instance is available for use, allowing sessions to be created on this instance at next request boundaries.

Fast Application Notification High Availability Events

This section describes the information delivered in the FAN event to a callout program. 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 when you receive FAN information through a callout, as in the following example:

SERVICEMEMBER VERSION=1.0
   service=test.company.com database=ractest
   instance=ractest11 host=ractest1_host0343_1 status=up reason=FAILURE
   timestamp=2012-02-01 22:06:02 timezone=-07:00 db_domain=company.com

Note that the preceding examples displays as one line.

DATABASE,
INSTANCE, NODE,
SERVICE,
SERVICEMEMBER,
SRV_PRECONNECT,
SERVICEMETRICS

VERSION

database

instance

host

service

SERVICEMEMBER VERSION=1.0 service=swingbench
 database=orcl instance=orcl_2 host=rwsbj13 status=up
 reason=USER card=1 timestamp=2012-05-29 17:26:37
 timezone=-07:00 db_domain=

SERVICEMEMBER VERSION=1.0 service=swingbench.example.com
 database=orcl instance=orcl1 host=rwsbj09 status=up
 reason=USER card=2 timestamp=2012-07-03 17:29:28
 timezone=-07:00 db_domain=example.com

SERVICEMEMBER VERSION=1.0 service=swingbench.example.com
 database=orcl instance=orcl2 host=rwsbj10 status=up
 reason=USER card=1 timestamp=2012-07-03 17:29:18
 timezone=-07:00 db_domain=example.com

status

reason

cardinality

SERVICEMEMBER VERSION=1.0 service=swingbench.example.com
database=orcl instance=orcl_2 host=mjkbj09 status=up
reason=USER card=1 timestamp=2012-07-12 14:46:46  timezone=-07:00 db_domain=example.com

incarnation

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

timestamp

timezone

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.

Subscription to High Availability Events

Oracle RAC uses FAN to notify applications about configuration changes and the current service level that is provided by each instance where the service is enabled. If you are using an OCI client or an ODP.NET client to receive FAN events, then you must enable the service used by that client to access the alert notification queue by using SRVCTL with the -notification parameter.

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 standalone when called, with optional arguments, from another program. The following is an example of the Grid_home/racg/usrco/callout.sh callout:

#! /bin/bash
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=public_nw_down
timestamp=08-Oct-2012 04:02:14 timezone=-08:00 reported=Fri Oct 8 04:02:14 PDT 2012

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 OCI connection handle and descriptor attributes (using OCIAttrGet()). Use this information to take actions on sessions that match the FAN event data.

In general, events are only posted to user callouts on the node from which the event originated. For example, if the database on node1 goes down, then the callout is posted to node1, only. The only exceptions to this are node down and VIP down events—these events are posted to all nodes, regardless of from where they originated.

Overview of Transaction Guard

One of the fundamental problems for recovering applications after an outage is that the commit message that is sent back to the client is not durable. If there is a communication disruption between the client and the server, then the client receives an error message indicating that the communication failed (also called a recoverable error ). This error does not inform the application whether the submission executed any commit operations; or if a procedural call ran to completion executing all expected commits and session state changes or failed part way through; or worse yet, is still running disconnected from the client.

Transaction Guard provides a generic protocol and API for applications to use for at-most-once execution in case of planned and unplanned outages and repeated submissions. Applications use a new concept called the logical transaction ID (LTXID) to determine the outcome of the last transaction open in a database session following an outage. Without using Transaction Guard, applications that attempt to retry operations following outages can cause logical corruption by committing duplicate transactions. Application Continuity uses this feature, and it is available to other applications to determine the last outcome on a failed session.

Transaction Guard Configuration Checklist

Before configuring services for Transaction Guard, use the following configuration checklist:

• Grant permission to the application user who will call GET_LTXID_OUTCOME, as follows:

  GRANT EXECUTE ON DBMS_APP_CONT to user_name;
  

• Set values for the -commit_outcome and -retention_timeout service parameters.

• If you are using Oracle RAC, Oracle Data Guard, or Oracle Active Data Guard, then Oracle recommends that you use FAN for fast notification of an outage.

Configuring Services for Transaction Guard

To configure services to use Transaction Guard, set the following service parameters:

• -commit_outcome: Set the -commit_outcome service parameter to TRUE. This service parameter determines whether the transaction commit outcome is accessible after the COMMIT has executed and an outage has occurred. While Oracle Database has always made COMMIT durable, Transaction Guard makes the outcome of the COMMIT durable, and is used by applications to enforce the status of the last transaction executed before an outage.

• -retention: Use the -retention service parameter with -commit_outcome. This service parameter determines the amount of time, in seconds, that the COMMIT outcome is retained. Oracle recommends that most installations use the default value.

The following SRVCTL command configures a policy-managed service named sales for Transaction Guard:

$ srvctl add service -db crm -service sales -serverpool spool_1
    -commit_outcome TRUE -retention 86400 -notification TRUE

The following SRVCTL command configures an administrator-managed service named sales for Transaction Guard:

$ srvctl add service -db crm -service sales -preferred crm_1,crm_2
    -available crm_3,crm_4 -commit_outcome TRUE -retention 86400
    -notification TRUE

You can also modify an existing service to configure it for Transaction Guard by using the srvctl modify service command.

Configuration Checklist for Application Continuity on Oracle RAC

Before using Application Continuity with an Oracle RAC database, use the following configuration checklist:

• Service Configuration Checks:

  • Use a database service (also referred to as an application service) to connect to the database. Do not use the default database service (DB_NAME or DB_UNIQUE_NAME), or the Oracle SID or instance name for client connections.

  • Ensure you enable the appropriate attributes for the service.

    See Also:

    "Creating Services for Application Continuity and Transaction Guard"

• Software Configuration Checks (database and middle tier):

  • Use Oracle Database 12c, or later.

  • Use the JDBC Universal Connection Pool (12.1) or WebLogic Active GridLink (12.1.2 or later) configured with the JDBC Replay data source, or use the JDBC Replay data source with your own JDBC connection pool.

  • Oracle recommends that you use FAN and FCF for WebLogic Active GridLink Data Source or Oracle JDBC Universal Connection Pool.

    See Also:

    Oracle Database 2 Day + Real Application Clusters Guide for information about simple FAN

  • Check the resource requirements; ensure there is sufficient CPU and memory in the middle tier and database tier.

    Note:

    Application Continuity can lead to a slight increase in CPU consumption on the server for managing purposes and on the client due to Java garbage collection. An increase in memory consumption is expected, too, because the replay driver needs to retain the request state. The individual increase in CPU and memory consumption differs on a case-by-case basis. CPU overhead on the database server may be reduced if the platform supports the current Intel and Sparc processors.

  • After consulting the application certification, GRANT keeping mutables to the users who will use replay.

  • Use a connection string that sets connection retries and a delay between these retries. For an example of configuring the connection properties using JDBC, see "Generic Database Clients".

• Application Code Checks (consult with the application developer):

  • Decide whether to use an optional callback for initializing connections from the application to the database. When using Oracle WebLogic Server or the Universal Connection Pool, connection labeling is recommended. If registered, a callback will execute at run time and replay.

  • Determine whether the application uses Oracle JDBC concrete classes. If it does, and if access is needed to Oracle proprietary APIs, then plan to replace these concrete classes with standard JDBC or Oracle JDBC interfaces.

    See Also:

    My Oracle Support Note 1364193.1 for information about these interfaces: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1364193.1

  • Determine whether the application uses mutable function calls SYSDATE, SYSTIMESTAMP, SYS_GUID, or Oracle sequences. If so, then determine whether it is correct to configure the application to keep the original values during failover.

  • Determine whether replay must be explicitly disabled for any code path in the application.

  • If the application borrows and returns connections from the Oracle WebLogic Server pool or Universal Connection Pool, then, for each request, do not change anything.

    If the application uses Oracle pools, and does not return connections between requests, then determine whether there is a property to set to return connections to the pool. If there is no property to set, or if you are using the application's own connection pool, then you can add beginRequest and endRequest boundaries.

This section includes the following topics:

• Administering Mutable Values

• Disabling Replay

GRANT [KEEP DATE_TIME | KEEP SYS_GUID]...[to USER]
REVOKE [KEEP DATE_TIME | KEEP SYS_GUID]...[from USER]

GRANT KEEP DATE_TIME, KEEP SYS_GUID to [custom user];
GRANT KEEP DATE_TIME, KEEP SYS_GUID to [apps user];

CREATE SEQUENCE [sequence object] [KEEP|NOKEEP];
ALTER SEQUENCE [sequence object] [KEEP|NOKEEP];

GRANT KEEP SEQUENCES...[to USER] on [sequence object];
REVOKE KEEP SEQUENCES...[from USER] on [sequence object];

GRANT KEEP SEQUENCES to [apps user] on [sequence object];
GRANT KEEP SEQUENCES to [custom user] on [sequence object];

alter system kill session 'sid, serial#, @inst' noreplay;
alter system disconnect session 'sid, serial#, @inst' noreplay

How Application Continuity Works for Applications

Application Continuity operates on request boundaries. A request is a unit of work submitted by the application. It is normally executed between borrowing and returning connections for Universal Connection Pool, Oracle WebLogic Server, DRCP, and third-party connection pools. A request is marked by the beginRequest and endRequest APIs. Oracle Database provides these APIs, by default, when using one of the aforementioned connections pools.

Application Continuity works for applications, as follows:

1. You either create a new database service or modify an existing database service so that the -failovertype parameter is set to TRANSACTION, and the -commit_outcome parameter is set to TRUE.

   See Also:

   "Creating Services for Application Continuity and Transaction Guard"

2. When an application opens a connection to the database using this service or borrows a connection from an Oracle connection pool using this service, the session handle carries a logical transaction ID (LTXID) for each transaction.

3. When the application submits a request, the server and replay driver collaborate to determine what states are to be captured for replay if there is an outage. If a recoverable error occurs (planned or unplanned), then the error message is sent back to the replay driver.

4. For JDBC-thin (type 4) applications, the replay driver receives the FAN message (down or error). Fast Connection Failover (FCF) aborts the dead session. The replay driver then:

   1. Replaces the dead physical session with a new clean session and reestablishes FAN in case a later error occurs during or after replay.

   2. Uses Transaction Guard to determine the outcome of the in-flight transaction, if one was open, to prepare for replay.

   3. Optionally, calls back using a labeling callback or reconnect callback for the initial state, if one of these is registered.

   4. Rebuilds the database session, recovering the transactional and nontransactional states, and validating at each step that the data and messages seen by the client driver are the same as those that the client may have seen and potentially made a decision on.

5. Oracle Database validates replay when replay is initiated and as replay progresses. If the database session cannot be rebuilt correctly, because the client data cannot be repositioned to the same data values and messages that the client potentially made decisions on previously, then the replay driver returns the original error to the application.

Failing Over OCI Clients with TAF

When Oracle Net Services establishes a connection to an instance, the connection remains open until the client closes the connection, the instance is shutdown, or a failure occurs. If you configure transparent application failover (TAF) for the connection, then Oracle Database replays the session at a surviving instance when an outage occurs.

TAF can restart a query after failover has completed but for other types of transactions, such as INSERT, UPDATE, or DELETE, the application must rollback the failed transaction and resubmit the transaction. You must also reexecute any session customizations, in other words, ALTER SESSION statements, after failover has occurred. However, with TAF, a connection is not moved during normal processing, even if the workload changes over time.

Services simplify the deployment of TAF. You can define a TAF policy for a service, and all connections using this service will automatically have TAF enabled. This does not require any client-side changes. The TAF setting on a service overrides any TAF setting in the client connection definition.

You can define a TAF policy for all users of a service by defining the -failovermethod and -failovertype parameters. You can further define the TAF policy by setting the number of times that a failed session attempts to reconnect to the service and how long it should wait between reconnection attempts using the -failoverretry and -failoverdelay parameters, respectively.

To define a TAF policy for a service, use SRVCTL as in the following example, where the service name is tafconn.example.com and the database name is CRM:

$ srvctl modify service -db crm -service tafconn.example.com -failovermethod BASIC
    -failovertype SELECT -failoverretry 10 -failoverdelay 30

OCI applications with TAF enabled should use FAN high availability events for fast connection failover.

Overview of the Load Balancing Advisory

Load balancing distributes work across all of the available Oracle RAC database instances. Oracle recommends that applications use connection pools with persistent connections that span the instances that offer a particular service. 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 and run-time 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, OCI session pool, Oracle WebLogic Server Active GridLink for Oracle RAC, and the ODP.NET Connection Pools. Third-party applications can also subscribe to load balancing advisory events by using JDBC and Oracle RAC FAN API or by using callbacks with OCI.

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 run-time 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 -db db_unique_name -service online
      -rlbgoal SERVICE_TIME -clbgoal 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 -db db_unique_name -service sjob
      -rlbgoal THROUGHPUT -clbgoal LONG
  

Setting the run-time 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 run-time connection load balancing feature of an Oracle integrated client such as JDBC, Universal Connection Pool (or the deprecated Implicit Connection Cache), ODP.NET Connection Pools, OCI session pools, or Oracle WebLogic Server Active GridLink for Oracle RAC. Other client applications can take advantage of FAN programatically by using the Oracle RAC FAN API to subscribe to FAN events and execute event-handling actions upon receipt. Table 5-4 describes the load balancing advisory FAN event parameters.

VERSION

EVENT_TYPE

SERVICE

DATABASE

INSTANCE

PERCENT

FLAG

TIMESTAMP

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
   { {instance=sales_4 percent=38 flag=GOOD aff=TRUE}{instance=sales_1
   percent=62 flag=GOOD aff=TRUE} } timestamp=2012-07-16 07:56:05')

Following is an example of a load balancing advisory event for the lba_serv service offered on two instances (orcl1 and orcl2), as captured from Oracle Notification Service using the Oracle RAC FAN API:

Notification Type: database/event/servicemetrics/lba_serv.example.com
   VERSION=1.0 database=orcl service=lba_serv.example.com { {instance=orcl2
   percent=50 flag=UNKNOWN aff=FALSE}{instance=orcl1 percent=50 flag=UNKNOWN
   aff=FALSE} } timestamp=2012-07-06 13:19:12

Overview of Oracle Integrated Clients and FAN

The overall goals of FAN are to enable end-to-end, lights-out recovery of applications and load balancing based on real transaction performance. Applications use the FAN high availability (HA) events to achieve very fast detection of failures, balancing of connection pools following failures, and distribution of connections again when the failed components are repaired.

The FAN events carrying load balancing advice help connection pools consistently deliver connections to available instances that provide the best service. FAN HA is integrated with the JDBC-thin and OCI drivers. FAN HA and FAN load balancing are both integrated with the JDBC Universal Connection Pool (and the deprecated Implicit Connection Cache), the OCI session pools, the ODP.NET connection pool, and Oracle WebLogic Server Active GridLink for Oracle RAC.

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 instances or services that are 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 client drivers that are integrated with FAN can:

• 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

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

• Balance work requests at run time using load balancing advisory events

The use of client drivers or connection pools and FAN requires that you properly configure the Oracle Notification Service to deliver the FAN events to the clients. In addition, for load balancing, configure 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-Thin Clients for Fast Connection Failover

Enabling Fast Connection Failover (FCF) for Universal Connection Pool and Oracle WebLogic Server Active GridLink for Oracle RAC enables the use of FAN HA and load balancing advisory events. For Universal Connection Pool to use FAN, your application can use the JDBC development environment for either JDBC OCI or JDBC Thin clients. The Java Database Connectivity Oracle Call Interface (JDBC/OCI) driver connection pooling functionality is part of the JDBC-thin client. This functionality is provided by the OracleOCIConnectionPool class.

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

JDBC application developers can programmatically 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 Run-time Connection Load Balancing

Oracle JDBC Universal Connection Pool and Oracle WebLogic Server Active GridLink for Oracle RAC leverage the load balancing functionality provided by an Oracle RAC database. Run-time connection load balancing requires the use of an Oracle JDBC driver and an Oracle RAC database.

The Universal Connection Pool and Oracle WebLogic Server Active GridLink for Oracle RAC are 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, which was introduced in Oracle Database 10g release 1 for use with Oracle RAC databases. In addition to Oracle Database 12c, you can also use the Universal Connection Pool with Oracle Database 10g or Oracle Database 11g.

Run-time connection load balancing requires that FCF is enabled and configured properly. 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 -db db_unique_name -service service_name
   -rlbgoal SERVICE_TIME -clbgoal SHORT

Configuring JDBC-Thin Clients for Application Continuity for Java

The Replay data source (oracle.jdbc.replay.OracleDataSource) is a JDBC-thin data source that Application Continuity requires for Java. This data source serves as the connection factory that produces new physical JDBC connections, for both Universal Connection Pool and Oracle WebLogic Server Active GridLink for Oracle RAC data sources. The JDBC replay driver maintains a history of calls during a client conversation with Oracle Database 12c, in collaboration with Oracle Database. Following any outage of the session caused by a loss of database service, planned or unplanned, under the direction of the database, the JDBC replay driver attempts to rebuild the non-transactional and transactional database session states, so that the outage appears as a delayed execution.

To use Application Continuity for Java and the JDBC replay driver, you must use an Oracle Database 12c client and connect to an Oracle Database 12c database. Application Continuity for Java is supported in the following configurations:

• JDBC applications using Oracle JDBC Replay data source and using neither Universal Connection Pool or Oracle WebLogic Server Active GridLink—typical third-party, JDBC-based connection pools

• JDBC applications using Universal Connection Pool data sources—standalone or third-party application servers configured to use a Universal Connection Pool data source

• JDBC applications using only Oracle WebLogic Server Active GridLink but not Universal Connection Pool data sources—typical Oracle WebLogic Server J2EE cases

To configure JDBC-thin clients to use the JDBC Replay Driver:

1. Ensure that you are using an application that is certified for replay.

   See Also:

   "Configuration Checklist for Application Continuity on Oracle RAC"

2. Use SRVCTL to create a service for use by the application, if one does not already exist. Set the -failovertype parameter to TRANSACTION and the -commit_outcome parameter to TRUE for this service.

   See Also:

   "Creating Services for Application Continuity and Transaction Guard" for more information about creating services.

3. Configure the connection element using the replayDataSource object, as shown in the following example:

   replayDataSource rds = PoolDataSourceFactory.getreplayDataSource();
   rds.setConnnectionPoolName("replayExample");
   rds.setONSConfiguration("nodes=racnode1:4200,racnode2:4200");
   rds.setFastConnectionFailoverEnabled(true);
   rds.setConnectionFactoryClassName("oracle.jdbc.replay.OracleDataSource");
   
   Connection conn = replayDataSource.getConnection();
   

4. When connecting to the database, use a URL that can access all instances offering the service.

Configuring JDBC-Thin Clients for Transaction Guard

Transaction Guard provides a protocol and a generic tool for applications to use for at-most-once execution in case of planned and unplanned outages. Applications use the logical transaction ID to determine the outcome of the last transaction open in a database session following an outage. Without Transaction Guard, end users or applications that attempt to retry operations following outages can cause logical corruption by committing duplicate transactions or committing transactions out of order.

Enabling OCI Clients for Fast Connection Failover

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, OCI drivers (including your own connection pools), OCI connection pool, and OCI session pools. FAN is posted over the Oracle Notification Service for both high availability and load balancing events.

To use FCF, you must use a service with FAN enabled. FAN is published over Oracle Notification Service. Client applications can also 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 FAN, connection load balancing, and run-time connection load balancing, as shown in the following example:

   $ srvctl modify service -db crm -service ociapp.example.com -notification TRUE
   

2. Link the application with a thread library.

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

Enabling OCI Clients for Run-time Connection Load Balancing

As of Oracle Database 12c, 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.

Run-time connection load balancing is enabled by default in an Oracle Database 11g release 11.1, or later, client communicating with a server of Oracle Database 10g release 10.2, or later. 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.

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 that you configure FAN, the load balancing advisory goal (-rlbgoal parameter), and the connection load balancing goal (-clbgoal parameter) for a service that is used by the session pool, as shown in the following example:

$ srvctl modify service -db crm -service ociapp.example.com -rlbgoal SERVICE_TIME
    -clbgoal SHORT -notification TRUE

Configuring OCI Clients to use Transaction Guard

OCI supports FAN messages and Transaction Guard. FAN is designed to quickly notify an OCI-based application of outages at the node, database, instance, service, and public network levels. Once notified of the failure, an application can leverage Transaction Guard to reliably determine the outcome of the last in-flight transaction.

Transaction Guard avoids the costs of ambiguous errors that lead to user frustration, customer support calls, and lost opportunities. Transaction Guard is safer and performs better, with lower overheads, than home grown solutions for a known outcome.

Enabling ODP.NET Clients to Receive FAN High Availability Events

ODP.NET connection pools can subscribe to FAN HA 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.

When connecting to Oracle Database 12c and later, ODP.NET uses Oracle Notification Service, rather than Advanced Queuing.

Enable Fast Connection Failover for ODP.NET connection pools by subscribing to FAN high availability events. To enable Fast Connection Failover, 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";

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

When connecting to Oracle Database 12c and later, ODP.NET uses Oracle Notification Service, rather than Advanced Queuing.

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

1. Enable Oracle Notification Service notifications by using SRVCTL, and set the run-time load balancing goal, as shown in the following example:

   $ srvctl modify service -db crm -service odpapp.example.com
       -notification TRUE -clbgoal LONG -rlbgoal SERVICE_TIME
   

2. Ensure Oracle Notification Service (ONS) is configured for FAN events including run-time load balancing advice.

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

Configuring ODP.NET Clients to use Transaction Guard

ODP.NET supports FAN messages and Transaction Guard. FAN is designed to quickly notify an ODP.NET-based application of outages at the node, database, instance, service, and public network levels. Once notified of the failure, an application can leverage Transaction Guard to reliably determine the outcome of the last in-flight transaction.

Transaction Guard avoids the costs of ambiguous errors that lead to user frustration, customer support calls, and lost opportunities. Transaction Guard is safer and performs better, with lower overheads, than home grown solutions for a known outcome.

Overview of XA Transactions and DTP Services

A global (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 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 Oracle RAC 11g release 1 (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. If the XA application does not use suspend and resume on the same transaction branch, and does not issue savepoints that span branches, then tightly coupled XA transactions no longer require the special type of singleton services to be deployed on Oracle RAC databases. If your application cannot determine whether a transaction branch has been suspended and resumed, then the application must continue to use DTP services or preferably use XA affinity.

XA affinity (placing all branches of the same XA transaction at the same Oracle RAC instance) is a requirement when suspending and resuming the same XA branch or if using savepoints across branches. It also provides much better performance because different transactions can be balanced. XA affinity is available with Oracle WebLogic Server Active GridLink for Oracle RAC, JDBC Universal Connection Pool, and Oracle Tuxedo.

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 and XA Affinity for XA Transactions

To provide improved application performance with distributed transaction processing (DTP) in Oracle RAC, you may want to take advantage of DTP services or XA affinity. 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. DTP or XA affinity is required, if suspending and resuming the same XA branch.

In addition, connection pools at the application server tier that load balance across multiple connections to an Oracle RAC database can use XA affinity 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 distributed transaction processing (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 distributed transaction processing (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 -db crm -service xa_01.example.com -preferred RAC01
       -available 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 -db crm -service xa_01.example.com -serverpool dtp_pool
       -cardinality SINGLETON
   

2. Set the DTP parameter (-dtp) 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 -db crm -service xa_01.example.com -dtp TRUE
   

Relocating DTP Services in Administrator-Managed Databases

If the instance that provides a distributed transaction processing (DTP) service, for example XA_01, fails, then the service fails over in the same manner as any other service. Because there is a requirement that sessions exist on exactly one instance, however, the -f (force kill) option for database sessions always applies.

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 DTP service.

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');
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'hot_batch', 
module_name =>'posting'); 

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 is similar to the following:

AGGREGATION            SERVICE                MODULE        ACTION
------------           --------------------   ----------    -------------
SERVICE_MODULE_ACTION  erp                    payroll       exceptions pay
SERVICE_MODULE         erp                    payroll
SERVICE_MODULE         hot_batch              posting

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.

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 for that database. Depending on the service management policy, you may have to manually restart the services when you start the database. Both the srvctl stop database and srvctl stop service commands accept the -force option to forcibly disconnect connections. To drain sessions for planned outages do not use the -force option.

  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. You can 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, such as when you are performing an upgrade and you want to prevent connection requests from succeeding.

• 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. Access this page, as follows:

1. In Oracle Enterprise Manager, go to the Cluster Database Home page.

   See Also:

   Oracle Database 2 Day DBA for details on logging in to Oracle Enterprise Manager.

2. From the Availability menu, select Cluster Managed Database Services to display the Cluster Managed Database Services page.

3. Enter or confirm the credentials for the Oracle RAC database and host operating system and click Continue to display the Cluster Managed Database Services page.

From the Cluster Managed Database Services page you can drill down 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 server pool and nodes offering the service in a policy-managed environment

• 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

• Creating Services for Application Continuity and Transaction Guard

• Starting and Stopping Services with SRVCTL

• Enabling and Disabling Services with SRVCTL

• Relocating Services with SRVCTL

• Obtaining the Status of Services with SRVCTL

• Obtaining the Configuration of Services with SRVCTL

$ srvctl add service -db racdb -service app2 -serverpool Srvpool1
    -failovertype TRANSACTION -commit_outcome TRUE -replay_init_time 1800
    -retention 86400 -notification TRUE -rlbgoal SERVICE_TIME -clbgoal SHORT
    -failoverretry 30 -failoverdelay 10

$ srvctl modify service -db racdb -service app1 -clbgoal SHORT
    -rlbgoal SERVICE_TIME -failoverretry 30 -failoverdelay 10
    -failovertype TRANSACTION -commit_outcome TRUE -replay_init_time 1800
    -retention 86400 -notification TRUE

$ srvctl modify service -db racdb -service app2 -commit_outcome TRUE
    -retention 86400 -notification TRUE

GRANT EXECUTE ON DBMS_APP_CONT;

$ srvctl start service -db db_unique_name [-service service_name_list]
    [-instance inst_name] [-startoption start_options]

$ srvctl stop service -db db_unique_name -service service_name_list
    [-instance inst_name] [-startoption start_options]

$ srvctl enable service -db db_unique_name -service service_name_list
    [-instance inst_name]

$ srvctl disable service -db db_unique_name -service service_name_list
    [-instance inst_name]

$ srvctl relocate service -db apps -service crm -oldinst apps1 -newinst apps3

$ srvctl relocate service -db apps -service crm -currentnode node1
    -targetnode node3

$ srvctl status service -db 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 -db apps -service erp

Service name: erp
Service is enabled
Server pool: pool1
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Global: false
Commit Outcome: true
Failover type: TRANSACTION
Failover method: NONE
TAF failover retries: 30
TAF failover delay: 10
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: SERVICE_TIME
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 1800 seconds
Session State Consistency: STATIC
Preferred instances: apps
Available instances: