Re: 9i, pfiles and spfiles

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!!
--------------------------------------------