Source: canned muffins. CC-BY-2.0

Just a short heads-up for those who might run into similar issues and having a hard time finding the root cause:

I just ran into a bug on a Windows 12.2.0.1 DB with the 190416 Bundle Patch: A newly scheduled job just wouldn’t run, and a query on DBA_SCHEDULER_JOBS revealed that other jobs (even Oracle’s own jobs) had not run in a while.

Running the job manually worked. Only when it was scheduled it just wouldn’t start.

There is an old but still relevant post in the Oracle Community, giving good advice on troubleshooting common scheduler issues: Answers to “Why are my jobs not running?”. I checked back with that but already had tried all recommended steps anyway. There was not entry in the Alert Log nor a trace file from the scheduler that would give a hint to the cause of the issue.

What led me to the right search terms was when I tried to run the job in a background session:

SQL> exec dbms_scheduler.run_job('perfstat.statspack_snap', USE_CURRENT_SESSION => FALSE)
BEGIN dbms_scheduler.run_job('perfstat.statspack_snap', USE_CURRENT_SESSION => FALSE); END;

*
ERROR at line 1:
ORA-02800: Requests timed out
ORA-06512: at "SYS.DBMS_ISCHED", line 238
ORA-06512: at "SYS.DBMS_SCHEDULER", line 568
ORA-06512: at line 1

This led me to MOS Doc ID 2465102.1 “Oracle Scheduler Does Not Execute Any Jobs After Applying Windows Database Bundle Patch”. Due to a bug, new views aren’t created:

• sys.scheduler$_job_refresh
• sys.scheduler$_lwjob_refresh
• sys.scheduler$_window_refresh

Although the text mentions only Windows DB Bundle Patch 12.2.0.1.181130 to be affected by this bug, the solution was applicable to our more recent BP as well.

Running “catschv.sql” as SYS solved the issue immediately.

SQL> @?/rdbms/admin/catschv.sql

So, if you observe a similar behaviour, check if the above views are missing and install them with “catschv.sql”. Just be aware that all due jobs will run immediately, then.

In any other case, refer to the links below.

Weblinks

•  “Why are my jobs not running?” (Oracle Community)
• Master Note: Troubleshooting Oracle Scheduler (Doc ID 1520580.1) (public)
• DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1) (Login required)