1

1. Problem summary

I have a problem with DBMS SCHEDULERs time it is 1 hour late since DST changing 2 weeks ago. If I create a job and set the date & time the startdate is 1 hour later than system time. Everything was working fine before DST change for 2 weeks since then our jobs have a delay of 1 hour. We are running an oracle 10g database with a windows server 2003 OS.

I have seen almost all posts on google regarding this issue and most of them talked about using a Region and Timezone in the startdate would fix the issue but we were already using a Timezone and Region for our Scheduler jobs so that didn't help me.

2. What I have tried

I tried to verify all the timezone settings on the OS and Database level.

The Windows Server 2003 Timezone

Time Zone: (GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna

DBTIMEZONE is set to: +00:00

SESSIONTIMEZONE is set to: +02:00

SCHEDULER DEFAULT_TIMEZONE

select * from dba_scheduler_global_attribute where attribute_name ='DEFAULT_TIMEZONE';

Europe/Berlin

here I checked the time on os and database level

Time on the windows server 2003 OS of the database is correct

C:\Documents and Settings\xx>time The current time is: 13:08:15.71

systimestamp is correct

SYSTIMESTAMP: 11-APR-21 01.09.00.280000 PM +02:00

currenttimestamp is correct

CURRENTTIMESTAMP: 11-APR-21 01.10.13.249000 PM +02:00

DBMS_SCHEDULER.STIME IS NOT CORRECT (1h late)

STIME: 11-APR-2021 12:18:08.358000 PM +01:00 Europe/Berlin

Then I tried to schedule a job like this:

BEGIN DBMS_SCHEDULER.CREATE_JOB (

job_name => 'thoops.testjob',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''THOOPS'', ''GC_BLOBU''); END;',

start_date => TO_DATE('10-APR-2021 01:00','DD-MON-YYYY HH24:MI'),

repeat_interval => 'FREQ=DAILY',

end_date => TO_DATE('30-APR-2021 01:00','DD-MON-YYYY HH24:MI'),

enabled => TRUE,

comments => 'scheduler debug job');

END;

then I did change the start_date like this.

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

name => 'TESTJOB',

attribute => 'start_date',

value => TO_TIMESTAMP ('11.04.2021 14:30:00', 'DD.MM.YYYY HH24:MI:SS.FF') AT TIME ZONE

'Europe/Berlin'); END;

when I check the start_date for my testjob then it's 1hour late:

SELECT start_date, next_run_date FROM dba_scheduler_jobs WHERE JOB_NAME = 'YBATESTJOB';

start_date: 11.04.21 15:30:00.000000000 EUROPE/BERLIN

next_run_date: 11.04.21 15:30:00.000000000 EUROPE/BERLIN

when I change the start_date to systimestamp then it works:

select systimestamp from dual; 11.04.21 14:00:40.577000000 +02:00

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

name => 'TESTJOB',

attribute => 'start_date',

value => SYSTIMESTAMP AT TIME ZONE 'Europe/Berlin');

END;

SELECT start_date, next_run_date FROM dba_scheduler_jobs WHERE JOB_NAME = 'YBATESTJOB';

start_date: 11.04.21 14:01:42.342000000 EUROPE/BERLIN

next_run_date: 12.04.21 14:01:42.300000000 EUROPE/BERLININ

I am really confused and don't know how to fix this

I tried to fix the DBMS scheduler time but I don't know how.. can you please help me out here because I have read though all possible posts with this issue and I couldn't find a solution.

thank you in advance. best regards Sonny

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
Sony Montana
  • 21
  • 1
  • 1
    10g is pretty old. I don't know if there have been any changes to the Berlin time zone since it was released. Have your DBAs applied the most recent time zone files to the database? – Justin Cave Apr 11 '21 at 13:24
  • hi justin i am the DBA of the database and we didnt' change anything in the system since 5 or 6 years and everything was running even in the DST changes last year thats why i am really confused whats going on.. do you have more details about the time zone files you mentioned? – Sony Montana Apr 11 '21 at 13:39
  • 1
    Since time zones change periodically and countries change when DST starts and ends, there are regular time zone updates. You should be able to apply the latest version of the time zone file to your database. https://oracle-base.com/articles/misc/update-database-time-zone-file has some information but I'm not sure that the `dbms_dst` package existed back in the 10g days. The Metalink document where you can download the patches should have installation instructions. – Justin Cave Apr 11 '21 at 13:43
  • yes the dbms_dst package exist only from Oracle 11gR2+ since we are running 10g i can't use that one. Maybe there is a way how to do that manually? – Sony Montana Apr 11 '21 at 14:19
  • I'm sure that the Metalink document where you download the time zone files includes installation instructions for old versions of the database. It's been so long since I've dealt with a 10g database that I'd need to go read the old Metalink document. – Justin Cave Apr 11 '21 at 14:37
  • we dont have access to the metalink repository because our company didnt want to pay for the service.. so i cant check that.. would you mind to check it for me please? – Sony Montana Apr 11 '21 at 14:59
  • If you don't have access to Metalink, I don't know how you'd get the timezone files to apply so I'm not sure that instructions on how to apply them would be very helpful. – Justin Cave Apr 11 '21 at 15:50
  • i see i hope there is another way how i can fix my issue maybe the reason is not in the timezone files.. thanks anyway – Sony Montana Apr 11 '21 at 16:47

0 Answers0