DBMS_SCHEDULER Jobs not running on 12.2 (Windows)

4605619232_03d6aa0776

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.

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

 

 

Advertisements

2 thoughts on “DBMS_SCHEDULER Jobs not running on 12.2 (Windows)

    1. Uwe M. Küchler Post author

      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.
      Cheers, Uwe

      Like

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.