Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Database instance times off by one hour

T. Money
Occasional Advisor

Database instance times off by one hour

g Enterprise. Operating system is HP-UX 11i. The time is off by one hour on instance 1 (see sqlplus output below):

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.
8 REPLIES
Steven E. Protter
Exalted Contributor

Re: Database instance times off by one hour

Shalom,

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
TwoProc
Honored Contributor

Re: Database instance times off by one hour

I'd start here on Metalink:
Subject: Timestamps & time zones - Frequently Asked Questions
Doc ID: Note:340512.1
We are the people our parents warned us about --Jimmy Buffett
Paul Sperry
Honored Contributor

Re: Database instance times off by one hour

why invalid?
T. Money
Occasional Advisor

Re: Database instance times off by one hour

Thanks for the responses and sorry for the confusion, I'm new to the ITRC.

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.
T. Money
Occasional Advisor

Re: Database instance times off by one hour

I have been working through the metlink note that was posted with no luck so far.

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>
TwoProc
Honored Contributor

Re: Database instance times off by one hour

If you read the link I posted re: Metalink, there is a link on the page specifically telling you how to set the time zones in Oracle. I also see that it was probably you that posted a thread in the DB forums on Metalink as well, so I believe that you can get to those pages in the Metalink Doc ID.

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?
We are the people our parents warned us about --Jimmy Buffett
TTr
Honored Contributor

Re: Database instance times off by one hour

> $ whoami
> 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.
T. Money
Occasional Advisor

Re: Database instance times off by one hour

Problem solved.

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.