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

then you vi the spfile, fix it and save it as a pfile, startup the db w/the pfile and recreate the spfile.

joe

Niall Litchfield wrote:

>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
Received on Wed Jul 16 2003 - 15:19:20 CDT