Backdated Oracle Scheduler

Oracle scheduler is Oracle's job queue framework for scheduling various jobs. With scheduler, we can even schedule the jobs outside the scope of the database, like running a perl or shell script. While creating the job, we define the job's run interval and as per that only the job runs. We can query DBA_SCHEDULER_JOBS view to see all the jobs in system and their corresponding schedules. Here is a sample output of the same from my server:

 SQL> select JOB_NAME, STATE, LAST_START_DATE, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS where OWNER <> 'SYS';  
 JOB_NAME                     STATE         LAST_START_DATE                         NEXT_RUN_DATE  
 ---------------------------------------- --------------- ---------------------------------------- ----------------------------------------  
 ORA_ASM_REPORT               SCHEDULED    21-FEB-15 01.00.00.984664 AM US/CENTRAL 22-FEB-15 01.00.00.000000 AM US/CENTRAL  
 ORA_BACKUP_DAILY             SCHEDULED    21-FEB-15 02.15.00.639829 AM US/CENTRAL 22-FEB-15 03.30.00.700000 AM US/CENTRAL  
 SQL> select SYSDATE from dual;  
 SYSDATE  
 ---------  
 26-FEB-15  

The DBA_SCHEDULER_JOBS.LAST_START_DATE stores the date information related to the last run of that job and similarly DBA_SCHEDULER_JOBS.NEXT_RUN_DATE stores the date for the next scheduled run of that job. As you can see in above example that the current date is Feb-26-14 and the NEXT_RUN_DATE is set to Feb-22-14, means these job's next run date is in past and these will never run if we don't fix that.

This scenario can happen mainly if the actual server was in long maintenance and it missed the actual scheduled NEXT_RUN_DATE because of that and next time when we start the server, it still keep waiting for that old date only. There is no inbuilt trigger or intellegence in database to find out that the NEXT_RUN_DATE has already been past and it needs to be reset.

Now, in order to reset it manually, we need to first disable the job queue completely at the system level:

 SQL> show parameter job_queue_processes  
 NAME                 TYPE    VALUE  
 ------------------------------------ ----------- ------------------------------  
 job_queue_processes         integer   100  
 SQL> alter system set job_queue_processes=0;  
 System altered.  

Now we can set the job_queue_processes back to 100 to enable it and it will reset the date as well but the key here is that it will kick off all the jobs immediately regardless of it's actual timings. If you are doing this in the middle of day then you really don't want to kick off those jobs in business hours. So in order to avoid that, next we need to disable the jobs manually:

 SQL> exec dbms_scheduler.disable('ORA_ASM_REPORT');  
 PL/SQL procedure successfully completed.  
 SQL> exec dbms_scheduler.disable('ORA_BACKUP_DAILY');  
 PL/SQL procedure successfully completed.  

And now, we can set the job_queue_processes back to it's original value and since the jobs are disabled, it won't kick off immediately:

 SQL> alter system set job_queue_processes=100;  
 System altered.  
 SQL> show parameter job_queue_processes  
 NAME                 TYPE    VALUE  
 ------------------------------------ ----------- ------------------------------  
 job_queue_processes         integer   100  

And finally we can enable the jobs as well and that should reset the NEXT_RUN_DATE as per the defined scheduled of the job without running them immediately:

 SQL> exec dbms_scheduler.enable('ORA_ASM_REPORT');  
 PL/SQL procedure successfully completed.  
 SQL> exec dbms_scheduler.enable('ORA_BACKUP_DAILY');  
 PL/SQL procedure successfully completed.  

Now let's query the DBA_SCHEDULER_JOBS view again to check the schedule:

 SQL> select JOB_NAME, STATE, LAST_START_DATE, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS where OWNER <> 'SYS';  
 JOB_NAME                      STATE        LAST_START_DATE                         NEXT_RUN_DATE  
 ---------------------------------------- --------------- ---------------------------------------- ----------------------------------------  
 ORA_ASM_REPORT               SCHEDULED    21-FEB-15 01.00.00.984664 AM US/CENTRAL 26-FEB-15 01.00.00.000000 AM US/CENTRAL  
 ORA_BACKUP_DAILY             SCHEDULED    21-FEB-15 02.15.00.639829 AM US/CENTRAL 26-FEB-15 03.30.00.700000 AM US/CENTRAL  

And we have everything back on track once again!!


Thanks
Daljit Singh