Re: Has anyone tried 'persistent initialization parameters' feature in 9i?

Hi all.

I'd only like to add one note to Howard's excellent explanation. Be careful in that some of the parameters are settable in the spfile and some of them are not. I was playing with it and noticed that shared pool can be altered on the fly (and the spfile updated), but large pool can't.

Dave Haas

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:3bf42228$0$382$afc38c87_at_news.optusnet.com.au...
> Not sure how one goes about explaining how it works. The spfile, though,
is
> nothing like the init.ora. Sure, it contains all the parameters, just as
it
> always did, but it's a *binary* file, not a text file. Which means the
only
> person that can edit its contents is Oracle itself.
>
> When you therefore issue any 'alter system' command, there is now
implicitly
> a question you have to answer: is the setting you are changing only going
> to be changed for the lifetime of this Instance, or are you trying to
change
> it only for future Instances, or do you want both -this Instance changed,
> and all future Instances to use the new setting, too.
>
> Hence, a traditional favourite like 'alter system set
> shared_pool_size=34000000' now has additional components: it can submitted
> as 'alter system set shared_pool_size=34000000 scope=memory' (this
onstance
> only); 'alter system set shared_pool_size=34000000 scope=spfile' (future
> instances only) or (and this is the default) 'alter system set
> shared_pool_size=34000000 scope=both'.
>
> With either of the last two options, Oracle edits the spfile and adjusts
the
> relevant parameter. That way, when you next startup, and the spfile is
> read, the changed values are picked up and applied to the new Instance.
> It's this that makes the changes 'persistent' across startups.
>
> When starting up, Oracle looks for an spfile first in the default
location;
> if it can't find one, it looks for an init.ora in the default location.
> That is, of course, if you just say 'startup'. If you include the
> 'pfile=xxx' with that command, then Oracle obviously doesn't use the
spfile
> at all, but the old-fashioned init.ora that you've explicitly requested.
> There is NO command to allow an spfile to be specified at the startup
> command which resides in a non-default location. However, you can get
> around that restriction by storing your spfile somewhere weird (say
> 'x:\blah') and then having a traditional init.ora containing just one
> parameter -spfile=x:\blah\spfileT54.ora. Then you can "startup
> pfile=Z:\oracle\init.ora", and your spfile will still get used.
>
> The key features, in short, are that it's a binary file that Oracle
> maintains. It's used in preference to an init.ora if there is one. And
it
> must reside on the server side.
>
> I wrote a large set of notes on this as well as other 9i new features, and
> they're available from the 'books' link on my site.
>
> Regards
> HJR
> --
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "Alan" <desertflowerln_at_yahoo.com> wrote in message
> news:b1217045.0111151112.fad5bb8_at_posting.google.com...
> > The spfile.ora is brand new in 9i. spfile is similar to pfile. Oracle
> > claims this will help DBAs to maintain database parameter changes
> > during shutdown/startup, and can startup db without using local copy
> > of init.ora file. Can anyone explain how this works, and when/how to
> > use spfile exactly?
>
>
Received on Thu Nov 15 2001 - 18:17:13 CST