Re: 9i, pfiles and spfiles

Glen A Stromquist wrote:

> Hi Howard - glad I could help!

I'm sure you just did all of this so that I could learn, didn't you ;-)

> Fixing it was no problem, by the end of the day yesterday I'd figured
> out that compatible=9.2 was needed because of my system tablespace and
> it was starting ok, so this morning just re-created an spfile from it
> and all's ok, although I'm still reading to find out why I want to do
> this over the old init file.

See below.

>
> I was using Kedit when I edited the original spfile, which must have
> changed it to a text file when saved. At least now I know that a "*." in
> front of a parameter does not affect the startup.

True. And it will work just fine if, in an init.ora, you leave the * out as well. "*" just means 'all instances'. So does a lack of a "*". And that betrays the real reason why the spfile was invented: RAC. Because in a RAC you might want one instance to have an 80M shared pool, and the other to have 120M. So how do you distinguish between the two settings? You have:

INST1.shared_pool_size=80M and
INST2.shared_pool_size=120M

The the right settings apply to the right instance. By the way, the command to set that up in an spfile would be:

alter system set shared_pool_size=80M scope=spfile sid='INST1'; and alter system set shared_pool_size=120M scope=spfile sid='INST2';

By default, the sid clause will be set to "*", so that by default any alter system that affects the spfile will apply to all instances in the RAC.

By the way, the instance.parameter notation works perfectly well with a boring old init.ora, too. But the init.ora, being a mere text file, can't be dropped onto a raw partition... and unless you have gone to the expense of putting a cluster file system onto your shared disk (or taken the slightly brave step of using the freebie CFSes that Oracle have produced for Linux and Windows) then that must mean you can't have one init.ora that can be used to control the entire set of instances comprising your RAC. Hence the need for a binary version of the init.ora, which can indeed be dropped onto a raw partition.

The other reason for Oracle switching to an spfile is that precisely because it's binary, the only thing that can edit it is your server process. And that's good, because it means the file is immune from the stupidity of users who think that including comments, explanations and change history within the init.ora is a good idea... it actually just results in an enormous mess which no computer system could hope to manage properly. So a binary version means that managing the file and, the important bit, making changes to it *automatically* becomes feasible. And in 9iR2 there's at least one bit of functionality that does precisely that: Data Guard. When you suddenly decide that you want one of your standby databases to be updated at a lag of 30 minutes instead of the current 15, that actually means 'change my LOG_ARCHIVE_DEST_3 parameter to include a DELAY clause'... but by talking to DMON (the Data Guard Broker) to request the change, you actually get DMON to modify the spfile for you, so you don't have to worry about *how* to implement the change, merely request *what* you want to change. So if you want Data Guard in 9iR2, you absolutely have to have an spfile.

And that's a long-winded way of saying, the spfile will become more and more important to Oracle as a way of hiding from the user *how* to do things, and thus allow him or her to concentrate more of what it is they want done. If you want a self-tuning database, too, you're going to have to have this 'I will automate the configuration changes needed to secure a particular outcome' facility.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------