Operating System - HP-UX
1830044 Members
6958 Online
109998 Solutions
New Discussion

Oracle SYSDATE show old date

 
SOLVED
Go to solution
Eric Antunes
Honored Contributor

Re: Oracle SYSDATE show old date

Hi Steve,

You really need to set a timezone since it is not set in your system.

First check /usr/lib/tztab and find your timezone for your geagraphic area. If you can't find a timezone for you, you can use GMT. Then, execute:

#/sbin/set_parms

Best Regards,

Eric Antunes

Each and every day is a good day to learn.
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Yogeeraj ...

I will read up on the Metallink report on this bug 3368245 -- to see if it applys

thank you
Steve
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Eric Antunes

I did fail to state this is a LINUX machine

Steve
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

help

SQL> startup
ORA-00065: initialization of FIXED_DATE failed

Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

You warned me

We rebooted the machine and thios morning I noticed that it did not start so I tryied to start it up and got that error message. to be more clear.

Steve
Not as is, is now
Eric Antunes
Honored Contributor

Re: Oracle SYSDATE show old date

Hi Steve,

First you must know what init.ora file you are using. By default, you are using the init.ora located at $ORACLE_HOME/dbs but someone may have changed this.

Anyway, do:

vi $ORACLE_HOME/dbs/init.ora (or your init.ora location)

make sure there is no FIXED_DATE parameter in it and:

SQL> startup pfile=$ORACLE_HOME/dbs/init.ora

Best Regards,

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

Re: Oracle SYSDATE show old date

That is, you must DELETE the fixed_date line in the init.ora...
Each and every day is a good day to learn.
Simon Wickham_6
Regular Advisor

Re: Oracle SYSDATE show old date

Hi Steve,

You have the FIXED_DATE parameter set in the database and are using a SPFILE instead of a pfile. You unset the parameter using:

alter system set FIXED_DATE=NONE

Available workarounds are:
1)Recreate spfile
2)Use scope=memory while unsetting the parameter:
ALTER SYSTEM SET FIXED_DATE=NONE SCOPE=MEMORY;
3)Use PFILE

Oracle uses OS date & time so check this with the sysdate funtion :
select to_char(sysdate, 'DD-MM-RRRR HH24:MI') from dual

Regards,
Simon
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

this thread is getting too long -- OK


SQL> startup pfile=$ORACLE_HOME/dbs/initPRX.ora
ORACLE instance started.

Total System Global Area 42742804 bytes
Fixed Size 451604 bytes
Variable Size 41943040 bytes
Database Buffers 204800 bytes
Redo Buffers 143360 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

Not as is, is now
Simon Wickham_6
Regular Advisor

Re: Oracle SYSDATE show old date

Hi Steve,

Are All Control Files Missing?

Please see attached.

Regards,
Simon
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

My control files were not listed in the initPRX.ora file and I placed them in the file and ...
control_files = (/pcsi/app/oracle/oradata/prx/control01.ctl, /pcsi/app/oracle/oradata/prx/control02.ctl, /pcsi/app/oracle/oradata/prx/control03.ctl)

----------------

startup pfile=$ORACLE_HOME/dbs/initPRX.ora
ORACLE instance started.

Total System Global Area 42742804 bytes
Fixed Size 451604 bytes
Variable Size 41943040 bytes
Database Buffers 204800 bytes
Redo Buffers 143360 bytes
ORA-00209: control file blocksize mismatch, check alert log for more info
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Is this the right action?

Action: Restore a good copy of the control file. If the control file is known to be clean set the DB_BLOCK_SIZE to match control file headers block size value.

if it is how do I find the value?
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Simon

My control file did not apear missing
Not as is, is now
Yogeeraj_1
Honored Contributor

Re: Oracle SYSDATE show old date

hi steve,

can you please check if all the 3 control files are of same size?

also, can you try to execute:

alter database backup controlfile to trace;


kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
tcsa
Frequent Advisor

Re: Oracle SYSDATE show old date

Hello!!

I must disagree with ""Yogeeraj"". In Oracle, all controlfiles are the same size since they are all identical copies of each other. I don't think they can be different from each other in term of size. But with redo logfiles you can different sizes for logfile belonging to different groups. For instance size of logfiles of group 1 can be different from the sizes of logfiles of group 2. But this is not recommended by oracle.
Determination is key for success.
Hein van den Heuvel
Honored Contributor

Re: Oracle SYSDATE show old date


Whoah... calm down now. You guys are late to the party.
Steve has no real problem with with the
control files, or redologs. Not really.

His DB was coming up fine, except with undesirable sysdate settings. As he is trying to fix that, he is experimenting with other, explicit, p-files pointing to mismatching control files. Once he figures out how to deal with the spfile and/or lines up a proper initXXX.ora file pointing to his real control files all will be well again.
No need to restore anything. Doing so (without backups) would create bigger problems. These are basic Oracle DBA questions/skill and he'll figure it out just fine I'm sure.

fwiw,
Hein
Eric Antunes
Honored Contributor

Re: Oracle SYSDATE show old date

Steve,

Hein is right: you were using another parameter file maybe in another location than $ORACLE_HOME/dbs or with spfile.

Try a find:

$find $ORACLE_BASE -name init*.ora

Best Regards,

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

Re: Oracle SYSDATE show old date

Better yet:

$find $ORACLE_BASE -name init*.ora -exec grep -l -e 'fixed_date' -e 'FIXED_DATE' {} \;|more

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Eric ---
[root@prolinux prx]# find $ORACLE_BASE -name init*.ora -exec grep -l -e 'fixed_date' -e 'FIXED_DATE' {} \;|more
[root@prolinux prx]#

this found none
Not as is, is now
Eric Antunes
Honored Contributor

Re: Oracle SYSDATE show old date

Hi Steve,

Try the following:

SQL>shutdown immediate;
SQL>alter system set fixed_date=none scope=both;
SQL>startup;

Best Regards,

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

Re: Oracle SYSDATE show old date

Then,

SQL>SELECT name,value FROM v$parameter WHERE name = 'spfile';
SQL>create pfile='$ORACLE_HOME/dbs/init_from_spfile.ora' from spfile='';
SQL>exit;
$vi $ORACLE_HOME/dbs/init_from_spfile.ora

...
delete the fixed_date line
...
save the file

$sqlplus '/ as sysdba'
SQL>CREATE SPFILE FROM PFILE='$ORACLE_HOME/dbs/init_from_spfile.ora';


Best Regards,

Eric Antunes


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

Re: Oracle SYSDATE show old date

Finally, read more about spfile in Metalink Note 249664.1...
Each and every day is a good day to learn.
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

completly fixed -- THANK YOU ALL
Not as is, is now
Stephen Badgett
Regular Advisor

Re: Oracle SYSDATE show old date

Meta link Note:112003.1 & 249664.1 did the trick but mostly it was instruction from all you guys

Thank you

Not as is, is now