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):
Beginning with Oracle Database 11g Release 1 (11.1), the
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
Starting with Oracle Database 11g Release 2 (11.2), setting
DBMS_JOBjobs to not run. Previously, setting
DBMS_JOBjobs to not run, but
DBMS_SCHEDULERjobs 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.
please run an analyze and see what AU tells you 🙂