Specify Server for DBMS_Scheduler Job in Policy Managed RAC

jobsoracleoracle-11g-r2oracle-rac

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that limited the available instances and then using that service in a job class the job uses. My question is, how can this be done in 11.2 with policy management?

Pools can be created that have only a single server in them and databases can be assigned to multiple pools, but as I understand it, a server can only be assigned to a single pool. Therefore, a service can't be created that uses a single server and still have other services that use a pool defined with multiple servers including that one.

I also know that services can be created as either SINGLETON or UNIFORM, but since SIGNLETON doesn't provide for allowed servers or even preferred servers, I'm not sure how this would help.

Surely I am missing something that makes this all possible.

Best Answer

dbms_scheduler.set_attribute is what you're looking for - it forces a job to run on a given node (specified by instance_id):

begin
dbms_scheduler.create_job (
job_name=>'PHIL',
job_type=>'plsql_block',
job_action=>'NULL;',
repeat_interval=>'freq=minutely;interval=2');

end;
/

begin
dbms_scheduler.set_attribute('PHIL','instance_id',1);
end;
/

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

I realise that this isn't what you asked, but it's easier than messing with pools!

Related Question