Re: init.ora.37200311431 and SPFILEs

"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 Wed Aug 06 2003 - 17:08:37 CDT