| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to make SPFILE in sync with INIT.ORA ?
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
> 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
Received on Tue Jul 15 2003 - 20:15:49 CDT
 |
 |