Skip to content

How Oracle Restore SPFILE

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.

  1. Restore SPFILE from a Copy
  2. Restore SPFILE from PFILE
  3. Restore SPFILE by 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 $?

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.


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.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *