Operating System - HP-UX
1748221 Members
4350 Online
108759 Solutions
New Discussion юеВ

Re: Oracle SYSDATE show old date

 
SOLVED
Go to solution
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
John Wimmer_1
Advisor

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.