Houston – we have deleted all SYS DBMS_SCHEDULER Jobs !

Bad, bad PL/SQL….

A customer of me has executed a simple script to cleanup DBMS_SCHEDULER jobs and programs for an application. Unfortunately he did as user SYS. The content of the SQL script:

All the Oracle internal maintenance jobs and programs like the AUTO_SPACE_ADVISOR_PROG, GATHER_STATS_PROG, FILE_WATCHER_PROGRAM are deleted.

On a 12.1.0.2 single instance database on Linux are per default 19 DBA_SCHEDULER_JOBS and 10 DBA_SCHEDULER_PROGRAMS pre-configured.

Oracle has a MOS note called  How to Rebuild DBMS_SCHEDULER Default Jobs and Autotasks (Doc ID 2089546.1) – but this note is old and incomplete. It does not help to rebuild all the jobs and programs in a 12.1.0.2 database.

After some discussions with My Oracle Support, this way here works to rebuild the jobs. Unfortunately it’s an “offline” task, you have to restart your database in UPGRADE mode. But it works.

1. Make sure that you have a Backup of your Database

2. Restart the Database in UPGRADE Mode

3. Run these Commands as SYSDBA in Order to recreate the missing Scheduler Jobs

All jobs except XMLDB_NFS_CLEANUP_JOB are re-created now. To rebuild the XMLDB_NFS_CLEANUP_JOB you can execute this command as user SYS in SQL*Plus. It’s just an extract from the file ?/rdbms/admin/xdbu102.sql which creates the required job during the regular instance creation process.

4. Restart the Database

5. Run these Queries to verify invalid Objects

6. Verify that DBMS_SCHEDULER Jobs are back

7. Verify DBMS_SCHEDULER Programs are back

Summary

Deleting the SYS DBMS_SCHEDULER jobs is one of these mistakes which can be easily repaired. But during the rebuild action, the database is not available for the end users and this is bad. I’m looking forward that there will be an online solution available maybe in the future.

Another approach could be to execute a FLASHBACK QUERY against the dba_source view to get the DDL for the missing jobs and programs before the delete action. But this has to be tested first.