Skip to Main Content
  • Questions

  • RMAN : how to restore a dropped tablespace if no catalog and no PITR

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: November 03, 2020 - 9:20 am UTC

Last updated: December 17, 2024 - 6:24 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked


Hello experts,

I am in 12.2, multi-tenant architecture, no RMAN catalog, auto backup control file.

I have a problem to restore with RMAN a deleted tablespace.

I create it and I made a complete backup of my container with the PDB and the tbs.
SQL> CREATE TABLESPACE ZZTBS DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
...
Starting backup at 02-NOV-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
input datafile file number=00014 name=/u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
channel ORA_DISK_1: starting piece 1 at 02-NOV-20
channel ORA_DISK_1: finished piece 1 at 02-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_02/o1_mf_nnndf_TAG20201102T102548_ht097xb2_.bkp tag=TAG20201102T102548 comment=NONE
...


We see that the backup is OK : BS 2, Key 16 and, most important, the column Name is fill with the datafile of my tbs.
RMAN> list backup;
List of Backup Sets
===================
...
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.41G      DISK        00:00:34     02-NOV-20      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20201102T102548
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_02/o1_mf_nnndf_TAG20201102T102548_ht097xb2_.bkp
  List of Datafiles in backup set 2
  Container ID: 3, PDB Name: ORCL
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  9       Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
  10      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
  11      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
  12      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
  13      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
  14      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
  16      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf


I delete my tbs.
SQL> drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.



The problem is that, after the delete tbs, in the control file there is no more reference to my tbs.
So, when I use RMAN, connected to the PDB, I get an error message saying that it does not know my tbs.
RMAN> LIST BACKUP OF TABLESPACE ZZTBS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/02/2020 10:28:10
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "ZZTBS"


Now, the column Name is empty...
RMAN> list backup;
List of Backup Sets
===================
...
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.41G      DISK        00:00:34     02-NOV-20      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20201102T102548
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_02/o1_mf_nnndf_TAG20201102T102548_ht097xb2_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  9       Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
  10      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
  11      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
  12      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
  13      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
  14      Full 2166604    02-NOV-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
  16      Full 2166604    02-NOV-20              NO



I tried to go through an auxiliary database, this one is created, the tbs datafile is restored but, at the end, I still have this message saying that the tbs is unknown.
RMAN> RECOVER TABLESPACE ZZTBS until SCN 2168640 auxiliary destination '/u01/oraaux/';
...
Creating automatic instance, with SID='zBxq'
...

-- Hey, Oracle know my tbs; I think it uses the backuped control file!
List of tablespaces that have been dropped from the target database:
Tablespace ZZTBS
...
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_02/o1_mf_s_1055414350_ht0b0yfl_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_02/o1_mf_s_1055414350_ht0b0yfl_.bkp tag=TAG20201102T103910
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oraaux/ORCL12C/controlfile/o1_mf_ht0ck6w8_.ctl
Finished restore at 02-NOV-20

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

Removing automatic instance
shutting down automatic instance 
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/oraaux/ORCL12C/controlfile/o1_mf_ht0ck6w8_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/02/2020 11:05:01
RMAN-06136: ORACLE error from auxiliary database: RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "ZZTBS"



I especially do not want to do the PITR database because you must not overwrite the operations made on the database since the removal of the tbs.
The only scenario I can think of would be to create an auxiliary database from the RMAN backup containing my tbs and then to export / import datapump to my source database but I don't see how to do it.
Or maybe you have a better idea...

So, if you can help me, a big big thank you in advance :-)









and Connor said...

Well...its amazing how you can miss the little things when you dig *too deep* into the detail.

I've been going back and forth with the Backup/Recovery team, and we've been looking for bugs, doing tests with and without an RMAN catalog etc etc.

And I was just about out of ideas, when running a test for the n'th the blindingly obvious dawned on me..... the syntax is wrong !!!

Here's my example

RMAN> recover tablespace DEMO until logseq 6 auxiliary destination '/u01/oradata/aux';

Starting recover at 17-NOV-20
using channel ORA_DISK_1

Creating automatic instance, with SID='tyxC'

[snip]

Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/17/2020 20:19:13
RMAN-06004: Oracle error from recovery catalog database: RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "DEMO"


and there is the corrected syntax :-)

                         ####
                         ####  Need the pdb name!!!
                         ####
RMAN> recover tablespace pdb1:demo until logseq 6 auxiliary destination '/u01/oradata/aux';

Starting recover at 17-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK

Creating automatic instance, with SID='kvym'

initialization parameters used for automatic instance:
db_name=DB192
db_unique_name=kvym_pitr_pdb1_DB192
compatible=19.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2048M
processes=200
db_create_file_dest=/u01/oradata/aux
log_archive_dest_1='location=/u01/oradata/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
max_string_size=EXTENDED
#No auxiliary parameter file used


starting up automatic instance DB192

Oracle instance started

Total System Global Area    2147482136 bytes

Fixed Size                     9136664 bytes
Variable Size                503316480 bytes
Database Buffers            1627389952 bytes
Redo Buffers                   7639040 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  logseq 6 thread 1;
# restore the controlfile
restore clone controlfile;
 
# mount the controlfile
sql clone 'alter database mount clone database';
 
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 17-NOV-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.2.0/dbhome_1/dbs/c-2928453766-20201117-05
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.2.0/dbhome_1/dbs/c-2928453766-20201117-05 tag=TAG20201117T020328
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/aux/DB192/controlfile/o1_mf_hv98qyrx_.ctl
Finished restore at 17-NOV-20

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  logseq 6 thread 1;
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := 'PDB1'; -- pdbname
  sqlstatement := 'alter tablespace '||  'DEMO' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  9 to new;
set newname for clone datafile  1 to new;
set newname for clone datafile  11 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  10 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
set newname for datafile  19 to 
 "/u01/oradata/DB192/pdb1/demo.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  9, 1, 11, 4, 3, 10, 19;
 
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace DEMO offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oradata/aux/DB192/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 17-NOV-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/oradata/aux/DB192/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/oradata/aux/DB192/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oradata/aux/DB192/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/oradata/bkp/0jvfn9cp_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/oradata/bkp/0jvfn9cp_1_1 tag=TAG20201117T020201
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u01/oradata/DB192/pdb1/demo.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/oradata/bkp/0kvfn9e6_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/oradata/bkp/0kvfn9e6_1_1 tag=TAG20201117T020201
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 17-NOV-20

datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1056745866 file name=/u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_system_hv98tlyw_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=1056745866 file name=/u01/oradata/aux/DB192/datafile/o1_mf_system_hv98r7r3_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1056745866 file name=/u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_undotbs1_hv98tlxq_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=1056745866 file name=/u01/oradata/aux/DB192/datafile/o1_mf_undotbs1_hv98r7rw_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1056745866 file name=/u01/oradata/aux/DB192/datafile/o1_mf_sysaux_hv98r7rb_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1056745866 file name=/u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_sysaux_hv98tlyn_.dbf

contents of Memory Script:
{
# set requested point in time
set until  logseq 6 thread 1;
# online the datafiles restored or switched
sql clone 'PDB1' "alter database datafile 
 9 online";
sql clone "alter database datafile  1 online";
sql clone 'PDB1' "alter database datafile 
 11 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB1' "alter database datafile 
 10 online";
sql clone 'PDB1' "alter database datafile 
 19 online";
# recover and open resetlogs
recover clone database tablespace  "PDB1":"DEMO", "PDB1":"SYSTEM", "SYSTEM", "PDB1":"UNDOTBS1", "UNDOTBS1", "SYSAUX", "PDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  9 online

sql statement: alter database datafile  1 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  10 online

sql statement: alter database datafile  19 online

Starting recover at 17-NOV-20
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/oradata/arch/1_4_1056679246.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/oradata/arch/1_5_1056679246.dbf
archived log file name=/u01/oradata/arch/1_4_1056679246.dbf thread=1 sequence=4
archived log file name=/u01/oradata/arch/1_5_1056679246.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-NOV-20

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB1 open';
}
executing Memory Script

sql statement: alter pluggable database  PDB1 open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'PDB1' 'alter tablespace 
 DEMO read only';
# create directory for datapump import
sql 'PDB1' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/oradata/aux''";
# create directory for datapump export
sql clone 'PDB1' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/oradata/aux''";
}
executing Memory Script

sql statement: alter tablespace  DEMO read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oradata/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oradata/aux''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_kvym_notg":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Master table "SYS"."TSPITR_EXP_kvym_notg" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_kvym_notg is:
   EXPDP>   /u01/oradata/aux/tspitr_kvym_38369.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace DEMO:
   EXPDP>   /u01/oradata/DB192/pdb1/demo.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_kvym_notg" successfully completed at Tue Nov 17 20:33:18 2020 elapsed 0 00:00:34
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'PDB1' 'drop tablespace 
 DEMO including contents keep datafiles cascade constraints';
}
executing Memory Script

Oracle instance shut down

sql statement: drop tablespace  DEMO including contents keep datafiles cascade constraints
starting full resync of recovery catalog
full resync complete

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_kvym_Fctf" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_kvym_Fctf":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_kvym_Fctf" successfully completed at Tue Nov 17 20:33:33 2020 elapsed 0 00:00:04
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'PDB1' 'alter tablespace 
 DEMO read write';
sql 'PDB1' 'alter tablespace 
 DEMO offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  DEMO read write
starting full resync of recovery catalog
full resync complete

sql statement: alter tablespace  DEMO offline
starting full resync of recovery catalog
full resync complete

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_temp_hv98x99g_.tmp deleted
auxiliary instance file /u01/oradata/aux/DB192/datafile/o1_mf_temp_hv98wzm7_.tmp deleted
auxiliary instance file /u01/oradata/aux/DB192/onlinelog/o1_mf_3_hv98wdsv_.log deleted
auxiliary instance file /u01/oradata/aux/DB192/onlinelog/o1_mf_2_hv98wdsj_.log deleted
auxiliary instance file /u01/oradata/aux/DB192/onlinelog/o1_mf_1_hv98wds6_.log deleted
auxiliary instance file /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_sysaux_hv98tlyn_.dbf deleted
auxiliary instance file /u01/oradata/aux/DB192/datafile/o1_mf_sysaux_hv98r7rb_.dbf deleted
auxiliary instance file /u01/oradata/aux/DB192/datafile/o1_mf_undotbs1_hv98r7rw_.dbf deleted
auxiliary instance file /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_undotbs1_hv98tlxq_.dbf deleted
auxiliary instance file /u01/oradata/aux/DB192/datafile/o1_mf_system_hv98r7r3_.dbf deleted
auxiliary instance file /u01/oradata/aux/DB192/823B94BC877B18D5E053B601A8C0020F/datafile/o1_mf_system_hv98tlyw_.dbf deleted
auxiliary instance file /u01/oradata/aux/DB192/controlfile/o1_mf_hv98qyrx_.ctl deleted
auxiliary instance file tspitr_kvym_38369.dmp deleted
Finished recover at 17-NOV-20

RMAN> quit


Recovery Manager complete.
[oracle@db192 ~]$ 
[oracle@db192 ~]$


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect

David D., November 18, 2020 - 8:42 am UTC

Hello Connor,

Thank you very very much for your answer.

Yes, you're right, sometime we don't see the obvious thing. It was only a syntax problem but I loose many many hours on it...

Well, I will not forget the lesson :-)

Have a very nice day, you and the RMAN Team.

David D.
Connor McDonald
November 18, 2020 - 11:51 pm UTC

Rest assured I spent probably in total a couple of days going through various backup/recovery scenarios on my VM with a catalog, without a catalog, 18c, 19c etc etc...

I both chuckled and growled when I discovered the mistake :-)

how find SCN?

ankit, November 19, 2020 - 8:35 am UTC

as per text in question like "RMAN> RECOVER TABLESPACE ZZTBS until SCN 2168640 auxiliary destination '/u01/oraaux/';"
so my question is how dba can find the scn number exactly which is restore like " SCN 2168640 "?
I am new to oracle dba world.
Connor McDonald
November 20, 2020 - 3:56 am UTC

You do not *have* to use an SCN. You can just use a time if you prefer, eg

RECOVER TABLESPACE ZZTBS until time "to_date('22-APR-2015 13:30:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/u01/oraaux/';"

Find the SCN

David D., November 20, 2020 - 8:33 am UTC

Hello,

"so my question is how dba can find the scn number exactly which is restore like " SCN 2168640 "?"
When you drop a tablespace, the operation is written in the file alert_<SID>.log. There you can find the date and time of operation and use a function to convert it to SCN.

Recovery tabelspace using auxilliary destination

padmanathan, December 16, 2024 - 8:07 pm UTC

It works perfect in 19c as well.

RMAN> recover tablespace 'TPDB2':'TEST' UNTIL TIME "to_date('16-DEC-2024 17:25:27','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/u01/TSPITR';
recover tablespace 'TPDB2':'TEST' UNTIL TIME "to_date('16-DEC-2024 17:25:27','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/u01/TSPITR';
Starting recover at 16-DEC-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=185 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=13 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=345 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=19.0.0.1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace TPDB2:SYSTEM
Tablespace UNDOTBS1
Tablespace TPDB2:UNDOTBS1

Creating automatic instance, with SID='rFkn'

initialization parameters used for automatic instance:
db_name=ODB19CT
db_unique_name=rFkn_pitr_TPDB2_ODB19CT
compatible=19.0.0
db_block_size=8192
db_files=1024
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
db_domain=testdbsubpriv.testdbvcn.oraclevcn.com
sga_target=14848M
processes=200
db_create_file_dest=/u01/TSPITR
log_archive_dest_1='location=/u01/TSPITR'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/oracle/dcs/commonstore/wallets/ODB19CT_v2m_iad
tde_configuration='keystore_configuration=FILE'
#No auxiliary parameter file used


starting up automatic instance ODB19CT

Oracle instance started

Total System Global Area 15569252736 bytes

Fixed Size 9195904 bytes
Variable Size 2013265920 bytes
Database Buffers 13522436096 bytes
Redo Buffers 24354816 bytes
Automatic instance created


List of tablespaces that have been dropped from the target database:
Tablespace TPDB2:TEST

contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-DEC-2024 17:25:27','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 16-DEC-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=175 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=93 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=176 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=19.0.0.1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +RECO/ODB19CT_V2M_IAD/AUTOBACKUP/2024_12_16/s_1187889066.533.1187889067
channel ORA_AUX_DISK_1: piece handle=+RECO/ODB19CT_V2M_IAD/AUTOBACKUP/2024_12_16/s_1187889066.533.1187889067 tag=TAG20241216T171106
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/TSPITR/ODB19CT_V2M_IAD/controlfile/o1_mf_mp1125lm_.ctl
Finished restore at 16-DEC-24

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-DEC-2024 17:25:27','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 57 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 59 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 58 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 7 to new;
set newname for datafile 61 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 57, 4, 59, 3, 58, 61;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 7 to /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 16-DEC-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +RECO/ODB19CT_V2M_IAD/BACKUPSET/2024_12_16/nnndn0_tag20241216t171004_0.516.1187889005
channel ORA_AUX_DISK_1: piece handle=+RECO/ODB19CT_V2M_IAD/BACKUPSET/2024_12_16/nnndn0_tag20241216t171004_0.516.1187889005 tag=TAG20241216T171004
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00057 to /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00059 to /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00058 to /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00061 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +RECO/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/BACKUPSET/2024_12_16/nnndn0_tag20241216t171004_0.517.1187889029
channel ORA_AUX_DISK_1: piece handle=+RECO/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/BACKUPSET/2024_12_16/nnndn0_tag20241216t171004_0.517.1187889029 tag=TAG20241216T171004
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 16-DEC-24

datafile 61 switched to datafile copy
input datafile copy RECID=42 STAMP=1187898626 file name=+DATA/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/DATAFILE/test.286.1187898611
datafile 1 switched to datafile copy
input datafile copy RECID=43 STAMP=1187898626 file name=/u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_system_mp112hqv_.dbf
datafile 57 switched to datafile copy
input datafile copy RECID=44 STAMP=1187898626 file name=/u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_system_mp113lvk_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=45 STAMP=1187898626 file name=/u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_undotbs1_mp112hr7_.dbf
datafile 59 switched to datafile copy
input datafile copy RECID=46 STAMP=1187898626 file name=/u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_undotbs1_mp113lvz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=47 STAMP=1187898626 file name=/u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_sysaux_mp112hqp_.dbf
datafile 58 switched to datafile copy
input datafile copy RECID=48 STAMP=1187898626 file name=/u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_sysaux_mp113lvq_.dbf

contents of Memory Script:
{
# set requested point in time
set until time "to_date('16-DEC-2024 17:25:27','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'TPDB2' "alter database datafile
57 online";
sql clone "alter database datafile 4 online";
sql clone 'TPDB2' "alter database datafile
59 online";
sql clone "alter database datafile 3 online";
sql clone 'TPDB2' "alter database datafile
58 online";
sql clone 'TPDB2' "alter database datafile
61 online";
# recover and open resetlogs
recover clone database tablespace "TPDB2":"TEST", "SYSTEM", "TPDB2":"SYSTEM", "UNDOTBS1", "TPDB2":"UNDOTBS1", "SYSAUX", "TPDB2":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 57 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 59 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 58 online

sql statement: alter database datafile 61 online

Starting recover at 16-DEC-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

Executing: alter database datafile 11 offline
Executing: alter database datafile 5, 6, 7 offline
Executing: alter database datafile 8, 9, 10, 12 offline
Executing: alter database datafile 13, 14, 15, 16 offline
Executing: alter database datafile 60 offline
starting media recovery

archived log for thread 1 with sequence 102 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_102.534.1187889887
archived log for thread 1 with sequence 103 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_103.535.1187889887
archived log for thread 1 with sequence 104 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_104.536.1187889889
archived log for thread 1 with sequence 105 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_105.537.1187889893
archived log for thread 1 with sequence 106 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_106.539.1187889895
archived log for thread 1 with sequence 107 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_107.540.1187889899
archived log for thread 1 with sequence 108 is already on disk as file +RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_108.556.1187890093
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_102.534.1187889887 thread=1 sequence=102
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_103.535.1187889887 thread=1 sequence=103
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_104.536.1187889889 thread=1 sequence=104
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_105.537.1187889893 thread=1 sequence=105
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_106.539.1187889895 thread=1 sequence=106
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_107.540.1187889899 thread=1 sequence=107
archived log file name=+RECO/ODB19CT_V2M_IAD/ARCHIVELOG/2024_12_16/thread_1_seq_108.556.1187890093 thread=1 sequence=108
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-DEC-24

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database TPDB2 open';
}
executing Memory Script

sql statement: alter pluggable database TPDB2 open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'TPDB2' 'alter tablespace
"TEST" read only';
# create directory for datapump import
sql 'TPDB2' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/TSPITR''";
# create directory for datapump export
sql clone 'TPDB2' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/TSPITR''";
}
executing Memory Script

sql statement: alter tablespace "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/TSPITR''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/TSPITR''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_rFkn_Cxcp":
EXPDP> ORA-39396: Warning: exporting encrypted data using transportable option without password

EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Master table "SYS"."TSPITR_EXP_rFkn_Cxcp" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_rFkn_Cxcp is:
EXPDP> /u01/TSPITR/tspitr_rFkn_47648.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST:
EXPDP> +DATA/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/DATAFILE/test.286.1187898611
EXPDP> Job "SYS"."TSPITR_EXP_rFkn_Cxcp" completed with 1 error(s) at Mon Dec 16 19:51:43 2024 elapsed 0 00:00:20
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_rFkn_aBiu" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_rFkn_aBiu":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_rFkn_aBiu" successfully completed at Mon Dec 16 19:51:51 2024 elapsed 0 00:00:04
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'TPDB2' 'alter tablespace
"TEST" read write';
sql 'TPDB2' 'alter tablespace
"TEST" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace "TEST" read write

sql statement: alter tablespace "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_temp_mp115kvg_.tmp deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_temp_mp115gc9_.tmp deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/onlinelog/o1_mf_3_mp114bm3_.log deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/onlinelog/o1_mf_2_mp114blo_.log deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/onlinelog/o1_mf_1_mp114bld_.log deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_sysaux_mp113lvq_.dbf deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_sysaux_mp112hqp_.dbf deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_undotbs1_mp113lvz_.dbf deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_undotbs1_mp112hr7_.dbf deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/295B0073D2395A62E0632704A8C0C97D/datafile/o1_mf_system_mp113lvk_.dbf deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/datafile/o1_mf_system_mp112hqv_.dbf deleted
auxiliary instance file /u01/TSPITR/ODB19CT_V2M_IAD/controlfile/o1_mf_mp1125lm_.ctl deleted
auxiliary instance file tspitr_rFkn_47648.dmp deleted
Finished recover at 16-DEC-24


RMAN>

Connor McDonald
December 17, 2024 - 6:24 am UTC

nice demo
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.