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

Giganews Newsgroups
Subject: Re: Bad value in SPFILE. . . Database won't come up
Posted by:  Howard J. Rogers (h…
Date: Wed, 28 Jan 2004

"Forte Agent" <14431…> wrote in message
> 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.


Oracle insights


In response to

Bad value in SPFILE. . . Database won't come up posted by Forte Agent on Tue, 27 Jan 2004