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!!!
Keep learning... Have a great day!!!
