Hi all,
Some time ago a client asked me to check why a Job started running in a different time. This was a week after the DST change. Easy guess, right?
The job was scheduled using timezone offset (PM-04:00) instead of timezone region. Considering the DST change, the offset changed as well (-5:00), but not the job schedule.
To fix the job time definitely, I changed schedule to use timezone region that database is in. Check below the investigation and solution:
SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB'; OWNER JOB_NAME REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE ---------- ---------- ----------------- ----------------------------------- -------------------- GREPORA MYJOB FREQ=DAILY; 08-NOV-17 09.00.00.330739 PM -04:00 08-NOV-17 09.00.00.300000 PM -04:00 SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'DEFAULT_TIMEZONE'; ATTRIBUTE_NAME VALUE ----------------- --- DEFAULT_TIMEZONE EST5EDT SQL> SELECT tzabbrev, TZ_OFFSET(tzname), tzname FROM V$TIMEZONE_NAMES WHERE tzname IN ('EST', 'EDT') OR tzabbrev IN ('EST', 'EDT') ORDER BY 1,2; TZABBREV TZ_OFFS TZNAME --------- ------- --------------------------- EDT -04:00 America/Santo_Domingo EDT -05:00 America/Fort_Wayne EDT -05:00 America/Grand_Turk EDT -05:00 America/Indiana/Indianapolis EDT -05:00 America/Indiana/Marengo EDT -05:00 America/Indiana/Petersburg EDT -05:00 US/Michigan EDT -05:00 America/Detroit EDT -05:00 US/Eastern EDT -05:00 US/East-Indiana EDT -05:00 Jamaica EDT -05:00 EST5EDT EDT -05:00 Canada/Eastern EDT -05:00 America/Toronto EDT -05:00 America/Thunder_Bay EDT -05:00 America/Port-au-Prince EDT -05:00 America/Pangnirtung EDT -05:00 America/Nipigon EDT -05:00 America/New_York EDT -05:00 America/Nassau EDT -05:00 America/Montreal EDT -05:00 America/Louisville EDT -05:00 America/Kentucky/Monticello EDT -05:00 America/Kentucky/Louisville EDT -05:00 America/Jamaica EDT -05:00 America/Iqaluit EDT -05:00 America/Indiana/Vevay EDT -05:00 America/Indiana/Vincennes EDT -05:00 America/Indiana/Winamac EDT -05:00 America/Indianapolis EDT -06:00 America/Indiana/Tell_City EDT -06:00 America/Cancun EST +10:00 Australia/Queensland EST +10:00 Australia/Lindeman EST +10:00 Australia/Brisbane EST +10:30 Australia/Broken_Hill EST +10:30 Australia/Yancowinna EST +11:00 Australia/Currie EST +11:00 Australia/Canberra EST +11:00 Australia/ACT EST +11:00 Antarctica/Macquarie EST +11:00 Australia/Hobart EST +11:00 Australia/LHI EST +11:00 Australia/Lord_Howe EST +11:00 Australia/Melbourne EST +11:00 Australia/NSW EST +11:00 Australia/Sydney EST +11:00 Australia/Tasmania EST +11:00 Australia/Victoria EST -04:00 America/Antigua EST -04:00 America/Moncton EST -04:00 America/Santo_Domingo EST -05:00 US/Eastern EST -05:00 America/Grand_Turk EST -05:00 America/Atikokan EST -05:00 Jamaica EST -05:00 EST5EDT EST -05:00 EST EST -05:00 America/Indiana/Indianapolis EST -05:00 Canada/Eastern EST -05:00 America/Toronto EST -05:00 America/Thunder_Bay EST -05:00 America/Resolute EST -05:00 America/Indiana/Marengo EST -05:00 America/Indiana/Petersburg EST -05:00 America/Cayman EST -05:00 America/Indiana/Vevay EST -05:00 America/Indiana/Vincennes EST -05:00 America/Indiana/Winamac EST -05:00 America/Indianapolis EST -05:00 America/Iqaluit EST -05:00 America/Jamaica EST -05:00 America/Kentucky/Louisville EST -05:00 America/Kentucky/Monticello EST -05:00 US/Michigan EST -05:00 America/Louisville EST -05:00 America/Coral_Harbour EST -05:00 America/Detroit EST -05:00 America/Fort_Wayne EST -05:00 America/Montreal EST -05:00 America/Nassau EST -05:00 America/New_York EST -05:00 America/Nipigon EST -05:00 America/Panama EST -05:00 America/Pangnirtung EST -05:00 America/Port-au-Prince EST -05:00 US/East-Indiana EST -06:00 US/Central EST -06:00 CST EST -06:00 America/Rankin_Inlet EST -06:00 America/Merida EST -06:00 America/Menominee EST -06:00 America/Managua EST -06:00 America/Knox_IN EST -06:00 America/Indiana/Tell_City EST -06:00 America/Indiana/Knox EST -06:00 America/Chicago EST -06:00 America/Cancun EST -06:00 US/Indiana-Starke EST -07:00 America/Cambridge_Bay 100 rows selected. SQL> BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'GREPORA.MYJOB', attribute => 'start_date', value => TO_TIMESTAMP_TZ('09-11-2017 21:00:00 EST5EDT' ,'DD-MM-YYYY HH24:MI:SS TZR')); END; / PL/SQL procedure successfully completed. SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB'; OWNER JOB_NAME REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE ---------- ---------- ----------------- ------------------------------- -------------------- GREPORA MYJOB FREQ=DAILY; 08-NOV-17 09.00.00.3 09-NOV-17 09-NOV-17 09.00.00.0
Hope this helps you!
Cheers!