Handling Tricky Oracle Job Scheduling Problem

Oracle has made the life of database administrators easy by offering job queue that can be used for scheduling the operations that will be performed routinely in your Oracle databases. Oracle DBMS_JOB package is used for scheduling the Oracle jobs.
Basically Oracle starts a coordinator job queue (CJQ0) process to run scheduled jobs. Oracle helper processes (J000 – J999) executes the scheduled jobs. The CJQ0 process will be keeping track of the schedule and starts helper processes so as to execute the scheduled jobs.
Being database administrator you must have faced the situation where you submit a job in DBA_JOBS and specify some interval for it. For most of us it works fine but sometimes the tricky Oracle Jobs in DBA_JOBS package does not run as we scheduled them.
The situation becomes quite frustrating when you schedule a job for say X minutes but it does not run every X minutes. However when you perform manual job runs with DBMS_JOB.RUN then it runs successfully according to scheduled time interval. In this article we will discuss how to handle this tricky situation and make your job run every X minutes.
First of all you have to ensure that the job_queue_process is not set to zero as it will disable all job queue processing and stop the CJQ0 process. You need to look for the number of jobs in your instance and ensure that your database is set up to process jobs. Below SQL command checks for job_queue_processes initialization parameter.
SHOW PARAMETER JOB_QUEUE_PROCESSES
It is advised to set job_queue_processes parameter to a value higher than the maximum number of simultaneous jobs you expect to run. Suppose you have number of jobs set to 10. You have a number of jobs running and when some more jobs are added then it gets stuck. In such scenarios you should increase the number of jobs to 20. Headroom is important because some jobs may require more than one process.
If your coordinator job queue CJQ0 process is responsible for not running scheduled jobs per schedule then you can use below approach to solve this problem. Suppose CJQ0 initiates process J001 for executing scheduled jobs but it hangs and does not initiate any J001 process. As a result your job is not able to run every minute.
Kill Coordinator Job Queue:
First of all you will have to kill Coordinator Job Queue process CJQ0 from the operating system. If your Oracle database is running in Windows then Task Manager will not show the Oracle processes. In such scenario you will have to log in to your Oracle database and find SID and SPID values by running below command.
SELECT A1.SID, A2.SPID FROM V$SESSION A1,V$PROCESS A2 WHERE A1.PADDR = A2.ADDR AND TYPE='BACKGROUND' AND A1.PROGRAM LIKE '%CJQ%'
Now you will have to substitute the SID and SPID values from above step into below command and run it in %ORACLE_HOME%\bin
ORAKILL mySID mySPID
Set job_queue_process:
Now you will have to set job_queue_process initialization parameter to the value of zero.
ALTER SYSTEM SET JOB_QUEUE_PROCESS=0;
Set job_queue_process :
Again you will have to set job_queue_process initialization parameter to the value of 20 or whatever value you want to set based on the maximum number of simultaneous jobs you expect to run.
ALTER SYSTEM SET JOB_QUEUE_PROCESS=20;
Fork new process:
FORK() is used to create the process. Finally you need to fork a new CJQ0 process and all J001 process for running jobs.