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