Sunday, November 6, 2022

Oracle Database DBMS_Scheduler job to run on preferred node in RAC -- instance_stickiness

 

We observed  that Rac 1st  node seems highly loaded  though we had database service spread across  instances . Checking further  came across dba_scheduler_jobs feature   instance_stickiness   introduced back in 11g but not known much . 


If instance_stickiness is set to FALSE, each instance of the job runs on the first instance available. For environments other than Oracle RAC, this attribute is not useful because there is only one instance


dba_scheduler_jobs.instance_id will be NULL unless you have explicitly set it with set_attribute.

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.


BEGIN
  dbms_scheduler.create_job(
     job_name => 'TEST_JOB'
    ,job_type => 'PLSQL_BLOCK'
    ,job_action => 'begin null; end; '
    ,start_date => TIMESTAMP'2020-07-24 04:15:01 US/Eastern' 
     ,repeat_interval => 'FREQ=DAILY'
    ,enabled => TRUE
    ,comments => 'scheduler for LATE FEE CALULATION FOR LOANS');


  dbms_scheduler.set_attribute(
                              'TEST_JOB',
                              'INSTANCE_ID',
                              '1'
                             );
END;
/



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



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




Using Job Class and Database Service : 

This can also be achieved using   service defined in Job class 

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 a job classes as follows.

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



SQL> BEGIN
DBMS_SCHEDULER.create_job_class(
job_class_name => 'BATCH_JOB_CLASS',
service        => 'BATCH_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      => 'BATCH_JOB_CLASS',
   enabled     => 'TRUE'
   );
END;
/




Reference : 

How To Run A Job On Specific Node Using DBMS_SCHEDULER (Doc ID 472535.1)

Doc ID 2214476.1

No comments:

Post a Comment