Re: spfile vs pfile in 9i

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 24 Oct 2004 00:21:03 +1000
Message-Id: <417a68c4$0$24889$afc38c87@news.optusnet.com.au>

Kenneth Koenraadt wrote:

> On Sat, 23 Oct 2004 05:55:30 +1000, "Howard J. Rogers"
> <hjr_at_dizwell.com> wrote:
>
>

>>In summary: don't over-sell the spfile. It is merely a binary version of
>>the init.ora, and intrinsically has no advantages over the init.ora at
>>all. With one glaring exception: it's new, Oracle is pushing it, and has
>>already developed one key piece of advanced functionality (namely Data
>>Guard) which *demands* the use of an spfile. You can confidently expect
>>there will be others that do the same thing as future versions
>>materialise. Therefore, it's the way of the future and we might as well
>>all get used to it.
>>
>>But functionally, in the terms you have used, they are identical.
>>
>>Regards
>>HJR
>>

>
> One clear advantage there is: Instant validation.

Oh please! Come off it. Try setting your shared pool size to zero. The spfile is quite happy to accept it. That doesn't sound like validation to me.

Point of fact, the spfile does NO validation whatsoever.

> Assume you add a parameter to an init.ora-file (with the vi editor or
> so) and by accident entered a non-displayable sign into it or simple
> entered an invalid value. The validation will only take place at
> startup. With an SPfile, however,
>
> Alter system set <parameter>=<value> scope=spfile
>
> which is functionally equal to changing the init.ora, but Oracle will
> complain immediately if anything wrong.

Right. So you are saying that attempts to "enter an invalid value" will be prohibited when the spfile is involved, are you? (Rhetorical question: you just did, three paragraphs back). Instead of merely asserting something, let's put it to the test shall we?

SQL> alter system set sahred_poolsize=90M scope=spfile; alter system set sahred_poolsize=90M scope=spfile

ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

Right. If you mean that the SQL parse engine spots completely daft typing, I agree with you. Vi would do no such thing. One up to the spfile. Yeah! But let's pursue the matter:

SQL> alter system set shared_pool_size=0 scope=spfile; System altered.

Well, stone the crows. Looks like the spfile's brilliant validation techniques have decided to go on strike. Shurely shome mishtake??! That looks suspiciously like I've just been able to "enter an invalid value" without the spfile complaining about it.

SQL> alter system set db_cache_size=0;
alter system set db_cache_size=0
*
ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero
---------------

Well, here is a totally pathetic attempt to set the size of the current buffer cache to zero (because lacking a scope= clause, my command has attempted to modify the currently-running instance), and the instance is smart enough to work out that it isn't going to be able to do very much without a buffer cache. So it intercepts the attempt to screw things up and stops the attempt dead in its tracks.

SQL> alter system set db_cache_size=0 scope=spfile; System altered.

But stone me if I limit the change to the spfile alone, the oh-so wonderful spfile hasn't got a clue what I'm on about and is quite happy to let a totally illegal, illogical and frankly utterly stupid value be set for it.

Now that's what I call instant validation!

Your point, therefore, is demonstrably not true.

Seriously: the spfile does NO validation. What validation there is, or that you seem to think there is, comes from the fact that 'alter system' commands are treated and parsed as SQL statements, and obvious mis-types are spotted and rejected. But that is hardly worth the effort, really, is it? In terms of validating that what you are setting is legal and valid: no, it doesn't even get close.

HJR
>
>
> - Kenneth Koenraadt
Received on Sat Oct 23 2004 - 09:21:03 CDT