Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Has anyone tried 'persistent initialization parameters' feature in 9i?

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

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Fri, 16 Nov 2001 07:13:58 +1100
Message-ID: <3bf42228$0$382$afc38c87@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 - 14:13:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US