Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> daylight savings issue with dbms_scheduler, jobs are running 1 hour after/before during DST switch
sishyadba
post Apr 23 2012, 10:04 AM
Post #1


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



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






Go to the top of the page
 
+Quote Post
burleson
post Apr 23 2012, 11:14 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,607
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
sishyadba
post Apr 23 2012, 12:40 PM
Post #3


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



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
Go to the top of the page
 
+Quote Post
vishalb jayce
post Apr 25 2012, 05:14 AM
Post #4


Member
**

Group: Members
Posts: 29
Joined: 20-April 12
From: Hyderabad India
Member No.: 47,109



QUOTE (sishyadba @ Apr 23 2012, 01:40 PM) *
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
Go to the top of the page
 
+Quote Post
sishyadba
post Apr 25 2012, 12:34 PM
Post #5


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



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
Go to the top of the page
 
+Quote Post
vishalb jayce
post May 15 2012, 04:21 AM
Post #6


Member
**

Group: Members
Posts: 29
Joined: 20-April 12
From: Hyderabad India
Member No.: 47,109



QUOTE (sishyadba @ Apr 25 2012, 01:34 PM) *
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
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 21st October 2014 - 08:28 AM