| 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 ?
This can be solved by creating the PFILE again from the SPFILE; and this
doesn't need the instance to be up. However, you did drive the nail home,
Niall, with this example.
Part of the problem could be avoided by restricting the ALTER SYSTEM, SYSDBA or SYSOPER privileges; but in a large shop with a lot of production support DBAS....!! Arup Nanda
> Not only that but this sort of thing should be avoidable.
>
> SQL> alter system set db_cache_size=10m scope=both;
>
> System altered.
>
> <time passes>
>
> SQL> alter system set db_block_buffers=1000 scope=spfile;
>
> System altered.
>
> SQL> shutdown;
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SQL> startup
> ORA-00381: cannot use both new and old parameters for buffer cache size
> specific
> ation
> SQL>
>
>
> What do you do if you didn't have an old fashioned text file? I'd also
> like to know how spfile changes get documented, it scares me enough that
> someone can change parameters on the fly, let alone that that can be
> done without comment or recording who did it. I'll admit thugh that this
> last is actually a failure of management and not technology.
>
> Niall
> > -----Original Message-----
> > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> > Behalf Of Arup Nanda
> > Sent: 16 July 2003 06:09
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: How to make SPFILE in sync with INIT.ORA ?
> >
> >
> > 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
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, July 16, 2003 12:24 AM
> >
> >
> > > 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
> > > 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
Received on Wed Jul 16 2003 - 16:01:13 CDT
 |
 |