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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to make SPFILE in sync with INIT.ORA ?

Re: How to make SPFILE in sync with INIT.ORA ?

From: Arup Nanda <orarup_at_hotmail.com>
Date: Wed, 16 Jul 2003 00:12:43 -0400
Message-Id: <25929.337933@fatcity.com> 





Dennis,



>    My understanding is that Oracle felt if it was going to be a 24x7x365

> database, it had to have more parameters that could be changed online




I couldn't agree more. 9i had more parameters that can be changed online;
hope there are more in line.



> it doesn't make sense to rely on the DBA

> to remember to also update the init.ora file. You should be able to just

> make the change once.




Absolutely. That's the point I was trying to raise. Oracle did provide the
spfile route to make _persistent_ changes on the fly, but for only those
parameters that can be changed through alter system set ... The problem was
there is a different file that needs to be updated when making those other
parameter changes. There should be only one place to change - hopefully
spfile - either through the alter system command or othe editing. This
editing could be done through a GUI interface, too, if needed; although,
call me old-fashioned (and I am old, anyway, at least in IT timeline),
personally I prefer the vi editor. And this is not an impossible idea. I
gave the example of the listener.ora file. It can be edited (so old
fashioned), through the lsntctl SAVE_CONFIG command (so spfile-like) or
through Net Assistant (so GUI-friendly). The end result is the same - one
file - regardless of how you modify a parameter. The same approach could
have been done in pfile-spfile case.



>  I think with the pfile and spfile, Oracle was trying to give us the best

> of both worlds.




Hmm! You think so? I think they just gave us a half-cooked hair-brained
split-pea honey-mustard-ketchup-coated germ of a solution - sort of like
Oracle 6 _without_ the Transaction Processing Option, if anyone remembers
that. After you wipe off all those condiments, you find a Dr. Jekyll & Mr.
Hyde parameter file(s)!



Regards,



Arup







> Arup

>    My understanding is that Oracle felt if it was going to be a 24x7x365

> database, it had to have more parameters that could be changed online.

Some


> parameters were made changeable in Oracle9i, with hints of more to come.

>    If the future is online changes, it doesn't make sense to rely on the

DBA



> to remember to also update the init.ora file. You should be able to just

> make the change once.

>    Also, there is a new generation of I.S. people coming along that

doesn't


> think you can operate a computer without a mouse. Against Microsoft's

super


> GUI interfaces, the idea of manually editing a text configuration file

seems


> very last century.

>    I think with the pfile and spfile, Oracle was trying to give us the

best


> of both worlds. How well they succeeded is a matter of judgement.

>

> Dennis Williams

> DBA


> Lifetouch, Inc.

> dwilliams_at_lifetouch.com

>

> -----Original Message-----

> Sent: Tuesday, July 15, 2003 9:14 PM

> To: Multiple recipients of list ORACLE-L

>

>

> The ability to change the system parameters without bouncing the system is

> not provided by spfile, neither in RAC nor single instance databas. It

> depends upon the the parameter that can be changed dynamically or not.

>

> I guess you wanted to convey the impression that the using spfiles the

> parameters can be changed and the changes can be persistent across

> shutdowns. But that is not just in RAC; it's true for single instance DBs,

> too.

>

> Now, suppose you want to set a parameter that can't be changed using ALTER

> SYSTEM, such as, say, java_pool_size. How do you plan to make the change?

> You have to open up the old favorite init.ora file and start the database

> with pfile=init.ora option. At that stage the spfile is not active and

your


> issuing Alter system set db_cache_size = 800m scope = [ memory | spfile |

> both ] sid = * has no effect. You must create the spfile from the pfile

and


> then use the newly created spfile to use this "dynamic" parameter

> persistent. Note the complexity involved - spfile allowed you to make the

> changes to some parmeters using alter system persistent; but for all other

> parametrs you are forced to use pfile. What happens if you ommit the

> pfile=init.ora clause? The database will pickup the spfile, which will

_not_


> have your changes.

>

> To fully appreciate the value of the spfile parameter, Oracle should have

> allowed editing spfile directly and completely done away with pfile.

> Splitting functionality across two different implementations adds to

> difficulties, does not resolve them. I hope future versions of Oracle do

> offer that functionality. It's not that difficult or unheard of, either.

> Listener.ora, tnsnames.ora are all editable and also read by Net

Assistant.


>

> just my .02

>

> Arup Nanda

>

>

> ----- Original Message -----

> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>

> Sent: Tuesday, July 15, 2003 8:09 PM

>

>

> > Spfile is shared and can be modified dynamically without bouncing the

> > instances.

> >

> > Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile |

> both ]

> > sid = *

> >

> > Without spfile, you can still make this change dynamically in memory,

but


> > you would have to manually update init.ora file to reflect your change.

> > Spfile allows you to automatically capture these dynamic changes.

> >

> > Gerardo

> >

> > -----Original Message-----

> > Sent: Tuesday, July 15, 2003 4:45 PM

> > To: Multiple recipients of list ORACLE-L

> >

> >

> > And how, exactly?

> >

> > ----- Original Message -----

> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>

> > Sent: Tuesday, July 15, 2003 7:29 PM

> >

> >

> > > You'll appreciate spfiles if you're using RAC.

> > >

> > > -----Original Message-----

> > > Sent: Tuesday, July 15, 2003 2:09 PM

> > > To: Multiple recipients of list ORACLE-L

> > >

> > >

> > > And create pfile from spfile; does a similar job.

> > >

> > > I'm rapidly beginning to think that spfiles are just not worth it. Now

> > > how to reverse the policy decision having mandated them for 9i

> > > installs :(

> > >

> > > Niall

> > >

> > > > -----Original Message-----

> > > > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf

> > > > Of Arup Nanda

> > > > Sent: 15 July 2003 04:44

> > > > To: Multiple recipients of list ORACLE-L

> > > > Subject: Re: How to make SPFILE in sync with INIT.ORA ?

> > > >

> > > >

> > > > As user sys, issue

> > > >

> > > > CREATE SPFILE FROM PFILE;


> > > >

> > > > This will create the spfile. You must have started the database

> > > > using the pfile to use this command.

> > > >

> > > > HTH.


> > > >

> > > > Arup Nanda

> > > > ----- Original Message -----

> > > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>

> > > > Sent: Monday, July 14, 2003 10:59 PM

> > > >

> > > >

> > > > > Guys,

> > > > >

> > > > > i have a 9iR2/win2k test instance.

> > > > > i just renamed CONTROL01.CTL once and tried to start the instance.

> > > > >

> > > > > SQL>startup

> > > > > But it gave a ORA-00205 error.

> > > > >

> > > > > so i removed CONTROL01.CTL  from INIT.ORA file and started the

> > > > > instance

> > > > > SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'

> > > > > Now it worked fine.

> > > > >

> > > > > BUT:


> > > > > SQL>startup

> > > > > This is not possible because the SPFILE still has

> > > > CONTROL01.CTL in it.

> > > > >

> > > > > How do i make my SPFILE in sync with the INIT.ORA ?

> > > > >

> > > > > Regards,

> > > > > Jp.

> > > > >

> > > > >

> > > > >

> > > > > --

> > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > > > > --

> > > > > Author: Prem Khanna J

> > > > >   INET: jprem_at_kssnet.co.jp

> > > > >

> > > > > Fat City Network Services    -- 858-538-5051

http://www.fatcity.com


> > > > > San Diego, California        -- Mailing list and web

> > > > hosting services

> > > > >

> > > > --------------------------------------------------------------------

> > > > -

> > > > > To REMOVE yourself from this mailing list, send an E-Mail message

> > > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and

> > > > > in the message BODY, include a line containing: UNSUB ORACLE-L (or

> > > > > the name of mailing list you want to be removed from).  You may

> > > > also send

> > > > > the HELP command for other information (like subscribing).

> > > > >

> > > > --

> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > > > --

> > > > Author: Arup Nanda

> > > >   INET: orarup_at_hotmail.com

> > > >

> > > > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> > > > San Diego, California        -- Mailing list and web hosting

services


> > > > --------------------------------------------------------------------

> > > > -

> > > > To REMOVE yourself from this mailing list, send an E-Mail message

> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')

> > > > and in the message BODY, include a line containing: UNSUB

> > > > ORACLE-L (or the name of mailing list you want to be removed

> > > > from).  You may also send the HELP command for other

> > > > information (like subscribing).

> > > >

> > >

> > > --

> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > > --

> > > Author: Niall Litchfield

> > >   INET: niall.litchfield_at_dial.pipex.com

> > >

> > > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> > > San Diego, California        -- Mailing list and web hosting services

> > > ---------------------------------------------------------------------

> > > To REMOVE yourself from this mailing list, send an E-Mail message

> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> > > the message BODY, include a line containing: UNSUB ORACLE-L (or the

> > > name of mailing list you want to be removed from).  You may also send

> > > the HELP command for other information (like subscribing).

> > > --

> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > > --

> > > Author: Molina, Gerardo

> > >   INET: Gerardo.Molina_at_schwab.com

> > >

> > > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> > > San Diego, California        -- Mailing list and web hosting services

> > > ---------------------------------------------------------------------

> > > To REMOVE yourself from this mailing list, send an E-Mail message

> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> > > the message BODY, include a line containing: UNSUB ORACLE-L (or the

> > > name of mailing list you want to be removed from).  You may also send

> > > the HELP command for other information (like subscribing).

> > >

> > --

> > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > --

> > Author: Arup Nanda

> >   INET: orarup_at_hotmail.com

> >

> > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> > San Diego, California        -- Mailing list and web hosting services

> > ---------------------------------------------------------------------

> > To REMOVE yourself from this mailing list, send an E-Mail message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the

> > message BODY, include a line containing: UNSUB ORACLE-L (or the name of

> > mailing list you want to be removed from).  You may also send the HELP

> > command for other information (like subscribing).

> > --

> > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > --

> > Author: Molina, Gerardo

> >   INET: Gerardo.Molina_at_schwab.com

> >

> > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> > San Diego, California        -- Mailing list and web hosting services

> > ---------------------------------------------------------------------

> > To REMOVE yourself from this mailing list, send an E-Mail message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> > the message BODY, include a line containing: UNSUB ORACLE-L

> > (or the name of mailing list you want to be removed from).  You may

> > also send the HELP command for other information (like subscribing).

> >

> >

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> --

> Author: Arup Nanda

>   INET: orarup_at_hotmail.com

>

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> --

> Author: DENNIS WILLIAMS

>   INET: DWILLIAMS_at_LIFETOUCH.COM


>

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

Received on Tue Jul 15 2003 - 23:12:43 CDT












Original text of this message













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