oracle time os older than system date

[Jeeshan]

OS: 11iv1
Oracle: 10g with CRS

when they issues SQL command
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
07-MAR-09 01.45.16.468920 AM -05:00

but when they issued it comes like this

SQL> host date
Sat Mar 7 12:45:26 bgh 2009

where is the problem actually?

[Volker Borowski · Accepted solution]

Hi,

probably the listener has been started with a diffrent timezone than the user is using.

Your nearly exact 1 hour offset makes a timezone problem most likely.

Volker

[Jeeshan]

then what will be my action points?

[Bill Hassell]

HP-UX keeps only one time: UTC, also known as GMT or Zulu time. There is a variable called TZ which is set in the file /etc/TIMEZONE that is sourced when you login. However, Oracle doesn't login. It is usually started at bootup time or some other means. You'll need to determine how Oracle (and other tools that access Oracle) are started. The environment must have the TZ variable set to the same value.

[Volker Borowski]

Hi,

ensuring that TZ environment is set
properly is to be the first to do.

The second thing might be that the DB is
configured in a diffrent timezone than UTC.

SELECT DBTIMEZONE FROM dual;

Should give you the offset, that the DB is
using (unlikely, but possible).

You should check all userenvironments and scripts involved and may be log the output
of "date" and "echo $TZ" in those scripts
to verify your current setup.

The one hour offset is a quite common problem in middle europe, because we are one hour of GMT/UTC. So you log in to do something and everything is done in CET.
Than you have i.E. a logrotate action on that
likes to restart the listener which is run
by cron at night and next day you get one
hour offsets.

I do not know. if your client is may one hour off ??

Is "bgh" the timezone that the database is running in, or just the TZ of your User when you log in?

By the way, if you have multiple clients using diffrent timezones on this database, it is vital for you to know exactly what TZ the database has and what TZ a user might be using. Think of a point in time recovery, where you need to target a specific recovery minute...
Would be bad if you are one hour off then!

Good hunting
Volker

[Jeeshan]

Thanks Volker and Bill for your valuable time.

The problem has resolved.

Actually there is nothing wrong with system time. My TZ value has no problem. Everything is fine in respect of Time Zone.

The main problem as mentioned Volker in first answer is as I asked my DBA team to check the CRS environment. The problem resides there. When anyone login through Listener the database takes time from CRS not system.

Changing time zone in CRS solves it.

BTW, thanks all.