Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!newsfeed.telusplanet.net!newsfeed2.telusplanet.net!newsfeed.telus.net!edtnps84.POSTED!not-for-mail From: Glen A Stromquist User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: 9i, pfiles and spfiles References: <3f85ea99$0$28118$afc38c87@news.optusnet.com.au> <1Eqhb.4476$XS4.953@edtnps84> <3f866097$1$30614$afc38c87@news.optusnet.com.au> In-Reply-To: <3f866097$1$30614$afc38c87@news.optusnet.com.au> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 86 Message-ID: Date: Fri, 10 Oct 2003 15:17:51 GMT NNTP-Posting-Host: 216.123.238.50 X-Trace: edtnps84 1065799071 216.123.238.50 (Fri, 10 Oct 2003 09:17:51 MDT) NNTP-Posting-Date: Fri, 10 Oct 2003 09:17:51 MDT Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:245028 Howard J. Rogers wrote: > 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 Hi Howard - glad I could help! Fixing it was no problem, by the end of the day yesterday I'd figured out that compatible=9.2 was needed because of my system tablespace and it was starting ok, so this morning just re-created an spfile from it and all's ok, although I'm still reading to find out why I want to do this over the old init file. I was using Kedit when I edited the original spfile, which must have changed it to a text file when saved. At least now I know that a "*." in front of a parameter does not affect the startup. And not only can you start with a text-version init.ora thats named to look like an spfile, it'll start fine when the contents "look" like an spfile as well...