Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


DBTIMEZONE settings


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


Related Links

Partitioning an already populated table
how to refresh materialized view by trigger
From MySQL to Oracle
Is Oracle10G SQL3 compatible?
Can I connect 9i server with 10g client
Function or rule based
I cannot logon the repository again after restarting computer
How to convert this no of minutes to hours:min format
end of communication channel
CREATE TABLE
HELP for a NOOB! - Accessing HR Schema
INNER JOIN
How to print from local printer???
ORA-00600 kcrf_pvt_strand_bind1
Want to find out all the available Oracle instance names in OpenVMS
SPFILE VS PFILE