Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle SYSDATE show old date

SOLVED
Go to solution
Stephen Badgett
Regular Advisor

Oracle SYSDATE show old date

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
Not as is, is now
48 REPLIES
Oviwan
Honored Contributor

Re: Oracle SYSDATE show old date

Hi

is the local time correct?

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

Regards
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Look right

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

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

SQL>
Not as is, is now
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle SYSDATE show old date

What does the UNIX date command display when logged in as the same user?
If it ain't broke, I can fix that.
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle SYSDATE show old date

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().
If it ain't broke, I can fix that.
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

SQL> host date
Tue Jan 24 14:08:41 PST 2006
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Yes this is a local server
Not as is, is now
Indira Aramandla
Honored Contributor
Solution

Re: Oracle SYSDATE show old date

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
Never give up, Keep Trying
Hein van den Heuvel
Honored Contributor

Re: Oracle SYSDATE show old date

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

Re: Oracle SYSDATE show old date

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Simon Wickham_6
Regular Advisor

Re: Oracle SYSDATE show old date

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 = '
Frank de Vries
Respected Contributor

Re: Oracle SYSDATE show old date

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.
Look before you leap

Re: Oracle SYSDATE show old date

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
Frequent Advisor

Re: Oracle SYSDATE show old date

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.
Determination is key for success.
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

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
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

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?
Not as is, is now
Indira Aramandla
Honored Contributor

Re: Oracle SYSDATE show old date

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
Never give up, Keep Trying
Eric Antunes
Honored Contributor

Re: Oracle SYSDATE show old date

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





Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: Oracle SYSDATE show old date

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Oracle SYSDATE show old date


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

Re: Oracle SYSDATE show old date

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Oracle SYSDATE show old date

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

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?
Not as is, is now
Yogeeraj_1
Honored Contributor

Re: Oracle SYSDATE show old date

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Oracle SYSDATE show old date

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)