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

One tablespace associates with 3 data files?
archiver struck 00257
reclaim space on truncting partitions
issues on view
RMAN Recovery Error (using Skip tablespace clause)
control two database with one database control
10.2.0.4 Patch problem
copy access tables to oracle
materialize views Server transfer
How to install Oracle companion software?
Query is consuming 1hr in Production database.
FTP bat file
Table Security
UPDATE STATISTICS
Issueduring verifying logical and physical dependencies of tablespaces.
Quotas