A Services Deployment Example

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.

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 falls below 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.

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 RACAD Appendix B for a complete list of SRVCTL commands and syntax.

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

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

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 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 - this ensures 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
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)))

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

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

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

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

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 your 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 will result in disconnected sessions when VIPs are relocated automatically back to their owning nodes.

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

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

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

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.

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

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.

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

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