Oracle SYSDATE show old date

[Stephen Badgett]

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

[Oviwan]

Hi

is the local time correct?

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

Regards

[Stephen Badgett]

Look right

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

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

SQL>

[A. Clay Stephenson]

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

[A. Clay Stephenson]

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().

[Stephen Badgett]

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

[Stephen Badgett]

Yes this is a local server

[Indira Aramandla · Accepted solution]

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

[Hein van den Heuvel]

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.

[Yogeeraj_1]

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

[Simon Wickham_6]

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

[Frank de Vries]

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.

[John Wimmer_1]

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.

[tcsa]

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.

[Stephen Badgett]

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

[Stephen Badgett]

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?

[Indira Aramandla]

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

[Eric Antunes]

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

[Yogeeraj_1]

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

[Hein van den Heuvel]

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.

[Yogeeraj_1]

hi steve,

The FIXED_DATE parameter should unset in INIT.ORA.

Just to test if this is the real cause, you can dynamically unset it using the command ALTER SYSTEM SET FIXED_DATE = NONE;

Once this command has been issued, SYSDATE will return to normal functionality.


good luck

kind regards
yogeeraj

[Yogeeraj_1]

hi steve,

The FIXED_DATE parameter should be unset in INIT.ORA.

Just to test if this is the real cause, you can dynamically unset it using the command ALTER SYSTEM SET FIXED_DATE = NONE;

Once this command has been issued, SYSDATE will return to normal functionality.


good luck

kind regards
yogeeraj

[Stephen Badgett]

Hi all ...

Eric Antunes ...

[oracle@prolinux oracle]$ date; cat /etc/TIMEZONE
Fri Jan 27 08:55:42 PST 2006
cat: /etc/TIMEZONE: No such file or directory


Yogeeraj ...

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

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


Hein. ...

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

YES - as seen above

I also looked in my init.ora file and found no FIXED_DATE --


Yogeeraj ... (this did it )

SQL> ALTER SYSTEM SET FIXED_DATE = NONE;

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

TO_CHAR(SYSDATE,'D
------------------
27-JAN-06 09:08:31


Now will it stay that way after a reboot of oracle?

[Yogeeraj_1]

hi steve,

Only if your init.ora does not contain the line (e.g.):

FIXED_DATE='01-JAN-03'

if you have it, you will have to remove it from there. This will ensure that sysdate will resume "normal" behaviour.

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

if you are using SPFILE, you should be cautious NOT to set FIXED_DATE=NONE

Metalink reports a bug -- see note 3368245.8
Subject: Bug 3368245 - Setting FIXED_DATE to NONE with SPFILE causes ORA-65 on STARTUP

Again, i recommend that you remove it so that your SYSDATE returns to normal functionality.

hope this helps too!

kind regards
yogeeraj