USING RMAN Restoring the SPFILE - with and without AUTOBACKUP

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