Configured the DBMS Scheduler job to run on preferred node in RAC

Configured the DBMS Scheduler job to run on preferred node in RAC by two ways:

  1. Use Service attribute for specify the instance.
  2. Use instance id and Instance stickiness attribute in job scheduler.

Use Service attribute for defining job

Running the DBMS scheduler job in particular node with help of Services. In RAC environment, we associate the service to the job class. Service associated with the particular node in RAC Environment.

Example of managing services in RAC Environment:

--Create the service on two nodes.
srvctl add service -d ORCL -s TEST_SERVICE -r RAC1,RAC2
srvctl add service -d ORCL -s TEST_SERVICE -preferred RAC1 -available RAC2

--Stop and start the service on a single or multiple nodes.
srvctl stop service -d orcl -s TEST_SERVICE -i RAC1,RAC2
srvctl start service -d orcl -s TEST_SERVICE -i RAC1

--Disable and enable the service on a single or multiple nodes.
srvctl disable service -d ORCL -s TEST_SERVICE -i RAC1,RAC2
srvctl enable service -d ORCL -s TEST_SERVICE -i RAC1

--Display the current status of the service.
srvctl status service -d ORCL -s TEST_SERVICE -v

--Remove the service from both nodes.
srvctl remove service -d ORCL -s TEST_SERVICE -i RAC1,RAC2

Example of using Services in DBMS Scheduler jobs:

--- Create job class with service name
BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name => 'TEST_JOB_CLASS',
    service        => 'TEST_SERVICE');
End;
/

-- Create job by using that job class
Begin
DBMS_SCHEDULER.CREATE_JOB (
   job_name  => 'TEST_JOB',
   job_type  => 'PLSQL_BLOCK',
   job_action => 'Begin dbms_output.put_line(''Hello'') end;',
   number_of_arguments  => 0,
   start_date      => Sysdate,
   repeat_interval  => 'freq=daily;byhour=9,21;byminute=0;bysecond=0;',
   job_class      => 'TEST_JOB_CLASS',
   enabled     => 'TRUE'
   );
END;
/

Using instance ID and instance stickiness attribute in job scheduler

Instance ID value indicate the instance on which the job is to be run.

Instance Stickiness value true means job will run on same node until node is extremely overloaded or not available. False means job will run on any available node. Its default value is true. Job will continue on that on which its first run or started in RAC case. No use in Standalone server.

--Check the job condition and status with following query
 select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like 'JOB%';

--Change the attribute value with DBMS set attribute procedure
--select Preferred instance run the job is 1
exec dbms_scheduler.set_attribute(name => 'TEST_JOB' ,attribute=>'INSTANCE_ID', value=>'1');

--Disable the instance stickness attribute.
exec dbms_scheduler.set_attribute(name => 'TEST_JOB' ,attribute=>'INSTANCE_STICKINESS', value=>FALSE);

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.