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

Answered by: Connor McDonald - Last updated: November 20, 2020 - 3:56 am UTC

Category: Database Administration - Version: 12.2

Viewed 100+ times

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 we 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 ~]$ 



and you rated our response

  (3 ratings)

Reviews

Perfect

November 18, 2020 - 8:42 am UTC

Reviewer: David D. from Paris - FRANCE

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

Followup  

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?

November 19, 2020 - 8:35 am UTC

Reviewer: ankit

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

Followup  

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

November 20, 2020 - 8:33 am UTC

Reviewer: David D. from Paris, France

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.

More to Explore

Backup/Recovery

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