Thanks for the explanation. I know that spfile is in binary format and
is used by Oracle engine, but don't know how it works. Your example
hits the top of the nail.
-Alan
"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 Fri Nov 16 2001 - 11:20:42 CST
