Restoring the SPFILE – with and without AUTOBACKUP
Let us look at some backup and recovery scenarios pertaining to the SPFIILE. The SPFILE is a small but very important file and if we lose the spfile, we cannot start the database even if all the other database files are present and intact.
So in my opinion, it is quite an important thing to consider in our disaster recovery strategy.
Remember best practise is to turn the autobackup of the control file to ON (it is OFF by default).
SPFILE is automatically backed up along with the database control file when any of the following events occur and when the control file autobackup has been enabled in RMAN. .
RMAN> show controlfile autobackup;
RMAN configuration parameters for database with db_unique_name GAVIN are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
When does the SPFILE gat backed up with AUTOBACKUP now tuned on?
- After every BACKUP or CREATE CATALOG command
- After every BACKUP command contained in a RUN block
- After every structural change to the database occurs like adding a new tablespace, altering the state of a tablespace or datafile (for example, bringing it online), adding a new online redo log, renaming a file
Where does the SPFILE autobackup reside?
RMAN> show controlfile autobackup format ;
RMAN configuration parameters for database with db_unique_name GAVIN are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
This is the default setting for CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
By default, RMAN will send the autobackup to the flash recovery area (if used).
Let us now remove the default keyword
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;
new RMAN configuration parameters are successfully stored
In this case the SPFILE (and control file) autobackup is located anywhere you specify, but default location will be %ORACLE_HOME%\Database on Windows and $ORACLE_HOME/dbs on UNIX.
RMAN> list backup of spfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
252 Full 9.73M DISK 00:00:00 13-JUN-13
BP Key: 267 Status: AVAILABLE Compressed: NO Tag: TAG20130613T144508
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-2968723077-20130613-00
Let us now specify an actual location on disk instead of just the %F,
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/backup/%F’;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/backup/%F’;
new RMAN configuration parameters are successfully stored
RMAN> list backup of spfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
256 Full 9.73M DISK 00:00:01 13-JUN-13
BP Key: 271 Status: AVAILABLE Compressed: NO Tag: TAG20130613T155004
Piece Name: /u01/backup/c-2968723077-20130613-01
Note the SPFILE autobackup is now located at /u01/backup and we can see the format of the backup file on disk is no longer OMF.
The DBID (2968723077) and the timestamp (20130613) is now contained in the backup file name c-2968723077-20130613-01
Let us now revert the autobackup back to the FRA.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;
RMAN configuration parameters are successfully reset to default value
RMAN> show CONTROLFILE AUTOBACKUP FORMAT;
RMAN configuration parameters for database with db_unique_name GAVIN are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
RMAN> list backup of spfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
266 Full 9.73M DISK 00:00:01 13-JUN-13
BP Key: 281 Status: AVAILABLE Compressed: NO Tag: TAG20130613T162411
Piece Name: /u01/app/oracle/fast_recovery_area/GAVIN/autobackup/2013_06_13/o1_mf_s_818007851_8vlsdd3n_.bkp
Autobackup is now back to OMF.
Recovery scenarios involving loss of SPFILE
Case 1) Autobackup in Flash (or now called Fast) Recovery Area
The SPFILE has accidently been deleted and now the database is not starting up after a shutdown has been executed.
FRA has been configured.
If FRA has been configured, the backup of the SPFILE is located in the autobackup sub-directory.
For example:
/u01/app/oracle/fast_recovery_area/GAVIN/autobackup/2013_06_10/ o1_mf_s_818007851_8vlsdd3n_.bkp
Note that it is stored in OMF format in this example. The ‘s’ in the string identifies the OMF as a backup related to the SPFILE
To recover from loss of SPFILE if you are NOT using an RMAN Catalog, we need to do two things first :
1) Set the DBID
2) Issue the STARTUP NOMOUNT FORCE command from an RMAN prompt (note – not SQL*PLUS)
RMAN> SET DBID=2968723077;
executing command: SET DBID
RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initgavin.ora’
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 104859368 bytes
Database Buffers 46137344 bytes
Redo Buffers 5439488 bytes
This is a typical error we will face when either restoring the SPFILE or control file from an autobackup.
RMAN> restore controlfile from autobackup;
Starting restore at 13-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130612
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130611
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130610
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130609
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130608
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130607
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/13/2013 17:15:52
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
The reason in this case is that since the spfile is missing and we have mounted the instance using a dummy spfile, the database needs to know where to look to find the autobackup of the spfile .
So now we include the db_file_recovery_dest and db_name parameters in the RESTORE SPFILE command.
RMAN> restore spfile from autobackup db_recovery_file_dest=’/u01/backup/fast_recovery_area’ db_name=’GAVIN’;
Starting restore at 13-JUN-13
using channel ORA_DISK_1
recovery area destination: /u01/backup/fast_recovery_area
database name (or database unique name) used for search: GAVIN
channel ORA_DISK_1: AUTOBACKUP /u01/backup/fast_recovery_area/GAVIN/autobackup/2013_06_10/o1_mf_s_818007851_8vlsdd3n_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/fast_recovery_area/GAVIN/autobackup/2013_06_10/o1_mf_s_818007851_8vlsdd3n_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-13
Case 2) Autobackup in non-FRA location – non OMF
RMAN> startup nomount force
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initgavin.ora’
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 104859368 bytes
Database Buffers 46137344 bytes
Redo Buffers 5439488 bytes
RMAN> set DBID=2968723077
executing command: SET DBID
RMAN> run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/backup/%F’;
3> restore spfile from autobackup;
4> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 13-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613
channel ORA_DISK_1: AUTOBACKUP found: ‘/u01/backup/c-2968723077-20130613-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP ‘/u01/backup/c-2968723077-20130613-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-13
Case 3) Total Disaster Recovery ( restore by specifying the backup file name)
In this scenario, the entire database server has crashed and we have lost the entire database files including the SPFILE.
A new server has been provisioned and all the latest backup files have been restored from tape to a location on disk /u01/backup.
In this case the backup files are OMF and we have been able to identify the SPFILE backup file from the ‘s’ keyword in the backup file name
RMAN> restore spfile from ‘/u01/backup/o1_mf_s_818007851_8vlsdd3n_.bkp’;
Starting restore at 13-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/o1_mf_s_818007851_8vlsdd3n_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-13
Case 4) Total Disaster Recovery ( restore by specifying restore from AUTOBACKUP)
In this case, the scenario is the same as the above.
But what happens if we want to use the AUTOBACKUP command to restore the spfile because many backup files have been restored and we are not sure which backup file contains the SPFILE backup.
But what happens in this case when we try to restore the SPFILE from the location where the backup has been restored.
RMAN> run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/backup/%F’;
3> restore spfile from autobackup;
4> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 17-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130617
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130615
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130614
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130612
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130611
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/17/2013 15:29:58
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
So to work around this, we tried to fool RMAN by creating the directory structure when using a FRA.
We create the directory structure GAVIN/autobackup/2013_06_17 under the to level location /u01/backup and copy the backup pieces to this location.
-bash-3.2$ cd /u01/backup
-bash-3.2$ mkdir -p GAVIN/autobackup/2013_06_17
-bash-3.2$ mv /u01/backup/o1* /u01/backup/GAVIN/autobackup/2013_06_17
Now we are able to restore the SPFILE from autobackup!
RMAN> run {
2> restore spfile from autobackup db_recovery_file_dest=’/u01/backup/’ db_name=’GAVIN’;
3> }
Starting restore at 17-JUN-13
using channel ORA_DISK_1
recovery area destination: /u01/backup/
database name (or database unique name) used for search: GAVIN
channel ORA_DISK_1: AUTOBACKUP /u01/backup/GAVIN/autobackup/2013_06_17/o1_mf_s_818349778_8vx79lo6_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130617
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/GAVIN/autobackup/2013_06_17/o1_mf_s_818349778_8vx79lo6_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-JUN-13
postgres 
