where does sysdate and time come from

[bookouri]

I guess this problem has something to do with the DST mess. But, on one of my production 9i databases, the machine date/time is correct. The date/time in Oracle Enterprise manager on that machine is one hour behind. If I do a sysdate from dual on that machine, the hour is correct but the minutes are 28 minutes off from the machine time. Where is oracle getting its date/time information from? How can I get oracle synced up with the machine date/time?

thanks for any info

 

[Scunningham99]

time/date is from system/// which os are ypu using?

Have you tried bouncing the database as I know there is a bug with dst if getting date using time_stamp.

rgds


Sy UK

 

[bookouri]

yep, we bounced the db, rebooted the server (win2003) and the OS date/time is correct. The oracle sysdate is still off 1 hour. Timestamp is not off now.

 

[DrD123]

Hi bookouri,

Have you applied the Oracle TZ patches that were released? If not the time might be off for another couple of weeks, and you'll run into the same issue on the other end of DST.

If it's 9.2.0.6 you just need to copy the TZ files from the patch and restart the dB's, if it's 9.2.0.8, you apply the whole patch.

Good luck,
DrD

 

[bookouri]

No, i thought oracle would get the date/time from the OS. I figured if the date/time came from the OS and it was correct on the OS then oracle wouldnt be doing anything funky with it. I did do a search for patches at one time though and didnt turn up anything that seemed to be related to the time change problem.

ill take another look

 

[DrD123]

Hi bookouri,

You most definitely need to patch Oracle. It uses the system clock for the time, but does some translations with the timezone from the timezone files.

Also if you're using Java in the dB, you'll need to update that as well.

I'm not too sure the Oracle patch # for Windows, sorry.

You can check your dB's to see if by chance they are or are not patched with the following query:

select case to_number(to_char(to_timestamp_tz
('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
+ to_dsinterval('0 08:00:00'),'HH24'))
when 8 then 'The patch has not been applied'
when 9 then 'The patch has been applied correctly'
else 'Error' end "TZTEST (RUN FROM DB HOME)"
from dual;

Good luck,
DrD