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 22.214.171.124 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:
Although the text mentions only Windows DB Bundle Patch 126.96.36.199.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.
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.
- “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)
Thanks for sharing. I had the same issue recently and had to resort to a restart.
Thanks for the feedback, Steve.
You might have run into a different scenario, though, if restarting solved it. The bug described above is persistent and can only be solved by installing the missing views.
Hello! Thanks for your sharing. Please prompt where i can get file “catschv.sql” .
it is located where all the “cat*” scripts are: in %ORACLE_HOME% (on Windows).
Shortcut to run it in sqlplus:
Thanks for sharing. It took me all of the day googling and trying various troubleshooting tips before finding your article.
Thanks Sverker, good to know it helped.
Would be even better if it showed up higher at Google ;-)
Thank you for sharing this.