Recovery scenarios involving loss of SPFILE

·          Autobackup in Flash  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:

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)

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 15 19:53:09 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN>

RMAN> set DBID=1792754794

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/db_1/dbs/initrman.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes

Variable Size                 75497576 bytes

Database Buffers              79691776 bytes

Redo Buffers                   2494464 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 15-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160315

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160314

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160313

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160312

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160311

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160310

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160309

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 03/15/2016 19:54:15

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

NOTE : Above thing is a typical error we will face when either restoring the SPFILE or control file from an autobackup.

RMAN> restore spfile from autobackup db_recovery_file_dest='/u01/backup/fast_recovery_area'  db_name='RMAN';

Starting restore at 15-MAR-16

using channel ORA_DISK_1

recovery area destination: /u01/backup/fast_recovery_area

database name (or database unique name) used for search: RMAN

channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160315

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160314

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160313

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160312

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160311

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160310

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160309

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 03/15/2016 19:54:44

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile from autobackup db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'  db_name='RMAN';

Starting restore at 15-MAR-16

using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area

database name (or database unique name) used for search: RMAN

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/RMAN/autobackup/2016_03_15/o1_mf_s_906579426_cgj5rcvc_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160315

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/RMAN/autobackup/2016_03_15/o1_mf_s_906579426_cgj5rcvc_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 15-MAR-16

RMAN> exit

Recovery Manager complete.

SQL> shut abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  313860096 bytes

Fixed Size                                1336232 bytes

Variable Size                        197135448 bytes

Database Buffers               109051904 bytes

Redo Buffers                         6336512 bytes

Database mounted.

Database opened.

SQL>