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
from0
to1000
.Starting with Oracle Database 11g Release 2 (11.2), setting
JOB_QUEUE_PROCESSES
to0
causes bothDBMS_SCHEDULER
andDBMS_JOB
jobs to not run. Previously, settingJOB_QUEUE_PROCESSES
to0
causedDBMS_JOB
jobs to not run, butDBMS_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!
Does AutoUpgrade requires job_queue_processes set to a non-zero value though? We want to set it to zero so scheduled dba_jobs and scheduler jobs doesn’t run. Some of the jobs has caused blocking sessions when we does a RU patching, we assume a similar thing might happen when using AutoUpgrade.
Edwin,
please run an analyze and see what AU tells you š
Cheers,
Mike