Skip Headers
Oracle® Real Application Clusters Administration and Deployment Guide
11g Release 2 (11.2)
Part Number E10718-03
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

4 Introduction to Automatic Workload Management

This chapter describes how to manage workloads in Oracle Real Application Clusters (Oracle RAC) to provide high availability and scalability for your applications. This chapter includes the following topics:

Overview of Automatic Workload Management

Automatic workload management enables you to manage workload distributions to provide optimal performance for users and applications. Automatic workload management comprises the following:

When a user or application connects to a database, Oracle recommends that you specify a service in the connect data portion of the connect string. Oracle Database automatically creates one database service when the database is created. For many installations, this may be all you need. To enable more flexibility in the management of the workload using the database, Oracle Database enables you to create multiple services and specify which instances offer the services. Continue reading this chapter to understand the added features that you can use with services if you are interested in greater workload management flexibility.

Note:

The features discussed in this chapter do not work with the default database service. You must create cluster managed services to take advantage of these features. You can only manage services that you create. Any service created by the database server is managed by the database server.

You can deploy Oracle RAC and single-instance Oracle database environments to use automatic workload management features in many different ways. Depending on the number of nodes and your environment's complexity and objectives, your choices for optimal automatic workload management and high-availability configuration depend on several considerations that this chapter describes. The following section describes the various service deployment options.

Automatic Workload Repository

The Automatic Workload Repository (AWR) tracks service level statistics as metrics. Server generated alerts can be placed on these metrics when they exceed or fail to meet certain thresholds. You can then respond, for example, by changing the priority of a job, stopping overloaded processes, or by modifying a service level requirement, enabling you to maintain continued service availability despite service level changes. You can configure the service level for one service to have priorities relative to other services, and you can also configure:

Service Deployment Options

This section describes the following service deployment topics:

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

You can define services for both policy-managed and administrator-managed databases.

See Also:

"Oracle RAC Database Administration" for more information about policy-managed and administrator-managed databases

  • Policy-managed database: When you define services for a policy-managed database, you define the service to a server pool where the database is running. You can define the service as either uniform (running on all instances in the server pool) or singleton (running on only one instance in the server pool). For singleton services, Oracle RAC chooses on which instance in the server pool the service is active. If that instance fails, then the service fails over to another instance in the server pool. A service can only run in one server pool.

  • Administrator-managed database: When you define a service for an administrator-managed database, you define which instances normally support that service. These are known as the PREFERRED instances. You can also define other instances to support a service if the service's preferred instance fails. These are known as AVAILABLE instances.

    When you specify PREFERRED instances, you are specifying the number of instances on which a service normally runs. Oracle Clusterware attempts to ensure that the service always runs on the number of instances for which you have configured the service. Afterwards, due to either instance failure or planned service relocations, a service may be running on an AVAILABLE instance. You cannot control which AVAILABLE instance to which Oracle Clusterware relocates the services if there are multiple instances in the list. When a service moves to an AVAILABLE instance, Oracle Database does not move the service back to the PREFERRED instance when the PREFERRED instance restarts because:

    • The service is running on the desired number of instances

    • Maintaining the service on the current instance provides a higher level of service availability

    • Not moving the service back to the initial PREFERRED instance prevents a second outage

    You can, however, easily automate fail back by using FAN callouts.

When you define a service, you can also define the management policy for that service. You can choose either an automatic or a manual management policy.

  • automatic: The service always starts when the database starts.

    Note:

    When you use automatic services in an administrator-managed database, during planned database startup, services may start on the first instances to start rather than their PREFERRED instances.

  • manual:Requires that you start the service manually after the database starts. Prior to Oracle RAC 11g release 2 (11.2), all services worked as though they were defined with a manual management policy.

If you configured Oracle Data Guard in your environment, you can define a role for each service. When you specify a role for a service, Oracle Clusterware automatically starts it only when the database role matches the role you specified for the service. Valid roles are PRIMARY, PHYSICAL_STANDBY, LOGICAL_STANDBY, and SNAPSHOT_STANDBY.

See Also:

Resource profiles are automatically created when you define a service. A resource profile describes how Oracle Clusterware should manage the service and which instance the service should failover to if the preferred instance stops. Resource profiles also define service dependencies for the instance and the database. Due to these dependencies, if you stop a database, then the instances and services are automatically stopped in the correct order.

Services are integrated with Resource Manager, which enables you to restrict the resources that are used by the users who connect with a service in an instance. The Resource Manager enables you to map a consumer group to a service so that users who connect with the service are members of the specified consumer group. Also, the Automatic Workload Repository (AWR) enables you to monitor performance by service.

Oracle Net Services provides connection load balancing to enable you to spread user connections across all of the instances that are supporting a service. For each service, you can define the method you want the listener to use for load balancing by setting the connection load balancing goal, CLB_GOAL. You can also specify a single transparent application failover (TAF) policy for all users of a service by defining the FAILOVER_METHOD, FAILOVER_TYPE, and so on. (See the Oracle Database Net Services Administrator's Guide for more information about configuring TAF.)

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. FAN has two methods for publishing events to clients, the Oracle Notification Service (ONS) that is used by Java Database Connectivity (JDBC) clients including the Oracle Application Server, and Oracle Streams, and Advanced Queueing that is used by Oracle Call Interface and Oracle Data Provider for .NET (ODP.NET) clients. When using Advanced Queueing, you must enable the service to use the queue by setting AQ_HA_NOTIFICATIONS to true.

With run time connection load balancing, applications can use load balancing advisory events to provide better service to users. The Oracle JDBC, Oracle Universal Connection Pool (UCP) for Java, Oracle Call Interface, Connection Manager (CMAN), and ODP.NET clients are automatically integrated to take advantage of load balancing advisory events. The load balancing advisory informs the client about the current service level that an instance is providing for a service. The load balancing advisory also recommends how much of the workload should be sent to that instance. In addition, Oracle Net Services provides connection load balancing to enable you to spread user connections across all of the instances that support a service. To enable the load balancing advisory, set the GOAL parameter on the service.

Distributed transaction processing applications have unique requirements. To make it easier to use Oracle RAC with global transactions, set the distributed transaction processing parameter on the service so that all tightly coupled branches of a distributed transaction processing transaction are run on the same instance.

See Also:

"Services and Distributed Transaction Processing in Oracle RAC" for more information about distributed transaction processing in Oracle RAC

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.

Note:

You can explicitly manage only the services that you create. If a feature of the database creates an internal service, you cannot manage it using the information in this chapter.

Connection Load Balancing

Oracle Net Services provides the ability to balance client connections across the instances in an Oracle RAC configuration. There are two types of load balancing that you can implement: client-side and server-side load balancing. Client-side load balancing balances the connection requests across the listeners. With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service by using the load balancing advisory. In an Oracle RAC database, client connections should use both types of connection load balancing.

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, POSTMAN, with the srvctl utility to define the connection load balancing goal for long-lived sessions:

    srvctl modify service -d db_unique_name -s POSTMAN -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 ORDER, using SRVTCL to set the goal to SHORT:

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

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

  • Configures and enables server-side load balancing

  • Sets the local and remote listener parameters (Note: If you do not use DBCA, you should set the LOCAL_LISTENER and REMOTE_LISTENER database parameters manually, especially if you do not use port 1521.)

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

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 TAF for the connection, then Oracle Database moves the session to 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 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. To define a TAF policy for a service, use the srvctl utility as in the following example:

$ srvctl modify service -d crm -s  gl.us.oracle.com -q TRUE -P BASIC
   -e SELECT -z 180 -w 5 -j LONG

See Also:

Oracle Database Net Services Administrator's Guide for more information about configuring TAF

Client-side load balancing is defined in your client connection definition by setting the parameter LOAD_BALANCE=ON (the default is ON for description lists). 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 providing 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. When using SCAN, Oracle Net automatically load balances client connection requests across the three IP addresses you defined for the SCAN, except when using EZConnect. To see what services a listener supports, run the lsnrctl services command.

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 within which Oracle Net waits for a response from the listener.

You can avoid delays by setting the oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR property, as follows:

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

The parameter value is specified in milliseconds. Therefore, it is possible to reduce the timeout to 500Ms if the application retries connecting.

For Oracle Call Interface clients, create a local sqlnet.ora file on the client side. Configure the connection timeout in this file by adding the following line:

sqlnet.outbound_connect_timeout = 1

The granularity of the timeout value for the Oracle Call Interface client is in seconds. The sqlnet.ora file affects all connections using this client. With Oracle Database 11g Release 2 (11.2), Oracle Net Services introduces the ability to add the connect_timeout and retry_count parameters to individual tnsnames.ora connection strings.

(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)

The granularity is seconds. Oracle Net waits for 10 seconds to receive a response, after which it assumes a failure. Oracle Net goes through the address list three times before it returns a failure to the client.

Note:

Do not configure the connection timeout in the sqlnet.ora file on the server.

See Also:

Oracle Database Net Services Administrator's Guide for detailed information about both types of load balancing

Fast Application Notification

This section provides a detailed description of FAN under the following topics:

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 to shut down services when the number of available instances decreases, for example, if nodes fail

  • Automate the fail back of a service to PREFERRED instances if needed

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

The 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 use the service goal CLB_GOAL_SHORT, 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.

Managing Unplanned Outages

You can assign services in Oracle RAC to one or more instances. 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 Advanced Queuing. You can also program notification using FAN callouts.

Note:

Oracle Database does not run Oracle RAC callouts with guaranteed ordering. Callouts are run asynchronously and they are subject to scheduling variabilities.

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, Oracle RAC provides interfaces that relocate, disable, and enable services to minimize service disruption to application users. Once you complete the operation, you can return the service to normal operation.

Due to dependencies, if you manually shutdown your database, then all of your services 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.

Fast Application Notification High Availability Events

Table 4-1 describes the FAN event record parameters and the event types, followed by name-value pairs for the event properties. 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=mix1 database=ractest instance=rac1host=staih01 status=up
reason=FAILURE card=1 timestamp=2009-07-02 22:06:02

Table 4-1 Event Record Parameters and Descriptions

Parameter Description

VERSION

Version of the event record. Used to identify release changes.

EVENT TYPE

SERVICE, SERVICE_MEMBER, DATABASE, INSTANCE, NODE, SRV_PRECONNECT. Note that Database and Instance types provide the database service, such as DB_UNIQUE_NAME.DB_DOMAIN.

DATABASE UNIQUE NAME

The unique database supporting the service; matches the initialization parameter value for DB_UNIQUE_NAME, which defaults to the value of the initialization parameter DB_NAME.

INSTANCE

The name of the instance that supports the service; matches the ORACLE_SID value.

NODE NAME

The name of the node that supports the service or the node that has stopped; matches the node name known to Cluster Synchronization Services (CSS).

SERVICE

The service name; matches the service in DBA_SERVICES.

STATUS

Values are UP, DOWN, NOT_RESTARTING, PRECONN_UP, PRECONN_DOWN, and UNKNOWN.

REASON

Failure, Dependency, User, Autostart, Restart.

CARDINALITY

The number of service members that are currently active; included in all UP events.

INCARNATION

For node DOWN events; the new cluster incarnation.

TIMESTAMP

The local time zone to use when ordering notification events.

A FAN record matches the database signature of each session as shown in Table 4-2.

Table 4-2 FAN Parameters and Matching Database Signatures

FAN Parameter Matching Oracle Database Signature

SERVICE

sys_context('userenv', 'service_name')

DATABASE UNIQUE NAME

sys_context('userenv', 'db_unique_name')

INSTANCE

sys_context('userenv', 'instance_name')

CLUSTER NODE NAME

sys_context('userenv', 'server_host')

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 the following 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 directory Grid_home/racg/usrco on every node that runs Oracle Clusterware. If you are using scripts, then set the shell as the first line of the executable. 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 following output is from the previous example:

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

See Also:

Table 4-1, "Event Record Parameters and Descriptions" for information about the callout and event details

A FAN record matches the database signature of each session, as shown in Table 4-2. The signature information is also available using OCI_ATTRIBUTES. These attributes are available in the Oracle Call Interface Connection Handle. Use this information to take actions on sessions that match the FAN event data.

Load Balancing Advisory

This section describes the load balancing advisory under the following topics:

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. 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 THROUGHPUT or SERVICE_TIME 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. The load balancing advisory is also open for third-party subscription by way of the JDBC and Oracle RAC FAN API or through callbacks with the 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. This function enables the load balancing advisory for that service and FAN load balancing events are published. There are two types of service-level goals for run time:

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

    srvctl modify service -d db_unique_name -s OE -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:

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

    Setting the goal to NONE disables load balancing for the service. You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views.

    See Also:

    "Administering Services" for more information about administering services and adding goals to services

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, ODP.NET, or Oracle Call Interface. Client applications can subscribe to these events directly by way of the ONS API. Table 4-3 describes the load balancing advisory FAN event parameters.

Table 4-3 Load Balancing Advisory FAN Events

Parameter Description

VERSION

Version of the event record. Used to identify release changes.

EVENT TYPE

SERVICE, SERVICE_MEMBER, DATABASE, INSTANCE, NODE, ASM, SRV_PRECONNECT. Note that Database and Instance types provide the database service, such as DB_UNIQUE_NAME.DB_DOMAIN.

SERVICE

The service name; matches the service in DBA_SERVICES.

DATABASE UNIQUE NAME

The unique database supporting the service; matches the initialization parameter value for DB_UNIQUE_NAME, which defaults to the value of the initialization parameter DB_NAME.

INSTANCE

The name of the instance that supports the service; matches the ORACLE_SID value.

PERCENT

The percentage of work requests to send to this database instance.

FLAG

Indication of the service quality relative to the service goal. Valid values are GOOD, VIOLATING, NO DATA, and BLOCKED.

TIMESTAMP

The local time zone to use when ordering notification events.

Use the following example to monitor load balancing advisory events:

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 ;

Oracle Clients That Are Integrated with Fast Application Notification

Oracle has integrated FAN with many of the common client application environments that are used to connect to Oracle RAC databases. Therefore, the easiest way to use FAN is to use an integrated Oracle Client.

You can use the Oracle Call Interface Session Pools, CMAN session pools, and JDBC and ODP.NET connection pools. Oracle Call Interface applications with TAF enabled should use FAN high availability events for fast failover. The overall goal is to enable applications to consistently obtain connections to available instances that provide the best service. 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. 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 service(s) that the connection pool is using. Oracle recommends that you configure both client-side and server-side load balancing with Oracle Net Services, which is the default when you use DBCA to create your database. Oracle connection pools that are integrated with FAN can:

The next sections describe how to enable FAN events for the several specific client development environments:

Enabling JDBC Clients for Fast Connection Failover

Enabling Fast Connection Failover (FCF) for the Oracle JDBC Universal Connection Pool (UCP) enables FAN high availability events and the load balancing advisory. Your application can use the JDBC development environment for either thick or thin JDBC clients to use FAN. For Java applications, Oracle recommends the UCP. The UCP is integrated to take advantage of Load Balancing Advisory information. You can use UCP, introduced in Oracle Database 11g Patchset 1 (11.1.0.7), with Oracle Database 10g or Oracle Database 11g.

To configure the JDBC client, set the FastConnectionFailoverEnabled property before making the first getConnection() request to a data source. When you enable FCF, the failover applies to every connection in the connection cache. If your application explicitly creates a connection cache using the Connection Cache Manager, then you must first set FastConnectionFailoverEnabled.

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 by enabling the code to respond to FAN events 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

See Also:

Using FAN with Thick JDBC and Thin JDBC Clients

You can enable FCF Oracle's Implicit Connection Cache or UCP. Oracle recommends using the UCP for Java. The Implicit Connection Cache will be deprecated in the next release of Oracle Database. Enabling FCF with thin or thick JDBC clients enables the connection pool to receive and react to all FAN events.

This procedure explains how to enable FAN events for JDBC. For thick JDBC clients, if you enable FCF, do not enable TAF, either on the client or for the service. To enable FCF, you must first enable the UCP or the Implicit Connection Cache, as described in the following procedure:

  1. On a cache enabled DataSource, set the DataSource property FastConnectionFailoverEnabled to true as in the following example to enable FAN for the Oracle JDBC Implicit Connection Cache:

    OracleDataSource ods = new OracleDataSource()
...
ods.setConnectionCachingEnabled(True);
ods.setFastConnectionFailoverEnabled(True);
ods.setConnectionCacheName("MyCache");
ods.setConnectionCacheProperties(cp);
ods.setURL("jdbc:oracle:thin:@(DESCRIPTION= 
   (LOAD_BALANCE=on) 
   (ADDRESS=(PROTOCOL=TCP)(HOST=MYRACSCAN)(PORT=1521)) 
   (CONNECT_DATA=(service_name=service_name)))");

    Alternatively, you can use UCP for Java and set the PoolDataSource property for FastConnectionFailoverEnabled and the ONSConfiguration, as shown in the following syntax example. Applications must have both ucp.jar and ons.jar in their classpath.

    PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setONSConfiguration("nodes=racnode1:4200,racnode2:4200");
pds.setFastConnectionFailoverEnabled(true);
......

    Note:

    Use the following system property to enable FAN without making data source changes: -D oracle.jdbc.FastConnectionFailover=true.

  2. Review the ONS and eONS configurations on each node that is running Oracle Clusterware as in the following example:

    srvctl config nodeapps -s

    Note:

    ONS and eONS configuration should have been automatically completed during the Oracle Clusterware installation.

  3. The information in the Oracle Cluster Registry (OCR) for ONS daemons is automatically configured for Oracle Database 10g and higher. If you are upgrading from an Oracle9i version of the database, then add ONS daemons to remote nodes (nodes outside the cluster), with the following command:

    srvctl modify nodeapps -t host_port_list

  4. Configure remote ONS subscription. Remote ONS subscription offers the following advantages:

    • Support for an All Java mid-tier software

    • An ONS daemon is not necessary on the client system, so you do not have to manage this process

    • Simple configuration by way of a DataSource property

    When using remote ONS subscription for Fast Connection Failover, an application uses setONSConfiguration, using the string remoteONSConfig, on an Oracle DataSource instance with Implicit Connection Cache configured, as in the following example:

    ods.setONSConfiguration("nodes=racnode1:6251,racnode2:6251");

    If you are using the Universal Connection Pool, then use the following example instead, where the port number used is the same as the remote port configured in Step 2:

    pds.setONSConfiguration(“nodes=racnode1:6251,racnode2:6251”);

  5. When you start the application, ensure that the ons.jar file is located on the application CLASSPATH. The ons.jar file is part of the Oracle client installation.

See Also:

Oracle Database JDBC Developer's Guide for more information about JDBC

Enabling Oracle Call Interface Clients for Fast Connection Failover

Oracle Call Interface clients can enable Fast Connection Failover by registering to receive notifications about Oracle RAC high availability FAN events and respond when events occur. This improves the session failover response time in Oracle Call Interface and also removes terminated connections from connection and session pools. This feature works on Oracle Call Interface applications, including those that use TAF, connection pools, or session pools.

First, you must enable a service for high availability events. This automatically populates the advanced queuing ALERT_QUEUE. If your application is using TAF, then enable the TAF settings for the service. Configure client applications to connect to an Oracle RAC database. Clients 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, Oracle Call Interface:

  • Terminates affected connections at the client and returns an error

  • Removes connections from the Oracle Call Interface connection pool and the Oracle Call Interface 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.

Note:

Oracle Call Interface does not manage UP events.

Perform the following steps to configure Fast Connection Failover with an Oracle Call Interface client:

  1. Ensure that the service that you are using has Advanced Queuing notifications enabled by setting the services' values of AQ_HA_NOTIFICATIONS to TRUE. For example:

    $ srvctl modify service -d crm -s gl.us.oracle.com -q TRUE -P BASIC
   -e SELECT -z 180 -w 5 -j LONG

  2. Enable OCI_EVENTS at environment creation time on the client as follows:

    ( OCIEnvCreate(...) )

  3. Client applications must link with the client thread or operating system library.

  4. Optionally register a client EVENT callback

To see the alert information, you can query the views DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY.

See Also:

Oracle Call Interface Programmer's Guide for more information about Oracle Call Interface and the Oracle Database Net Services Administrator's Guide for more information about configuring TAF

Enabling Oracle Call Interface Clients for Run Time Connection Load Balancing

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

To fully optimize session pools with Oracle RAC, sessions given to the requesting threads are appropriately mapped to instances based on the instance load. The Oracle Call Interface session pools use service metrics information received from the Load Balancing Advisory in Oracle RAC to allocate the sessions, resulting in better performance

Oracle Call Interface dynamically maps sessions to the threads based on the instance load. It uses service metrics provided by the Load Balancing Advisory to perform run time connection load balancing.Foot 1  The number of connections can exceed the number of threads at certain times, but the Oracle Call Interface eventually adjusts this automatically, without any need for user intervention.

If the session pool supports services that span multiple instances, then the work requests are distributed across instances so that the instances providing better service are given more requests. For session pools that support services at only one instance, the first available session in the pool is adequate.

Run time connection load balancing is enabled by default in a Oracle Database 11g release 2 (11.2) (or higher) client talking to a server running Oracle Database 10g release 2 (10.2) (or higher). To disable run time connection load balancing, set the mode parameter to OCI_SPC_NO_RLB when calling OCISessionPoolCreate().

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 Oracle Call Interface environment in OCI_EVENTS and OCI_THREADED mode.

  3. Enable event notification for the service for which the session pool is created.

    To enable event notification, use srvctl with the -q option (for AQ HA notifications) set to TRUE.

  4. Modify the service to set up its Runtime Load Balancing Goal (with the -B option) and Connection Load Balancing Goal (with the -j option) as shown in the following example:

    $ srvctl modify service -d crm -s  myService -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 that stops and ODP.NET proactively disposes connections that are no longer valid. ODP.NET establishes connections to existing Oracle RAC instances if the removal of severed connections brings the total number of connections below the value that is set for the MIN_POOL_SIZE parameter.

The procedures for enabling ODP.NET are similar to the procedures for enabling JDBC in that you must set parameters in the connection string to enable Fast Connection Failover. Perform the following steps to enable FAN:

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

    $ srvctl modify service -d crm -s  gl.us.oracle.com -q TRUE -j LONG

  2. Execute the following for the users that will be connecting by way of the .Net Application, where user_name is the user name:

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

  3. Enable Fast Connection Failover for ODP.NET connection pools by subscribing to FAN high availability events. Do this by setting the ha events connection string attribute to true at connection time. Note that this only works if you are using connection pools. In other words, do this if you have set the pooling attribute to true, which is the default. The following example shows this in more detail where user_name is the name of the user and password is the password:

    // C#
using System;
using Oracle.DataAccess.Client;
 
class HAEventEnablingSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    // Open a connection using ConnectionString attributes
    // Also, enable "load balancing"
    con.ConnectionString =
      "User Id=user_name;Password=password;Data Source=oracle;" +
      "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
      "HA Events=true;Incr Pool Size=5;Decr Pool Size=2";
 
    con.Open();
 
    // Create more connections and carry out work against the DB here.
 
    // Dispose OracleConnection object
    con.Dispose();
  }
}

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

Use the following procedures to enable ODP.NET 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 gl.us.oracle.com -q TRUE -j LONG -m BASIC -e SELECT

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

  3. Execute the following for the user that will be connecting by way of the .Net Application where user_name is the name of the user:

    EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','SYS.SYS$SERVICE_METRICS', 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:

    // C#
using System;
using Oracle.DataAccess.Client;
 
class LoadBalancingEnablingSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    // Open a connection using ConnectionString attributes
    // Also, enable "load balancing"
    con.ConnectionString =
      "User Id=user_name;Password=password;Data Source=oracle;" +
      "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
      "Load Balancing=true;Incr Pool Size=5;Decr Pool Size=2";
 
    con.Open();
 
    // Create more connections and carry out work against the DB here.
 
    // Dispose OracleConnection object
    con.Dispose();
  }
}

Note:

ODP.NET does not support connection re-distribution when a node starts. However, if you have enabled failover on the server-side, then ODP.NET can migrate connections to available instances.

See Also:

Enabling Event Notification for Connection Failures in Oracle RAC

Event notification is enabled if the SQL_ORCLATTR_FAILOVER_CALLBACK and SQL_ORCLATTR_FAILOVER_HANDLE attributes of the SQLSetConnectAttr function are set when a connection failure occurs in an Oracle RAC database environment. The symbols for the new attributes are defined in the sqora.h file. The SQL_ORCLATTR_FAILOVER_CALLBACK attribute is used to specify the address of a routine to call when a failure event takes place.

The SQL_ORCLATTR_FAILOVER_HANDLE attribute is used to specify a context handle that is passed as a parameter in the callback routine. This attribute is necessary for the ODBC application to determine which connection the failure event is taking place on.

The function prototype for the callback routine is as follows:

void failover_callback(void *handle, SQLINTEGER fo_code)

The handle parameter is the value that was set by the SQL_ORCLATTR_FAILOVER_HANDLE attribute. Null is returned if the attribute has not been set.

The fo_code parameter identifies the failure event that is taking place. The failure events map directly to the events defined in the Oracle Call Interface programming interface. The list of possible events is as follows:

The following is a sample program that demonstrates how to use this feature where user_name is the name of the user and password is the password:

/*
NAME
ODBCCallbackTestDESCRIPTION
Program to demonstrate the connection failover callback feature.PUBLIC FUNCTION(S)   
mainPRIVATE FUNCTION(S)NOTESCommand Line: ODBCCallbackTest filename [odbc-driver]*/
#include <malloc.h>
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqora.h>
#define TRUE  1
#define FALSE 0
/*
 * Funtion Prototypes
 */
void display_errors(SQLSMALLINT HandleType, SQLHANDLE Handle);
void failover_callback(void *Handle, SQLINTEGER fo_code);
/*
 * Macros
 */
#define ODBC_STS_CHECK(sts) \
if (sts != SQL_SUCCESS) \
{ \
display_errors(SQL_HANDLE_ENV, hEnv); \
display_errors(SQL_HANDLE_DBC, hDbc); \
display_errors(SQL_HANDLE_STMT, hStmt); \
return FALSE; \
}
/*
 * ODBC Handles
 */
SQLHENV *hEnv = NULL; // ODBC Environment Handle
SQLHANDLE *hDbc = NULL; // ODBC Connection Handle
SQLHANDLE *hStmt = NULL; // ODBC Statement Handle
/*
 * MAIN Routine
 */
main(int argc, char **argv)
{
SQLRETURN rc;
/*
 * Connection Information
 */
SQLTCHAR *dsn = "odbctest";
SQLTCHAR *uid = "user_name";
SQLTCHAR *pwd = "password";
SQLTCHAR *szSelect = "select * from emp";
/*
 * Allocate handles
 */
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE *)&hEnv);
ODBC_STS_CHECK(rc)
rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
ODBC_STS_CHECK(rc);
rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, (SQLHANDLE *)&hDbc);
ODBC_STS_CHECK(rc);
/*
 * Connect to the database
 */
rc = SQLConnect(hDbc, dsn, (SQLSMALLINT)strlen(dsn),
uid, (SQLSMALLINT)strlen(uid),
pwd, (SQLSMALLINT)strlen(pwd));
ODBC_STS_CHECK(rc);
/*
 * Set the connection failover attributes
 */
rc = SQLSetConnectAttr(hDbc, SQL_ORCLATTR_FAILOVER_CALLBACK, &failover_callback,0);
ODBC_STS_CHECK(rc);
rc = SQLSetConnectAttr(hDbc, SQL_ORCLATTR_FAILOVER_HANDLE, hDbc, 0);
ODBC_STS_CHECK(rc);
/*
 * Allocate the statement handle
 */
rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, (SQLHANDLE *)&hStmt);
ODBC_STS_CHECK(rc);
/*
 * Wait for connection failovers
 */
while (TRUE)
{
  sleep(5000);
  rc = SQLExecDirect(hStmt,szSelect, strlen(szSelect));
  ODBC_STS_CHECK(rc);
  rc = SQLFreeStmt(hStmt, SQL_CLOSE);
  ODBC_STS_CHECK(rc);
}
/*
 * Free up the handles and close the connection
 */
rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
ODBC_STS_CHECK(rc);
rc = SQLDisconnect(hDbc);
ODBC_STS_CHECK(rc);
rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
ODBC_STS_CHECK(rc);
rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
ODBC_STS_CHECK(rc);
return TRUE;
}
/*
 * Failover Callback Routine
 */
void failover_callback(void *Handle, SQLINTEGER fo_code)
{
switch (fo_code)
{
case ODBC_FO_BEGIN:
    printf("ODBC_FO_BEGIN recevied");
        break;
case ODBC_FO_ERROR:
    printf("ODBC_FO_ERROR recevied");
        break;
case ODBC_FO_ABORT:
        printf("ODBC_FO_ABORT recevied");
        break;
case ODBC_FO_REAUTH:
        printf("ODBC_FO_REAUTH recevied");
        break;
case ODBC_FO_END:
        printf("ODBC_FO_END recevied");
        break;
default:
        printf("Invalid or unknown ODBC failover code recevied");
        break;
};
return;
}
/*
 * Retrieve the errors associated with the handle passed
 * and display them.
 */
void display_errors(SQLSMALLINT HandleType, SQLHANDLE Handle)
{
SQLTCHAR MessageText[256];
SQLTCHAR SqlState[5+1];
SQLSMALLINT i=1;
SQLINTEGER NativeError;
SQLSMALLINT TextLength;
SQLRETURN sts = SQL_SUCCESS;
if (Handle == NULL) return;
/*
 * Make sure all SQLState text is null terminated
 */
SqlState[5] = '\0';
/*
 * Fetch and display all diagnostic records that exist for this handle
 */
while (sts == SQL_SUCCESS)
{
NativeError = 0;
TextLength = 0;
sts = SQLGetDiagRec(HandleType, Handle, i, SqlState, &NativeError,
(SQLTCHAR *)&MessageText, sizeof(MessageText),&TextLength);
if (sts == SQL_SUCCESS)
{
  printf("[%s]%s\n",NativeError, &MessageText);
  if (NativeError != 0)
  {
    printf("Native Error Code: %d", NativeError);
  }
  i++;
}
}
return;
}

Services and Distributed Transaction Processing in Oracle RAC

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

This feature is controlled through the GLOBAL_TXN_PROCESSES initialization parameter, which is set to 1 by default. This parameter specifies the initial number of GTXn background processes for each Oracle RAC instance. Keep this parameter at its default value clusterwide to allow distributed transactions to span multiple Oracle RAC instances.

This 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. Tightly coupled XA transactions no longer require the special type of singleton services (that is, Oracle Distributed Transaction Processing (DTP) services) to be deployed on Oracle RAC database. XA transactions are transparently supported on Oracle RAC databases with any type of services configuration.

See Also:

Oracle Database Advanced Application Developer's Guide for complete information about using Oracle XA with Oracle RAC, and Oracle Database Reference for information about the GLOBAL_TXN_PROCESSES initialization parameter

To provide improved application performance with distributed transaction processing in Oracle RAC, you may want to take advantage of the specialized service referred to as a DTP Service. 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:

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.

An external transaction manager, such as 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.

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.

See Also:

Oracle Database Advanced Application Developer's Guide for more information about transaction branch management in Oracle RAC

Enabling Distributed Transaction Processing for Services

For services that you are going to use for distributed transaction processing, create the service using Oracle Enterprise Manager, or SRVCTL and define only one instance as the preferred instance. You can have as many AVAILABLE instances as you want. For example, the following SRVCTL command creates a singleton service for database crm, xa_01.service.us.oracle.com, whose preferred instance is RAC01:

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

Then mark the service for distributed transaction processing by setting the DTP parameter to TRUE; the default is FALSE. Oracle Enterprise Manager enables you to set this parameter on the Cluster Managed Database Services: Create Service or Modify Service page. You can also use SRVCTL to modify the DTP property of the singleton service, as follows:

$ srvctl modify service -d crm -s xa_01.service.us.oracle.com -x TRUE

If, for example, RAC01 (that provides service XA_01) fails, then the singleton service that it provided fails over to another instance, such as RAC02 or RAC03.

If services migrate to other instances after the cold-start of the Oracle RAC database, then you might have to force the relocation of the service to evenly re-balance the load on all of the available hardware. Use data from the GV$ACTIVE_SERVICES view to determine whether to do this.

Administering Services

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 and Oracle Database can measure the performance for each service. 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 each service's automatic workload management characteristics. The characteristics of a service include:

In addition to creating services, you can:

Notes:

  • When you use services, do not set a value for the SERVICE_NAMES parameter; Oracle Database controls the setting for this parameter for the services that you create and for the default database service. The service features that this chapter describes are not directly related to the features that Oracle Database provides when you set SERVICE_NAMES. In addition, setting a value for this parameter may override some benefits of using services.

  • Using service names to access a queue provides location transparency for the queue in an Oracle RAC database.

  • If you specify a service using the DISPATCHERS initialization parameter, it overrides any service in the SERVICE_NAMES parameter, and cannot be managed. (For example, stopping the service with a SRVCTL command does not stop users connecting with the service.)

Administering Services with Oracle Enterprise Manager and SRVCTL

You can create and administer services with Oracle Enterprise Manager and the SRVCTL utility. The following sections describe how to perform service-related tasks using these tools:

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:

  • 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

See Also:

Oracle Enterprise Manager Concepts for more information about administering services with Oracle Enterprise Manager

Service-Related Tasks That You Can Perform with Oracle Enterprise Manager

You can perform service-related tasks as described for the following Oracle Enterprise Manager pages:

Cluster Managed Database Services Page

The Cluster Managed Database Services page enables you to:

  • View a list of services for the cluster, the instances on which each service is currently running, and the status for each service

  • Start or stop a service, or enable or disable a service

  • Access the Create Service and Edit Service pages

  • View all the TNS strings for a service

  • Access the Services Detail page to perform instance-level tasks for a service

  • Test the connection for a service

  • Delete a service

Cluster Managed Database Services Detail Page

The Cluster Managed Database Services Detail page enables you to:

  • View the status of a service on all of its preferred and available instances; the status can be Running, Stopped, or Disabled

  • View the attributes of the service

  • Stop or start a service for an instance of a cluster database

  • Disable or enable a service for an instance of a cluster database

  • Relocate a service to manually re-balance the services load

  • View performance charts for the service

Create Services Page

The Create Services page enables you to:

  • Create the service with and specify its high availability configuration and service properties

  • Specify whether or not the service should be started after it has been created, and whether or not the tnsnames.ora file should be updated with the new service information

  • Select the desired service properties, such as:

    • TAF policy

    • Distributed transaction processing

    • Whether or not the Load Balancing Advisory should be enabled for the service

    • Connection Load Balancing Goal

    • Runtime Load Balancing Goal

    • Whether or not Fast Application Notification (FAN) for Oracle Call Interface and ODP.NET Applications should be enabled

    • Service threshold levels (for generating alerts with Enterprise Manager)

    • Resource management consumer group or job scheduler mappings

    See Also:

    Oracle Database Net Services Administrator's Guide for more information about configuring TAF

Accessing the Oracle Enterprise Manager Services Pages

To access the Cluster Managed Database Services page and detail pages for service instances:

  1. On the Cluster Database Home page, click Availability to view the Availability subpage.

  2. From the Cluster Database Availability page, under the Services heading, click Cluster Managed Database Services.

    The Cluster and Database Login page appears.

  3. Enter credentials for the database and for the cluster that hosts the cluster database, then click Continue.

    The Cluster Managed Database Services page appears and displays services that are available on the cluster database instances. For information about performing tasks on this page, see the online help for this page.

    Note:

    You must have SYSDBA credentials to access a cluster database. Cluster Managed Database Services does not permit you to connect as anything other than SYSDBA.

  4. To manage a service at the instance level, either click a service name or select a service name, then select Manage from the Actions drop-down list and click Go.

    The Cluster Managed Database Services Detail page for the service appears. For information about performing tasks on this page, see the online help for this page.

To access the Relocate page:

  1. Perform steps 1 - 3 from the previous procedure set.

  2. From the Cluster Managed Database Services page, either click a service name or select a service name, then select Manage in the Actions drop-down list, then click Go.

    The Cluster Managed Database Services Detail page for the service appears.

  3. Select the instance from which you want to move the service, then click Relocate.

    The Relocate Service from Instance page appears, where you can perform manual load balancing of services.

    For information about performing tasks on this page, see the online help for this page.

Administering Services with SRVCTL

When you create a service with SRVCTL, you must start it 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:

See Also:

Appendix A, "Server Control Utility Reference" for more information about SRVCTL commands that you can use to manage services, including descriptions of options

Creating Services with SRVCTL

To create a service with SRVCTL, enter a command from the command line using the following syntax:

srvctl add service -d db_unique_name -s service_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]

Note:

The service_name parameter entry has a 4KB limit. Therefore, the total length of the names of all services assigned to an instance cannot exceed 4KB.

Starting and Stopping Services with SRVCTL

Enter the following SRVCTL syntax from the command line:

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]

Enabling and Disabling Services with SRVCTL

Use the following SRVCTL syntax from the command line to enable and disable services:

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]

Relocating Services with SRVCTL

Run the srvctl relocate service command from the command line to relocate a service. For example, the following command relocates the crm service from instance apps1 to instance apps3:

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

Obtaining the Statuses of Services with SRVCTL

Run the srvctl relocate service command from the command line to obtain the status of a service. For example, the following command returns the status of the crm service that is running on the crm database:

srvctl status service -d apps -s crm

Obtaining the Configuration of Services with SRVCTL

Run the srvctl relocate service command from the command line to obtain the high availability configuration of a service. For example, the following command returns the configuration of the crm service that is running on the crm database:

srvctl config service -d apps -s crm -a

See Also:

Appendix A, "Server Control Utility Reference" for information about other administrative tasks that you can perform with SRVCTL

Measuring Performance by Service Using the Automatic Workload Repository

Services add a new dimension for performance tuning. With services, workloads are visible and measurable and resource consumption and wait times are attributable by application. Tuning by using 'service and SQL' replaces tuning by 'session and SQL' in the majority of systems where all sessions are anonymous and shared.

The Automatic Workload Repository (AWR) maintains performance statistics that include information about response time, throughput, resource consumption, and wait events for all services and work that a database performs. Oracle Database also maintains metrics, statistics, wait events, wait classes, and SQL-level traces for services. You can optionally augment these statistics by defining modules within your application to monitor certain statistics. You can also define the actions within those modules that business critical transactions should execute in response to particular statistical values. Enable module and action monitoring using the DBMS_MONTIOR PL/SQL package as follows:

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

Use the DBA_ENABLED_AGGREGATIONS view to verify that you have enabled monitoring.

Statistics aggregation and tracing by service are global in scope for Oracle RAC databases. In addition, they are persistent across instance restarts and service relocations for both Oracle RAC and single-instance Oracle databases.

The service, module, and action names are visible in V$SESSION, V$ACTIVE_SESSION_HISTORY, and V$SQL views. The call times and performance statistics are visible in V$SERVICE_STATS, V$SERVICE_EVENT, V$SERVICE_WAIT_CLASS, V$SERVICEMETRIC, and V$SERVICEMETRIC_HISTORY. When you enable statistics collection for an important transaction, you can see the call speed for each service, module, and action name at each database instance using the V$SERV_MOD_ACT_STATS view.

The following sample SQL*Plus script provides service quality statistics every five seconds. You can use these service quality statistics to monitor the quality of a service, to direct work, and to balance services across Oracle RAC instances:

SET PAGESIZE 60 COLSEP '|' NUMWIDTH 8 LINESIZE 132 VERIFY OFF FEEDBACK OFF
COLUMN service_name FORMAT A20 TRUNCATED HEADING 'Service'
COLUMN begin_time HEADING 'Begin Time' FORMAT A10
COLUMN end_time HEADING 'End Time' FORMAT A10
COLUMN instance_name HEADING 'Instance' FORMAT A10
COLUMN service_time HEADING 'Service Time|mSec/Call' FORMAT 999999999
COLUMN throughput HEADING 'Calls/sec'FORMAT 99.99 
BREAK ON service_name SKIP 1 
SELECT 
    service_name 
  , TO_CHAR(begin_time, 'HH:MI:SS') begin_time 
  , TO_CHAR(end_time, 'HH:MI:SS') end_time 
  , instance_name 
  , elapsedpercall  service_time
  ,  callspersec  throughput
FROM  
    gv$instance i     
  , gv$active_services s     
  , gv$servicemetric m 
WHERE s.inst_id = m.inst_id  
  AND s.name_hash = m.service_name_hash
  AND i.inst_id = m.inst_id
  AND m.group_id = 10
ORDER BY
   service_name
 , i.inst_id
 , begin_time ;

Service Thresholds and Alerts

Service level thresholds enable you to compare achieved service levels against accepted minimum required levels. This provides accountability for the delivery or the failure to deliver an agreed service level. The end goal is a predictable system that achieves service levels. There is no requirement to perform as fast as possible with minimum resource consumption; the requirement is to meet the quality of service.

You can explicitly specify two performance thresholds for each service: the response time for calls, or SERVICE_ELAPSED_TIME, and the CPU time for calls, or SERVICE_CPU_TIME. The response time goal indicates that the elapsed time should not exceed a certain value, and the response time represents wall clock time. Response time is a fundamental measure that reflects all delays and faults that might be blocking the call from running on behalf of the user. Response time can also indicate differences in node power across the nodes of an Oracle RAC database. The service time and CPU time are calculated as the moving average of the elapsed, server-side call time. The AWR monitors the service time and CPU time and publishes AWR alerts when the performance exceeds the thresholds. You can then respond to these alerts by changing the priority of a job, stopping overloaded processes, or by relocating, expanding, shrinking, starting or stopping a service. This permits you to maintain service availability despite changes in demand.

Example of Services and Thresholds Alerts

To check the thresholds for the payroll service, use the AWR report. You should record output from the report over several successive intervals during which time the system is running optimally. For example, assume that for an e-mail server, the AWR report runs each Monday during the peak usage times of 10:00 a.m. to 2:00 p.m. The AWR report would contain the response time, or DB time, and the CPU consumption time, or CPU time, for calls for each service. The AWR report would also provide a breakdown of the work done and the wait times that are contributing to the response times.

Using DBMS_MONITOR, set a warning threshold for the payroll service at 0.5 seconds and a critical threshold for the payroll service at 0.75 seconds. You must set these thresholds at all instances within an Oracle RAC database. You can schedule actions using Oracle Enterprise Manager jobs for alerts, or you can schedule actions to occur programmatically when the alert is received. In this example, thresholds are added for the servall service and set 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 => 'servall');

Verify the threshold configuration 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. As an example, set the following:

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

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

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

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

Verify the enabled service, module, action configuration with 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

Footnote Legend

Footnote 1: Run time connection load balancing is basically routing work requests to sessions in a session pool that can best serve the work. It comes into effect when selecting a session from an existing session pool. Thus, run time connection load balancing is a very frequent activity.
Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us