Setting good execution time of DBMS_SCHEDULER job

Long time ago, one of my colleagues complained about some missing data in replication from Oracle to MS SQL Server database. Replication was done daily and initiated within Oracle database with DBMS_SCHEDULER package. Source data was created within manually initiated procedure, so the time was always approximately the same.
Time of the complaint was in the week after change of daylight time, so the first place to look was the execution time of DB job. Of course, execution time was shifted for exactly one hour and replication was having only partial data.

To cut a long story, the problem was when creating DBMS_SCHEDULER job. Solution and the right way is to create the scheduler job with TZR in start time.

Example of altering the job to follow your daylight saving time is:

BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    (name => 'REPLICATION.REPLICATION_JOB'
     , attribute => 'START_DATE'
     , value => TO_TIMESTAMP_TZ('2013/10/31 04:00 Europe/Zagreb','yyyy/mm/dd hh24:mi tzr'));
END;
/

I hope this will help someone and remind me when I'll create DB job next time :)

Kresimir Skoda

Read more posts by this author.

Subscribe to Kresimir's web place

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!