Re: spfile vs pfile in 9i

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 27 Oct 2004 15:07:03 +0200
Message-ID: <clo6ho$ras$1@news.BelWue.DE>

Howard J. Rogers wrote:
[...]
> 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).

It's actually documented in the Database reference, when they talk about parameters that take multiple values like ye good ole' rollback_segments.

However, it's more a warning than a documentation:

<quote>
Some parameters, such as ROLLBACK_SEGMENTS, accept multiple value entries. Enter multiple values enclosed in parentheses and separated by commas. For example:

ROLLBACK_SEGMENTS = (SEG1, SEG2, SEG3, SEG4, SEG5) Alternatively, you can enter multiple values without parentheses and commas. For example:

ROLLBACK_SEGMENTS = SEG1 SEG2 SEG3 SEG4 SEG5 Either syntax is valid.

If you enter values for one parameter in multiple entries, then the entries must be on consecutive lines. If they are not, then the first entry will not be processed properly. For example, in the following entry the setting for SEG3 and SEG4 will override the setting for SEG1 and SEG2:

ROLLBACK_SEGMENTS = SEG1 SEG2
OPEN_CURSORS = 10
ROLLBACK_SEGMENTS = SEG3 SEG4
</quote>

Nice wording, eh? "The first entry will not be processed properly. " Because it gets read, set and discarded.

So they don't encourage setting a parameter several times throughout the file, which of course would be a dumb thing to do. So a little later after the above quote we find:

<quote>
Note:

Listing parameters in alphabetical order in the initialization parameter file can help you to find them and can help ensure that each parameter is specified only once. </quote>

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

I'd expect that a line containing an ifile= or spfile= would be followed at that point. I always understood that ifile was not primarily intended to enable a dba to relocate parameterfiles wherever (s)he sees fit, but to create generic and specific parameterfiles, where the specific parameter file includes the generic. And to do so there must be a rule to when a ifile gets included.

Funnily enough, again in the Database Reference where the parameters for pfiles are described we find:

<quote>
Use IFILE to embed another parameter file within the current parameter file. For example:

IFILE = COMMON.ORA You can have up to three levels of nesting. In this example, the file COMMON.ORA could contain a second IFILE parameter for the file COMMON2.ORA, which could contain a third IFILE parameter for the file GCPARMS.ORA. You can also include multiple parameter files in one parameter file by listing IFILE several times with different values:

IFILE = DBPARMS.ORA
IFILE = GCPARMS.ORA
IFILE = LOGPARMS.ORA

Note:
You must list multiple entries on contiguous lines of the parameter file. </quote>

So the same rules apply: if you want to have a parameter with multiple values, then you need to put them on subsequent lines, else they won't be processed properly, i.e. only the last contigous set survives.

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

If you hadn't asked, I wouldn't have started to look ;-). Actually, most of the time the documentation mentions that you can't use the ifile parameter to include a server parameter file, but you must use spfile. So in a way, the same rules that apply to ifile should apply to spfile - even if the developers didn't intended them in the first place.

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

At time of crisis you shouldn't have to start to learn, but be prepared ;-)

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

That's where I'm not so sure. My quotes come from the latest version of Oracle, and by saying what can go wrong when scattering the same parameter several times through the parameter file, I think they say how it is processed. But the exact wording of course leaves room for them to change this behaviour, so your concern is duly appreciated.

>
>

[...]

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

I thought of mentioning this option but decided against it. With intelligent grep'ing you'll even get all alter system ...scope= lines in one go, so that's really not to hard.

>
> On the other hand, put like that, I can see why RMAN for one *does* back the
> thing up!
>
> Cheers,
> HJR
I'd never thought that I could get so passionate about two lines in a text file. Good discussion!

Cheers
Holger Received on Wed Oct 27 2004 - 08:07:03 CDT