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

True... the presence of the spfile doesn't suddenly make parameters which were never system modifiable (as shown in v$parameter) capable of being modified on the fly.

But *usually*, one that is not system modifiable can still be changed with a "scope=spfile" at the end. For example, alter system set sql_trace=TRUE produces an error, but alter system set sql_trace=true scope=spfile doesn't.

I agree that it does appear inconsistent, though. large_pool_size is one it won't work for, whatever scope you put on the command. I suspect that may be a glitch of sorts.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Dave Haas" <davehaas_at_--nospam--hotmail.com> wrote in message
news:dWYI7.5237$HE3.1265588_at_news1.telusplanet.net...


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

> >

> >

>

>