On Thu, 14 Oct 2004 16:04:16 +0200, Holger Baer 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?
>
> Regards,
>
> Holger
Too bad the metalink post didn't have your solution. The docs say the ALTER
SYSTEM RESET is for RAC. Guess that's not entirely true. Great work!
Received on Thu Oct 14 2004 - 11:44:40 CDT
