daylight savings issue with dbms_scheduler, jobs are running 1 hour after/before during DST switch Options

[sishyadba]

Hi All,


Hope everyone is doing good. During the daylight savings time our scheduler jobs are running either an hour before/after depending upon the time switch.

I went through the Oracle documentation and found below suggestions which I have already tried in vain.

Document says scheduler first picks the timezone from the start date of the job if provided so i tried setting the
start date using the TO_TIMESTAMP_TZ('2012/01/22 18:50:00 US/Eastern','yyyy/mm/dd hh24:mi:ss tzr')
which did not help me fix the problem. I have noticed that oracle automatically converted into tzh:tzm format.

second solution
setting default timezone of scheduler to the TZR i.e (US/Eastern) instead of the TZh:TZM value. I did that using below script
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute (
attribute => 'default_timezone',
value => 'US/Eastern');
END;


above 2 solutions did not work for me. I have read on internet from some article that below query should return something like
"4/23/2012 11:02:13 US/Eastern" after setting the default timezone of scheduler to TZR but I am still getting "4/23/2012 11:02:13.715816000 AM -04:00".

select dbms_scheduler.stime
from dual;


I would really appreciate your help if you can help me with that.

Thanks to you all in advance.

Sri

[burleson]

Hi Sri,

Please read:

http://www.dba-oracle.com/t_oracle_dayligh..._conversion.htm


Does this patch apply to you?

http://www.dba-oracle.com/oracle_news/news...7_dst_patch.htm

[sishyadba]

Hi Don,

Thanks a lot for your reply. I read your article.
I think the issue we have is not related to the timezone file as the jobs are running at wrong time (1 hour earlier /after) until we drop them and recreate them accordingly.

And dba's also confirmed that we are up to date with the patches. We are on 11g R2 at present.

It' still a mystery for all of us. Looks like I will have to do more research on this issue.

Thanks again for giving me more info.

Sri

[vishalb jayce]

Hi Don,

Thanks a lot for your reply. I read your article.
I think the issue we have is not related to the timezone file as the jobs are running at wrong time (1 hour earlier /after) until we drop them and recreate them accordingly.

And dba's also confirmed that we are up to date with the patches. We are on 11g R2 at present.

It' still a mystery for all of us. Looks like I will have to do more research on this issue.

Thanks again for giving me more info.

Sri

Hi Sri,

Can i know which version of oracle are you using 11.2.0.2 or 11.2.0.3 ?

iam working on oracle 11.2.0.3 but for me it looks fine....

FYI...


SQL> select dbms_scheduler.stime
2 from dual;

STIME
-----------------------------------------------------------
25-APR-12 03.13.01.570000000 AM US/PACIFIC

SQL> BEGIN
2 DBMS_SCHEDULER.set_scheduler_attribute (
3 attribute => 'default_timezone',
4 value => 'US/Eastern');
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> select dbms_scheduler.stime
2 from dual;

STIME
-----------------------------------------------------------
25-APR-12 06.13.50.432000000 AM US/EASTERN

SQL>


-vishal

[sishyadba]

Hi Vishal,


Thanks for looking at the post. My oracle version is 11.2.0.1 .

I have tried running the same query multiple times and multiple ways but still getting the same issue with the DST.
It returns correct on our production which is 10.2.0.3.

Sri

[vishalb jayce]

Hi Vishal,


Thanks for looking at the post. My oracle version is 11.2.0.1 .

I have tried running the same query multiple times and multiple ways but still getting the same issue with the DST.
It returns correct on our production which is 10.2.0.3.

Sri

Hi Sri,

i have tried in oracle 11.2.0.1, please have look at it...

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 15 14:47:49 2012

Copyright © 1982, 2010, Oracle. All rights reserved.

Enter user-name: system
Enter password:
ERROR:
ORA-28002: the password will expire within 7 days



Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production


SQL> select dbms_scheduler.stime from dual;

STIME
----------------------------------------------------------
15-MAY-12 02.49.52.959000000 PM ASIA/CALCUTTA

SQL> BEGIN
2 DBMS_SCHEDULER.set_scheduler_attribute (
3 attribute => 'default_timezone',
4 value => 'US/Eastern');
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> select dbms_scheduler.stime from dual;

STIME
----------------------------------------------------------
15-MAY-12 05.20.05.897000000 AM US/EASTERN

SQL>

Can you please check it in any other environment........

-vishal