On ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Few days ago I received an email from a user who had two empty table, one referenced by the other. He was not able to execute a truncate command on the parent table and asked me to solve his error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

Truncate command is very useful when you want to completely remove all data from a table: for very large tables it is usually the best way to remove your data.
When you use the TRUNCATE command Oracle sets back to zero the HWM (high-water mark) of the table and, compared with the DELETE command, less UNDO and REDO information is generated.
You can query the dba_extents to see how many extents are allocated for your table.

However using TRUNCATE command has some "disadvantages": first you have to remember it's a DDL command so it will commit automatically any pending transactions. Another one is that you can receive the following error when the table you are truncating is referenced by an "enabled" foreign key: ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
To successfully complete your TRUNCATE command you have to temporarily disable the foreign key.

Let's see it with an example.
Create two tables: one table (hr.parent) has one primary key referenced by a second table (hr.child) using a constraint.

SQL> create table hr.parent (a number);

Table created.

SQL> create table hr.child (b number, a_ref number);

Table created.

SQL> create index parent_pk on hr.parent(a);

Index created.

SQL> alter table hr.parent add constraint parent_pk primary key (a);

Table altered.

SQL> alter table hr.child add constraint child_fk foreign key (a_ref) references hr.parent (a);

Table altered.

No extent is allocated for PARENT table. I'm working with Oracle Database 11G R2, so have a look at deferred_segment_creation initialization parameter to justify why you have zero extent allocated (or read this link)

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         0

And no rows still are created.

SQL> select * from hr.parent;

no rows selected

SQL> select count(*) from hr.child;

  COUNT(*)
----------
         0

In this situation we can truncate our PARENT table with any problem.

SQL> truncate table hr.parent;

Table truncated.

But what does it happen when records are inserted into it ? Let's populate PARENT table with some data:

SQL> begin
  2  for  indx IN 1 .. 10000
  3  loop
  4      execute immediate 'insert into hr.parent values (:1)' using indx;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

PARENT table is now formed by 3 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3

As you can see we are no more allowed to truncate PARENT table even if any data on CHILD table are referencing to it.

SQL>  truncate table hr.parent;
 truncate table hr.parent
                   *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> select count(*) from hr.child;

  COUNT(*)
----------
         0

To successfully complete the truncate command we have to disable the foreign key...

SQL> alter table hr.child modify constraint child_fk disable;

Table altered.

... execute the truncate command...

SQL>  truncate table hr.parent;

Table truncated.

.. and enable again the foreign key.

SQL> alter table hr.child modify constraint child_fk enable;

Table altered.

PARENT table is now formed by one extent.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1

Let's populate again PARENT table with 10000 records:

SQL> begin
  2  for  indx IN 1 .. 10000
  3  loop
  4      execute immediate 'insert into hr.parent values (:1)' using indx;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

PARENT table is formed by 3 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3

Let's see what it happens to the HWM using delete command.

SQL> delete from hr.parent;

10000 rows deleted.

SQL> commit;

Commit complete.

PARENT table is always formed by 3 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3

It's still not possible to truncate PARENT table...

SQL> truncate table hr.parent;
truncate table hr.parent
                  *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

... even when no rows are available and referenced.

SQL> select count(*) from hr.parent;

  COUNT(*)
----------
         0

SQL> select count(*) from hr.child;

  COUNT(*)
----------
         0

You can truncate PARENT table again only if foreign key is disabled

SQL> alter table hr.child modify constraint child_fk disable;

Table altered.

SQL> truncate table hr.parent;

Table truncated.

PARENT table is now formed by 1 extents.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1

If you want to see the extent allocated as soon as you create PARENT table, you have to use the "segment creation immediate" option on your create table statement.

SQL> drop table hr.child; 

Table dropped.

SQL> drop table hr.parent;

Table dropped.

SQL> create table hr.parent (a number) segment creation immediate;

Table created.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1

That's all.

How to recover a never backed up tablespace after losing its datafile and even the current controlfile after the autobackup feature completes its job

This post will take into consideration a Recovery Manager setting using the CONTROLFILE AUTOBACKUP feature, a tablespace created after the only available full backup (so this backup doesn't have information on this tablespace), some rows committed on the new tablespace, a crash happened after a backup of the current controlfile was completed.
This crash envolves the lost of the "never backed up" tablespace and of the current controlfile.

Let's start with an example. Our instance is up and running.

[oracle@localhost orcl]$ ps -ef | grep smon
oracle   13600     1  0 06:11 ?        00:00:00 ora_smon_orcl
oracle   13794 13766  0 06:24 pts/5    00:00:00 grep smon

Connect with RMAN client to see persistent settings.

[oracle@localhost orcl]$ rman target /

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

Connect with sqlplus client and (try to) drop the tablespace YYY including its contents and datafiles.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> drop tablespace YYY including contents and datafiles;
drop tablespace YYY including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'YYY' does not exist

Now delete all backups and copies. I want to be sure I don't have a valid backup of YYY tablespace.

[oracle@localhost orcl]$ rman target /

RMAN> delete noprompt backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
139     138     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T055405_8c9khy7t_.bkp
140     139     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431150_8c9kshfg_.bkp
141     140     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp
142     141     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T060546_8c9l5vgg_.bkp
143     142     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800432539_8c9m4wy1_.bkp
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T055405_8c9khy7t_.bkp RECID=139 STAMP=800430846
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431150_8c9kshfg_.bkp RECID=140 STAMP=800431151
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp RECID=141 STAMP=800430848
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T060546_8c9l5vgg_.bkp RECID=142 STAMP=800431547
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800432539_8c9m4wy1_.bkp RECID=143 STAMP=800432540
Deleted 5 objects

RMAN> delete noprompt copy;  

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
159     1    6       A 27-11-2012 04:11:24
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_6_8c9khxlq_.arc

168     1    7       A 27-11-2012 05:54:05
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9m31n0_.arc

160     1    7       A 27-11-2012 05:54:05
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc

169     1    8       A 27-11-2012 06:05:46
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9m31rn_.arc

161     1    8       A 27-11-2012 06:05:46
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc

170     1    9       A 27-11-2012 06:08:47
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_9_8c9m31yz_.arc

deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_6_8c9khxlq_.arc RECID=159 STAMP=800430845
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9m31n0_.arc RECID=168 STAMP=800432481
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc RECID=160 STAMP=800431546
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9m31rn_.arc RECID=169 STAMP=800432481
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc RECID=161 STAMP=800431979
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_9_8c9m31yz_.arc RECID=170 STAMP=800432482
Deleted 6 objects

Now it's time to take a full database backup including the archived redo log. This backup doesn't contain a tablespace named YYY.

RMAN> backup database plus archivelog;

Starting backup at 27-11-2012 06:25:54
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=171 STAMP=800432754
channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:25:54
channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:25:55
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T062554_8c9mclss_.bkp tag=TAG20121127T062554 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-11-2012 06:25:55

Starting backup at 27-11-2012 06:25:56
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:25:56
channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:36:22
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T062556_8c9mcnw9_.bkp tag=TAG20121127T062556 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:10:26
Finished backup at 27-11-2012 06:36:22

Starting backup at 27-11-2012 06:36:22
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=172 STAMP=800433383
channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:36:23
channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:36:25
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T063623_8c9mz80m_.bkp tag=TAG20121127T063623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-11-2012 06:36:25

Starting Control File and SPFILE Autobackup at 27-11-2012 06:36:25
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800433385_8c9mz9sj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 27-11-2012 06:36:28

My only current backup is formed by the following backup sets:

RMAN> list backup;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
143     247.00K    DISK        00:00:00     27-11-2012 06:25:54
        BP Key: 144   Status: AVAILABLE  Compressed: YES  Tag: TAG20121127T062554
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T062554_8c9mclss_.bkp

  List of Archived Logs in backup set 143
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1       14560297   27-11-2012 06:21:21 14560798   27-11-2012 06:25:54

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
144     Full    9.45M      DISK        00:00:02     27-11-2012 06:31:37
        BP Key: 145   Status: AVAILABLE  Compressed: NO  Tag: TAG20121127T063135
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800433095_8c9mp8mn_.bkp
  SPFILE Included: Modification time: 27-11-2012 06:12:53
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 14560974     Ckp time: 27-11-2012 06:31:35

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
145     Full    680.23M    DISK        00:10:18     27-11-2012 06:36:14
        BP Key: 146   Status: AVAILABLE  Compressed: YES  Tag: TAG20121127T062556
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T062556_8c9mcnw9_.bkp
  List of Datafiles in backup set 145
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  6       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
  7       Full 13915815   02-09-2012 22:03:34 /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
  9       Full 14560804   27-11-2012 06:25:56 /home/oracle/app/oracle/oradata/orcl/example02.dbf

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
146     224.00K    DISK        00:00:01     27-11-2012 06:36:24
        BP Key: 147   Status: AVAILABLE  Compressed: YES  Tag: TAG20121127T063623
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T063623_8c9mz80m_.bkp

  List of Archived Logs in backup set 146
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    2       14560798   27-11-2012 06:25:54 14561201   27-11-2012 06:36:23

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
147     Full    9.45M      DISK        00:00:01     27-11-2012 06:36:26
        BP Key: 148   Status: AVAILABLE  Compressed: NO  Tag: TAG20121127T063625
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800433385_8c9mz9sj_.bkp
  SPFILE Included: Modification time: 27-11-2012 06:12:53
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 14561210     Ckp time: 27-11-2012 06:36:25

RMAN> list copy;  

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
171     1    1       A 27-11-2012 06:21:21
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_1_8c9mcl8b_.arc

172     1    2       A 27-11-2012 06:25:54
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_2_8c9mz7dq_.arc

I'm going to create the new YYY tablespace and a new table (IMPORTANT_YYY_TRANSACTION) containing some committed rows.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL>  create tablespace YYY DATAFILE '/home/oracle/app/oracle/oradata/orcl/tbs01.dbf' size 1M autoextend on next 1M maxsize 10M;

Tablespace created.

SQL> create table IMPORTANT_YYY_TRANSACTION (a number, b number) tablespace YYY;

Table created.

SQL> begin
    for indx IN 1 .. 1000
    loop
    execute immediate 'insert into IMPORTANT_YYY_TRANSACTION(a,b) values (:1, :2)' using indx, indx*2;
    end loop;
    commit;
    end;  2    3    4    5    6    7  
  8  /

PL/SQL procedure successfully completed.

SQL> alter system switch logfile;

System altered.

In Oracle Database 11gR2 the autobackup feature starts within a delay trying to encompass all of the structural changes made to the database rather than creating a new backup of the controlfile on each structural change. This delay could be critical for you and for your transactions and simply implies different ways to restore and recover them. Because I don't want to wait 5 minutes before autobackup controlfile feature starts and completes its job, I force the database to get a controlfile copy using RMAN. The important concept in this scenario is that I have a valid controlfile copy taken after the creation of a new and never backed up tablespace.

[oracle@localhost orcl]$ rman target /

RMAN> backup current controlfile;

Starting backup at 27-11-2012 06:50:02
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:50:06
channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:50:07
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_ncnnf_TAG20121127T065003_8c9nryp2_.bkp tag=TAG20121127T065003 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-11-2012 06:50:07

Starting Control File and SPFILE Autobackup at 27-11-2012 06:50:07
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800434207_8c9ns04h_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 27-11-2012 06:50:08

Let's simulate a loss of our current controlfile and of our "never backed up" new tablespace (YYY).

[oracle@localhost orcl]$ mv control01.ctl control01.ctl.bck
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl.bck
[oracle@localhost orcl]$ mv tbs01.dbf tbs01.dbf.bck

The instance is still up and running

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   13600     1  0 06:11 ?        00:00:01 ora_smon_orcl
oracle   14016 13766  1 06:50 pts/5    00:00:00 grep smon

I force the instance to abort

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> shutdown abort;
ORACLE instance shut down.

Let's try to recover our database. Connecting the RMAN client, the output log shows a "not started" instance.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:51:19 2012

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

connected to target database (not started)

Let's start the instance in nomount mode.

RMAN> startup nomount;

Oracle instance started

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                394267320 bytes
Database Buffers              54525952 bytes
Redo Buffers                   6008832 bytes

We have lost our current controlfile and want to restore it using the autobackup feature. RMAN is able to search from the available backup set and find the right one, in my case it is able to use "o1_mf_s_800434207_8c9ns04h_.bkp" backup set

RMAN> restore controlfile from autobackup;

Starting restore at 27-11-2012 06:51:38
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_11_27/o1_mf_s_800434207_8c9ns04h_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800434207_8c9ns04h_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 27-11-2012 06:51:44

The restored controlfile has references about YYY tablespace: that autobackup of the control file was created indeed after the creation of the lost tablespace.

[oracle@localhost orcl]$ strings /home/oracle/app/oracle/oradata/orcl/control01.ctl|grep -i YYY
YYY
YYY

Connect again with RMAN client to the "not mounted" instance.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:52:16 2012

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

connected to target database: ORCL (not mounted)

A controlfile is now available to mount the instance.

RMAN> alter database mount;

using target database control file instead of recovery catalog
database mounted

The report schema command in "List of Permanent Datafiles" section has information about YYY tablespace and its datafile, even if it is not able to get the right size.

RMAN> report schema;

Starting implicit crosscheck backup at 27-11-2012 06:52:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 27-11-2012 06:52:29

Starting implicit crosscheck copy at 27-11-2012 06:52:29
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-11-2012 06:52:29

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800434207_8c9ns04h_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795852591_86xq10nr_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795834324_86x564xb_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_09_26/o1_mf_s_795045371_867q3d12_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_23/o1_mf_s_800101490_8bzkk05s_.bkp

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    65       UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 ***     /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    0        YYY                ***     /home/oracle/app/oracle/oradata/orcl/tbs01.dbf
9    1        EXAMPLE2             ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

It's now possible to restore YYY tablespace

RMAN> restore tablespace YYY;

Starting restore at 27-11-2012 06:52:43
using channel ORA_DISK_1

creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/tbs01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 27-11-2012 06:52:43

On my local directory "tbs01.dbf" datafile is created.

[oracle@localhost orcl]$ ll -lrt
total 2825100
-rw-rw---- 1 oracle oracle    1056768 Sep  3 22:00 read_only01.dbf
drwxrwxr-x 2 oracle oracle       4096 Nov  9 07:51 non_default_location
-rw-rw---- 1 oracle oracle   20979712 Nov 27 02:47 temp02.dbf
-rw-rw---- 1 oracle oracle   20979712 Nov 27 06:25 temp01.dbf
-rw-rw---- 1 oracle oracle   52429312 Nov 27 06:36 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Nov 27 06:36 redo02b.log
-rw-rw---- 1 oracle oracle  235937792 Nov 27 06:40 users01.dbf
-rw-rw---- 1 oracle oracle    1056768 Nov 27 06:40 example02.dbf
-rw-rw---- 1 oracle oracle   85991424 Nov 27 06:40 example01.dbf
-rw-rw---- 1 oracle oracle    7348224 Nov 27 06:40 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle    1056768 Nov 27 06:48 tbs01.dbf.bck
-rw-rw---- 1 oracle oracle   52429312 Nov 27 06:49 redo03.log
-rw-rw---- 1 oracle oracle   52429312 Nov 27 06:49 redo03b.log
-rw-rw---- 1 oracle oracle   68165632 Nov 27 06:50 undotbs01.dbf
-rw-rw---- 1 oracle oracle  955260928 Nov 27 06:50 system01.dbf
-rw-rw---- 1 oracle oracle 1158684672 Nov 27 06:50 sysaux01.dbf
-rw-rw---- 1 oracle oracle   52429312 Nov 27 06:50 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Nov 27 06:50 redo01b.log
-rw-rw---- 1 oracle oracle    9846784 Nov 27 06:51 control01.ctl.bck
-rw-rw---- 1 oracle oracle    1056768 Nov 27 06:52 tbs01.dbf
-rw-rw---- 1 oracle oracle    9846784 Nov 27 06:52 control01.ctl

At this step you cannot directly recover your tablespace as the "RMAN-06067: RECOVER DATABASE required with a backup or created control file" error suggests.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:52:59 2012

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> recover tablespace YYY;

Starting recover at 27-11-2012 06:53:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/27/2012 06:53:11
RMAN-06067: RECOVER DATABASE required with a backup or created control file

You have to recover the entire database...

RMAN> recover database;

Starting recover at 27-11-2012 06:53:17
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_3_8c9nr8rf_.arc
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_3_8c9nr8rf_.arc thread=1 sequence=3
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-11-2012 06:53:18

...and finally open it with the resetlogs option

RMAN> alter database open resetlogs;

database opened

All previous data inserted and committed into IMPORTANT_YYY_TRANSACTION table are available again.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> select count(*) from IMPORTANT_YYY_TRANSACTION;

  COUNT(*)
----------
      1000

SQL> 

That's all.

How to recover a never backed up tablespace after losing its datafile and even the current controlfile but before the autobackup feature completes its job

During our daily dba job it sometimes happens to add a new tablespace: perhaps a new application deployment requires a dedicated schema where to save tables and information, and you want to localize those tables into a specific tablespace.

You are confident your backup script will still be able to guarantee a valid backup including the new tablespace: indeed in the next few hours your cron job will execute those RMAN backup scripts and take a full backup of your database.

The question is:
what does it happen if you lose your new tablespace before your nigthly backup script is successfully executed ?
Think 5 minutes: you already should know the answer. We saw a similar scenario in a post of this blog ("How to recover from a loss of a read-only tablespace" located at web page http://dbaworkshop.blogspot.it/2012/09/how-to-recover-from-loss-of-read-only.html).

All the required information is still available and written in your archived or online redo log. So the solution of the first question is as simple as write the following lines code:

RMAN> restore tablespace my_new_tablespace;
RMAN> recover tablespace my_new_tablespace

A more difficult question could be instead:
what does it happen when you lose your new tablespace with all your new transactions already committed five minutes before your full RMAN backup script is executed (same situation described above) AND, at the same time, your control file where information of your datafile was recorded ?
And no, the answer is not: "Today is a bad day".
Would be RMAN able to restore your tablespace using a controlfile coming from a backup older than your "CREATE TABLESPACE" command ?
Does your archived or online redo log have instead all the necessary information to restore and recover your tablespace ?
The restore and recovery process depends on the information contained in the available controlfile: there could be two or three different scenarios and I'm going to show them in this post and in the next one.

This post will take into consideration a Recovery Manager setting using the CONTROLFILE AUTOBACKUP feature, a tablespace created after the only available full backup (so this backup doesn't have information on this tablespace), some rows committed on the new tablespace, a crash happened few seconds before the autobackup feature completes its job to create a backup of the controlfile because of a structural change of the database.
Starting with Oracle 11gR2, RMAN creates the autobackup controlfile encompassing all of the structural changes that have occurred in the database within a few minutes.
"Within few minutes..." in my virtualized environment and several production installations means from 5 to even 10 minutes must pass before the autobackup controlfile is created.

So let's start with an example remembering that in this crash scenario you are going to lose the "never backed up" tablespace and the current controlfile at the same time.

Our instance is up and running.

[oracle@localhost orcl]$ ps -ef|grep smon
oracle    9488     1  0 02:47 ?        00:00:05 ora_smon_orcl
oracle   13341 13309  0 05:51 pts/5    00:00:00 grep smon

Connect with RMAN client to see my persistent settings.

[oracle@localhost orcl]$ rman target /

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

Connect with sqlplus client and (try to) drop the tablespace XXX including its contents and datafiles.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> drop tablespace XXX including contents and datafiles;
drop tablespace XXX including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'XXX' does not exist

Now delete all backups and copies. I want to be sure I don't have a valid backup of XXX tablespace.

[oracle@localhost orcl]$ rman target /

RMAN> delete backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
129     128     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_annnn_TAG20121126T220655_8c8p3zwk_.bkp
130     129     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_26/o1_mf_s_800403200_8c8pj2dv_.bkp
131     130     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_nnndf_TAG20121126T220657_8c8p42b4_.bkp
132     131     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_annnn_TAG20121126T221815_8c8ps84p_.bkp
133     132     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_26/o1_mf_s_800403497_8c8ps9rv_.bkp
134     133     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_ncnnf_TAG20121126T223003_8c8qhdq3_.bkp
135     134     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_26/o1_mf_s_800404205_8c8qhg3l_.bkp
136     135     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800419680_8c96m0y8_.bkp
137     136     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800422100_8c98yngz_.bkp
138     137     1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800425124_8c9cx4h2_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_annnn_TAG20121126T220655_8c8p3zwk_.bkp RECID=129 STAMP=800402815
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_26/o1_mf_s_800403200_8c8pj2dv_.bkp RECID=130 STAMP=800403202
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_nnndf_TAG20121126T220657_8c8p42b4_.bkp RECID=131 STAMP=800402818
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_annnn_TAG20121126T221815_8c8ps84p_.bkp RECID=132 STAMP=800403496
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_26/o1_mf_s_800403497_8c8ps9rv_.bkp RECID=133 STAMP=800403497
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_26/o1_mf_ncnnf_TAG20121126T223003_8c8qhdq3_.bkp RECID=134 STAMP=800404204
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_26/o1_mf_s_800404205_8c8qhg3l_.bkp RECID=135 STAMP=800404206
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800419680_8c96m0y8_.bkp RECID=136 STAMP=800419680
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800422100_8c98yngz_.bkp RECID=137 STAMP=800422100
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800425124_8c9cx4h2_.bkp RECID=138 STAMP=800425124
Deleted 10 objects

RMAN> delete noprompt copy;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
156     1    3       A 26-11-2012 22:04:49
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_26/o1_mf_1_3_8c8p3zbb_.arc

157     1    4       A 26-11-2012 22:06:55
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_26/o1_mf_1_4_8c8ps77f_.arc

158     1    5       A 26-11-2012 22:18:15
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_5_8c9chdq1_.arc

deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_26/o1_mf_1_3_8c8p3zbb_.arc RECID=156 STAMP=800402815
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_26/o1_mf_1_4_8c8ps77f_.arc RECID=157 STAMP=800403495
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_5_8c9chdq1_.arc RECID=158 STAMP=800424685
Deleted 3 objects

Now it's time to take a full database backup including the archived redo log. This backup doesn't contain a tablespace named XXX.

RMAN> backup database plus archivelog;

Starting backup at 27-11-2012 05:54:05
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=159 STAMP=800430845
channel ORA_DISK_1: starting piece 1 at 27-11-2012 05:54:06
channel ORA_DISK_1: finished piece 1 at 27-11-2012 05:54:07
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T055405_8c9khy7t_.bkp tag=TAG20121127T055405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-11-2012 05:54:07

Starting backup at 27-11-2012 05:54:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: starting piece 1 at 27-11-2012 05:54:08
channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:05:45
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp tag=TAG20121127T055407 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:37
Finished backup at 27-11-2012 06:05:45

Starting backup at 27-11-2012 06:05:45
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=160 STAMP=800431546
channel ORA_DISK_1: starting piece 1 at 27-11-2012 06:05:47
channel ORA_DISK_1: finished piece 1 at 27-11-2012 06:05:48
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T060546_8c9l5vgg_.bkp tag=TAG20121127T060546 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-11-2012 06:05:48

Starting Control File and SPFILE Autobackup at 27-11-2012 06:05:48
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 27-11-2012 06:05:51

My only current backup is formed by the following backup sets:

RMAN> list backup;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
138     1.27M      DISK        00:00:00     27-11-2012 05:54:06
        BP Key: 139   Status: AVAILABLE  Compressed: YES  Tag: TAG20121127T055405
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T055405_8c9khy7t_.bkp

  List of Archived Logs in backup set 138
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    6       14557799   27-11-2012 04:11:24 14559404   27-11-2012 05:54:05

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
139     Full    9.45M      DISK        00:00:02     27-11-2012 05:59:12
        BP Key: 140   Status: AVAILABLE  Compressed: NO  Tag: TAG20121127T055910
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431150_8c9kshfg_.bkp
  SPFILE Included: Modification time: 26-11-2012 22:07:49
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 14559567     Ckp time: 27-11-2012 05:59:10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
140     Full    680.28M    DISK        00:11:35     27-11-2012 06:05:42
        BP Key: 141   Status: AVAILABLE  Compressed: YES  Tag: TAG20121127T055407
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp
  List of Datafiles in backup set 140
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  6       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
  7       Full 13915815   02-09-2012 22:03:34 /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
  9       Full 14559410   27-11-2012 05:54:08 /home/oracle/app/oracle/oradata/orcl/example02.dbf

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
141     315.50K    DISK        00:00:00     27-11-2012 06:05:47
        BP Key: 142   Status: AVAILABLE  Compressed: YES  Tag: TAG20121127T060546
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_annnn_TAG20121127T060546_8c9l5vgg_.bkp

  List of Archived Logs in backup set 141
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    7       14559404   27-11-2012 05:54:05 14559867   27-11-2012 06:05:46

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
142     Full    9.45M      DISK        00:00:01     27-11-2012 06:05:49
        BP Key: 143   Status: AVAILABLE  Compressed: NO  Tag: TAG20121127T060548
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp
  SPFILE Included: Modification time: 26-11-2012 22:07:49
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 14559876     Ckp time: 27-11-2012 06:05:48

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
159     1    6       A 27-11-2012 04:11:24
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_6_8c9khxlq_.arc

160     1    7       A 27-11-2012 05:54:05
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc

I'm going to create the new XXX tablespace and a new table (IMPORTANT_XXX_TRANSACTION) containing some commited rows.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> create tablespace XXX DATAFILE '/home/oracle/app/oracle/oradata/orcl/XXX01.dbf' size 1M autoextend on next 1M maxsize 10M;

Tablespace created.

SQL> create table IMPORTANT_XXX_TRANSACTION (a number, b number) tablespace XXX;

Table created.

SQL> begin
  2  for indx IN 1 .. 1000
  3  loop
  4  execute immediate 'insert into IMPORTANT_XXX_TRANSACTION(a,b) values (:1, :2)' using indx, indx*2;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> alter system switch logfile;

System altered.

As anticipated at the beginning of this post in Oracle Database 11gR2 the autobackup feature starts within a delay trying to encompass all of the structural changes made to the database rather than creating a new backup of the controlfile on each structural change. This delay could be critical for you and for your transactions and simply implies different ways to restore and recover them.
Let's simulate a loss of our current controlfile and of our "never backed up" new tablespace (XXX), before our instance remembers to create the "delayed" autobackup controlfile.

[oracle@localhost orcl]$ cd /home/oracle/app/oracle/oradata/orcl/
[oracle@localhost orcl]$ ls
APEX_1930613455248703.dbf  example01.dbf        XXX201.dbf.bck  redo01b.log  redo02.log   sysaux01.dbf  temp02.dbf
control01.ctl              example02.dbf        non_default_location      redo01.log   redo03b.log  system01.dbf  undotbs01.dbf
control01.ctl.bck          XXX01.dbf  read_only01.dbf           redo02b.log  redo03.log   temp01.dbf    users01.dbf
[oracle@localhost orcl]$ mv control01.ctl control01.ctl.bck
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl.bck
[oracle@localhost orcl]$ mv XXX01.dbf XXX01.dbf.bck

The instance is still up and running

[oracle@localhost orcl]$ ps -ef|grep smon
oracle    9488     1  0 02:47 ?        00:00:05 ora_smon_orcl
oracle   13499 13309  0 06:09 pts/5    00:00:00 grep smon

The rows are still available

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> select count(*) from IMPORTANT_XXX_TRANSACTION;

  COUNT(*)
----------
      1000

The alert log discovered a current controlfile is missing.

[oracle@localhost orcl]$ vi /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
...
Tue Nov 27 06:09:52 2012
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_13503.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...

The instance is not working anymore.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 27 06:10:22 2012

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

Connected to an idle instance.

SQL>  select count(*) from IMPORTANT_XXX_TRANSACTION;
 select count(*) from IMPORTANT_XXX_TRANSACTION
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

I force the instance to abort.

SQL> shutdown abort;
ORACLE instance shut down.

Inside the "lost" controlfile some references to the new tablespace are present.

[oracle@localhost orcl]$ strings control01.ctl.bck |grep -i XXX
XXX
XXX

Let's try to recover our database. Connecting the RMAN client, the output log shows a not started instance.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:11:05 2012

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

connected to target database (not started)

Start the instance in NOMOUNT mode
RMAN> startup nomount;

Oracle instance started

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                394267320 bytes
Database Buffers              54525952 bytes
Redo Buffers                   6008832 bytes

We have lost our current controlfile and want to restore it using the autobackup feature. RMAN is able to search from the available backup set and find the right one.

RMAN> restore controlfile from autobackup;

Starting restore at 27-11-2012 06:11:28
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_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 27-11-2012 06:11:32

The restored controlfile doesn't have any references about the XXX tablespace: that autobackup of the control file was created indeed before the creation of the lost tablespace.

[oracle@localhost orcl]$ strings control01.ctl |grep -i XXX

Connect again with RMAN client to the "not mounted" instance.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 27 06:12:41 2012

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

connected to target database: ORCL (not mounted)

A controlfile is now available to mount the instance.

RMAN> alter database mount;

using target database control file instead of recovery catalog
database mounted

The report schema command in "List of Permanent Datafiles" section does not have any information about XXX tablespace and its datafile.

RMAN> report schema;

Starting implicit crosscheck backup at 27-11-2012 06:12:57
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 27-11-2012 06:12:59

Starting implicit crosscheck copy at 27-11-2012 06:12:59
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-11-2012 06:12:59

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_27/o1_mf_s_800431548_8c9l5xbv_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795852591_86xq10nr_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795834324_86x564xb_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_09_26/o1_mf_s_795045371_867q3d12_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_23/o1_mf_s_800101490_8bzkk05s_.bkp

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    65       UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 ***     /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
9    1        EXAMPLE2             ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

The "RMAN-20202: Tablespace not found in the recovery catalog" and "RMAN-06019: could not translate tablespace name "XXX"" errors are thrown, because the controlfile used has no information about that tablespace as you can see in the following output:

RMAN> restore tablespace XXX;

Starting restore at 27-11-2012 06:13:47
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/27/2012 06:13:47
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "XXX"

To recover the missing and "never backed up" tablespace you must restore and recover the entire database.

RMAN> restore database;

Starting restore at 27-11-2012 06:14:31
using channel ORA_DISK_1

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_1930613455248703.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_27/o1_mf_nnndf_TAG20121127T055407_8c9kj0ty_.bkp tag=TAG20121127T055407
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:18
Finished restore at 27-11-2012 06:19:49

RMAN> recover database;

Starting recover at 27-11-2012 06:21:02
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_7_8c9l5tb5_.arc thread=1 sequence=7
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc thread=1 sequence=8
creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/XXX01.dbf
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc thread=1 sequence=8
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-11-2012 06:21:12

In the above log of the recover database command you can see the creation of datafile number 8 ...

...
creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/XXX01.dbf
...

... and in the alert log you can find also the following lines:

...
Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_27/o1_mf_1_8_8c9lcht5_.arc
File #8 added to control file as 'UNNAMED00008'. Originally created as:
'/home/oracle/app/oracle/oradata/orcl/XXX01.dbf'
...

Since I restored a controlfile from a backup, it is required to open the database using the resetlogs option:

RMAN> alter database open resetlogs;

database opened

The new current controlfile has records about the XXX tablespace and its datafile.

[oracle@localhost orcl]$ strings control01.ctl |grep -i XXX
/home/oracle/app/oracle/oradata/orcl/XXX01.dbf
/home/oracle/app/oracle/oradata/orcl/XXX01.dbf
XXX
XXX

All previous data inserted and committed into IMPORTANT_XXX_TRANSACTION table are available again.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> select count(*) from IMPORTANT_XXX_TRANSACTION;

  COUNT(*)
----------
      1000

That's all.

How to restore from a loss of all current control files to a non default location using a backup piece

Here again with a scenario simulating a loss of all the control files and restoring them to a non-default location: differently from previous posts (here and here) you don't have an autobackup control file available, but just a backup piece.

First of all just create a location outside the flash recovery area where to save the backup piece containing the current control file:

[oracle@localhost oracle]$ pwd
/home/oracle/app/oracle/
[oracle@localhost oracle]$ mkdir controlfile_copy
[oracle@localhost oracle]$ cd controlfile_copy/
[oracle@localhost controlfile_copy]$ ll
total 0

Now it's time to create the backup piece with the current control file.

RMAN> backup current controlfile to destination '/home/oracle/app/oracle/controlfile_copy';

Starting backup at 09-11-2012 06:29:08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-11-2012 06:29:10
channel ORA_DISK_1: finished piece 1 at 09-11-2012 06:29:11
piece handle=/home/oracle/app/oracle/controlfile_copy/ORCL/backupset/2012_11_09/o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp tag=TAG20121109T062908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-11-2012 06:29:11

In your local directory RMAN was able to create your backup piece as written in the above log and as you can verify moving through those directories:

[oracle@localhost controlfile_copy]$ ll
total 4
drwxrwx--- 3 oracle oracle 4096 Nov  9 06:29 ORCL
[oracle@localhost controlfile_copy]$ cd ORCL/backupset/2012_11_09/
[oracle@localhost 2012_11_09]$ ll
total 9680
-rw-rw---- 1 oracle oracle 9895936 Nov  9 06:29 o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp

Let's simulate the loss of all current control files.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

Instance is still running, so I have to kill it.

[oracle@localhost ~]$ ps -ef|grep smon
oracle    2384     1  0 03:58 ?        00:00:03 ora_smon_orcl
oracle    6758  3039  0 06:35 pts/1    00:00:00 grep smon
[oracle@localhost ~]$ kill -9 2384
[oracle@localhost ~]$ ps -ef|grep smon
oracle    6762  3039  0 06:35 pts/1    00:00:00 grep smon

Under my non_default_location directory I will restore the control file: currently it's empty.

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 0

Use RMN to connect to your target instance.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 9 07:46:58 2012

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

connected to target database (not started)

Start the database in NOMOUNT mode.

RMAN> startup nomount;

Oracle instance started

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes

Use the following RESTORE CONTROLFILE command to get the control file backup from your backup piece and restore it to your non default location:

RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' from '/home/oracle/app/oracle/controlfile_copy/ORCL/backupset/2012_11_09/o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp';

Starting restore at 09-11-2012 07:51:43
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: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 09-11-2012 07:51:46

After the restore process finishes you can find the control file under non_default_location directory

[oracle@localhost non_default_location]$ ll
total 9632
-rw-rw---- 1 oracle oracle 9846784 Nov  9 07:51 control01.ctl

Instance is still not able to go to the next mode because it doesn't know where restored control file is located.

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/09/2012 07:54:04
ORA-00205: error in identifying control file, check alert log for more info

Use sqlplus to connect the instance ...

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 07:54:50 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

... have a look at the control_files initialization parameter written in spfile ...

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/control01.ctl, 

                                                 /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

... and modify it according to your new control file location.

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' scope=spfile;

System altered.

Close the instance ...

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

.. and start it again in MOUNT mode using RMAN.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 9 07:56:56 2012

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes

You have to perform a recovery of the database after the restore of the control file, even if any datafile was restored.

RMAN> recover database;

Starting recover at 09-11-2012 07:57:34
Starting implicit crosscheck backup at 09-11-2012 07:57:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 09-11-2012 07:57:36

Starting implicit crosscheck copy at 09-11-2012 07:57:36
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-11-2012 07:57:36

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_07/o1_mf_ncnnf_TAG20121107T223343_89pnl8z3_.bkp.old
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795852591_86xq10nr_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795834324_86x564xb_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_09_26/o1_mf_s_795045371_867q3d12_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp

using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:13
Finished recover at 09-11-2012 07:57:52

After the restore of the control file you have to open the database with RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

If you want to come back to the original settings because the original location is available again, you have to modify again the control_files initialization parameter value.

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl

Set the control_files parameter to the original configuration:

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

Close your instance

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy your current control file to the original locations:

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 9632
-rw-rw---- 1 oracle oracle 9846784 Nov  9 08:01 control01.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

Connect to the instance...

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 08:07:28 2012

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

Connected to an idle instance.

... and start it

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             369101496 bytes
Database Buffers           79691776 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/control01.ctl,                                                
                                                 /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

That's all.

How to restore from a loss of all current control files to a non default location using autobackup

Like the previous scenario the following one simulates again a database losing all the control files, but they will be restored using the autobackup to a non-default location.
As already stated in the mentioned previous post when losing all current control files you are only able to open your database in NOMOUNT mode.

Also remember that "when you lose all (or one) control files and restore them (or one of them) from a backup control file, you have to perform a recovery of your database and open it with the RESETLOGS option, even if any datafile is restored (like in this scenario).
Anyway a control file restored from a backup has an SCN taken at that "remote" time, different compared with those currently available in the datafiles and redo logs and so they have to be resynchronized.
Generally speaking, having the instance in NOMOUNT mode means your control files are still not accessed (if available), so RMAN is not able to know how to find information about an unidentified database: DBID indeed is contained into the control file.
If you are using a flash recovery area or a recovery catalog (best practice's solution) then you don't have to set the DBID before executing the RESTORE command of your NOMOUNTED instance, saving time and avoiding extra manual steps always prone to error."

Let's start.

The instance is not running.

[oracle@localhost ORCL]$ ps -ef|grep smon
oracle   12901  2820  0 07:24 pts/1    00:00:00 grep smon

Let's simulate the loss of all current control files.

[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

In my future non default location there still isn't any file.

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 0

Connect through RMAN and...

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jul 23 07:27:53 2012

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

connected to target database (not started)

... start the instance in nomount mode

RMAN> startup nomount;

Oracle instance started

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes

Execute the following command to restore the autoback control file copy to a different location compared to the originals.

RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' from autobackup;

Starting restore at 23-07-2012 07:30:26
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_07_21/o1_mf_s_789209074_80omm3d0_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
Finished restore at 23-07-2012 07:30:29

After the execution of restore command you can find a control file under the specified location

[oracle@localhost non_default_location]$ ll
total 9536
-rw-rw---- 1 oracle oracle 9748480 Jul 23 07:30 control01.ctl

Is it possible to mount the database ? No, of course.

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/23/2012 07:34:00
ORA-00205: error in identifying control file, check alert log for more info

You have to modify at least the control_files parameter and set the location of the new available control file.

[oracle@localhost non_default_location]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 23 07:35:29 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' scope=spfile;

System altered.

Shutdown the instance.
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

Connect the instance with RMAN and start it in mount mode

[oracle@localhost orcl]$ rman target /

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes

Issue the recover command for the whole database...

RMAN> recover database;

Starting recover at 23-07-2012 07:38:49
Starting implicit crosscheck backup at 23-07-2012 07:38:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 23-07-2012 07:38:51

Starting implicit crosscheck copy at 23-07-2012 07:38:51
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 23-07-2012 07:38:52

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-07-2012 07:38:54

...and, as already stated, open it with the RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

Now if your original location become available again, you may want to configure the control_files parameter to the original value.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl
SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

Close the instance...

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

...and copy the only available control file to the original locations.

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 9536
-rw-rw---- 1 oracle oracle 9748480 Jul 23 07:30 control01.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

Connect to the instance and start it once again.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 23 07:40:13 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             369101496 bytes
Database Buffers           79691776 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

The instance is available, the database is in OPEN mode and ready to be used with control_files parameter modified.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/control01.ctl, /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

That's all.

How to restore from a loss of all current control files to the default location

The following scenario simulate a loss of all the control files and the restore process using a backup control file with any Recovery Catalog.

In this situation you are only able to open your database in NOMOUNT mode. Also remember that when you lose all (or one) control files and restore them (or one of them) from a backup control file, you have to perform a recovery of your database and open it with the RESETLOGS option, even if any datafile is restored (like in this scenario).

That's not always true when you're dealing with "created" control file (I hope to simulate that scenario one day), as long as you must specify RESETLOGS if the online logs are lost or NORESETLOGS if the online logs are available.

Anyway a control file restored from a backup has an SCN taken at that "remote" time, different compared with those currently available in the datafiles and redo logs and so they have to be resynchronized.
Generally speaking, having the instance in NOMOUNT mode means your control files are still not read (if available), so RMAN is not able to know how to find information about an "unidentified" database: DBID indeed is contained into the control file.
If you are using a flash recovery area or a recovery catalog (best practice's solution) then you don't have to set the DBID before executing the RESTORE command of your NOMOUNTED instance, saving time and avoiding extra manual steps always prone to error.

Let's start. My instance is running

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   11655     1  0 08:13 ?        00:00:00 ora_smon_orcl
oracle   11811  2820  0 08:20 pts/1    00:00:00 grep smon

Suddenly all my control file are lost.

[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

When trying to create a tablespace some errors are thrown:

SQL> create tablespace t1 datafile
'/home/oracle/app/oracle/oradata/orcl/t101.dbf' size 1M;
create tablespace t1 datafile '/home/oracle/app/oracle/oradata/orcl/t101.dbf'
size 1M
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

The instance is crashed

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   11655     1  0 08:13 ?        00:00:00 ora_smon_orcl

As you can verify the mentioned (/home/oracle/app/oracle/oradata/orcl/control01.ctl) file doesn't exist.

[oracle@localhost orcl]$ ll
total 2502160
-rw-rw---- 1 oracle oracle    7348224 Jul 21 08:14 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle   85991424 Jul 21 08:14 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 21 08:11 non_default_location
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:23 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:14 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:14 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:23 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 21 08:20 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 21 07:14 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 21 08:19 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 21 08:14 users01.dbf

Let's try to restore the missing control files, starting the instance in NOMOUNT mode:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes

Connect using RMAN and issue the RESTORE CONTROLFILE FROM AUTOBACKUP command. DBIS is not set, but because I'm using the flash recovery area, RMAN is able to find a backup control file.

[oracle@localhost orcl]$ rman target /

RMAN> restore controlfile from autobackup;

Starting restore at 21-07-2012 08:36:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file
name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 21-07-2012 08:36:25

Let's see if the instance is able to read our restored control files, bringing the database in MOUNT state:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

What does it happen if I try to simply open the database ? It fails with a clear error.

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/21/2012 08:42:50
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

As said at the beginning of this post when you restore a control file from a backup you have first to recover the database...

RMAN> recover database;

Starting recover at 21-07-2012 08:43:26
Starting implicit crosscheck backup at 21-07-2012 08:43:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 21-07-2012 08:43:28

Starting implicit crosscheck copy at 21-07-2012 08:43:28
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 21-07-2012 08:43:28

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc
archived log for thread 1 with sequence 3 is already on disk as file
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc
archived log for thread 1 with sequence 4 is already on disk as file
/home/oracle/app/oracle/oradata/orcl/redo01.log
archived log file
name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc
thread=1 sequence=2
archived log file
name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc
thread=1 sequence=3
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log
thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-07-2012 08:43:31

... and then open it with the RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

The database is now open and control files are available again.

[oracle@localhost orcl]$ ll
total 2511696
-rw-rw---- 1 oracle oracle    7348224 Jul 21 08:44 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle    9748480 Jul 21 08:45 control01.ctl
-rw-rw---- 1 oracle oracle   85991424 Jul 21 08:44 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 21 08:11 non_default_location
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:45 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:44 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:44 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:44 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 21 08:44 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 21 07:14 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 21 08:44 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 21 08:44 users01.dbf

That's all

How to retrieve the DATABASE IDENTIFIER dumping it from datafiles or online and archived redo logs

I think it's very difficult today to be in a situation where it's required to know your database identifier to recover the database.
But according to an OP (original poster) it seems it could happen: indeed on that Oracle forum's thread it was asked how it could be possible to determine a database identifier (DBID) when:
1) an instance is already crashed and no control files are available
2) no one saved the RMAN output of daily backup
3) you are using a flash recovery area and your autobackup are saved using OMF syntax.

A typical scenario where it is asked to know the database identifier is indeed when:
- you are restoring a control file (and I can image you lose all control files) AND
- you don't have a recovery catalog AND
- you don't have even a flash recovery area configured OR you have it but you didn't specify the %F in the RMAN autobackup option;

So if your instance is down and control files are unavailable you cannot open the database and query the V$DATABASE to know the DBID.
I mean you can't execute query like this:

SQL> select DBID from V$DATABASE;

      DBID
----------
1229390655

Then when you connect to your instance using RMAN it will show you the database identifier...

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Oct 16 06:49:13 2012

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

connected to target database: ORCL (DBID=1229390655)

...but as long as you didn't redirect RMAN output to some logs and preserved them in a safe directory or simply email it to you during daily backup, when instance is down and without control files RMAN could not help you anymore:

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Oct 16 06:57:44 2012

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

connected to target database (not started)

Moreover if you didn't specify the %F format to your autobackup control file option, I mean something like:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/app/oracle/autobackup_controlfile/%F';

...your database will save it using the OMF format. As you can read on the Oracle documentation at this link 
"All files in the fast recovery area are maintained by Oracle Database and associated file names are maintained in the Oracle Managed Files (OMF) format" indeed your autobackup control file won't be useful to deduct your DBID using the desired format c-IIIIIIIIII-YYYYMMDD-QQ (where IIIIIIIIII would be your database dentifier).

So then how can you proceed ? Is it no more possible to know the database identifier ?

My suggestion was to simply use the "ALTER SYSTEM DUMP" command.
As long as you can dump any datafiles, redo logs and even archived redo logs the instance could be in NOMOUNT mode: to obtain the desired DBID you have only to know the exact path of your file.

Have a look at the following samples:

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup nomount;

Command to dump the SYSTEM datafile:

SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/system01.dbf' block min 1 block max 2;
 
System altered.

Under the trace directory I found a new trace file which content is not so clear, but at least our database identifier (Db ID=1229390655) is showed.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/system01.dbf minblk 1 maxblk 2
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'

... 

Command to dump the UNDO datafile:

SQL>  alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'  block min 1 block max 2;
 
System altered.

Again on the same trace file Oracle appends the dump of the UNDO datafile requested and the same DBID.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf minblk 1 maxblk 2
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'
...

What about to dump an online redo log ?

SQL> alter system dump logfile '/home/oracle/app/oracle/oradata/orcl/redo01.log' ;
 
System altered.

Always on the same trace file you can find the DBID.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
DUMP OF REDO FROM FILE '/home/oracle/app/oracle/oradata/orcl/redo01.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'
        Activation ID=1323612153=0x4ee4b7f9
...

Finally even dumping an ARCHIVED redo log...

SQL> alter system dump logfile '/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_10_05/o1_mf_1_20_86xpzzvr_.arc';
 
System altered.
 

... and looking at the trace file, the DBID is showed again.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
DUMP OF REDO FROM FILE '/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_10_05/o1_mf_1_20_86xpzzvr_.arc'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'
...

You have no excuse to be not able retrieving a specific database identifier.

That's all.