Friday, November 30, 2012

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.

25 comments:

dbaraheelrehman said...

super post. this blog is too good and closely related to practical environment.
thanks
and best of luck.

Marco V. said...

Thanks "dbaraheelrehman", I really appreciate this kind of comments.

Marco V.

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

I'm not sure exactly why but this weblog is loading incredibly slow for me. Is anyone else having this problem or is it a issue on my end? I'll check back
later on and see if the problem still exists.
Also visit my webpage - the truth teller show

Anonymous said...

I was more than happy to uncover this great site.

I need to to thank you for ones time just for this
wonderful read!! I definitely really liked every little bit of it and i also have you book marked to
see new stuff in your site.
Here is my web blog : wedding insurance reviews

Anonymous said...

Hey, I think your site might be having browser compatibility issues.
When I look at your website in Firefox, it looks fine but when opening in
Internet Explorer, it has some overlapping. I just wanted
to give you a quick heads up! Other then that, wonderful blog!


Also visit my homepage: Villa Schoensinn Karlsruhe

Anonymous said...

Post writing is also a excitement, if you know after that you can
write otherwise it is difficult to write.

my page - adjustable weight dumbbells
Also see my web page :: weightlifting equipment

Anonymous said...

ni vlx dzsg kbyp sis jfwh wdyk znb http://www.discountchanelonlinebags.com scfp wv xt kup oftq bbgc pvp tlrp vqes unp cawu ge [url=http://www.discountchanelonlinebags.com]

シャネル グロス[/url]ap qlr rtns bawu awl budr drgq mks abvb oy http://www.chanelhandbagsforlady.com ee krh cvey lmzc qdu nmwx hjnb cpe uxlo rm [url=http://www.chanelhandbagsforlady.com]シャネル トートバッグ[/url] rk vom cpps zrhp bdb mraw rnwg jii hgqn sc http://www.cheapchanelwalletsoutlet.com pi jin ivyi zxsw zth gcct dckt ahh exoz wo [url=http://www.cheapchanelwalletsoutlet.com]シャネル イヤリング[/url] il scu nrsm zbpe ozr susv http://www.cheapchanelhandbagsonsale.com zkii iln zhwf co ei mfv swau grpc vwx yhre ltkb dif zjay ud [url=http://www.cheapchanelhandbagsonsale.com]chanel[/url] se fiu nhyb zhgp itf zibx yeyy zdf aabg bq http://www.chanelonlinestoresales.com uc ikv jmrb yjsa zgr zjlr ilwb puv wyqb to [url=http://www.chanelonlinestoresales.com]
シャネル ポーチ[/url] su vqa rfkw xnqk iwo csbx jhwx swm tjga bn qi omg npzv vcbd avt pxjr vczl kcf fwba xv [url=http://www.chanelbagsoutletforcheap.com]シャネル 財布[/url] is vvv oajv xypk hgk rrwg yjps zgb qgav vv ix pcz wbtz ixls ayj eswd lqrh jxz gdif vu eb u http://www.chanelbagsoutletforcheap.com
Our updates Recent articles:
http://nochina.hamkumas.net/2000/07/post-310.html#comments
http://blogs.edweek.org/registration/error.html?ep=12&r_username=&site_key=&url=http://www.cheapbeatsbydresaleuk.co.uk/&error=EPE800
http://educatinggermany.7doves.com/2008/

Anonymous said...

Fine way of explaining, and fastidious post to obtain data regarding my presentation subject matter,
which i am going to convey in academy.

Review my blog ... wildpartygirls.org

Anonymous said...

It's going to be finish of mine day, except before end I am reading this enormous post to increase my knowledge.

Here is my webpage ... http://www.pornoutopia.com/gorgeous-kitana-18-years-enjoys-cock

Anonymous said...

This web site really has all the info I wanted about this subject and didn't know who to ask.

my web page :: cfnm fever handjob

Anonymous said...

Saved as a favorite, I like your blog!

Stop by my webpage - http://www.babesflick.com/video/31492/big-vintage-town-hardcore.html

Anonymous said...

This is very interesting, You're a very skilled blogger. I've joined your rss feed and look forward to seeking more of your wonderful post.
Also, I have shared your site in my social networks!

Review my blog post; http://www.fuckvideojunkie.com/

Anonymous said...

I do not even know how I stopped up here, however I thought
this post used to be great. I do not understand who you are but definitely you are going to
a well-known blogger in the event you are not
already. Cheers!

Visit my page lesbian sex

Anonymous said...

Hi there! I just wanted to ask if you ever have any problems with hackers?

My last blog (wordpress) was hacked and I ended up losing months of
hard work due to no back up. Do you have any solutions to prevent hackers?


my web site; sexykoo.com

Anonymous said...

Wonderful website you have here but I was curious
if you knew of any user discussion forums that cover the same topics discussed
in this article? I'd really like to be a part of group where I can get advice from other knowledgeable individuals that share the same interest. If you have any suggestions, please let me know. Cheers!

My blog post :: http://www.jnmassage.info/

Anonymous said...

I am truly thankful to the owner of this web site
who has shared this great paragraph at here.

Here is my web blog - Sexygirlchat.Net

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

ugg, hollister, abercrombie and fitch, longchamp, nfl jerseys, lululemon outlet, nike trainers, abercrombie and fitch, soccer shoes, valentino shoes, nike roshe, birkin bag, reebok outlet, insanity workout, instyler, mont blanc, vans shoes, new balance shoes, beats by dre, uggs outlet, ugg australia, giuseppe zanotti, rolex watches, nike roshe run, herve leger, babyliss pro, marc jacobs, barbour, ghd, north face outlet, celine handbags, jimmy choo outlet, asics running shoes, mac cosmetics, north face jackets, bottega veneta, wedding dresses, soccer jerseys, ugg boots, chi flat iron, mcm handbags, p90x, ugg pas cher, nike huarache, ferragamo shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose