Upgrading to RAC and breaking jobs due to legacy instance affinity

I recently ran into an interesting situation where a standalone database was changed to a higher SLA requirement and altered to run across a cluster with RAC One Node. The application running in that database had jobs created with instance affinity set. There was no harm in doing so with the standalone system, but there was no advantage in doing so either. It was odd, but safe. This application was well over a decade old and I’m not sure anyone knew the history of when or why the jobs were set that way but they were and nobody noticed until after the active instance switched to a different node and suddenly the job stopped running.

The problem can arise with jobs created with DBMS_SCHEDULER or the old DBMS_JOB package. Once identified it is an easy fixed for either type of job, remember DBMS_JOB requires a COMMIT for the change to take effect.

BEGIN
    DBMS_SCHEDULER.set_attribute(name      => 'MY_JOB', 
                                 attribute => 'instance_id', 
                                 value     => NULL);
END;

BEGIN
    DBMS_JOB.instance(job => 123, instance => 0);
    COMMIT;
END;

While this problem showed up with RAC One Node, it could just as easily happen with a full RAC installation if the assigned instance happened to be down.

It should also be noted, if there is a reason to assign a job to a specific instance it may be better to use DBMS_SCHEDULER with services associated with the target instance(s) instead of directly assigning the number. You can not only achieve the same end result of job/instance association; but you also gain all of the functionality that comes with using a service. This is one of the many reasons to consider switching from legacy DBMS_JOB to DBMS_SCHEDULER.