How to run scheduler job on specific instance in RAC database

Here I'll show you how to configure scheduler job to run on specific instance in RAC database.

create test job on RAC database:

begin
	sys.dbms_scheduler.create_job(
	job_name        => 'SYS.TEST_SCHEDULER_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'declare var1 varchar2(1);
begin
	select * into var1 from dual;
	dbms_output.put_line(var1);
end;',
    start_date      => to_date('26-01-2024 00:00:00','dd-mm-yyyy hh24:mi:ss'),
	repeat_interval => 'Freq=Secondly;Interval=5',
	end_date        => to_date(null),
	job_class       => 'DEFAULT_JOB_CLASS',
	enabled         => true,
	auto_drop       => false,
	comments        => '');
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 job with its instance_id and instance_stickiness:

select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME='TEST_SCHEDULER_JOB';

change instance_id for job:

begin 
 dbms_scheduler.set_attribute(name => 'TEST_SCHEDULER_JOB' ,attribute=>'INSTANCE_ID', value=>'2');
end;
/

disable instance_stickiness attribute for job:

begin 
 dbms_scheduler.set_attribute(name => 'TEST_SCHEDULER_JOB' ,attribute=>'INSTANCE_STICKINESS', value=>FALSE);
end;
/