You might want to restore a spfile for several different reasons:
- You accidentally set a value in the spfile that keeps your instance from starting.
- You accidentally deleted the spfile.
- You are required to see what 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:
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;
- If you’re not using a recovery catalog, there are a number of ways to restore your spfile. The approach you take depends on several variables, such as whether you’re using an FRA
- 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 completeIn 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
Note For a complete description of all possible spfile and control file restore scenarios, see RMAN Recipes for Oracle Database 12c.
