Hello, we run our DB (oracle 11gR2) in a RAC-Environment with Linux OS. It seems, the DBA from our Hosting provider sets up the DBTIMEZONE with UTC on purpose . But we are in German and have the time zone "Europe/Berlin". if we run the following script SELECT DBTIMEZONE, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), SESSIONTIMEZONE, to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; we get the sysdate with UTC Time and this is two hours later then our local time. DBTIMEZONE : UTC SYSDATE: 2012-05-29 09:24:58 SessionTimeZone: Europe/Berlin Local Timestamp: 2012-05-29 11:24:58 this is very annoying. Specially if we run selects with SYSDATE, SYSTIMESTAMP, we get the wrong time and sometimes wrong day. is the setting "DBTIMEZONE is not same as OS- or local time zone" correct? can you give us some suggestions about the DBTIMEZONE setting? thanks... Bin
There are something does not suit me. I mean, SESSIONTIMEZONE should be the hour (like +02:00) but you posted Europe/Berlin. Please post "sho parameter nls" Edited by: Fran on 29-may-2012 6:33
One of the possible reason for this behaviour is the timezone setting (TZ env. variable) used by database listeners: http://programmersjournal.blogspot.com/2011/11/what-influences-oracle-sysdate.html You may need to change TZ setting for listeners and this requires listener stop/restart.
"Sysdate" is purely dependent on the operating system clock, hence it IS depending on the timezone information of this operating system and/or the operating system "TZ" variable settings when the database and listener are started (!!!). -- see MOS ID 1335999.1
Dear Bin, 1) please don't duplicate the questions: https://forums.oracle.com/forums/message.jspa?messageID=10362958#10362958 2) please close your old questions of you've received answers Sincerely yours, Constantine
Thanks for all of you! I've checked other intern databases of us, i get always same systimestamp in DB and OS. From my old experience I got also SYSDATE or SYSTIMESTAMP from DB like OS's time. I don't know why the DBA of our hosting provider sets up the DBTIMEZONE different than os. Is GMT/UTC-Timezone preferred in a RAC-Environment? We are in a single timezone land, in my view we don't need this setting. what are your suggestiongs? @Constantine, I've already closed the other message. Kind regards, Bin. Edited by: eax23 on May 29, 2012 4:42 PM
eax23 wrote: I don't know why the DBA of our hosting provider sets up the DBTIMEZONE different than os. Is GMT/UTC-Timezone preferred in a RAC-Environment? We are in a single timezone land, in my view we don't need this setting. what are your suggestiongs? The link I provided above stated: Database timezones should never be set to a named timezone that has DST. The preferred setting for DBTIMEZONE is +00:00 or UTC. See point 3) in Note 340512.1 Timestamps & time zones - Frequently Asked Questions. Regards, Constantine