American Database Consulting, LLC

How to Create Services on RAC 10g
Home
Downloads
BLOG
ORACLE
CommVault Consultation
Support
Useful Tips
Video Training
Contact Information
Resume

Services in Oracle Database 10g
==========================
 

Service Creation

The srvctl utility, dbca utility and DBMS_SERVICES package can all be used to create and modify services, but for this article we will restrict ourselves to looking at the srvctl utility. Let's assume we have two applications that should run in the following way:
  • OLTP - Should run on nodes 1 and 2 of the RAC, but is able to run on node 3 if nodes 1 and 2 are not available.
  • BATCH - Should run on node 3, but is able to run on nodes 1 and 2 if node 3 is not available.
To meet this requirement we might create the following services:
# Set environment.
                           export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
                           export PATH=$ORACLE_HOME/bin:$PATH
                           
                           # Create services.
                           srvctl add service -d ORCL -s OLTP_SERVICE -r ORCL1,ORCL2 -a ORCL3
                           srvctl add service -d ORCL -s BATCH_SERVICE -r ORCL3 -a ORCL1,ORCL2
The OLTP_SERVICE is able to run on all RAC nodes because ORCL3 is present in the available list, but will run in preference on nodes 1 and 2 (indicated by the -r option). The BATCH_SERVICE is able to run on all RAC nodes because ORCL1 and ORCL2 are in the available list, but will run in preference on node 3 (indicated by the -r option).

The services can be started and stopped using the following commands.
srvctl start service -d ORCL -s OLTP_SERVICE
                           srvctl start service -d ORCL -s BATCH_SERVICE
                           
                           srvctl stop service -d ORCL -s OLTP_SERVICE
                           srvctl stop service -d ORCL -s BATCH_SERVICE

Jobs and Services

The Oracle 10g scheduler allows jobs to be linked with job classes, which in turn can be linked to services to allows jobs to run on specific nodes in a RAC environment. To support our requirements we might create two job classes as follows.
-- Create OLTP and BATCH job classes.
                           BEGIN
                             DBMS_SCHEDULER.create_job_class(
                               job_class_name => 'OLTP_JOB_CLASS',
                               service        => 'OLTP_SERVICE');
                           
                             DBMS_SCHEDULER.create_job_class(
                               job_class_name => 'BATCH_JOB_CLASS',
                               service        => 'BATCH_SERVICE');
                           END;
                           /
                           
                           -- Make sure the relevant users have access to the job classes.
                           GRANT EXECUTE ON sys.oltp_job_class TO my_user;
                           GRANT EXECUTE ON sys.batch_job_class TO my_user;
These job classes can then be assigned to existing jobs or during job creation.
-- Create a job associated with a job class.
                           BEGIN
                             DBMS_SCHEDULER.create_job (
                               job_name        => 'my_user.oltp_job_test',
                               job_type        => 'PLSQL_BLOCK',
                               job_action      => 'BEGIN NULL; END;',
                               start_date      => SYSTIMESTAMP,
                               repeat_interval => 'FREQ=DAILY;',
                               job_class       => 'SYS.OLTP_JOB_CLASS',
                               end_date        => NULL,
                               enabled         => TRUE,
                               comments        => 'Job linked to the OLTP_JOB_CLASS.');
                           END;
                           /
                           
                           -- Assign a job class to an existing job.
                           EXEC DBMS_SCHEDULER.set_attribute('MY_BATCH_JOB', 'JOB_CLASS', 'BATCH_JOB_CLASS');

Connections and Services

The use of services is not restricted to scheduled jobs. These services can be used in the tnsnames.ora file to influence which nodes are used for each applications. An example of the tnsnames.ora file entries are displayed below.
OLTP =
                             (DESCRIPTION =
                               (LOAD_BALANCE = ON)
                               (FAILOVER = ON)
                               (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
                               (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
                               (ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
                               (CONNECT_DATA =
                                 (SERVICE_NAME = OLTP_SERVICE)
                                 (FAILOVER_MODE =
                                   (TYPE = SELECT)
                                   (METHOD = BASIC)
                                   (RETRIES = 20)
                                   (DELAY = 1)
                                 )
                               )
                             )
                           
                           BATCH =
                             (DESCRIPTION =
                               (LOAD_BALANCE = ON)
                               (FAILOVER = ON)
                               (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
                               (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
                               (ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
                               (CONNECT_DATA =
                                 (SERVICE_NAME = BATCH_SERVICE)
                                 (FAILOVER_MODE =
                                   (TYPE = SELECT)
                                   (METHOD = BASIC)
                                   (RETRIES = 20)
                                   (DELAY = 1)
                                 )
                               )
                             )

American Database Consulting and Service Company
4765 Rustler Ct.
Colorado Springs, CO 80918