Tuesday 14 June 2016

Scheduler Job stuck in the past

I am in the process of implementing backup log emails across the production environment so I can track production backups on a daily basis. Currently there isn't a daily check or monitoring scheme in place. This is not great if datablock corruption creeps into the database. The datablocks will be be harder to recover the longer the corruption remains undetected and RMAN helps identify corruption by reporting corruption during the backup feedback.

While implementing backup monitoring scripts, I came across an issue with one of the production servers. The database which fell over in April (see Database Fever) has not had a backup since the the 9th of June. The NEXT_RUN_DATE is in the past. This usually occurs when the server administrators time travel the Windows server.

This is a simple problem to fix, but it can be irritating if there are many jobs that are broken.

To force a new date, I execute the following adjustment: DBMS_SCHEDULER.SET_ATTRIBUTE. Original code was found on OTN, but I have adjusted here and there for my own needs.
I want the backup job to run at 8PM tonight - so I tell the job to run @ sysdate || 8pm.

DECLARE

v_new_next_date TIMESTAMP WITH TIME ZONE;

BEGIN

  SELECT TO_TIMESTAMP_TZ(TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY')||' 08:00:00 PM +00:00') 
    INTO v_new_next_date
    FROM dba_scheduler_jobs
   WHERE job_name = 'RMAN_BACKUP';
  
  DBMS_SCHEDULER.SET_ATTRIBUTE(NAME=>'RMAN_BACKUP', ATTRIBUTE=>'START_DATE',VALUE=>v_new_next_date);

END;
/

All done.

No comments:

Post a Comment