12 Design and Deployment Techniques

Service Topologies and Workload Management in Oracle Real Application Clusters

Services are the basis for workload management in Oracle RAC. Clients and mid-tier applications make connection requests by specifying a global service name. Because Oracle RAC can reallocate services among instances in response to planned and unplanned outages, services greatly extend the availability and scalability of Oracle RAC environments.

Recommended Oracle Real Application Clusters Service Configurations

The recommended service configuration is to uniformly distribute service assignments across all available nodes. This simplifies your configuration and provides optimal high availability. Another approach is to non-uniformly configure services. In other words, workload sharing configurations can resemble many different topologies.

For example, assume that you have a five-node cluster with two instances, A and B, serving as the preferred instances for Customer Relationship Management (CRM). This same cluster could have instances C, D, and E as the preferred instances for Accounts Payable (AP). Instances A and B are the available instances for AP if one or more of AP's preferred instances become unavailable. Instances C, D, and E are the available instances for CRM if one or more of the CRM preferred instances becomes unavailable.

This configuration enables each service to use a group of instances that acts as both the preferred instances and as the instances that are available in case of a failure. After an outage, a client recovers its connections on another instance in the same group.

In this configuration, during normal operations Oracle RAC routes application sessions by service to separate groups of instances. If a preferred instance becomes unavailable, then Oracle Clusterware relocates connections among the remaining Oracle RAC instances that offer that service.

Workload managed configurations achieve the highest availability and performance by transparently maintaining affinity based on service. Planned and unplanned outages on one domain can be isolated from other domains and the affected service is recovered or upgraded in isolation.

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. This enables 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:

• The measurement of service quality

• Event notification and alert mechanisms to monitor service quality changes

• Recovery scenarios for responses to service quality changes

The Automatic Workload Repository ensures that the Oracle Clusterware workload management framework and resource manager have persistent and global representations of performance data. This information helps Oracle schedule job classes by service and to assign priorities to consumer groups. If necessary, you can re-balance workloads manually with the DBMS_SERVICE.DISCONNECT_SESSION PL/SQL procedure. You can also use this procedure to disconnect a series of sessions and leave the service running.

Setting Service Levels and Thresholds

Enterprise Manager and local Listeners subscribe to events that indicate changes in service levels. You can set service level metric thresholds with either Enterprise Manager or with Oracle packages.

You can see historical values for metrics in the V$SERVICEMETRIC_HISTORY view. Information about a service from the application level is available in the V$SESSION and V$SQL views. Service levels, or thresholds, are the baseline operational levels, and events indicate violations of these baselines. You can also examine GV$SVCMETRIC for timings such as resource consumption. Use the V$ACTIVE_SERVICES and GV$ACTIVE_SERVICES views to identify which services are running on which instances.

How Oracle Clusterware Manages Service Relocation

When an instance goes offline due to a planned outage or failure, Oracle Clusterware relocates the service to another available instances and re-establishes the connection without service interruption. This occurs as long as the underlying service components on which the relocation relies are enabled for relocation and restart.

Tablespace Use in Oracle Real Application Clusters

In addition to using locally managed tablespaces, you can further simplify space administration by using automatic segment-space management and automatic undo management.

Automatic segment-space management distributes instance workloads among each instance's subset of blocks for inserts. This improves Oracle RAC performance because it minimizes block transfers. To deploy automatic undo management in an Oracle RAC environment, each instance must have its own undo tablespace.

Object Creation and Performance in Oracle Real Application Clusters

As a general rule, only use DDL statements for maintenance tasks and avoid executing DDL statements during peak system operation periods. In most systems, the amount of new object creation and other DDL statements should be limited. Just as in single-instance Oracle databases, excessive object creation and deletion can increase performance overhead.

Node Addition and Deletion and the SYSAUX Tablespace in Oracle RAC

If you add nodes to your Oracle RAC database environment, then you may need to increase the size of the SYSAUX tablespace. Conversely, if you remove nodes from your cluster database, then you may be able to reduce the size of your SYSAUX tablespace.

Distributed Transactions and Oracle Real Application Clusters

When you use Oracle Distributed Transaction Processing (DTP) in Oracle RAC, which includes both XA and distributed SQL transactions, all tightly coupled branches of a distributed transaction must be hosted on the same instance. To ensure this, create multiple DTP services, with one or more on each Oracle RAC instance. Each DTP service is a singleton service that is available on one and only one Oracle RAC instance. All access to the database server for distributed transaction processing must be done by way of the DTP services. Ensure that all of the branches of a single global distributed transaction use the same DTP service. In other words, a network connection descriptor, such as a TNS name, a JDBC URL, and so on, must use a DTP service to support distributed transaction processing.