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

[Joe Testa]

oh i forgot its "binary" but not THAT binary.

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>
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.
[EMAIL PROTECTED]
-----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" <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" 
<[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).

   

 

--
Joseph S Testa
Chief Technology Officer 
Data Management Consulting
p: 614-791-9000
f: 614-791-9001

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joe Testa
 INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).