Oracle Scheduler Job is Not Running as Expected After Bouncing Databases

One of our clients had issues with their scheduler jobs in DEV and PROD databases and all scheduler jobs stopped running after July 31st, 2021. Both databases were 19c and patched with July 2021 19.12 RU and the parameter job_queue_processes was set to 4000.

The solution

Here is how I checked the patch inventory and the parameter job_queue_processes of the database:

[oracle@server ~]$ $ORACLE_HOME/OPatch/opatch lspatches
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)

OPatch succeeded.

SQL> show parameter job_queue_processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 4000

So I created a test scheduler job and scheduled it to run every 2 minutes, but that didn’t work either:

SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'My_Count_Job',
4 job_type => 'STORED_PROCEDURE',
5 job_action => 'count_comp',
6 start_date => SYSTIMESTAMP,
7 repeat_interval => 'FREQ=MINUTELY; INTERVAL=2',
8 auto_drop => FALSE,
9 enabled =>TRUE,
10 comments => 'My new job');
11 END;
12 /

PL/SQL procedure successfully completed.

SQL> SET LINESIZE 1000
SQL> COLUMN OWNER FORMAT A20
SQL> COLUMN SCHEDULE_NAME FORMAT A20
SQL> COLUMN REPEAT_INTERVAL FORMAT A50
SQL> COLUMN START_DATE FORMAT A45
SQL> COLUMN LAST_START_DATE FORMAT A45
SQL> COLUMN END_DATE FORMAT A45
SQL> COLUMN JOB_NAME FORMAT a40
SQL> SELECT OWNER, JOB_NAME, SCHEDULE_NAME, START_DATE, LAST_START_DATE, REPEAT_INTERVAL, ENABLED FROM DBA_SCHEDULER_JOBS where job_name='MY_COUNT_JOB';
OWNER JOB_NAME SCHEDULE_NAME START_DATE LAST_START_DATE NEXT_RUN_DATE REPEAT_INTERVAL ENABL
-------------------- ---------------------------------------- -------------------- --------------------------------------------- --------------------------------------------- --------------------------------------------------------------------------- -------------------------------------------------- -----
SYS MY_COUNT_JOB 03-AUG-21 04.08.34.000000 PM -06:00 03-AUG-21 04.08.34.000000 PM -06:00 FREQ=MINUTELY; INTERVAL=2 TRUE

After reviewing the alert log, I found that the CJQ process was not running since the last database bounce:

[oracle@server ~]$ ps -ef | grep -i cjq 
oracle 63164 49635 0 11:39 pts/1 00:00:00 grep --color=auto -i cjq

Now that I had found the root cause, I needed to force a CJQ restart using the DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE package. I did this  by disabling the Oracle Scheduler and the transitional DBA_JOBs and then re-enabling both. 

Then, according to Oracle support, DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘SCHEDULER_DISABLED’, … ) is currently undocumented and thus is not a supported scheduler attribute.

Here’s how I fixed the issue:

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');

PL/SQL procedure successfully completed.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> exec dbms_ijob.set_enabled(FALSE);

PL/SQL procedure successfully completed.

SQL> exec dbms_ijob.set_enabled(TRUE);

PL/SQL procedure successfully completed.

SQL> alter system set job_queue_processes=4000;

System altered.

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

PL/SQL procedure successfully completed.

After I restarted the CJQ process, all scheduler jobs ran as expected. Here is my test job run log:

SQL> SELECT OWNER, JOB_NAME, SCHEDULE_NAME, START_DATE, LAST_START_DATE, NEXT_RUN_DATE, REPEAT_INTERVAL, ENABLED FROM DBA_SCHEDULER_JOBS where job_name='MY_COUNT_JOB';

OWNER JOB_NAME SCHEDULE_NAME START_DATE LAST_START_DATE NEXT_RUN_DATE REPEAT_INTERVAL ENABL
-------------------- ---------------------------------------- -------------------- --------------------------------------------- --------------------------------------------- --------------------------------------------------------------------------- -------------------------------------------------- -----
SYS MY_COUNT_JOB 04-AUG-21 03.38.40.000000 PM -06:00 04-AUG-21 03.40.40.209729 PM -06:00 04-AUG-21 03.42.40.000000 PM -06:00 FREQ=MINUTELY; INTERVAL=2 TRUE

SQL> SELECT LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS FROM DBA_SCHEDULER_JOB_LOG WHERE OWNER='SYS' and JOB_NAME='MY_COUNT_JOB';

LOG_ID LOG_DATE OWNER JOB_NAME STATUS
---------- --------------------------------------------------------------------------- -------------------- ---------------------------------------- ------------------------------
2210610 03-AUG-21 11.50.16.554309 AM -06:00 SYS MY_COUNT_JOB SUCCEEDED
2210710 04-AUG-21 03.34.34.059233 PM -06:00 SYS MY_COUNT_JOB SUCCEEDED
2210718 04-AUG-21 03.38.40.251223 PM -06:00 SYS MY_COUNT_JOB SUCCEEDED
2210726 04-AUG-21 03.40.40.256684 PM -06:00 SYS MY_COUNT_JOB SUCCEEDED
2210700 04-AUG-21 03.33.15.840301 PM -06:00 SYS MY_COUNT_JOB SUCCEEDED

I hope this is useful. Feel free to post  any questions in the comments. And don’t forget to sign up for more updates here.