Today we are going to learn about recovery with rman for loss of spfile. As we know that RMAN can be used either with or without a recovery catalog.

A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations.

Generally, a skillful DBA would urge that the Enterprise Manager instance schema and RMAN catalog schema be placed in the same utility database on a server distinct from the main servers. The RMAN schema generally only requires 15 megabyte per year per database backed up.

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:

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 autobacks 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:

We hope this information is valuable for you.


Leave a Reply