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.
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.
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.
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>
restore spfile from autobackup;
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;
Senior Oracle DBA