- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Database instance times off by one hour
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
тАО03-18-2008 07:41 AM
тАО03-18-2008 07:41 AM
Database instance times off by one hour
Instance 1:
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "SYSDATE" from DUAL;
SYSDATE
------------------------------------------
Tue 18-Mar-2008 09:10:05
Instance 2:
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') AS "SYSDATE" FROM DUAL;
SYSDATE
------------------------------------------
Tue 18-Mar-2008 10:10:18
Below is system prompt output showing the following things:
- User is Oracle
- Time zone environment variable is the SAME on both servers
- System date is the same on both servers
- Lsnrctl status shows that the listener was started with the same time zone setting on both servers.
At the command prompt on server 1 (running instance 1):
$ whoami
oracle
$ echo $TZ
EST5EDT
$ date
Tue Mar 18 10:13:01 EDT 2008
$ lsnrctl status
LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 18-MAR-2008 10:14:57
...
At the command prompt on server 2 (running instance 2):
$ whoami
oracle
$ echo $TZ
EST5EDT
$ date
Tue Mar 18 10:13:10 EDT 2008
$ lsnrctl status
LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 18-MAR-2008 10:15:09
...
The instance parameters are the same for both nodes of the cluster. Sysdate and systimestamp are supposed to pull from the operating system clock, so I cannot figure out why the time is off on instance 1. As you can imagine this is causing problems for users entering data that uses sysdate to timestamp new records.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 07:47 AM
тАО03-18-2008 07:47 AM
Re: Database instance times off by one hour
Check the time zone settings for the system.
set_parms timezone
IF this was done you must reboot for the settings to be present for all processes.
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 07:55 AM
тАО03-18-2008 07:55 AM
Re: Database instance times off by one hour
Subject: Timestamps & time zones - Frequently Asked Questions
Doc ID: Note:340512.1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 08:36 AM
тАО03-18-2008 08:36 AM
Re: Database instance times off by one hour
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 10:04 AM
тАО03-18-2008 10:04 AM
Re: Database instance times off by one hour
I had run set_parms on both systems. As evidence of this, the following output is identical on both servers, as set_parms timezone will alter this file:
$ cat /etc/TIMEZONE
TZ=EST5EDT
export TZ
Both systems have been rebooted in the last week since the time change so any changes would have taken effect.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 10:36 AM
тАО03-18-2008 10:36 AM
Re: Database instance times off by one hour
Below is more sqlplus output. I think that this shows the OS timezone is fine but the problem is Oracle-specific. When I log into sqlplus on the server running instance1, I first log in as sysdba and do not authenticate. When I do this I should be using the oracle user's profile and thus their timezone setting. When I select systimestamp it is correct, GMT-4. However, when I connect to the same instance and I authenticate as system and perform the same query, the time is off, GMT-5:
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 18 14:29:35 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
18-MAR-08 02.29.47.665687 PM -04:00
SQL> conn system/***@instanace1
Connected.
SQL> /
SYSTIMESTAMP
---------------------------------------------------------------------------
18-MAR-08 01.29.57.688174 PM -05:00
SQL>
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 11:39 AM
тАО03-18-2008 11:39 AM
Re: Database instance times off by one hour
In that link, it says that that timezone for a database is set when the DB is created, and it inherits that setting for the OS.
See section heading titled:
Why is the database time zone the value it is, and how can I change it?
If you get the Oracle error mentioned when you try to set the timezone - you'll need to consult note 230099.1. Sadly, it gives you the query on how to see if you'll need to delete time zone data from teh database, but doesn't explicitly give you the statement itself.
Hopefully, you'll be able to test this in a test database, and you won't need to delete the old data from the database.
Did you perhaps change the timezone of the OS that the database is running on, or did, the server move to a different time zone, or have you moved this database by cloning it from a server in a different timezone?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 05:28 PM
тАО03-18-2008 05:28 PM
Re: Database instance times off by one hour
> oracle
> $ echo $TZ
This may not be conclusive since the TZ may have been altered right at the point when the database is started up on server1.
So how do you start up the database? Is there a startup script? Manually? Do you use the "oraenv" script? Ensure that the TZ is set correctly right at the point when you start it up.
Check any startup scripts, including the "oraenv" and "dbstart" in case the TZ is hardcoded in there.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-20-2008 06:59 AM
тАО03-20-2008 06:59 AM
Re: Database instance times off by one hour
We abandoned the scientific approach and went with the imperical. Every indication was that the server times and time zones were identical, but to be sure we shut down the instance on server 1, ran set_parms timezone once again and rebooted.
The instance came back up, with the correct session time zone. So we are all set.
What had us stumped was that none of the clues pointed to a server timezone problem, all signs pointed to oracle.
Thanks for all the great help and suggestions.