Re: spfile vs pfile in 9i

Niall Litchfield wrote:

> "Richard Stanton" <richardpstanton_at_yahoo.com> wrote in message
> news:87bc8a5f.0410132342.64e5a5bf_at_posting.google.com...

>> richardpstanton_at_yahoo.com (Richard Stanton) wrote in message
>> news:<87bc8a5f.0410120846.75eeb98e_at_posting.google.com>...
>>> Hello
>>>
>>> can anyone explain to me what the advantages of using spfiles are over
>>> the traditional pfiles?  Is it just the ability to make changes to the
>>> database on the fly without editing files etc?

That is 1000% incorrect. It's burleson-speak (which amounts to the same thing).

The existence of the SPFILE makes not a single parameter more or less dynamically settable than when an init.ora is used.

So, with an spfile to hand, try issuing the command:

alter system set java_pool_size=16M;

...and see what happens [it fails, if you don't have an instance to work with right this second, because the parameter is not dynamic. Even though an spfile is being used].

Or, with an init.ora to hand, try issuing the command:

alter system set shared_pool_size=64M;

[That one works -yet it is a boring old init.ora that is being used].

If you meant to say that the existence of the spfile means you don't have to remember to manually edit a file to make the dynamic changes persist across instance startups, then say so. But try not to imply that dynamic changes are now possible with one that aren't with the other, because that is simply not true.

> ability to control multiple instances from one file.

You can do that with an init.ora. The init.ora is perfectly capable of using the "*." notation which the spfile introduced, and therefore multiple instances can be controlled just fine with one.

The real issue there is in RAC, where you indeed have multiple instances -and the one configuration file for all of them needs to be visible to all of them. Traditionally, that would have meant putting an init.ora on a raw partition on the shared drive. But text files on raw is a bit of a no-no. So a binary equivalent was invented that could be placed on raw.

Then Oracle and everyone else came out with cluster file systems, and the whole issue was rendered moot!

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 Received on Fri Oct 22 2004 - 14:55:30 CDT