How to restore the spfile while using the flash recovery area and not the recovery catalog

Imagine someone has deleted your spfile or you have modified it in depth that you are now unable to even startup your database.
Your only option is to restore your spfile and this step is easy if you have not forgot to setup your RMAN environment to use the AUTOBACKUP feature: RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Because I'm using the flash recovery area, this setup will use it as default location where to save the controlfile and spfile.
Every time you backup your database or change your database structure (add/drop a tablespace or datafile and so on) RMAN will automatically save your current controlfile and spfile.
It's important to know where your autobackup is saved because when you begin to restore your spfile, RMAN will open the database in NOMOUNT mode and without the spfile it won't have any possibilities to know where the flash recovery area is located.

It's completely different when you are using a recovery catalog because it knows exactly where the latest available autobackup is located.
If you are not using a recovery catalog, in order to restore the spfile you have also to set your database identifier (DBID).
When your database is open you can obtain that information querying the V$DATABASE view, but now your database can't even mount.
My approach is to preserve and send me by email the RMAN log of every backup I take: in that log you can extract the database identifier you need.
Indeed every time you connect to your database through RMAN you can see that value:

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Aug 27 08:06:12 2012

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

connected to target database: ORCL (DBID=1229390655)

RMAN>

Currently I'm using an spfile located in the default location ($ORACLE_HOME/dbs)

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/spfileorcl.ora

On the default parameter file location I have the following parameter files:

[oracle@localhost dbs]$ ll *.ora
-rw-r--r-- 1 oracle oracle 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oracle  621 Oct  2  2010 initorcl.ora
-rw-r----- 1 oracle oracle 3584 Aug 27 07:28 spfileorcl.ora

I simulate a deletion of my parameter files:

[oracle@localhost dbs]$ mv init.ora init.ora.20120828_020500.bck
[oracle@localhost dbs]$ mv initorcl.ora initorcl.ora.20120828_020500.bck
[oracle@localhost dbs]$ mv spfileorcl.ora spfileorcl.ora.20120828_020500.bck

Now let's see what it happens when I try to startup the database.

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 28 02:08:33 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'

We have to restore our spfile. Let's begin. Start RMAN:

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 28 02:14:02 2012

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

connected to target database (not started)

Force RMAN to start an instance without a parameter file. You have to use the FORCE option:

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1342848 bytes
Variable Size                 79692416 bytes
Database Buffers              75497472 bytes
Redo Buffers                   2486272 bytes

Now you have to set your DBID:

RMAN> set DBID 1229390655;

executing command: SET DBID

Let's see first what it happens when I specify the restore spfile from autobackup command and the autobackups are not saved on the default location ($ORACLE_HOME/dbs)

RMAN> restore spfile from autobackup;

Starting restore at 28-08-2012 02:18:51
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: 20120828
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120827
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120826
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120825
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120824
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120823
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120822
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 08/28/2012 02:18:54
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN is not able to know where the autobacks are. It doesn't know if I used a flash recovery area and where eventually that FRA was located.
So I have to explicitly tell RMAN where autobackup is located and which to use. To identify the autobackup I need to locate my flash recovery area and go through the autobackup directory

[oracle@localhost dbs]$ cd /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup
[oracle@localhost autobackup]$ ll -lrt
total 20
drwxrwx--- 2 oracle oracle 4096 Jul 17 09:05 2012_07_17
drwxrwx--- 2 oracle oracle 4096 Jul 21 08:44 2012_07_21
drwxrwx--- 2 oracle oracle 4096 Jul 25 07:53 2012_07_25
drwxrwx--- 2 oracle oracle 4096 Jul 26 06:59 2012_07_26
drwxrwx--- 2 oracle oracle 4096 Aug 21 07:57 2012_08_21

The latest autobackup is located in the 2012_08_21 directory:

[oracle@localhost autobackup]$ ls -lrth 2012_08_21
total 19M
-rw-rw---- 1 oracle oracle 9.5M Aug 21 07:42 o1_mf_s_791883766_8377m7v2_.bkp
-rw-rw---- 1 oracle oracle 9.5M Aug 21 07:57 o1_mf_s_791884673_8378hktd_.bkp

I will use the o1_mf_s_791884673_8378hktd_.bkp autobackup to restore the spfile.

RMAN> restore spfile from '/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp';

Starting restore at 28-08-2012 03:18:57
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-08-2012 03:18:58

Now I can bounce the instance and successfully open the database.

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                352324280 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6008832 bytes

As you can see there's a new spfile on the default parameter file location

[oracle@localhost dbs]$ ls -lrt *.ora
-rw-rw---- 1 oracle oracle 3584 Aug 28 02:56 spfileorcl.ora

According to the "Oracle Database Backup and Recovery Reference 11g Release 2 (11.2)" manual on page 3-40 (or n.248 of that pdf) if you look at "Table 3-10 RESTORE ... FROM AUTOBACKUP" you can also do not set the DBID when you specify RECOVERY AREA and DB_NAME on the restore command, that is:

[oracle@localhost dbs]$ rm spfileorcl.ora
[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 28 03:44:23 2012

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

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1342848 bytes
Variable Size                 79692416 bytes
Database Buffers              75497472 bytes
Redo Buffers                   2486272 bytes

RMAN> restore spfile from autobackup recovery area='/home/oracle/app/oracle/flash_recovery_area' db_name=orcl;

Starting restore at 28-08-2012 03:45:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-08-2012 03:45:55

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                352324280 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6008832 bytes

That's all.

How to perform a SCN-based incomplete recovery

When you don't know the date and time to which you want to recover the database, SCN-based incomplete recovery could be taken into consideration especially if you are able to retrieve a particular SCN.
In this scenario I will perform a SCN-based incomplete recovery after I have used the Oracle LogMiner utility to retrieve a useful SCN where to stop my recovery process.
Before starting the recovery process I want to be sure a valid backup is available.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Apr 23 07:42:39 2013

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
257     B  A  A DISK        22-04-2013 21:33:38 1       1       YES        TAG20130422T213335
258     B  F  A DISK        22-04-2013 21:45:34 1       1       YES        TAG20130422T213339
259     B  A  A DISK        22-04-2013 21:45:38 1       1       YES        TAG20130422T214537
260     B  F  A DISK        22-04-2013 21:45:41 1       1       NO         TAG20130422T214539
261     B  F  A DISK        23-04-2013 05:02:30 1       1       NO         TAG20130423T050224
262     B  F  A DISK        23-04-2013 06:17:30 1       1       NO         TAG20130423T061726

Now I'm going to execute few sql statements to modify my database and to know the SCN at which those operations were performed.
Lukely in a normal situation you don't have to get every time the current SCN after each DML operations: but in this scenario these values will be useful because I want to compare them with those returned by the LogMiner utility when I need to recover the database.
So approximately at SCN equals to 14928918 my database has two tables with few rows into my schema.

SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      2000

SQL> select count(*) from marcov.t2;

  COUNT(*)
----------
      4000

SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  14928918

When SCN is equals to 14928987 in the same schema I have created another table and inserted few rows.

SQL> create table marcov.new_deployment (a number);

Table created.

SQL> insert into marcov.new_deployment select * from marcov.t1;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from marcov.new_deployment;

  COUNT(*)
----------
      2000

SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  14928987

At SCN number 14929031 my recently created table no more exists because it was "inadvertently" dropped:

SQL> drop table marcov.new_deployment purge;

Table dropped.

SQL> select count(*) from marcov.new_deployment;
select count(*) from marcov.new_deployment
                            *
ERROR at line 1:

-- SCN of reference
SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  14929031

I want to perform an incomplete recovery and stop it just before the moment at which someone dropped my table.
I know someone dropped my table so I can use the LogMiner utility and look at every operation performed on my table.
First of all I need to specify which redo log (online or archived of course) should be analyzed.
I was alerted as soon as the table was accidentally dropped so I would try to start my redo log analysis from the CURRENT one.
If you want to analyze more redo logs at the same time you can use the DBMS_LOGMNR.ADDFILE option into the DBMS_LOGMNR.ADD_LOGFILE procedure or start the LogMiner using DBMS_LOGMNR.START_LOGMNR with the CONTINUOUS_MINE as option.
Let me see which redo log is the CURRENT.

SQL> select member from v$logfile a, v$log b where a.group#=b.group# and b.status = 'CURRENT';

MEMBER
------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/redo02.log
/home/oracle/app/oracle/oradata/orcl/redo02b.log

Before starting a LogMiner session you have to add the redo log file you want to analyze using the DBMS_LOGMRN.ADD_LOGFILE procedure. In my case I want to add my CURRENT redo log file.

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo02.log', options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

Then you can start your LogMiner session specifying to use the dictionary currently in use in the database.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

After you have successfully executed the DBMS_LOGMRN.START_LOGMNR, the v$logmnr_contents view will contain the redo data of interest.
To query the v$logmnr_contents view your user must have the SELECT ANY TRANSACTION privilege.
In the following statement I want to know the operations occurred on my dropped table, when they were performed, the associated SCN and the statement executed.

SQL> col sql_redo format a60
SQL> col ts format a30
SQL> select scn, to_char(timestamp, 'dd/mm/yyyy hh24:mi:ss') ts, operation, sql_redo from v$logmnr_contents where table_name = 'NEW_DEPLOYMENT' order by ts;

       SCN TS                             OPERATION                        SQL_REDO
---------- ------------------------------ -------------------------------- ------------------------------------------------------------
  14928965 23/04/2013 22:16:49            DDL                              create table marcov.new_deployment (a number);
  14929007 23/04/2013 22:17:54            DDL                              drop table marcov.new_deployment purge;

As you can see my table was created when SCN was equal to 14928965 and dropped when it was 14929007.
These values are logically consistent of course with the previous calculated SCN using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER procedure: I obtained a SCN equals to 14928987 after the table was created (14928965 from v$logmnr_contents view), but still not dropped and equals to 14929031 few seconds later the table was dropped (14929007 from v$logmnr_contents view). I have all the necessary information so I can close the LogMiner session and perform a SCN-based incomplete recovery just one step before the SCN be equal to 14929007.

SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

To perform an incomplete recovery the database must be in MOUNT mode.

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                373295800 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6008832 bytes

In a previous post related on time-based incomplete recovery I issued a series of single RMAN commands: now I want to use another syntax using the set until ... option (set until scn in this case) and a run { ... } block.

RMAN> run {
2> set until scn 14929007;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause

Starting restore at 23-04-2013 22:22:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
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 /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/APEX02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/app/oracle/oradata/orcl/test01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp tag=TAG20130422T213339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:32
Finished restore at 23-04-2013 22:26:46

Starting recover at 23-04-2013 22:26:47
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 49 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_49_8qd5pd13_.arc
archived log for thread 1 with sequence 50 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_50_8qd6ckq5_.arc
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_1_8qf0kqy2_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=48
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp tag=TAG20130422T214537
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_48_8qgv8t7p_.arc thread=1 sequence=48
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_48_8qgv8t7p_.arc RECID=278 STAMP=813536810
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_49_8qd5pd13_.arc thread=1 sequence=49
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_50_8qd6ckq5_.arc thread=1 sequence=50
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_1_8qf0kqy2_.arc thread=1 sequence=1
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_23/o1_mf_1_1_8qgs3znf_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:25
Finished recover at 23-04-2013 22:27:16

database opened

As you can see my table is now available again.

SQL> select count(*) from marcov.new_deployment;

  COUNT(*)
----------
      2000

That's all.