Re: spfile vs pfile in 9i

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 27 Oct 2004 00:27:13 +1000
Message-Id: <417e5eb3$0$22806$afc38c87@news.optusnet.com.au>

Niall Litchfield wrote:

> Howard J. Rogers wrote:

>> 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?
>> >
>> > dynamic changes without restart is one.
>>
>> That is 1000% incorrect. It's burleson-speak (which amounts to the

> same

>> thing).

>
> ouch!
>
>

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

> <snip>

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

>
> That was what I had in mind. You are correct however that I didn't say
> it, and that what I did say was incorrect and misleading for which I
> apologize. I also had in mind that Oracle itself utilizes the spfile
> (specifically) for (at least) DataGuard and is likely to do so more as
> time goes on.
>

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

>
> That I didn't know. Thank you.
>
>

>> 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.
>
> I'm not a fan of the spfile. I cannot understand why it isn't text
> only, indeed it would seem to be the sort of thing that xml was
> invented for. I do hope my post wasn't a sales pitch saying you must
> use it.
>
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com

Do you know that I didn't read who the original post was coming from? Had I realised it was from you, Niall, I (maybe) wouldn't have used the "Burlesquash (TM)" tone I did. I suppose I am sort-of proud that I treat friend and foe alike, purely on the content of posts. But had I realised who had written the original, I would certainly not have wished to imply you were selling the spfile to anyone -which, nevertheles, was not what I had in mind at all. My apologies if you think I did, though. And I might have been kinder about the Burlesonian comparisons. Sorry about that one...

As for why the spfile isn't XML or text: just bear in mind the real reason why it was invented. Four years ago, there were very few viable cluster file systems, so if you wanted a RAC that would have meant using raw partitions. And you can't put a text file down on a raw partition. You have to use a binary file. And that's why the spfile was invented.

All this guff about "instant validation" and "dynamic parameters" that waft from time to time from certain quarters is missing the point entirely: those are merely side-effects of the fact that three/four years ago, if you wanted RAC, you used raw; if you used raw, you couldn't share a text-based init.ora; if you share a binary spfile, you need a tool to edit said binary file; and the tool that edits Oracle binary files best, and has done so for years, is known as an instance. As soon as you have the instance editing the file, you have parsing of the necessary alter system commands (hence the "instance validation" myth); and as soon as you have alter system commands you have the 'every parameter is dynamic' myth.

So that's where the spfile came from. But as you say, and it's an important point, where it started from and where it is headed are two different things: Data Guard in 9i Release 2 is only the first of many key features I would expect to start making use of the fact that a binary file controlled by the instance is capable of dynamic and automatic modification. Other key functionality is therefore bound to start making the spfile compulsory.

I disliked the spfile intensely when it first came out. I still wish it would do true validation, and notice when stupid values are set for parameters. But apart from that, I have reluctantly come to the conclusion that it *is* the way of the future, and we might as well get used to it. And if its stops people putting a change history into what should be a purely functional file, I'll be happy.

OK. You can come out from behind the sofa now. The scary man has gone.

Regards and apologies once more,
HJR Received on Tue Oct 26 2004 - 09:27:13 CDT