Database Reference
In-Depth Information
Restoring the spfile
You might want to restore a
spfile
for several different reasons:
spfile
that keeps your instance from starting.
•
You accidentally set a value in the
spfile
.
•
You accidentally deleted the
spfile
looked like at some point in time in the past.
One scenario (this has happened to me more than once) is that you're using a
spfile
, and one of the DBAs on
your team does something inexplicable, such as this:
•
You are required to see what the
SQL> alter system set processes=1000000 scope=spfile;
The parameter is changed in the
spfile
on disk, but not in memory. Sometime later, the database is stopped for
some maintenance. When attempting to start the database, you can't even get the instance to start in a nomount state.
This is because a parameter has been set to a ridiculous value that will consume all the memory on the box. In this
scenario the instance may hang, or you may see one or more of the following messages:
ORA-01078: failure in processing system parameters
ORA-00838: Specified value of ... is too small
If you have an RMAN backup available that has a copy of the
spfile
as it was before it was modified, you can
simply restore the
spfile
. If you are using a recovery catalog, here is the procedure for restoring the
spfile
:
$ rman target / catalog rcat/foo@rcat
RMAN> startup nomount;
RMAN> restore spfile;
spfile
.
The approach you take depends on several variables, such as whether you're using an FRA
•
If you're not using a recovery catalog, there are a number of ways to restore your
•
you've configured a channel backup location for the autobackup
•
you're using the default location for autobackups
I'm not going to show every detail of these scenarios. Usually, I determine the location of the backup piece that
contains the backup of the
spfile
and do the restore, like this:
RMAN> startup nomount force;
RMAN> restore spfile to '/tmp/spfile.ora'
from '/u01/O12C/rman/rman_ctl_c-3423216220-20130113-00.bk';
You should see a message such as this:
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
In this example the
spfile
is restored to the
/tmp
directory. Once restored, you can copy the
spfile
to
ORACLE_HOME/dbs
, with the proper name. For my environment (database name:
O12C
) this would
be as follows:
$ cp /tmp/spfile.ora $ORACLE_HOME/dbs/spfileo12c.ora
