Re: spfile vs pfile in 9i

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 27 Oct 2004 05:39:19 +1000
Message-Id: <417ea7d9$0$22601$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

> 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
>> 
>> 
>> 
>> That seems to me to be awfully complicated and potentially confusing (I
>> hate the idea of utilising the old flaw that if there are multiple
>> repeats of a parameter, it's the last one that gets used).

>
> 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.

Exactly. You actually have to fix the problem twice. Which I suppose might mean that at least the person involved will remember not to stuff things up again. But its wasted effort, and twice as many chances of setting something incorrectly.

But my real concern is that you are relying on what I believe to be an undocumented feature (unless you can point me at a bit of doco saying otherwise).

Namely this: we know that in a plain init.ora, if I repeat a parameter two, three or more times discretely through the entire file, it is the last statement of the parameter that gets used.

Your method has an init.ora which calls the spfile. In my mind, that means the spfile is invoked *after* the init.ora. Yet in that special case, it appears explicit parameter settings in the init.ora over-ride those found in the spfile. That is (a) not normal experience of what happens with repeated parameters; (b) is, as I say, undocumented to my knowledge; and therefore (c) liable to be "fixed" in a future release -at which point your suggestion will not continue to work.

I could be wrong on that, of course. I see plenty of references to using an init.ora containing an "SPFILE=/blah/blah/blah" line. But all those references explicitly mention having a one-line init.ora, not that it can itself contain parameters. But I am in speed-reading mode, so I might have missed it...

>> 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?"

I think that sort of confusion is just par for the course when one works with the spfile (which is why I also wish that they'd done the job properly, abolished the init.ora entirely, and shipped a small command-line tool that does nothing but edit spfiles directly).

Since you can issue these commands at any time, it is just part of the business of getting to know and love the spfile that you have to understand that "the two can co-exist, but the spfile takes precedence". That seems to me an awful lot easier to formulate and learn as a piece of dogma than to start mucking around with temporary init.oras at a time of crisis.

But what I deem easier or simpler to learn or remember is besides the point. My real concern is that your method relies on undocumented and in any case non-logical behaviour on Oracle's part... and that your method therefore cannot be relied upon to always work.  

>> 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.

Absolutely agree. I see no reason whatever for restoring an spfile. I'm not entirely sure there's ever a need to back one up, either! The alert log is the backup for the init.ora, and therefore if I lost my spfile, I could extract the last list of 'non-default initialisation parameters' from the alert log, and thus create a replacement init.ora. Create spfile from pfile, and I'm back in business with an spfile.

Of course, that replacement would not contain the latest results from 'alter system' commands... but since they are all listed in plain text in the alert log too, after the set of initialisation parameters you've just swiped, it wouldn't be too hard to 'replay' them.

On the other hand, put like that, I can see why RMAN for one *does* back the thing up!

Cheers,
HJR
>
> Regards
> Holger
Received on Tue Oct 26 2004 - 14:39:19 CDT