Re: spfile vs pfile in 9i

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 28 Oct 2004 05:19:28 +1000
Message-Id: <417ff4af$0$21974$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

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

I don't want to necessarily prolong discussion about a two-line text file, as you put it, but that wasn't the 'undocumented' thing I was talking about. The business about repeating parameters, and only having the last repeat take effect; or about listing parameters multiple times consecutively and having the multiple values concatenated; none of that is new, and has been around and well-documented since at least version 8.0.

The undocumented feature I'm worried about is: if you have an init.ora that contains SPFILE=/somewhere AND a "shared_pool_size=80M", according to you that means the 80M setting for the shared pool will over-ride whatever the value is in the spfile.

I can find no documentation on that behaviour. And it is, to my mind, in contradiction to the extensively-quoted documentation regarding what happens when a single parameters is set multiple times.

If S_P_S is set once in the init.ora and once in the spfile, I would expect the spfile's setting to take precedence over the init.ora's, based on that documentation. Because, to my way of thinking, the init.ora is read as a whole, and only then is the spfile read and applied.

Or does it depend on the ordering of the SPFILE= and the S_P_S parameters in the init.ora? Because that is even worse, if so!

When I wake up properly, I'll play around with it and find out for myself, I guess.

Regards
HJR Received on Wed Oct 27 2004 - 14:19:28 CDT