Run jobs in a particular instance using services

Hello and welcome to my first post.

In this post I want to explain how to assign an instance to our jobs using services (10g R1 & R2). In other words to “tell” a job to run on a particular instance(s).

To achieve this we need to perform three main steps:

1st.- Create the services
2nd.- Create the job classes and assign a service to them
3rd.- Create a job and assign a job class to it.

1st.- Create the services

Services are a very helpful tool on RAC databases, they will let us distribute workload and resources along our instances without sacrificing high availability features.

For this example we will have three instances: ORCL1, ORCL2 and ORCL3 running on a 10g database on Linux. We will also use the database user cacosta.

First we create 3 services:
OLTP_SERVICE,  BATCH_SERVICE,  REPORT_SERVICE

OLTP_SERVICE will run either on instances ORCL1 or ORCL2.
BATCH_SERVICE & REPORT_SERVICE will run only on ORCL3
(for this we use -r parameter).

When we create the services we can specify other AVAILABLE instances in case that the PREFERRED ones are not accessible (-a parameter).

$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
$srvctl add service -d ORCL -s REPORT_SERVICE -r ORCL3 -a ORCL1,ORCL2

Now we need to start the services

$srvctl start service -d ORCL -s OLTP_SERVICE
$srvctl start service -d ORCL -s BATCH_SERVICE
$srvctl start service -d ORCL -s REPORT_SERVICE

To review our work:
$srvctl status service -d ORCL
Service OLTP_SERVICE is running on instance(s) ORCL1 ORCL2
Service BATCH_SERVICE is running on instance(s) ORCL3
Service REPORT_SERVICE is running on instance(s) ORCL3

And also to detail the instance configuration per service:

$srvctl config service -d ORCL

OLTP_SERVICE PREF: ORCL1 ORCL2 AVAIL: ORCL3
BATCH_SERVICE PREF: ORCL3 AVAIL: ORCL1 ORCL2
REPORT_SERVICE PREF: ORCL3 AVAIL: ORCL1 ORCL2

Plase note that we can also create the services using the Enterprise Manager (Maintenance tab) or the DBCA

2nd.- Create the job classes and assign a service to them

This is a link step, I mean, we create the job classes to associate them to our services, and then we associate them to our jobs.

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

DBMS_SCHEDULER.create_job_class(
job_class_name => 'REPORT_JOB_CLASS',
service        => 'REPORT_SERVICE');
END;
/

Note: we can query our job classes in the dba_scheduler_job_classes table

Now we grant the permissions:

GRANT EXECUTE ON sys.oltp_job_class TO cacosta;
GRANT EXECUTE ON sys.batch_job_class TO cacosta;
GRANT EXECUTE ON sys.report_job_class TO cacosta;

3rd.- Create a job and assign a job class to it
Now we are ready to create our jobs and assign them a job class, here is an example that will run in the service OLTP_SERVICE.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"CACOSTA"."OLTP_JOB"',
program_name => 'my_oltp_procedure',
start_date => systimestamp',
job_class => 'OLTP_JOB_CLASS',
comments => 'This job will run on ORCL1 and ORCL2 instances',
auto_drop => FALSE,
enabled => TRUE);
END;

And that’s it, we can now distribute jobs across our RAC instances using job classes and services

Monitoring the jobs:
We can see the jobs in the table dba_scheduler_jobs, and to see where are they running we can query dba_scheduler_running_jobs.

Just as an ending comment I would like to say that the use of services is not restricted to jobs only, for example, we could also create TNS entries for each service allowing database clients to connect according to their functions.

Advertisements
This entry was posted in High-Availability and tagged , , , , , , , , , , , . Bookmark the permalink.

One Response to Run jobs in a particular instance using services

  1. Pingback: Other DBA Related « Center Point for Oracle DBA & Kuwait Info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s