Recreate SPFILE dropped accidently from ASM

Today we had a situation where user accidentally dropped the SPFILE from ASM diskgroup on RAC.

Since this is RAC we had the pfile pointing to ASM diskgroup not a physical file.

Method I used to retore the SPFILE.

RMAN> list backup of SPFILE;
List of Backup Pieces for backup set 11456205 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
11456219 1 AVAILABLE +DB_FRA_01/DBNAME/autobackup/2010_08_30/s_728389367.386.728389369

Backup Set Copy #2 of backup set 11456205
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
SBT_TAPE 00:00:01 08/30/2010 11:47:18 NO TAG20100830T102247

List of Backup Pieces for backup set 11456205 Copy #2
BP Key Pc# Status Media Piece Name
------- --- ----------- ----------------------- ----------
11458334 1 AVAILABLE 202210 c-3704148265-20100830-02


Above clearly shows we have the copy SPFILE in disk and in TAPE and I use the disk rather tape copy for restore.

RMAN> Restore spfile to ',location> from <+DB_FRA_01/DBNAME/autobackup/2010_08_30/s_728389367.386.728389369>;

Restore Complete

SQL> create pfile='/export/home/oracle/sshaik/shaik1.ora' from spfile='/export/home/oracle/sshaik/sshaik.ora';

File created.



SQL> create spfile='+DB_DATA_01/DBNAME/spfileaadp1.ora' from pfile='/export/home/oracle/sshaik/sshaik1.ora';

File created.


Now bounce the instance to see the new created SPFILE :-)