Oracle RAC 11gR2- Configure job scheduler to run on a preferred node

Oracle RAC 11gR2- Configure job scheduler to run on a preferred node

In Oracle RAC, a job that is defined in the scheduler may run on either node by default. However, there are JOB_SCHEDULER properties you can configure to change that.

INSTANCE_STICKINESS

This attribute should only be used for a database running in an Oracle Real Application Clusters (Oracle RAC) environment. By default, it is set to TRUE. If you set instance_stickiness to TRUE, jobs start running on the instance with the lightest load and the Scheduler thereafter attempts to run on the instance that it last ran on. If that instance is either down or so overloaded that it does not start new jobs for a significant period of time, another instance runs the job. If the interval between runs is large, instance_stickiness is ignored and the job is handled as if it were a non-sticky job.

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.

INSTANCE_ID

Valid only in an Oracle Real Application Clusters environment. Indicates the instance on which the job is to be run.

Basic Example

SQL> select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like 'JOB%';
OWNER JOB_NAME INSTA INSTANCE_ID
------------------------------ ------------------------------ ----- --------
USER DBA_JOB_1 TRUE

Use the DBMS_SCHEDULER.SET_ATTRIBUTE package

SQL> exec dbms_scheduler.set_attribute(name => 'USER.DBA_JOB_1' ,attribute=>'INSTANCE_ID', value=>'1');
PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.set_attribute(name => 'USER.DBA_JOB_1' ,attribute=>'INSTANCE_STICKINESS', value=>FALSE);
PL/SQL procedure successfully completed.

Results

SQL> select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like '%JOB%';
OWNER JOB_NAME INSTA INSTANCE_ID
------------------------------ ------------------------------ ----- --------
USER DBA_JOB_1 FALSE 1

Regards,
Wei Shan

Advertisements
  1. Leave a comment

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: