- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle Database time differ than HP-UX OS
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-26-2010 10:02 PM
тАО01-26-2010 10:02 PM
Oracle Database time differ than HP-UX OS
The Oracle Database time is differ than the real UNIX TZ, Kindly follow below details for more info.
A connection establish via $sqlplus system@db11/db22 instance the time is differ than @sqlplus system,
OS:HP-UX 11.11
DB:Oracle RAC 10g R2
SQL> select to_char(sysdate,'dd:mon:yyyy ss:mi:hh24') from dual;
Unix time zone set as
Asia/Riyadh
/etc/TIMEZONE = WAT-3
SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
TZ_OFFS TZ_OFFS
------- -------
+03:00 -04:00
==================================
login sqlplus@system time is (OK)
==================================
$ . ./.oraenv.db
$ sqlplus system
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 23 10:53:09 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'D
------------------
23-JAN-10 10:53:45
========================================
login sqlplus@system@NPRD1 (time not-OK)
=========================================
$ . ./.oraenv.db
$ sqlplus system@NPRD1
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 23 10:55:25 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'D
------------------
23-JAN-10 02:55:36
# Added a line " (ENVS='TZ=WAT-3') " and /home/oracle/.dtprofile after reboot issue not resolved.
I appreciate all who share their live experience to solve this problem............
Have a charming day !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-26-2010 11:21 PM
тАО01-26-2010 11:21 PM
Re: Oracle Database time differ than HP-UX OS
Maybe you have a problem with database Time Zone
select tzname,tzabbrev
from V$TIMEZONE_NAMES
where tzabbrev = 'UTC+3'
If returns Asia/Riyadh, you can use UTC.
If returns nothing, try:
select tzname,tzabbrev
from V$TIMEZONE_NAMES
where tzabbrev = 'GMT+3'
and below you should use GMT instead UTC.
The field TZNAME is the one you can use in ORACLE. (can be 1: Asia/Riyadh or 2: Etc/GTM+3)
select DBTIMEZONE from dual;
should return Asia/Riyadh or UTC+3 (GMT+3)
If not, set it to the right one, using
ALTER database SET TIME_ZONE = 'Asia/Riyadh';
or:
ALTER database SET TIME_ZONE = 'Etc/GMT+3';
Also, in Unix you should have the correct TZ for your area:
in /etc/TIMEZONE you should have:
TZ=UTC+3
or, better:
TZ=Asia/Riyadh
Remove the TZ var. from /home/oracle/.dtprofile. There is not necessary to reboot your server.
Afther that, you can check that the oracle DB time is the same as reported by unix date command by using:
SQL> select SYSTIMESTAMP from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-JAN-10 08.57.29.133588 AM +02:00
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
$ date
Wed Jan 27 08:57:39 EET 2010
$
Best regards,
Horia.
Horia.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-26-2010 11:56 PM
тАО01-26-2010 11:56 PM
Re: Oracle Database time differ than HP-UX OS
Find below out put of above command's. The output of sysdate is wrong when i connected via sqlplus system@instance , If i connect via sqlplus system time is ok.
Alter DB tried on test based environment did not workout
Note: HP-UX time set via set_parms timezone Asia/Riyadh /etc/TIMEZONE TZ=WAT-2 (system defined)
SQL> select tzname,tzabbrev
from V$TIME 2 ZONE_NAMES
where tz 3 abbrev = 'UTC+3';
no rows selected
SQL> select tzname,tzabbrev
fr 2 om V$TIMEZONE_NAMES
where tza 3 bbrev = 'GMT+3';
TZNAME
--------------------------------------------------------
TZABBREV
--------------------------------------------------------
Etc/GMT+3
GMT+3
SQL> select DBTIMEZONE from dual;
DBTIME
------
-04:00
Rgd's
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 12:02 AM
тАО01-27-2010 12:02 AM
Re: Oracle Database time differ than HP-UX OS
ALTER database SET TIME_ZONE = 'Asia/Riyadh'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns
SQL> ALTER database SET TIME_ZONE = 'Etc/GMT+3';
ALTER database SET TIME_ZONE = 'Etc/GMT+3'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 12:27 AM
тАО01-27-2010 12:27 AM
Re: Oracle Database time differ than HP-UX OS
You get that error becouse you do have colums with timestamps with local time zone.
The solution in this case is to actually drop the columns and to recreate those.
Read (in full):
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3083336233923
"Followup June 9, 2005 - 6pm Central time zone:
you can alter the database IF you don't have any timestamps with local time zone, you need not
recreate (that was my first comment above in fact) "
Best regards,
Horia.
Horia.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 01:14 AM
тАО01-27-2010 01:14 AM
Re: Oracle Database time differ than HP-UX OS
WAT-3 = GMT+1-3=GMT-2
But you said that your server location is: Asia/Riyadh
meaning: GMT+3
So you should decide where your server is located.
Horia.
Horia.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 01:24 AM
тАО01-27-2010 01:24 AM
Re: Oracle Database time differ than HP-UX OS
$ TZ=GMT+3
$ date
Wed Jan 27 06:19:37 GMT 2010
$ sqlplus /nolog;
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 27 06:19:49 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba;
Connected.
SQL> select DBTIMEZONE from dual;
DBTIME
------
+03:00
SQL> select SYSTIMESTAMP from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-JAN-10 06.20.46.289103 AM -03:00
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Producti
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
$ TZ=WAT-3
$ date
Wed Jan 27 12:21:43 WAT 2010
$ sqlplus /nolog;
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 27 12:22:23 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba;
Connected.
SQL> select DBTIMEZONE from dual;
DBTIME
------
+03:00
SQL> select SYSTIMESTAMP from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-JAN-10 12.22.40.064240 PM +03:00
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
$
Horia.
Horia.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 02:26 AM
тАО01-27-2010 02:26 AM
Re: Oracle Database time differ than HP-UX OS
The GMT of Riyadh is GMT+3 , But when i go through in HP-UX with setting parameters for timezone #set_params timezone - > Asia ->Riyadh , UNIX itself assing WAT-3 under /etc/TIMEZONE. It means time zone (WAT-3)is ok.
Now The sysdate is also ok when i connect via sqlplus sys as sysdba , But if i connect via listener_SID or instance ID (i.e sqlplus system@db1, The time is not correct .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 02:52 AM
тАО01-27-2010 02:52 AM
Re: Oracle Database time differ than HP-UX OS
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=322646
read Bill Hassell's posts.
Horia.
Horia.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2010 04:08 AM
тАО01-27-2010 04:08 AM
Re: Oracle Database time differ than HP-UX OS
So if you don't like WAT-3, you can use "TZ=Riyadh-3".
>Horia: in /etc/TIMEZONE you should have: TZ=UTC+3
>better: TZ=Asia/Riyadh
The first is never a good idea and it is 6 hours off. Using GMT-3 gives you the right time but it says you are in GMT, London.
The second will not work, it's the same as GMT0.
>Another observation:
>WAT-3 = GMT+1-3=GMT-2
No, WAT-3 is WAT-3, 3 hours ahead of UTC/GMT, not 2.
>read Bill Hassell's posts.
You need to be careful because Bill has it backwards, GMT3 is in the Atlantic or Brazil.