topic Re: Oracle SYSDATE show old date in Operating System - HP-UX

Oracle SYSDATE show old date

Stephen Badgett — Tue, 24 Jan 2006 15:39:19 GMT

Why does current date in Oracle show an old date?

SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
as "SYSDATE"
from dual;
2 3
SYSDATE
------------------------
Wed 01-Jan-2003 10:00:00

Steve

Re: Oracle SYSDATE show old date

Oviwan — Tue, 24 Jan 2006 15:52:29 GMT

Hi

is the local time correct?

select logon_time from v$session where sid=1;
is this time ok?

Regards

Re: Oracle SYSDATE show old date

Stephen Badgett — Tue, 24 Jan 2006 15:54:36 GMT

Look right

SQL> select logon_time from v$session where sid=1;

LOGON_TIM
---------
09-JAN-06

SQL>

Re: Oracle SYSDATE show old date

A. Clay Stephenson — Tue, 24 Jan 2006 15:56:14 GMT

What does the UNIX date command display when logged in as the same user?

Re: Oracle SYSDATE show old date

A. Clay Stephenson — Tue, 24 Jan 2006 16:10:59 GMT

Oh, and just so we know we are on the same page, I assume that this is a local Oracle connection so that the UNIX date command and the Oracle sysdata function have something in common. Sysdate should do nothing more than invoke the time() system call and then pass that to localtime().

Re: Oracle SYSDATE show old date

Stephen Badgett — Tue, 24 Jan 2006 17:10:28 GMT

SQL> host date
Tue Jan 24 14:08:41 PST 2006

Re: Oracle SYSDATE show old date

Stephen Badgett — Tue, 24 Jan 2006 17:10:54 GMT

Yes this is a local server

Re: Oracle SYSDATE show old date

Indira Aramandla — Tue, 24 Jan 2006 20:56:50 GMT

Hi Steve,

This is what metalink says
The SYSDATE function simply performs a system-call to the Operating System to get the time (a "gettimeofday" call).

The OS (Unix) TZ environment variable influences the time that the OS will pass on to Oracle. So even though sysdate itself does not use the timezones in the database, it is influenced by the (non-Oracle) TZ environment variable on the OS.

To debug situations in which you have a unexplained difference between the oracle sysdate and the system time you see on Unix, use the following method:

telnet to the unix machine

connect using sqlplus in the telnet session:

1)) once through the listener using a tnsnames alias
$sqlplus user/password@[tnsnames alias]
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

2) once trough a "local" ORACLE_SID connection
$env | egrep 'ORACLE_SID'
$sqlplus user/password
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

Check that the time in the banner of sqlplus ( SQL*Plus: Release 10.1.0.4.0 - Production on Wo Jan 11 15:05:46 2006 ) is reflecting the time based on the current TZ set in the Unix (!) session.

If the results are different this means that the listener is started with a different TZ then you current user environment. To resolve this simply stop and start listener with the TZ you want to use. if you are using MTS then you might see a correct result with a dedicated connection, in that case stop and start also the database with the correct TZ.

Indira A

Re: Oracle SYSDATE show old date

Hein van den Heuvel — Tue, 24 Jan 2006 21:28:31 GMT

Is that clock ticking, or stuck at 1-Jan-2003?

What exact Oracle version/platform?

A broken timezone setting seems like the most likely cause.

You may also want do an strace on your Oracle slave and actually 'see' the gettimeofday.

fwiw,
Hein.

Re: Oracle SYSDATE show old date

Yogeeraj_1 — Wed, 25 Jan 2006 07:40:06 GMT

hi steve,

Just to clarify a bit, can you please post the output like the following:

$ export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
$ sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 25 16:39:53 2006

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name: scott/tiger@mydb

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select sysdate from dual;

SYSDATE
--------------------
25-jan-2006 16:35:14

SQL>

thanks

kind regards
yogeeraj

Re: Oracle SYSDATE show old date

Simon Wickham_6 — Wed, 25 Jan 2006 08:49:39 GMT

Hi Steve,

I would try the following.

Get current system timestamp with time zone information
SQL> select SYSTIMESTAMP from dual;

Check the database time zone
SQL> select DBTIMEZONE from dual;

ALTER database SET TIME_ZONE = '
Switch back DBTIMEZONE to hh:mm format
SQL> ALTER database SET TIME_ZONE = '';

Regards,
Simon

Re: Oracle SYSDATE show old date

Frank de Vries — Wed, 25 Jan 2006 09:51:07 GMT

A few pointers:

The Oracle sysdate (and time) is
derived from the OS.
So if you are on unix , runing
the date command and set a new
date would effect that.

Also as was mentioned earlier, the TZ variable is important as it dictates
the timezone. To set it permanently
you need to update a unix file, I believe
it is /etc/timezone(s) , need to check.

Then you can synchronize your time
with the ntpd protocol to some
central server.

Re: Oracle SYSDATE show old date

John Wimmer_1 — Wed, 25 Jan 2006 11:58:56 GMT

There is an init.ora parameter FIXED_DATE that can be set to a specific date for testing purposes (or whatever other purpose that you might find useful) Check to make sure that it's not set.

Re: Oracle SYSDATE show old date

tcsa — Wed, 25 Jan 2006 14:55:45 GMT

Why don't you check the Operating System time too before you try anything. From what I know oracle dbms gets it time from the OS. So if the oracle database is displaying wrong date the problem might be with the OS.
In case it is the OS which I think is, make sure of the consequences and the impact of resetting clock. Resetting the clock might make your backup obsolete. So becarefull.

Re: Oracle SYSDATE show old date

Stephen Badgett — Thu, 26 Jan 2006 15:15:36 GMT

I am going to try to answer these as I do them

yogeeraj and Simon ...

SQL> select sysdate from dual;

SYSDATE
--------------------
01-jan-2003 10:00:00

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
26-JAN-06 08.54.56.474850 AM -08:00

SQL> select DBTIMEZONE from dual;

DBTIME
------
-08:00

This look right

Re: Oracle SYSDATE show old date

Stephen Badgett — Thu, 26 Jan 2006 16:14:37 GMT

Indira A ...

select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jan 26 12:17:15 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>
SQL>
SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
01-JAN-03 10:00:00

This seems like what you said so, how do I start listener with the correct TZ?

Re: Oracle SYSDATE show old date

Indira Aramandla — Thu, 26 Jan 2006 19:23:26 GMT

Hi Steve,

On Unix you can use the OS "TZ" environment variable to alter the time that the OS will pass on to Oracle.

You set the TZ appropriate for the database and then start the listener. It is also a good idea to set the correct TZ in the Unix environment of the user who re-starts the database

TIMEZONE (TZ) is sourced during login from the userâ s profile (Eg:- for a Posix shell, when /etc/profile is sourced during login, /etc/TIMEZONE is sourced if avaailable).

/etc/TIMEZONE is intended to contain the default TZ variable.

The /etc/default/tz file is used to help define the default TZ value when it is not defined.

When you logon as oracle echo $TZ to see what it has beens set to. Then set the TZ to the correct timezone and export the variable and resrat the listener.

Indira A

Re: Oracle SYSDATE show old date

Eric Antunes — Fri, 27 Jan 2006 05:18:24 GMT

Hi Steve,

Login as the unix database owner user (usually oracle) and give us the following outputs:

$date; cat /etc/TIMEZONE

Best Regards,

Eric Antunes

Re: Oracle SYSDATE show old date

Yogeeraj_1 — Fri, 27 Jan 2006 06:46:43 GMT

hi steve,

this is really very strange!

can you also post the output of:

select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from user_objects
where rownum <2;

thanks

kind regards
yogeeraj

Re: Oracle SYSDATE show old date

Hein van den Heuvel — Fri, 27 Jan 2006 07:43:50 GMT

So as I asked earlier, that clock does not tick... iti is stuck at 10:00 1/1/3 right?

Well then John Wimmers suggestion may point to the problem. Someone has set "fixed_date" to that string.
Maybe they thought it was to give it a fixed format, not a fixed values.

Anyway, set to blank (alter system) and/or restat having made sure it is not in the INIT files.

fwiw,
Hein.