Oracle SYSDATE show old date

[Eric Antunes]

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

[Stephen Badgett]

Yogeeraj ...

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

thank you
Steve

[Stephen Badgett]

Eric Antunes

I did fail to state this is a LINUX machine

Steve

[Stephen Badgett]

help

SQL> startup
ORA-00065: initialization of FIXED_DATE failed

[Stephen Badgett]

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

[Eric Antunes]

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

[Eric Antunes]

That is, you must DELETE the fixed_date line in the init.ora...

[Simon Wickham_6]

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]

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

[Simon Wickham_6]

Hi Steve,

Are All Control Files Missing?

Please see attached.

Regards,
Simon

[Stephen Badgett]

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

[Stephen Badgett]

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?

[Stephen Badgett]

Simon

My control file did not apear missing

[Yogeeraj_1]

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

[tcsa]

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.

[Hein van den Heuvel]

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]

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

[Eric Antunes]

Better yet:

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

Best Regards,

Eric Antunes

[Stephen Badgett]

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

[Eric Antunes]

Hi Steve,

Try the following:

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

Best Regards,

Eric Antunes

[Eric Antunes]

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

[Eric Antunes]

Finally, read more about spfile in Metalink Note 249664.1...

[Stephen Badgett]

completly fixed -- THANK YOU ALL

[Stephen Badgett]

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

Thank you