And I'd like to add mine. There are a lot of questions that TFM does not
answer. I've been trying to decide whether the spfile was boon or bane
and found your experiences helpful.
fdp
Fred Pierce (DNRC)
Avialantic.com fpierce_at_avialantic.com
www.avialantic.com
Randy Nichols wrote:
> My thanks also.
>
> "Keld Nielsen" <keldnielsen_at_tiscali.you.know.dk> wrote in message
> news:3f317cfc$0$32465$edfadb0f_at_dread16.news.tele.dk...
>
>>"quarkman" <quarkman_at_myrealbox.com> wrote in message
>>news:oprth4fen2zkogxn_at_haydn...
>>
>>>On Wed, 06 Aug 2003 16:50:26 GMT, Randy Nichols <randynichols_at_yahoo.com>
>>>wrote:
>>>
>>>
>>>>We are running Oracle 9.2.0.3 under Windows 2003, Windows 2000, and
>>>>Windows
>>>>NT4 (and starting to run under Linux).
>>>>
>>>>Literature always refers to the "init.ora" initialization file. The
>>>
>>file
>>
>>>>on
>>>>my NT4 installation is named "init.ora.37200311431".
>>>>
>>>>My guess is this may have something to do with using a "Server
>>>
> Parameter
>
>>>>File" (SPFILE). Does the fact that I do not have a simple "init.ora"
>>>>file
>>>>name mean that my startup parameters are being set from an SPFILE?
>>>>
>>>>If I change something in the "init.ora.37200311431", will it be
>>>
> ignored?
>
>>>>Is there an advantage to using a SPFILE?
>>>>
>>>>If I am in fact using an SPFILE, how does one change/maintain it?
>>>>
>>>>How can I go back to using a simple "init.ora" file to initialize the
>>>>database?
>>>>
>>>>Thanks,
>>>>
>>>>-Randy Nichols
>>>>
>>>>
>>>
>>>
>>>By default, the initSID.ora is stored in ORACLE_HOME/dbs (or
>>>ORACLE_HOME\database on Windows). But you will also find that the
>>
> database
>
>>>creation assistant sets up an ORADATA\ADMIN directory under ORACLE_BASE
>>>(usually C:\oracle on Windows platforms). In there, you'll find a PFILE
>>>directory, and in there, you'll find the *real* initSID.ora. The one in
>>>ORACLE_HOME\database merely has a line in it saying where to find the
>>
>>other
>>
>>>one (IFILE=C:\oracle\oradata\admin\file\initxxx.ora).
>>>
>>>So editing the one with numbers after it is not going to help you at
>>
> all.
>
>>>If there's an SPFILE, it also has to live in ORACLE_HOME\database, but
>>
> you
>
>>>can again move it anywhere so long as you leave a regular init.ora
>>
> behind
>
>>>that has one line in it: SPFILE=C:\somwhere\somewherelse\spfilexxx.ora.
>>>
>>>There are some advantages to using an SPFILE, and some known
>>
>>disadvantages.
>>
>>>The advantages are that when you modify a dynamic parameter (alter
>>
> system
>
>>>set shared_pool_size=120M), that implicitly not only changes the
>>
> currently
>
>>>running instance, but modifies the spfile setting as well, so the next
>>
>>time
>>
>>>you startup, the new setting is already in place. In the dark old days
>>>before 9i, achieving both things would have involved issuing the alter
>>>system command to get the currently-running instance right, and *then*
>>>remembering to edit your init.ora yourself (and yes, I realise that
>>
> before
>
>>>9i, you couldn't actually modify the shared_pool_size dynamically).
>>>
>>>Big deal, as far as I'm concerned: it's shifted the burden in other
>>>respects. 'Alter system' commands used to have no lasting effect. Now,
>>
> by
>
>>>default, they do. So if I want to make just a temporary change to the
>>>currently running instance, I have to remember to append the
>>
>>'SCOPE=MEMORY'
>>
>>>clause to the alter system command so it doesn't try and edit my spfile.
>>>
>>>Then there are the bugs: try changing the DISPATCHERS= parameter with an
>>>alter system command, with no SCOPE clause. It won't work, because you
>>>can't, for some reason, edit that parameter in the spfile. At all.
>>>
>>>Then there are the quirks. Try 'alter system set shared_pool_size=0
>>>scope=spfile'. Zero is, of course, an utterly illegal value, but the
>>
>>spfile
>>
>>>is merrily altered anyway.
>>>
>>>Then there's the rigmarole of correcting such quirks, or modifying your
>>>DISPATCHERS parameter: create pfile from spfile. Edit the traditional
>>>init.ora that results. Then create spfile from pfile. Then startup.
>>
>>Strikes
>>
>>>me it was easier just to: edit init.ora, startup.
>>>
>>>There are two occasions when you *have* to use an spfile: Data Guard
>>>requires it (standby databases). And so does RAC *if* you are using raw
>>>devices and want to have a common configuration file (because you can't
>>
>>put
>>
>>>a text file init.ora onto a raw partition).
>>>
>>>And there is one major plus point in the spfile's favour: there's only
>>
>>ever
>>
>>>one of them. If you have a team of DBAs, each managing the database from
>>>their own PCs using Enterprise Manager, each DBA has a local copy of the
>>>init.ora. So which one is the 'right' one? With the SPFILE, everything
>>>stays on the server end, and there's no confusion about what file is the
>>>definitive one.
>>>
>>>Like anything else, therefore, it's a tool which you may or may not find
>>>helpful or a hinderance, and which has benefits as well as costs.
>>>Personally, unless I'm working with Data Guard, I never have one (most
>>
> RAC
>
>>>implementations these days could use a cluster file system, onto which
>>
> an
>
>>>init.ora *can* be placed for common-file configuration). But it's, as
>>
>>ever,
>>
>>>an 'all depends' situation at the moment. Give it enough time, though,
>>
> and
>
>>>I expect it will be the only way to do things.
>>>
>>>~QM
>>
>>I would like to thank you for a very good and helpful explanation !
>>
>>
>>
>
>
>
Received on Mon Aug 11 2003 - 08:13:56 CDT
