Recovery scenarios involving loss of SPFILE

In this session we will see how to recover spfile from rman backup.

Considering we have RMAN backup available that has a copy of the 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:

amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.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=1500410030;

executing command: SET DBID

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/amit/app/oracle/product/12.1.0/db_1/dbs/inittest12c.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                293601384 bytes
Database Buffers             771751936 bytes
Redo Buffers                   5455872 bytes

This is a typical error we will face when either restoring the SPFILE or control file from an autobackup.

RMAN> restore spfile from autobackup;

Starting restore at 25-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151125
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151124
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151123
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151122
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151121
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151120
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151119
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 11/25/2015 16:01:20
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 '/data/amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp';

Starting restore at 25-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /data/amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-NOV-15

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/inittest12c.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: 20151119
channel ORA_DISK_1: AUTOBACKUP found: '/u01/backup/c-2968723077-20151119-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

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!