Services in Oracle Database 10g

 

In Real Application Cluster (RAC) environments it is sometimes desirable to run applications on a subset of RAC nodes, or have preferred nodes for specific applications. In Oracle 10g this is accomplished using services.

• Cluster Configuration
• Service Creation
• Jobs and Services
• Connections and Services
  

Cluster Configuration

Before using services, you must check the cluster configuration is correct. The following command and output show the expected configuration for a three node database called ORCL.

srvctl config database -d ORCL
server01 ORCL1 /u01/app/oracle/product/10.1.0/db_1
server02 ORCL2 /u01/app/oracle/product/10.1.0/db_1
server03 ORCL3 /u01/app/oracle/product/10.1.0/db_1

The configuration should have been performed during the database creation, but it can be performed subsequently using the following commands.

srvctl add database -d ORCL -o /u01/app/oracle/product/10.1.0/db_1
srvctl add instance -d ORCL -i ORCL1 -n server01
srvctl add instance -d ORCL -i ORCL2 -n server02
srvctl add instance -d ORCL -i ORCL3 -n server03

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

Provided applications use the appropriate connection identifier they should only connect to the nodes associated to the service.

For more information see: