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 smonConnect 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'; # defaultConnect 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 existNow 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:51My 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.bckThe 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 smonThe 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: 0I 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 XXXLet'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 bytesWe 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:32The 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 XXXConnect 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 mountedThe 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.dbfThe "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:12In 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 openedThe 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 XXXAll 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.