On Thu, 14 Oct 2004, holger.baer_at_science-computing.de wrote:
> Brian Dick wrote:
>> metalink shows this as a bug that is still under
>> investigation. Oracle is saving the parameter as
>> fixed_date='NONE' when it should be saving the paramenter as
>> fixed_date=NONE without the quotes. I was able to recreate the
>> problem and a work-around. The official way to recover is
>> from a backup for your spfile, you can copy it over the
>> spfile. Assuming you are using Windows, the spfiles are in the
>> %ORA_HOME%\database directory. If you have a backup that is a
>> pfile, start the instance with the pfile. Then use the
>> following command. create spfile from
>> pfile='your.backup.pfile.ora'; I didn't have a backup and this
>> is my work-around. 1) Edit the spfile with a binary editor.
>> 2) Change the fixed_date='NONE' to fixed_date=1JAN00. 3) Run
>> command startup. 4) Run command create pfile='c:\pfile.ora'
>> from spfile. 5) Run command shutdown. 6) Edit c:\pfile.ora
>> with a text editor. 7) Remove the fixed_date=* line. 8) Run
>> command startup pfile='c:\pfile.ora'. 9) Run command create
>> spfile from pfile='c:\pfile.ora'. 10) Run command shutdown.
>> 11) Run command startup. Good luck!
>
> A nicer workaround that I found recently but can't remember
> who's to credit:
>
> 1) create a new pfile e.g. temp.ora with the following content:
> spfile=<path to your spfile>/spfile<SID>.ora
> *.NLS_DATE_FORMAT='DD.MM.YYYY'
> *.FIXED_DATE='01.06.2004'
> 2) startup nomount
> 3) alter system reset FIXED_DATE scope=spfile sid='*'
> 4) shutdown
> 5) startup
>
> Benefits: No fiddling in the spfile, and a nice demonstration
> on how to use the alter system reset command correctly (the
> documentation isn't very clear on the subject that you *need*
> the sid part for reset).
>
> The mechanics behind this is, that you can have the same
> parameter appear several times in your spfile/pfile, but only
> the *last* one is actually evaluated. So you can start with a
> temporary pfile that includes a spfile and thus repair your
> spfile.
>
> The more I think about it, the more I believe I read this in
> Tom Kytes last book, but I'm not sure. Maybe someone has it has
> his/her desk and can clarify?
I actually just brought the instance up with an init file that I
created, then I did a "create file from spfile" and then shutdown
the database again. Edited that new init file, brought the
database up with this init file and then did a "create spfile
from file" and then shutdown once more and then started up once
more.
--
Galen Boyer
Received on Thu Oct 14 2004 - 09:30:28 CDT
