oracle restore spfile, controlfile and datafiles from RMAN

I am demonstrating a scenarion where I am  using ASM and have lost spfile, controlfile and datafiles. Now my job is to restore the database from RMAN backup.

This is little tricky but not impossible. Let me explain you how this will be done:

1. Get the DBID of the database from recovery catalog.
2. Create init<SID>.ora file
3. Connect to RMAN and set the DBID
4. Startup nomount
5. Restore spfile
6. Shutdown immediate
7. Update init<SID>.ora with spfile location
8. Startup nomount
9. Restore controlfile
10. startup mount
11. restore database
12. recover database
13. alter database open resetlogs;

Get the DBID of the database from recovery catalog.

SQL> select distinct DBID, NAME from RC_DATABASE_INCARNATION;
      DBID NAME
---------- --------
2410645064 RAC
1372201017 ORCL

Create init<SID>.ora file

$ cat initRAC1.ora
*.db_name=RAC

Connect to RMAN and set the DBID

[oracle@rac1 dbs]$ rman target / catalog catowner/******@rcat
Recovery Manager: Release 11.2.0
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> set dbid 2410645064;
executing command: SET DBID
database name is "RAC" and DBID is 2410645064
RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1861    ORCL     1372201017       PRIMARY          ORCL              
1       RAC      2410645064       PRIMARY          RAC                
1       RAC      2410645064       STANDBY          RAC_STBY          
RMAN> 

startup nomount

RMAN> startup nomount
Oracle instance started
Total System Global Area     238034944 bytes
Fixed Size                     2227136 bytes
Variable Size                180356160 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5120000 bytes
RMAN> 

Restore spfile

RMAN> restore spfile to '+DATA/RAC/spfileRAC.ora' from tag='TAG20140626T161003';
Starting restore at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/RAC/spfileRAC.ora
channel ORA_DISK_1: reading from backup piece +FRA/rac/autobackup/2014_06_26/s_851271003.263.851271009
channel ORA_DISK_1: piece handle=+FRA/rac/autobackup/2014_06_26/s_851271003.263.851271009 tag=TAG20140626T161003
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 30-JUN-14
RMAN>

Shutdown immediate

RMAN> shutdown immediate
Oracle instance shut down
RMAN>

Update init<SID>.ora with spfile location

$cat initRAC1.ora
SPFILE='+DATA/RAC/spfileRAC.ora'

Startup nomount

RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area     793350144 bytes
Fixed Size                     2232552 bytes
Variable Size                394268440 bytes
Database Buffers             394264576 bytes
Redo Buffers                   2584576 bytes
RMAN>

Restore controlfile

RMAN> restore controlfile;
Starting restore at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece +FRA/rac/autobackup/2014_06_27/s_851356825.376.851356835
channel ORA_DISK_1: piece handle=+FRA/rac/autobackup/2014_06_27/s_851356825.376.851356835 tag=TAG20140627T160025
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/rac/controlfile/current.262.851600129
Finished restore at 30-JUN-14
RMAN>

startup mount

RMAN> startup mount;
database is already started
database mounted
released channel: ORA_DISK_1
RMAN>

restore database

RMAN> restore database ;
Starting restore at 30-JUN-14
Starting implicit crosscheck backup at 30-JUN-14
allocated channel: ORA_DISK_1
Crosschecked 11 objects
Finished implicit crosscheck backup at 30-JUN-14
Starting implicit crosscheck copy at 30-JUN-14
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 30-JUN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +fra/RAC/AUTOBACKUP/2014_06_27/n_851344744.494.851344751
File Name: +fra/RAC/AUTOBACKUP/2014_06_27/s_851356825.376.851356835
File Name: +fra/RAC/AUTOBACKUP/2014_06_26/s_851271003.263.851271009
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/rac/datafile/system.263.851343649
channel ORA_DISK_1: restoring datafile 00002 to +DATA/rac/datafile/sysaux.268.851343643
channel ORA_DISK_1: restoring datafile 00003 to +DATA/rac/datafile/undotbs1.277.851343653
channel ORA_DISK_1: restoring datafile 00004 to +DATA/rac/datafile/users.273.851343655
channel ORA_DISK_1: restoring datafile 00005 to +DATA/rac/datafile/example.278.851343653
channel ORA_DISK_1: restoring datafile 00006 to +DATA/rac/datafile/undotbs2.274.851343653
channel ORA_DISK_1: restoring datafile 00007 to +DATA/rac/datafile/hdts.279.851343653
channel ORA_DISK_1: reading from backup piece +FRA/rac/backupset/2014_06_26/nnndf0_full_db_rac_0.500.851259117
channel ORA_DISK_1: piece handle=+FRA/rac/backupset/2014_06_26/nnndf0_full_db_rac_0.500.851259117 tag=FULL_DB_RAC
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:05
Finished restore at 30-JUN-14
RMAN>

recover database

RMAN> recover database;

alter database open resetlogs;

RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>