Re: Bad value in SPFILE. . . Database won't come up

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 28 Jan 2004 00:05:38 +1100
Message-ID: <401664dd$0$28869$afc38c87@news.optusnet.com.au>

"Forte Agent" <1443131_at_usenetplanet.com> wrote in message news:s3ic10hjfc9irv8qrsjb8sjdpo4gtbel5m_at_4ax.com...

>

> One of my database is refusing to be started on Windows 2000,
> complaining a parameter value in SP file is invalid. I tried to
> modify the parameter value using "alter system set xxxx=xxxx
> scope=spfile", but of course, it refuses to update it saying database
> is not up. So basically, I am stuck. What should I do to change this
> value in SPFILE so that I can open it? Thank you in advance.

Ah. You have been captured by the terrible spfile Catch-22.

You can't open the database because the spfile is stuffed. You can't edit the spfile without the database being open.

Fortunately, the way out here is that you can export the spfile to a regular old init.ora even when the instance has not been started. Then you can edit the init.ora in nothing more exciting than notepad. Then you can export that back to being an spfile. Then you can start your instance. Prudence dictates deleting the init.ora once your new spfile is working, but you don't have to: when both an init.ora and an spfile exist, the spfile takes precedence by default.

So, to export your bad spfile into an editable text file:

create pfile from spfile

And to re-create the spfile from the edited init.ora,

create spfile from pfile

The commands as presented assume your spfile is stored in the default directory (ORACLE_HOME\database) and is called by its default name of spfile[SID].ora. If either of those is not true, then you have to tell Oracle that:

create pfile from spfile='d:\somewhereodd\weirdname.ora'

Likewise, if you don't qualify the pfile clause, you will get, or be deemed to be working with, a file called init[SID].ora in ORACLE_HOME\database -but you can add a "='somewhere_else' clause to that part of the command, too.

Regards
HJR

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------

Received on Tue Jan 27 2004 - 07:05:38 CST