Timings to Restore SPFILE
At several occasions listed below, you may need to restore it from a previously backed up SPFILE.
- SPFILE was accidentally deleted.
- Startup failed due to incorrect values of initialization parameter.
- Building a standby database from the primary one.
- Duplicating a testing database from another one.
- Restoring a database server from the ground.
That is to say, if you need to restore it, at least one SPFILE backup.
Ways to Restore SPFILE
Since restoring SPFILE is more complicated than backing up SPFILE, we should handle it carefully.
In this post, there're several ways to restore SPFILE whenever you need to do it. We can restore it from a copy, PFILE or RMAN.
Restore SPFILE from a Copy
You have to make sure that SPFILE is not in use before you copy SPFILE back to the original place. Either of the following 2 cases means that SPFILE is not in use.
- Database is idle and stop.
- Database is started by PFILE.
Then we copy it back to the default location of SPFILE.
[oracle@test ~]$ cp -p /home/oracle/spfileORCLCDB.ora $ORACLE_HOME/dbs/spfileORCLCDB.ora
[oracle@test ~]$ echo $?
0
The way is simple and easy, but it's not applicable to RAC databases.
Restore SPFILE from PFILE
Once again, you have to make sure that SPFILE is not in use.
SQL> create spfile from pfile='/home/oracle/initORCLCDB.ora';
File created.
Then we check the file.
[oracle@test ~]$ ll $ORACLE_HOME/dbs/spfileORCLCDB.ora
-rw-r----- 1 oracle oinstall 3584 May 2 21:36 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileORCLCDB.ora
A new SPFILE has been created.
RAC Database
For RAC databases, you have to startup the database to at least NOMOUNT state by PFILE and specify the location of SPFILE. Otherwise, SPFILE falls in DB_UNKNOWN folder.
SQL> startup nomount pfile='/home/oracle/initORCLCDB.ora';
...
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/initORCLCDB.ora';
File created.
We have restored it.
Restore SPFILE by RMAN
There're 2 situations that we should think over if we plan to restore SPFILE by RMAN.
No Existing SPFILE
If there's no existing SPFILE at the original location, we can startup the database to NOMOUNT by force.
RMAN> startup force nomount;
...
RMAN> restore spfile from '/home/oracle/spfile-031r656h_1_1';
...
A new SPFILE will be created.
Please note that, STARTUP FORCE NOMOUNT is equal to STARTUP NOMOUNT FORCE.
Existing SPFILE
If there's an unusable SPFILE at the original location and you can't or wouldn't rename it to another, then you should start the database to NOMOUNT by PFILE, then restore it. Moreover, this method is specifically suitable for RAC databases.
RMAN> startup nomount pfile='/home/oracle/initORCLCDB.ora';
...
RMAN> restore spfile from '/home/oracle/spfile-031r656h_1_1';
...
The SPFILE at the original location will be overwritten.
AUTOBACKUP
If you have AUTOBACKUP configured and enabled, you can restore SPFILE from AUTOBACKUP.
RMAN> restore spfile from autobackup;
...
The instance will search several certain paths for AUTOBACKUP files.