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.

Is your DBMS_SCHEDULER stuck?

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!


Comments:

Post a Comment:
Comments are closed for this entry.
About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers