-- Can DBMS_JOBS Failover when using instance affinity? --

One of my customers recently approached me asking this question, first let me explain what is instance affinity for DBMS_JOBS.

Let's assume you have 3 jobs you need to run and you are using Oracle RAC, you have 3 instances (1 Database), and you want to be able to balance the load (not using SQL Net load balancing), you could use node affinity to ensure that a particular job only runs from one node.

DBMS_JOB.INSTANCE (JOB IN BINERY_INTEGER,
           INSTANCE                     IN BINARY_INTEGER,
           FORCE                          IN BOOLEAN DEFAULT FALSE);

In the above syntax the INSTANCE parameter represent the instance number where you want the job to run, the FORCE when FALSE (Default) and having INSTANCE > 0 will alter the job affinity for the specified instance assuming that instance is running, if INSTANCE = 0 then the job will be executed by any instance, If FORCE = TRUE then the job will be altered regardless of the state of the instance as long as the INSTANCE parameter is not negative or null.

Leaving that clear the answer is no, DBMS_JOB node affinity will not failover to a surviving instance during an Oracle RAC instance failover, it will simple not run while the instance is down.

People have suggested that Oracle should enhance DBMS_JOB to achieve that function, the answer is that they did, the DBMS_SCHEDULER uses job classes that you can associate to an specific service, and as long as you failover the service to the surviving instance the job will be executed there.

BEGIN
DBMS_SCHEDULER.CREATE_J0B_CLASS(
    JOB_CLASS_NAME => '[CLASS_NAME]',
    SERVICE => '[SERVICE_NAME]');
END;
/

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB(
     JOB_NAME => '[JOB_NAME]',
     PROGRAM_NAME => '[PROGRAM_NAME]',
     START_DATE => '[START_DATE]',
     JOB_CLASS => '[CLASS_NAME]',
     COMMENTS => '[COMMENTS]',
     AUTO_DROP => FALSE,
     ENABLES => TRUE);
END;

In this way you can distribute job across RAC instances using job classes and services.