DBMS_SCHEDULER jobs stuck after upgrade to 11.2? Check this parameter!

I think I learn something new practically every time that I talk to or work with customers. Here’s a nugget of information that may be worth its weight in gold if you are upgrading in a RAC environment. You might find after the upgrade that your DBMS_SCHEDULER jobs seem to be scheduled but unable to run, as if the scheduler is stuck for some reason.

The reason for this is an interesting parameter job_queue_processes whose behavior changed in 11.2. The parameter itself has been around for a long time, but here is the important note from the 11.2 Upgrade Guide, Appendix A (I’ve highlighted the important bit in red):

JOB_QUEUE_PROCESSES Parameter

Beginning with Oracle Database 11g Release 1 (11.1), the JOB_QUEUE_PROCESSES
parameter is changed from a basic to a non-basic initialization
parameter. Most databases only need to have basic parameters set in
order to run properly and efficiently. The default value is also changed
from 0 to 1000.

Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.

This can be really important if you have been in the habit of designating a single node in a cluster to run DBMS_JOB jobs. In 11.1 and earlier releases, you could do this by setting job_queue_processes to 0 on all nodes where you don’t want to run those DBMS_JOB jobs. This was just fine back in, say, 10.2.0.4. But, starting in 11.2 this means that all your DBMS_SCHEDULER jobs will also only run on one node, and if they get queued up on other nodes then they will not run. This includes the parallel slaves created by utlrp.sql to recompile processes after the upgrade.

The solution is to make sure that job_queue_processes is set to a non-zero value on all nodes in the cluster. Voila! The scheduler became unstuck, and jobs will run correctly.

The moral of the story is to make sure you read the Behavior Changes section of the Upgrade Guide! Perhaps this does not rival the morals in Aesop’s fables, but it is one that will be very useful to remember at upgrade time. Happy upgrading!