Re: spfile vs pfile in 9i

Howard J. Rogers wrote:
> Holger Baer wrote:
>
>

>>Niall Litchfield wrote:
>>[...]
>>
>>
>>
>>>In the case of the spfile, the fact that you can merrily stuff it up
>>>and then be unable to start the instance (because you can no longer
>>>edit the spfile) or correct the problem without restoring from backup
>>>is a rather large defect.
>>>Niall Litchfield
>>
>>But it's not much effort to get the instance up even without restoring
>>from backup (I've demonstrated it recently and I'm willing to repeat
>>this until the message get's through) ;-)
>>
>>1. create a init.ora with the first line containing
>>spfile=path/yourspfile.ora
>>2. the rest of the init.ora are settings that correct the wrong ones
>>    in the spfile. Since only from the last occurence of a setting
>>    the value is taken, you can now get your instance up and running
>>    by
>>startup open pfile=path/init.ora

Well, I thought that at first too. But in fact, IMO it's an elegant option to recover from a wrongly configured spfile.

This temporary init.ora that my solution suggested is only used for the short period that's necessary to start the instance. And what I left out in my first post is that I explicitly *don't* put the temporary init.ora somewhere into $ORACLE_HOME/dbs but e.g. into /tmp or such (let's see what flames I get for that one) ;-)

And it's not confusing either, because usually you got one parameter wrong. You fix that one in the init.ora, and nothing else. A bit of a drawback I admit, that you have to apply this change afterwards to the spfile.

>
> And whatever happened to:
>
> create pfile from spfile
> edit pfile
> create spfile from pfile
> startup
> ??

That's of course always a viable option. However most people will leave the pfile behind which might lead to other confusion: Why are there pfile *and* spfile? Of course, if available the latter get's picked up except the pfile parameter was manually supplied at startup, but sometime later someone will be sitting there and think "if I have both, since I prefer pfiles over spfile why shouldn't I just use the pfile this time?"

>
> The create X from Y commands do require SQL*Plus, but they don't require an
> instance.
>
> Regards
> HRJ
Besides, my point was (which I should have stressed more), that most people seem to despise spfiles because they are binary files so they can't edit them as they where used to with pfiles. There are ways around this, so no need for FUD.

And as you always say: Only fix what's actually broken. My method does that (so does create pfile from spfile/edit pfile/create spfile from pfile) and there is no need to restore a spfile from backup because of one falsely applied parameter.

Regards
Holger Received on Tue Oct 26 2004 - 12:13:04 CDT