Re: 9i, pfiles and spfiles

I agree that this is a nice mess of an spfile but I think they are a big improvement. Why ? The syntax for alter system allows changes to be made to the file, to the running instance or to both (subject to some limitiations - some parameters only take effect on restart so can't be made to a running instance or to "both". Previously for the both option you have to issue the alter session command and remember to update the init.ora just how you wanted it. Since spotting the SCOPE= option I've become a fan of the spfile and init.ora can go rot :-). Previous to that I couldn't see the point - too ingrained in my Ora 8i ways - that'll teach me to RTFM.

Andy

P.S. To save a file in vi use :w or :x to save and exit just like it's always been ;-O VI rules :-)

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3f866097$1$30614$afc38c87_at_news.optusnet.com.au...
> Glen A Stromquist wrote:
>
> snipped
>
>
> > "away it goes" meant that it started fine when I again pointed at the
> > spfile, which shouldnt work, right?
>
>
> Correct. Because you *can't* point startup directly at an spfile. For
> example, when I try what you go on to report you did (and I have a genuine
> spfile) then I get this:
>
>
> SQL> startup pfile=$ORACLE_HOME/dbs/spfilelx92
> LRM-00123: invalid character 0 found in the input file
> ORA-01078: failure in processing system parameters
>
> It's expecting a text file; you feed it a binary spfile; it vomits to
death.
>
> > yes, I get it now, the mystery to me was why did it start when I pointed
> > it to the spfile (shouldnt have) but wouldnt start just by issuing
> > "startup" as it would look for this same file.
>
> The fact that you said pfile=spfile.ora (or words to that effect) at
startup
> means that your spfile is *not* an spfile, but merely a boring old text
> file which happens to have a name which makes it *look* like an spfile.
> (See above)
>
> Let me guess: when you edited the spfile to add in your three parameters,
> you used vi? Or some other text editor? Therefore, when you saved (or
> whatever weird combination of characters it is to save something in vi
> these days!) what had been a *binary* spfile suddenly became a text file
of
> the same name.
>
> So *that's* why startup pfile=spfile.ora works, because you now do in fact
> have a regular init.ora. It's just called something rather unusual. But
> that's fine, because you can call your init.ora
> fredandmargaretathomeonthursday.com if you really had a mind to. So long
as
> you specify the right filename with the pfile= clause, it will work.
>
> But when you just issue startup, it doesn't work. Why? Because you don't
> *really* have an spfile, and you don't have an init.ora that's called
> initSID.ora either. So you therefore fit none of the default patterns of
> startup behaviour, and the thing keels over.
>
> So, the quick fix to this mess is simply this. Rename what is currently
> called spfileSID.ora to be initSID.ora. Then issue the startup command on
> its own... I'll lay odds it will now startup just fine. With that done,
> just issue the command "create spfile from pfile", and you'll have a new
> file created called spfileSID.ora which is really a genuine, binary
spfile.
>
> And it's precisely because of this utter chaos that can ensue that I'm not
a
> wild fan of the spfile. But it is the way of the future, and I suppose we
> had better just get on with it.
>
> By the way, Glen: don't take this the wrong way, but this is one of the
best
> examples of a stuff-up with the spfile that I've come across. It never
> occurred to me to test what happens when you have a text-version init.ora
> that's named to look like an spfile. So congratulations: you saved me the
> bother of finding out myself the hard way!!
>
> Best of luck fixing it:
> HJR
> --
> --------------------------------------------
> See my brand new website, soon to be full of
> new articles: www.dizwell.com.
> Nothing much there yet, but give it time!!
> --------------------------------------------
>
Received on Fri Oct 10 2003 - 09:55:29 CDT