SPFILE Backup & Restore Recommendations

I’m always amazed as how a little file as the SPFILE can complicate situations “when you can really use a break”. For example, you shutdown your database gracefully just to find out that you can’t bring it up again due to the fact that you have bad parameters in your SPFILE or the file somehow got deleted and you just came to find out. So you want to recover a backup copy of SPFILE, that’s when you discover that you don’t really know how Oracle is backing up your SPFILE, where the file is (disk, tape or both) and how to recover it.

I hope this post will answer all these questions.

 

SPFILE Backup

Our design goal is to have 2 backups of SPFILE: 1 on disk and the other backup on tape. This way you can first attempt to recover from disk which is faster, if you can’t then try the tape.

Here are the steps to accomplish our design (Tested in 10.2.0.4 Linux 64-bit):

 

1) Enable control file auto backup to disk.

Every time a change is made to control file, an auto backup will take place of both control file and SPFILE. Unfortunately SPFILE changes by itself don’t constitute an auto backup. In addition to auto backups when control file changes, every time you have a backup statement in your RMAN script (ex. backup database plus archive), an auto backup will be generated.

To enable control file auto backup:

rman> CONFIGURE CONTROLFILE AUTOBACKUP ON;

You can send your auto backups to anywhere on disk. The default is $ORACLE_HOME/dbs. I prefer sending auto backups to FRA (especially in a RAC environment). You can read about how to set up FRA and send auto backups to it here.

Here is an example of sending auto backups to a specific location on disk:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u04/backups/auto/%F’;


Now you accomplished first design goal which is backup SPFILE to disk.

 

2) Include SPFILE in your RMAN backup sets to tape.

The default behavior when you enable auto backup is NOT to backup SPFILE in RMAN backup pieces. To overwrite this behavior, this RMAN statement will create backup piece for SPFILE and implicitly create an auto backup on disk as configured in previous step.

backup
incremental level 0
spfile format ‘spfile_%d_%s_%T.bak’  tag ‘spfile backup’
database include current controlfile format ‘data_%d_%s_%T.bak’  tag ‘data backup’
archivelog all format ‘arc_%d_%s_%T.bak’  tag ‘archive backup’ delete input;

Now we accomplished second step which is backing up SPFILE to tape with your daily rman backup.

 

Recovery Scenarios

Here are the most common recovery scenarios I came across, remember Oracle will not recover an SPFILE file to its original location while the database is up even if existing SPFILE is deleted. You will get this “RMAN-06564 must use the TO clause when the instance is started with SPFILE”, which is a little confusing. So simply recover to a different location and do “cp”.

 

A) Recover latest SPFILE while database down


set dbid <your DBID>

startup nomount;

restore spfile from autobackup;

shutdown immediate

startup

 

B) Recover from auto backup in Flashback Recovery Area, database up

This is RAC/ASM with FRA placed in ASM

RMAN> restore spfile to ‘<path with filename>‘ from autobackup;
Starting restore at 11-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 instance=raca1 devtype=DISK
recovery area destination: +ARCDG
database name (or database unique name) used for search: RACA
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: +arcdg/RACA/AUTOBACKUP/2009_03_11/s_681250817.257.681250819

channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 11-MAR-09

Another syntax is to accomplish the same thing:

restore spfile to pfile ‘<path and filename>’ from autobackup db_recovery_file_dest=’+ARCDG’ db_name='<your db name>’;

 

C) Recover a specific backup of SPFILE

This is useful if you have bad parameters in your SPFILE and want to recover an older cleaner copy (SPFILE point in time recovery, now how about that )

list backup of spfile;

# select proper backup piece

restore spfile to ‘<path and filename except original location when DB started> from ‘<backup piece or auto backup>’;

 

D) Recover an SPFILE to PFILE

Always useful if you want to read content of your parameter file.

restore spfile to pfile ‘<your path and filename>‘  from autobackup;


 

Hazem Ameen
Senior Oracle DBA

Advertisements

6 thoughts on “SPFILE Backup & Restore Recommendations

  1. “For example, you shutdown your database gracefully just to find out that you can’t bring it up again due to the fact that you have bad parameters in your SPFILE or the file somehow got deleted and you just came to find out. So you want to recover a backup copy of SPFILE, that’s when you discover that you don’t really know how Oracle is backing up your SPFILE, where the file is (disk, tape or both) and how to recover it.”
    Can more?

  2. Hmm it looks like your site ate my first comment (it was super long) so I guess I’ll just sum it up what I submitted
    and say, I’m thoroughly enjoying your blog. I as well am an aspiring blog blogger but I’m still new to
    everything. Do you have any tips for first-time
    blog writers? I’d really appreciate it.

    1. The only recommendation I have for you is to write up on experiences you think is fairly unique and don’t fill your blog with stuff you can find everywhere

      Hazem

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s