Skip Headers

Oracle® Real Application Clusters Deployment and Performance Guide
10g Release 1 (10.1)

Part Number B10768-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

A Services Deployment Example

This appendix provides a services deployment example for a Oracle Real Application Clusters (RAC) database. This appendix includes the following topics:

Configuration Planning

This appendix contains an example of configuring and integrating Cluster Ready Services (CRS), a RAC database, and services to provide continuous application support. The basis for this example is a RAC database, called ORADB, running on a four-node cluster with one instance on each node. The instance names are RAC01, RAC02, RAC03, and RAC04. The database supports an application with five major components, ERP, CRM, SELF_SERVICE, which involve online transaction processing (OLTP) processing, and HOT_BATCH, and STD_BATCH, which are batch-oriented.

Configuration planning for high availability (HA) services involves defining which application, or parts of an application, you want to be manage with the services and which service features you want to enable.

Service Planning

To take advantage of all HA service capabilities, your plan needs to identify the service name, the primary user (client, application server, job scheduler, and so on), the preferred instances (where the service will start by default), and the available instances (where the service will run if a preferred instance becomes unavailable). You may also define the service priority (to rank importance of the services when competing for resources), and response time thresholds (to indicate when a service is not performing at its required rate).

This example includes all the options and Table A-1 summarizes the planned configuration for the ORADB database and its services, with columns Preferred Instances and Available Instances containing the HA information and columns Priority and Response Time containing the performance information.

Table A-1  Service Planning Work Sheet

Service Usage Preferred Instances Available Instances Priority Response Time (sec) Warning / Critical
ERP Client service RAC01, RAC02 RAC03, RAC04 HIGH 0.5 / 0.75
CRM Client service RAC03, RAC04 RAC01, RAC02 STANDARD 0.5 / 1.0
SELF_SERVICE Client service RAC01, RAC02, RAC03, RAC04 - STANDARD 1.0 / 1.5
HOT_BATCH Job scheduler RAC01 RAC02, RAC03, RAC04 HIGH 1.0 / 1.5
STD_BATCH Job scheduler RAC01, RAC02, RAC03, RAC04 - LOW 3.0 / 5.0

The plan calls for the ERP service to run on instances RAC01 and RAC02 when the cluster and database starts normally. That is, ERP will become available on instances RAC01 and RAC02 as they start up. The other two instances, RAC03 and RAC04, are available for the ERP service should one of its preferred instances fail. So, for example, if RAC01 becomes unavailable, either RAC03 or RAC04 takes over running the ERP service on behalf of the failed instance while RAC02, its remaining preferred instance, continues to run the ERP service. If both RAC01 and RAC02 are disabled, the ERP service runs on RAC03 and RAC04 instead.

The plan for the CRM service is similar to that for ERP but with the instances taking on the opposite roles: RAC03 and RAC04 are instances where the CRM service should start and RAC01 and RAC02 are the instances that take over should one or both of the preferred instances fail.

Both the SELF_SERVICE and STD_BATCH services are planned to start on all four instances whereas the HOT_BATCH service starts only on RAC01. Because they are already assigned to four instances, the plan does not define any available instances for the SELF_SERVICE and STD_BATCH services. The plan allows the HOT_BATCH service to use any of the other instances should RAC01 become unavailable.

Table A-1 also lists the planned priorities and response times for the services. The plan assigns the highest priority to the ERP and HOT_BATCH services, which means they will have precedence over the other services if resources become scarce - for example, if only instances RAC03 and RAC04 are available. In such a case, the service with the lowest priority rating, STD_BATCH, may be terminated and, if necessary, the CRM or SELF_SERVICE services could be flagged for termination. The response times are thresholds for which notifications should be triggered if performance fails to meet the listed values.

Cluster Node and Network Interface Planning

When you have completed your logical configuration, you may want to prepare an interface worksheet to record the cluster node interface names and addresses. Most of your interfaces, particularly the public interfaces, will have equivalent host names and domain names. In cases where names are resolvable to IP addresses, you may have provided these names when using the Oracle Universal Installer (OUI) and the Database Configuration Assistant (DBCA). Similarly, you may have entered the name, if there is one, or the IP address of the private interconnects used for the cluster interconnect interface. As you complete your interface worksheet, you may want to record the names, when they exist, along with the IP addresses.

Your virtual IP addresses (VIPs) should not be fixed to any physical interface on your network and VIPs may or may not have a corresponding name. In NetCA or in tnsnames.ora, you can enter either the names or the IP addresses of the VIPs. For vendor systems that support cluster aliases, you can replace the list of names or IP addresses with the corresponding cluster alias name or IP address. The cluster alias name for this example is clusalias. To execute some steps shown in this example, you will need to know the subnet mask for all of your VIPs used and location of your CRS home directory. In this example, the subnet mask is 255.255.255.0 for all VIPs and the CRS home directory is /private/oracle/crs, neither of which is included in the following table.

Table A-2 shows the interface worksheet for this example.

Table A-2 Example of a cluster node interface and address worksheet

Public physical node name | IP address |Physical interface name(s) Public virtual IP name |IP address |Logical interface name(s) Private interconnect IP address | Physical interface name
clusnode-1 | 139.184.101.201 | hme0 [, hme1] clusnode-1vip | 139.184.201.1 | hme0:1 [, hme1:1] 172.16.0.1 | qfe0
clusnode-2 | 139.184.101.202 |hme0 [, hme1] clusnode-2vip | 139.184.201.2 | hme0:1 [, hme1:1] 172.16.0.2 | qfe0
clusnode-3 | 139.184.101.203 | hme0 [, hme1] clusnode-3vip | 139.184.201.3 | hme0:1 [, hme1:1] 172.16.0.3 | qfe0
clusnode-4 | 139.184.101.204 | hme0 [, hme1] clusnode-4vip | 139.184.201.4 | hme0:1 [, hme1:1] 172.16.0.4 | qfe0

Manual Configuration for High Availability

The three steps in the first part of this example show you how to build the configuration, based on the information shown in Tables (UNKNOWN STEP NUMBER) and (UNKNOWN STEP NUMBER) . See Oracle® Real Application Clusters Administrator's Guide, Appendix B for a complete list of SRVCTL commands and syntax.


Note:

You must be logged into the system as root on UNIX or administrator on Windows when adding the VIPs. All other SRVCTL operations are executed as the oracle owner and dba group.

Step 1. Add Node Applications

Most of your node applications configuration should have been completed during your Cluster Ready Services (CRS) installation. You can verify this by running crs_stat command, which should show a sequence of resource metadata and a listener resource on each active node in the cluster. If you need to add new node application manually, for example, suppose you added clusnode-5 after your initial Oracle installation, you would use the following SRVCTL command logged in as root on UNIX or as Administrator on Windows:

srvctl add nodeapps -n clusnode-5 -o $ORACLE_HOME -A '139.184.201.5/255.255.255.0/hme0|hme1'

See Also:

Oracle Real Application Clusters Administrator's Guide, Appendix B, "Server Control (SRCVCTL) Reference" for a list of SRVCTL commands and examples

Step 2. Add Database and Instance Applications

Define the database and each of your four instances as follows (note that, in this example, the SPFILE location is $ORACLE_HOME/dbs/ORADB_spfile):

srvctl add database -d ORADB -o $ORACLE_HOME -s $ORACLE_HOME/dbs/ORADB_spfile
srvctl add instance -d ORADB -i RAC01 -n clusnode-1
srvctl add instance -d ORADB -i RAC02 -n clusnode-2
srvctl add instance -d ORADB -i RAC03 -n clusnode-3
srvctl add instance -d ORADB -i RAC04 -n clusnode-4

Step 3. Add Service Applications

Add the service definitions as follows:

srvctl add service -d ORADB -s ERP -r RAC01,RAC02 -a RAC03,RAC04
srvctl add service -d ORADB -s CRM -r RAC03,RAC04 -a RAC01,RAC02
srvctl add service -d ORADB -s SELF_SERVICE -r RAC01,RAC02,RAC03,RAC04
srvctl add service -d ORADB -s HOT_BATCH -r RAC01 -a RAC02,RAC03,RAC04
srvctl add service -d ORADB -s STD_BATCH -r RAC01,RAC02,RAC03,RAC04

Using Services

In this section of the example, you can see how to set up your Oracle Net Services configuration files and other application-related resources to ensure your application uses the services you have configured.

Using Services with Client Applications

Applications and mid-tier connection pools select a service by using the TNS connection data. The service must match the service that has been created using add service with SRVCTL or DBCA. You can check the services that are currently running by querying the V$ACTIVE_SERVICES view.

You may use the virtual addresses for client communication to ensure that connections and SQL statements issued against a node that is down do not result in a TCP/IP time out. If your system offers a cluster alias, you may use the cluster alias for the connection only. However, you must not use host names as addresses. The address lists in the following examples use either virtual IP addresses or cluster alias.

TNS Connection Description for ERP Service

TNS Connection Description for ERP Service
ERP= (DESCRIPTION= 
       (ADDRESS_LIST=
        (LOAD_BALANCE=yes) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521))
       ) 
       (CONNECT_DATA=(SERVICE_NAME=ERP)))

Alternatively, in the case of platforms supporting cluster aliases, the TNS alias can be simplified to:

ERP=(DESCRIPTION= 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusalias)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP)))

TNS Connection Description for ERP Service with TAF BASIC

ERP= (DESCRIPTION= 
     (LOAD_BALANCE=on) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP))
        (FAILOVER_MODE=(BACKUP=ERP)(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY =5))
      ) 

TNS Connection Description for ERP Service with TAF Preconnect

ERP= (DESCRIPTION= 
     (LOAD_BALANCE=on) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP))
        (FAILOVER_MODE=
           (BACKUP=ERP_PRECONNECT)(TYPE=SESSION)(METHOD=PRECONNECT)(RETRIES=180)(DELAY =5))
      ) 
 
ERP_PRECONNECT = (DESCRIPTION= 
     (LOAD_BALANCE=on) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP_PRECONNECT))
        (FAILOVER_MODE=
           (BACKUP=ERP)(TYPE=SESSION)(METHOD=BASIC)(RETRIES=180)(DELAY =5))
      ) 

Thick JDBC Connection Description for ERP Service

url="jdbc:oracle:oci:@TNS_ALIAS"
 
url="jdbc:oracle:oci:@(DESCRIPTION= 
     (LOAD_BALANCE=on) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP)))" 
 
url="jdbc:oracle:oci:@(DESCRIPTION= 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusalias)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP)))"

Thin JDBC Connection Description for ERP Service

url="jdbc:oracle:thin:@(DESCRIPTION= 
     (LOAD_BALANCE=on) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP)))" 
 
url="jdbc:oracle:thin:@(DESCRIPTION= 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusalias)(PORT=1521)) 
        (CONNECT_DATA=(SERVICE_NAME=ERP)))"

Listener Configuration for Services

You should cross-register your listeners using the REMOTE_LISTENERS initialization parameter so that all your listeners know about all of your services and the instances in which they run. The listeners should use server side load balancing, optionally based on session count for connection. The listeners must be listening on the VIPs and on the cluster aliases, when available. The listeners must not listen on the host name: listening on the host name results in disconnected sessions when VIPs automatically relocate to their owning nodes.

Sample listener.ora Entry

Each listener on each cluster node should have dual addressing, one pointing at the node VIP name (or address) and the other pointing at the host's physical IP address (or name).

# Listener name definition for host clusnode-1 (see Table A-2 for details):#
 
LISTENER_CLUSNODE-1 =
     (ADDRESS = (PROTOCOL = TCP)(HOST = clusnode-1vip)(PORT = 1521))
 
SID_LIST_LISTENER_CLUSNODE-1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = $ORACLE_HOME)
      (PROGRAM = extproc)
    )
  )

Sample Remote Listener Entries

# TNS alias entry maps to REMOTE_LISTENER initialization parameter:
LISTENERS_ORADB=
    (ADDRESS_LIST =
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-1vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-2vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-3vip)(PORT=1521)) 
        (ADDRESS=(PROTOCOL=TCP)(HOST=clusnode-4vip)(PORT=1521)))

Oracle Instance Parameters

You must ensure that the LOCAL_LISTENER, REMOTE_LISTENER, and ACTIVE_INSTANCE_COUNT initialization parameter values are valid to use the VIPs for your services. The listener definition values should the same as those defined in the section "Using Services with Client Applications". Follow these guidelines to set the correct values:

local_listener=LISTENER_CLUSNODE-1 -- TNS entry listing the virtual IP address for 
                                   -- node CLUSNODE-1
remote_listener=LISTENERS_ORADB    -- TNS entry listing the virtual IP addresses 
                                   -- used by database ORADB

You must ensure that the ACTIVE_INSTANCE_COUNT parameter is left at its default value - this parameter must not be set.

Manual Configuration for Workload Management

The four steps in this next part of this example show you how to complete your service configuration to enable workload management, DBMS_SCHEDULER.CREATE_JOB execution time, resource consumption, and wait events. The first two steps are required to configure the service priorities and the job classes for the server side services in the Automatic Workload Repository (AWR). Steps three and four define service performance thresholds and enable the measurement of modules and actions within services.

Step 1. Add Service Priorities

Before mapping services to consumer groups, you must create the required consumer groups and their related resource plans, which can be priority based or ratio based. For this example, the site already has three consumer groups named high_priority, standard_priority, and low_priority. These consumer groups map to a database resource plan that reflects the intended resource consumption.

The following SQL*Plus commands call PL/SQL to map each service to the desired consumer group and then display the results by querying DBA_SCHEDULER_JOB_CLASSES:

REM Create the consumer groups
execute dbms_resource_manager.create_pending_area;
execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'HIGH_PRIORITY', COMMENT => 'High priority consumer group');
execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'STANDARD_PRIORITY', COMMENT => 'Standard priority consumer group');
execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'LOW_PRIORITY', COMMENT => 'Low priority consumer group');
 
REM Create the service to consumer group mapping
execute DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME,VALUE => 'ERP',CONSUMER_GROUP => 'HIGH_PRIORITY');
execute DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME,VALUE => 'CRM',CONSUMER_GROUP => 'STANDARD_PRIORITY');
execute DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME,VALUE => 'SELF_SERVICE',CONSUMER_GROUP => 'STANDARD_PRIORITY');
execute DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME,VALUE => 'HOT_BATCH',CONSUMER_GROUP => 'HIGH_PRIORITY');
execute DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME,VALUE => 'STD_BATCH',CONSUMER_GROUP => 'LOW_PRIORITY');
execute dbms_resource_manager.submit_pending_area;
 
REM View the resource manager mappings
col value format a30 trunc
col attribute format a20 trunc
col consumer_group format a20 trunc
SELECT ATTRIBUTE, VALUE, CONSUMER_GROUP from DBA_RSRC_GROUP_MAPPINGS;

The query output would look like:

ATTRIBUTE            VALUE                          CONSUMER_GROUP
-------------------- ------------------------------ --------------------
SERVICE_NAME         ERP                            HIGH_PRIORITY
SERVICE_NAME         HOT_BATCH                      HIGH_PRIORITY
SERVICE_NAME         STD_BATCH                      LOW_PRIORITY
SERVICE_NAME         CRM                            STANDARD_PRIORITY
SERVICE_NAME         SELF_SERVICE                   STANDARD_PRIORITY
ORACLE_USER          SYS                            SYS_GROUP
ORACLE_USER          SYSTEM                         SYS_GROUP

You must ensure that the database user profiles include this mapping to prevent users from accessing services to which they are not entitled:

execute DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ( GRANTEE_NAME => 'PUBLIC', CONSUMER_GROUP => 'HIGH_PRIORITY', GRANT_OPTION => FALSE);

Step 2. Add Job Classes

The database employs two batch queues managed by the Job Scheduler, called HOT_BATCH and STD_BATCH. These queues correspond to job classes with services of the same name. The following PL/SQL code creates the job classes with assigned services:

REM For single instance, the services must be created explicitly:
REM execute dbms_service.create_service('HOT_BATCH', 'HOT_BATCH') ;
REM execute dbms_service.create_service('STD_BATCH', 'STD_BATCH') ;
 
REM Otherwise, for RAC, the instances were created with srvctl, so
REM job classes can be directly scheduled with the scheduler:
execute DBMS_SCHEDULER.CREATE_JOB_CLASS( JOB_CLASS_NAME => 'HOT_BATCH', RESOURCE_CONSUMER_GROUP => NULL, SERVICE => 'HOT_BATCH', LOGGING_LEVEL => DBMS_SCHEDULER.LOGGING_RUNS, LOG_HISTORY => 30, COMMENTS => 'P1 batch');
 
execute DBMS_SCHEDULER.CREATE_JOB_CLASS( JOB_CLASS_NAME => 'STD_BATCH', RESOURCE_CONSUMER_GROUP => NULL, SERVICE => 'STD_BATCH', LOGGING_LEVEL => DBMS_SCHEDULER.LOGGING_RUNS, LOG_HISTORY => 30, COMMENTS => 'P3 batch');
 
REM Verify the job class to service configuration
col service format a30 trunc
select JOB_CLASS_NAME, SERVICE from DBA_SCHEDULER_JOB_CLASSES;

The query output would look like:

JOB_CLASS_NAME                 SERVICE
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS
HOT_BATCH                      HOT_BATCH
STD_BATCH                      STD_BATCH

The jobs executing in these job classes execute at instances offering the service.

Step 3. Add Service Performance Thresholds

Add thresholds for the ERP and HOT-BATCH services as listed in table A-1.


Note:

The response target times are converted from seconds, shown in the planning worksheet (Table A-1), to microseconds, required by the DBMS_SERVER_ALERT.SET_THRESHOLD package in the following example.

The thresholds must be created for each RAC instance. Run the statements in this step in a SQL*Plus session:

REM ERP service, baseline at 0.25s, warning at 0.5s, critical at 0.75:
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge,    '500000', dbms_server_alert.operator_ge, '750000', 1, 5, 'RAC01', dbms_server_alert.object_type_service, 'ERP');
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '500000', dbms_server_alert.operator_ge, '750000', 1, 5, 'RAC02', dbms_server_alert.object_type_service, 'ERP');
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '500000', dbms_server_alert.operator_ge, '750000', 1, 5, 'RAC03', dbms_server_alert.object_type_service, 'ERP');
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '500000', dbms_server_alert.operator_ge, '750000', 1, 5, 'RAC04', dbms_server_alert.object_type_service, 'ERP');
 
REM HOT_BATCH service, baseline at 0.5, warning at 1.0s, critical at 1.5s:
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '1000000', dbms_server_alert.operator_ge, '1500000', 1, 5, 'RAC01', dbms_server_alert.object_type_service, 'ERP');
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '1000000', dbms_server_alert.operator_ge, '1500000', 1, 5, 'RAC02', dbms_server_alert.object_type_service, 'ERP');
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '1000000', dbms_server_alert.operator_ge, '1500000', 1, 5, 'RAC03', dbms_server_alert.object_type_service, 'ERP');
execute DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.elapsed_time_per_call, dbms_server_alert.operator_ge, '1000000', dbms_server_alert.operator_ge, '1500000', 1, 5, 'RAC04', dbms_server_alert.object_type_service, 'ERP');
 
REM verify the threshold configuration
select METRICS_NAME, WARNING_VALUE, CRITICAL_VALUE, OBSERVATION_PERIOD from dba_thresholds;

Step 4. Enable Service, Module, and Action Monitoring

You can enable performance data and tracing for important modules and actions within each service. The performance statistics are available in the V$SERV_MOD_ACT_STATS view. The following commands, executed in a SQL*Plus session, perform these actions:

  1. Enable monitoring for the exceptions pay action in the module, payroll, under the ERP service

  2. Enable monitoring for the all actions in the module, payroll, under the ERP service

  3. Enable monitoring for the all actions in the module, posting, under the HOT_BATCH service

  4. Confirm the configuration by querying DBA_ENABLED_AGGREGATIONS

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);
 
REM Verify the enabled service, module, action configuration
col AGGREGATION_TYPE format a20 trunc heading 'AGGREGATION'
col PRIMARY_ID format a20 trunc heading 'SERVICE'
col QUALIFIER_ID1 format a20 trunc heading 'MODULE'
col QUALIFIER_ID2 format a20 trunc heading 'ACTION'
select * from DBA_ENABLED_AGGREGATIONS ;

The query output would look like:

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

Using Services with Job Scheduler

Use the DBMS_SCHEDULER.CREATE_JOB procedure to define jobs to execute under the job classes. In this example, the MY_NAME.MY_PROC procedure will run in the HOT_BATCH service because of the job class assignment defined earlier, in Step 2:

execute DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'my_report_job',  JOB_TYPE => 'stored_procedure',
JOB_ACTION => 'my_name.my_proc();',  NUMBER_OF_ARGUMENTS => 4,START_DATE => SYSDATE+1,
REPEAT_INTERVAL => 5,  END_DATE => SYSDATE+30, JOB_CLASS => 'HOT_BATCH', ENABLED => TRUE,
AUTO_DROP => false, COMMENTS => 'my report on daily status');

Using Callouts for Fast Application Notification

Custom-written application callouts are programs or shell script wrappers that can be used to start and stop on- or off-cluster applications, or connection pools managed by middleware. They are immediately executed by RAC when a service or any part of the service starts, stops or fails to automatically restart. Other actions that can be encoded as callouts (besides restarting applications) include: logging fault tickets, e-mailing or paging administrators, and invoking third-party event systems or clusterware components.Callouts are not a requirement to deploy RAC-HA on CRS, but Oracle strongly advises customers to build notification mechanisms using callouts. Unless your CRS home directory is shared across the network, you must deploy each new callout under /private/oracle/crs/racg/usrco directory on each RAC node.The following example, a Bourne shell script, contains a number of callout options that are invoked whenever an HA event occurs. The callouts perform the following two actions: write an uptime status record to the log, and log a fault ticket (with the IT trouble ticket application) for all DOWN conditions.

#!/usr/bin/sh#
 
# Description: Example wrapper script to enable RAC event logging and notification
#              to generic third-party systems.  The script showcases two possible
#              methods to enable local or remote logging/notification of RAC-
#              detected events.#
 
# Note:  Unless your CRS home directory is on an NFS-mounted device, you should 
#        copy this script to the racg/usrco directories in your CRS home
#        directory, for all RAC nodes protected by Oracle Integrated Clusterware.  #        This is a one-time setup.#
 
# For additional details on callouts and what name=value pairs are passed by RAC,
# please review /private/oracle/crs/racg/usrco/README.
 
# Global variables:#
 
AWK=/usr/bin/awk
MY_CRS_HOME=/private/oracle/crs
 
# Scan and parse arglist:#
 
for ARGS in $*; do
   PROPERTY=`echo $ARGS | $AWK -F"=" '{print $1}'`
   VALUE=`echo $ARGS    | $AWK -F"=" '{print $2}'`
 
   #> map EVTTYPE to EVENT_TYP, NODE to HOST:
   case $PROPERTY in
     #> note: EVENT_TYP is one of: NODE, DATABASE, INSTANCE, SERVICE, SERVICEMEMBER
     EVENT_TYP | event_typ)     NOTIFY_EVENT_TYP=$VALUE ;;
     VERSION | version)         NOTIFY_VERSION=$VALUE ;;
     SERVICE | service)         NOTIFY_SERVICE=$VALUE ;;
     DATABASE | database)       NOTIFY_DBNAME=$VALUE ;;
     INSTANCE | instance)       NOTIFY_INSTANCE=$VALUE ;;
     HOST | host)               NOTIFY_HOST=$VALUE ;;
     STATUS | status)           NOTIFY_STATUS=$VALUE ;;
     TIMESTAMP | timestamp)     NOTIFY_SVRLOGDATE=$VALUE ;;
   esac
done
 
 
# ###################################################
# [1] Notification Method 1:  On-cluster file logging
# ###################################################
# This section simply writes one-line entries for each event published by RAC,
# and the log is written to standard RAC log directory.  It will blindly record
# all RAC events, regardless of state (UP, DOWN or NOT_RESTARTING):
 
RACEVT_LOGFILE=$MY_CRS_HOME/racg/log/rac_${NOTIFY_SERVICE}_uptime.log
 
echo RAC\(v$NOTIFY_VERSION\):  $NOTIFY_STATUS event, type "$NOTIFY_EVENT_TYP", \
     `if [ -n "$NOTIFY_SERVICE" ]; then \
        echo "for service $NOTIFY_SERVICE"
      fi` \
     \[`if [ -n "$NOTIFY_INSTANCE" ]; then \
        echo "inst: $NOTIFY_INSTANCE"
        fi` \
       `if [ -n "$NOTIFY_DATABASE" ]; then \
        echo "db: $NOTIFY_DATABASE"
        fi` \
       `if [ -n "$NOTIFY_HOST" ]; then \
        echo "db: $NOTIFY_HOST"
        fi` \
     \] received on $NOTIFY_SVRLOGDATE >>  $RACEVT_LOGFILE
 
 
 
# ########################################################
# [2] Notification Method 2:  On-cluster program execution
# ########################################################
# Let's assume you have a custom client program in /tmp (say logTicket) to which
# you can pass certain arguments.  This program connects to a customer-service
# application that processes incident tickets for your IT department:
#    % /tmp/logTicket {serverside_timestamp} \
#                     {databasename} {servicename} \
#                     {instancename} {hostname}#
 
# Let us also assume that a ticket would be logged only for NOT_RESTARTING events,
# as they are the ones that exceeded RAC-monitored timeouts and seriously need
# human intervention for full resolution.#
 
# -------------------
# ONE SOLUTION TO [2]:
# -------------------
if [ $NOTIFY_STATUS  = "NOT_RESTARTING" -o $NOTIFY_STATUS = "not_restarting" ]; then
  /tmp/logTicket $NOTIFY_SVRLOGDATE $NOTIFY_DBNAME \
                        $NOTIFY_SERVICE \
                        $NOTIFY_INSTANCE $NOTIFY_HOST   >> $RACEVT_LOGFILE
fi

Configuring JDBC Fast Application Notification

To use Fast Application Notification, the application must use the JDBC Implicit Connection Cache. JDBC connection pools are integrated with the callout mechanism, providing the following benefits:

Configuring the JDBC Client Side

Refer to JDBC User's Guide and Reference for configuring the JDBC Implicit Connection Cache and Oracle Notification Service (ONS). If ONS is not configured correctly, the implicit connection cache creation fails and an appropriate exception occurs upon the first getConnection()request.Set the ConnectionFailoverEnabled property before making the first getConnection() request to a DataSource. When Fast Connection Failover is enabled, the failover applies to every connection in the connection cache. If your application explicitly creates a connection cache using the Connection Cache Manager, you must first set ConnectionFailoverEnabled.

Configuring the RAC High Availability Server Side

The RAC event system needs to be configured to forward the HA events to every ONS, so that ONS clients at the mid-tier can receive and respond to the state changes.

Step 1 - Configure the ONS Daemon

The ONS daemon must be configured to broadcast the HA events from RAC to Oracle Application Server 10g clients. The ONS configuration file is located in --$ORACLE_HOME/opmn/-conf/ons.config and file should be built by OUI during installation Here is a sample RAC ons.config file:

localport=4100
loglevel=3
remoteport=4101
nodes=139.185.140.63:4101,139.185.140.64:4101,139.185.140.65:4101

where nodes is a list of all ONS daemons that talk to each other on RAC and Oracle Application Server. These values are given as a list of either host name or IP address plus port combinations in a comma separated list. Note that the port value that is given is the remote port that each ONS instance is listening on.

Step 2 - Check that the ONS Daemon is Running

The ONS daemon is running as a node application. To check node applications use the command: srvctl status nodeapps. Your results should be similar to the following:

NAME=ora.clusnode-4.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE

Use onsctl ping to check that the ONS daemon is active.

Using a Shared Oracle Home

ONS requires that the $ORACLE_HOME/opmn/log directory is private for each ONS daemon. If using a cluster file system for $ORACLE_HOME, each node should define $ORACLE_HOME/opmn/log as a link to a node specific directory, for example, $ORACLE_HOME/opmn/clusnode1/log.

Events for Shadow Preconnect Services in Real Application Clusters

When using Transparent Application Failover (TAF) PRECONNECT, Real Application Clusters (RAC) high availability maintains a preconnect service to support TAF Preconnect and applications that are configured to manage work on secondary RAC instances. Secondary instances are RAC instances that are not supporting the primary service.

In this type of configuration, Oracle maintains the shadow service on all instances that do not support the primary service. You can use events to stop and start secondary work. The events are posted to callouts and to the Oracle Notification Service (ONS).

To use events, configure the payload with the following format:

SRV_PRECONNECT VERSION=1.0 service=db_unique_name.db_domain
database=database name instance=instance name host=host name 
status=preconn_up reason=timestamp=27-Jan-2004 16:53:58 
reported=Tue Jan 27 16:53:59 PST 2004 
SRV_PRECONNECT VERSION=1.0 service=db_unique_name.db_domain
database=RACEY instance=instance name host=host name 
status=preconn_down reason=timestamp=27-Jan-2004 16:58:01
reported=Tue Jan 27 16:58:02 PST 2004

Up Event Example:

@ SRV_PRECONNECT VERSION=1.0 service=MYSERV.us.oracle.com 
database=RACEY instance=RACEY1 host=myhost-pc status=preconn_up 
reason=timestamp=27-Jan-2004 16:53:58 
reported=Tue Jan 27 16:53:59 PST 2004 

Down Event Example:

@ SRV_PRECONNECT VERSION=1.0 service=MYSERV.us.oracle.com
database=RACEY instance=RACEY1 host=myhost-pc status=preconn_down 
reason=timestamp=27-Jan-2004 16:58:01
reported=Tue Jan 27 16:58:02 PST 2004

High Availability Callouts and Oracle Notification Events

The notification interface is available as a server-side callout and as an Oracle Notification Services (ONS) event. The server-side callout is a script with the same payload as the ONS event that is run immediately on the server when the condition occurs. Use this method to start and stop server-side applications, to relocate low-priority services when high priority services arrive, and to post tickets for fault tracking. The following table describes the event payload.

Table A-3 Event payload parameters and descriptions

Parameter Description
Event type The event type for the component such as service, service_member, database, instance, or node
Service name The service name; matches the configured service in SERVICE$
Database name The database supporting the service; matches the initialization parameter value for DB_UNIQUE_NAME, which in turn defaults to the value of the initialization parameter DB_NAME
Instance The name of the instance that supports the service; matches the instance name
Node name The name of the node that supports the service or the node that has failed; matches the CSS node name
Status The new status; values are UP, DOWN, and NOT_RESTARTING
Cardinality Cardinality for the service on UP events
Time stamp The local time zone to use when ordering notification events
Incarnation Cluster incarnation for node down

When a session connects, mid-tiers can record the following values that match the high availability event payload.

sys_context('userenv', 'instance_name');
sys_context('userenv', 'server_host');
sys_context('userenv', 'service_name');
sys_context('userenv', 'db_unique_name');