We all are aware of spfile and pfile in Oracle.  Oracle requires an initialization file to define the attributes and characteristics of the starting instance and the connecting sessions there of. We are aware of the Initialization parameter file (also referred as init.ora file or PFILE). This file holds the setup parameters that define the attribute of the instance that is being started. Administrators can control, tune and optimize an instance by setting and modifying the initialization parameters in this file.

Some parameters can be dynamically modified to affect the present instance, while others require the instance to be brought down so that changes can take effect. This remains the same when using PFILE or SPFILE. A simple search on the net will reveal a lot of information regarding PFILE and SPFILE.

There are 2 types of parameter files, namely :
1. pfile (parameter file) – older way [ not recommended by Oracle ]
2. spfile (server parameter file) – newer way [ recommended by oracle ]

spfile was introduced starting from Oracle 9i, until that time text-based pfile was used to store database initialization parameters.

What happens if someone has deleted your spfile or you have modified it in depth that you are now unable to even startup your database.
Your only option is to restore your spfile and this step is easy if you have not forgotten to set up your RMAN environment to use the AUTOBACKUP feature:

Because I’m using the flash recovery area, this setup will use it as default location where to save the controlfile and spfile.
Every time you backup your database or change your database structure (add/drop a tablespace or datafile and so on) RMAN will automatically save your current controlfile and spfile.
It’s important to know where your autobackup is saved because when you begin to restore your spfile, RMAN will open the database in NOMOUNT mode and without the spfile it won’t have any possibilities to know where the flash recovery area is located.

It’s completely different when you are using a recovery catalog because it knows exactly where the latest available autobackup is located.
If you are not using a recovery catalog, in order to restore the spfile you have also to set your database identifier (DBID).
When your database is open you can obtain that information querying the V$DATABASE view, but now your database can’t even mount.
My approach is to preserve and send me by email the RMAN log of every backup I take: in that log you can extract the database identifier you need.
Indeed every time you connect to your database through RMAN you can see that value:

Currently I’m using an spfile located in the default location ($ORACLE_HOME/dbs)

On the default parameter file location I have the following parameter files:

I simulate a deletion of my parameter files:

Now let’s see what it happens when I try to startup the database.

We have to restore our spfile. Let’s begin. Start RMAN:

Force RMAN to start an instance without a parameter file. You have to use the FORCE option:

Now you have to set your DBID:

Let’s see first what it happens when I specify the restore spfile from autobackup command and the autobackups are not saved on the default location ($ORACLE_HOME/dbs)

RMAN is not able to know where the autobackups are. It doesn’t know if I used a flash recovery area and where eventually that FRA was located.
So I have to explicitly tell RMAN where autobackup is located and which to use. To identify the autobackup I need to locate my flash recovery area and go through the autobackup directory

The latest autobackup is located in the 2012_08_21 directory:

I will use the o1_mf_s_791884673_8378hktd_.bkp autobackup to restore the spfile.

Now I can bounce the instance and successfully open the database.

As you can see there’s a new spfile on the default parameter file location

According to the “Oracle Database Backup and Recovery Reference 11g Release 2 (11.2)” manual on page 3-40 (or n.248 of that pdf) if you look at “Table 3-10 RESTORE … FROM AUTOBACKUP” you can also do not set the DBID when you specify RECOVERY AREA and DB_NAME on the restore command, that is:

Once the update is done, Follow the same process of upgrading agents.

Thank you for giving your valuable time to read the above information.


For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.