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


Oracle DBTIMEZONE


Hi, I'm working on an Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit.  I'm experiencing problems while reading the SYSDATE value.  DB Server, at the oracle installation time, was set to local time, Caracas (UTC - 5:30), yesterday it has been fixed, set it to UTC date and time.  [oracle@mgvdb01 ~]$ date Wed Oct 17 16:07:46 UTC 2012   Now if I connect to database using local SQLPlus, everything is working fine:  SQL*Plus: Release 11.2.0.3.0 Production on *Wed Oct 17 16:04:02 2012*   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';   Session altered.   SQL> select sysdate from dual;   SYSDATE ------------------- *17/10/2012 16:04:08*   SQL> select dbtimezone,sessiontimezone from dual;   DBTIME ------ SESSIONTIMEZONE --------------------------------------------------------------------------- +00:00 +00:00   But if I connect from a remote computer (mine, set to ROME time zone, UTC +2) I got:  SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 17 18:05:41 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';   Session altered.   SQL> select sysdate from dual;   SYSDATE ------------------- *17/10/2012 11:36:17*   SQL> select dbtimezone,sessiontimezone from dual;   DBTIME ------ SESSIONTIMEZONE --------------------------------------------------------------------------- +00:00 +02:00   The second query is fine also from my computer, but the sysdate is not the utc, is still the local time (Caracas).รน What's wrong?  Thanks in advance, Samuel

What is the output of this query? SELECT SYSTIMESTAMP, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual; 

From local:  SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 18 08:33:09 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL> SELECT SYSTIMESTAMP, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;  2   SYSTIMESTAMP --------------------------------------------------------------------------- CURRENT_TIMESTAMP --------------------------------------------------------------------------- LOCALTIMESTAMP --------------------------------------------------------------------------- 18-OCT-12 08.33.11.134205 AM +00:00 18-OCT-12 08.33.11.134212 AM +00:00 18-OCT-12 08.33.11.134212 AM   From remote:  SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 18 10:34:58 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL>  SELECT SYSTIMESTAMP, CURRENT_TIMESTAMP, LOCALTIMESTAMP   2  FROM dual;   SYSTIMESTAMP --------------------------------------------------------------------------- CURRENT_TIMESTAMP --------------------------------------------------------------------------- LOCALTIMESTAMP --------------------------------------------------------------------------- 18-OCT-12 04.05.16.320813 AM -04:30 18-OCT-12 10.35.16.320832 AM +02:00 18-OCT-12 10.35.16.320832 AM 

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. SYSTIMESTAMP is the timestamp on the server machine itself and is obtained on Unix platforms by calling " GetTimeOfDay " and on Windows by calling "GetSystemTime" to get the servers local time. This means that SYSTIMESTAMP, just like SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.   Could you try from your remote terminal: SELECT systimestamp AT TIME ZONE 'Europe/Rome' FROM DUAL;   Edited by: marcopb on Oct 18, 2012 11:08 AM

If you want from your remote terminal (read session) to see time as in Caracas... you have to use at session level: ALTER SESSION SET TIME_ZONE = 'America/Caracas'; 

My problem is that I see correct time (UTC) while selecting from local (Caracas) SQLPlus, but from remote SQLPlus (my pc in Europe) I see Caracas time. Always asking for SYSDATE. How is it possible that SYSDATE returns 2 different values???

That's because "SYSDATE" and "SYSTIMESTAMP" are purely dependent on the operating system clock, hence it IS depending on the timezone information of this operating system and/or the operating system  settings when the database and listener where started (!!! This may be different then the current value you see !!!!).   Ciao 

Check also if you are in this case: How To setup TNS listener to Show More Than one Timezone [399448.1]

Sorry, but I still do not understand:  the (database server) operating system is in UTC time:  [oracle@mgvdb01 ~]$ date Thu Oct 18 17:23:59 UTC 2012   but the SYSDATE value I got from a remote connecion IS NOT IN UTC, but on Caracas datetime. The SYSDATE value I got from a locate connecion is in UTC. How it is possible if SYSDATE is operating system dependent? Shouldn't I read always the same value as it's operating system dependant?  I restart all database instance (shutting down all nodes before restart them); all listener were turned off and then on. But nothing changed: select SYSDATE from database server machine return UTC, select SYSDATE from a remote client return Caracas time.  Thanks in advance

You need to separate the operating system of the database server from the operating system of the database client.  Two different pieces of physical hardware with different clocks. You can see that here in what you posted. Compare the first two lines. 18-OCT-12 08.33.11.134205 AM +00:00 18-OCT-12 08.33.11.134212 AM +00:00 18-OCT-12 08.33.11.134212 AM 

Sorry, maybe I was not able to explain the problem. There is just 1 database, not two.  I'm on the database server (hostname mvgdb01, instance_name mgv1, UTC as date command says):  [oracle@mgvdb01 ~]$ date Fri Oct 19 07:20:47 UTC 2012 [oracle@mgvdb01 ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 19 07:20:54 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL> select instance_name from v$instance;   INSTANCE_NAME ---------------- mgv1   SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';   Session altered.   SQL> select sysdate from dual;   SYSDATE ------------------- 19/10/2012 07:21:19   SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   Then I connect to mgv1 from my pc (where I just have oracle client with sqlplus, UTC +2):  C:\Documents and Settings\samuel.rabini>sqlplus ++++++/++++++@MGV1   SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 19 09:25:06 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL> select instance_name from v$instance;   INSTANCE_NAME ---------------- mgv1   SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';   Session altered.   SQL> select sysdate from dual;   SYSDATE ------------------- 19/10/2012 02:55:55   SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64 bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   C:\Documents and Settings\samuel.rabini>   I'm connected to the same database, same instance, but sysdate from local and from remote has different values.  Hope to be more clear about the problem now.  Thanks in advance.

I noticed another glitch:  if I connect from nod1 to node2, sysdate returns me UTC-4:30, if I connect from node1 to node1 I get UTC.  Is it something related to the lister?  Thanks in advance.

I think I got something. Browsing on google I found out that this behaviour is releated to 2 things: - bad /etc/sysconfig/clock, now set to:  ZONE="Etc/UTC" UTC=true ARC=false  - using local_listener, $TZ env var must be set to UTC  Now it happens that if I start the database this way:  [oracle@mgvdb01 dbs]$ sqlplus / sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 19 09:53:16 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup ORACLE instance started.   Total System Global Area 2.1379E+10 bytes Fixed Size                  2237776 bytes Variable Size            1.9998E+10 bytes Database Buffers         1342177280 bytes Redo Buffers               36098048 bytes Database mounted. Database opened.   Remote client get UTC from sysdate. But if I start the instance using the cluster service utility:  srvctl start instance -d mgv -i mgv1   Remote client still get UTC-4:30 from sysdate. I noticed also that into the alert log, date log entry are in UTC-4:30 when is the cluster that starts the database instance.  Where can I find the parameter that cluster uses to set the time zone?  Thanks in advance.

This was the last stuff to execute:  srvctl setenv database -T -d mgv TZ=UTC   Now everything works fine from everywhere.


Related Links

Locking in oracle session
Recreating SYSAUX tablespace
book recommendation
general
ORA-000604
Update Blob column failed
Hot Backup
ORA-01991:INVALID password file.
sql query tuning
buffer cache size in 9i database
Many little datafiles or one big datafile?
Log Miner
Optimizer execution plan problem
hide some records
Clone database...applying correct log
Mview log purge question