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:
BEGIN
FOR JOB IN (SELECT * FROM USER_SCHEDULER_JOBS) LOOP
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => JOB.JOB_NAME, FORCE => TRUE);
END LOOP;
FOR PROG in (select * from USER_SCHEDULER_PROGRAMS) LOOP
DBMS_SCHEDULER.DROP_PROGRAM(PROGRAM_NAME => PROG.PROGRAM_NAME, FORCE => TRUE);
END LOOP;
END;
/
All the Oracle internal maintenance jobs and programs like the AUTO_SPACE_ADVISOR_PROG, GATHER_STATS_PROG, FILE_WATCHER_PROGRAM are deleted.
SQL> SELECT owner,job_name 2 FROM dba_scheduler_jobs 3 ORDER by job_name; no rows selected
SQL> SELECT owner,program_name 2 FROM dba_scheduler_programs 3 ORDER by program_name; no rows selected
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
SQL> shutdown immediate SQL> startup upgrade
3. Run these Commands as SYSDBA in Order to recreate the missing Scheduler Jobs
SQL> spool fix_cat_logfile.txt SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql SQL> @?/rdbms/admin/utlrp.sql SQL> @?/rdbms/admin/utlrp.sql SQL> @?/rdbms/admin/utlrp.sql SQL> spool off
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.
DECLARE
c number;
BEGIN
select count(*) into c
from ALL_SCHEDULER_JOB_CLASSES
where JOB_CLASS_NAME = 'XMLDB_NFS_JOBCLASS';
if c = 0 then
dbms_scheduler.create_job_class(
job_class_name => 'SYS.XMLDB_NFS_JOBCLASS',
logging_level => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
end if;
select count(*) into c
from ALL_SCHEDULER_JOBS
where JOB_NAME = 'XMLDB_NFS_CLEANUP_JOB';
if c = 0 then
dbms_scheduler.create_job(
job_name => 'SYS.XMLDB_NFS_CLEANUP_JOB' ,
job_type=>'STORED_PROCEDURE',
job_action=>'xdb.dbms_xdbutil_int.cleanup_expired_nfsclients',
job_class=>'SYS.XMLDB_NFS_JOBCLASS',
repeat_interval=>'Freq=minutely;interval=5');
end if;
execute immediate 'delete from noexp$ where name = :1' using 'XMLDB_NFS_JOBCLASS';
execute immediate 'insert into noexp$ (owner, name, obj_type) values(:1, :2, :3)' using 'SYS', 'XMLDB_NFS_JOBCLASS', '68';
end;
/
4. Restart the Database
SQL> shutdown immediate SQL> startup
5. Run these Queries to verify invalid Objects
SQL> SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS ---------------------------------------- ------------------------------ ------------ Oracle XML Database 12.1.0.2.0 VALID Oracle Database Catalog Views 12.1.0.2.0 VALID Oracle Database Packages and Types 12.1.0.2.0 VALID 3 rows selected. SQL> SELECT substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type 2 FROM dba_objects 3 WHERE status='INVALID' 4 ORDER BY owner, object_type; no rows selected
6. Verify that DBMS_SCHEDULER Jobs are back
SQL> SELECT owner,job_name 2 FROM dba_scheduler_jobs 3 ORDER BY job_name; OWNER JOB_NAME ---------------------- ------------------------------ SYS BSLN_MAINTAIN_STATS_JOB SYS CLEANUP_NON_EXIST_OBJ SYS CLEANUP_ONLINE_IND_BUILD SYS CLEANUP_ONLINE_PMO SYS CLEANUP_TAB_IOT_PMO SYS CLEANUP_TRANSIENT_PKG SYS CLEANUP_TRANSIENT_TYPE SYS DRA_REEVALUATE_OPEN_FAILURES SYS FGR$AUTOPURGE_JOB SYS FILE_SIZE_UPD SYS FILE_WATCHER SYS HM_CREATE_OFFLINE_DICTIONARY SYS LOAD_OPATCH_INVENTORY SYS ORA$AUTOTASK_CLEAN SYS PMO_DEFERRED_GIDX_MAINT_JOB SYS PURGE_LOG SYS RSE$CLEAN_RECOVERABLE_SCRIPT SYS SM$CLEAN_AUTO_SPLIT_MERGE SYS XMLDB_NFS_CLEANUP_JOB 19 rows selected.
7. Verify DBMS_SCHEDULER Programs are back
SQL> SELECT owner,program_name 2 FROM dba_scheduler_programs 3 ORDER by program_name; OWNER PROGRAM_NAME ------------------------- ------------------------ SYS AQ$_PROPAGATION_PROGRAM SYS AUTO_SPACE_ADVISOR_PROG SYS AUTO_SQL_TUNING_PROG SYS BSLN_MAINTAIN_STATS_PROG SYS FILE_WATCHER_PROGRAM SYS GATHER_STATS_PROG SYS HS_PARALLEL_SAMPLING SYS ORA$AGE_AUTOTASK_DATA SYS PMO_DEFERRED_GIDX_MAINT SYS PURGE_LOG_PROG 10 rows selected.
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.