RMAN : restaurer un tablespace droppé d'une PDB avec catalogue - RMAN: restore a tablespace dropped from a PDB with catalog

24 novembre 2020

RMAN : restaurer un tablespace droppé d'une PDB avec catalogue - RMAN: restore a tablespace dropped from a PDB with catalog

Introduction
Cet article est le pendant de celui-ci : "RMAN : restaurer un tablespace supprimé sans utiliser de catalogue RMAN".

Pour celui-ci j'ai créé un catalogue RMAN (voir ici : Création d'un catalogue RMAN) mais le process de restauration du tablespace est quasiment identique, que l'on ait un catalogue ou non; néanmoins il est intéressant de comparer les deux.

Quelles sont les différences, que l'on ait un catalogue ou non? C'est juste qu'en listant les sauvegardes existantes, la colonne Name est renseignée avec le nom du datafile alors qu'elle est vide lorsque nous n'utilisons pas de catalogue; Oracle lisant le control file actuel où le tbs n'existe plus, il ne sait pas à quoi associer le contenu de la sauvegarde et donc il met la colonne à vide.

Points d'attention
N/A.

Base de tests
Une base Oracle 18c multi-tenants.

Exemples

============================================================================================
Configuration de la base
============================================================================================
Configuration RMAN
Il faut d'abord modifier sous Linux et Oracle la variable NLS_DATE_FORMAT pour que les sauvegardes RMAN incorporent l'heure.
Terminal Linux où je vais lancer RMAN.
[oracle@vbgeneric ~]$ export NLS_DATE_FORMAT='MM-DD-YYYY HH24:MI:SS';

Idem pour la session SQL*Plus : je me connecte à la PDB via le service orcl.
[oracle@vbgeneric ~]$ sqlplus SYS/oracle@orcl as sysdba
SQL> alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS';

Création du tablespace de test ZZTBS et d'une table HR.T1 dans celui-ci.
SQL> CREATE TABLESPACE ZZTBS DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
SQL> CREATE TABLE HR.T1 (id number) TABLESPACE ZZTBS;
SQL> INSERT INTO HR.T1(id) SELECT rownum FROM dual CONNECT BY LEVEL <= 10;
SQL> COMMIT;

Connexion RMAN au CDB$ROOT via le service orcl12c et au catalogue. Attention, il faut se connecter à la base du catalogue RMAN avec le user créé spécialement pour gérer le catalogue et non pas SYS; dans cette base, il s'appelle usercat.
[oracle@vbgeneric ~]$ rman target SYS@orcl12c catalog usercat@ords
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Nov 20 08:13:12 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL12C (DBID=768045447)
recovery catalog database Password:
connected to recovery catalog database

Vérifier la configuration RMAN : il faut que l'autobackup du control file soit à ON.
RMAN> show all;
starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name ORCL12C are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2/db_1/dbs/snapcf_orcl12c.f'; # default

============================================================================================
Sauvegarde de la base et du container
============================================================================================
Actuellement ma base n'a aucun backup.
RMAN> list backup summary;
specification does not match any backup in the repository

Sauvegarde du container
Faire une sauvegarde RMAN complète du container et des pdbs : pour cela il faut bien être via RMAN dans le CDB$ROOT et pas dans la PDB. Pour en être sur, un autre article ici : "RMAN : identifier si on est connecté au CDB ou à une PDB"
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Sauvegarde des archived logs.
Starting backup at 11-20-2020 08:17:14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=1 STAMP=1056958131
input archived log thread=1 sequence=19 RECID=2 STAMP=1056961034
channel ORA_DISK_1: starting piece 1 at 11-20-2020 08:17:15
channel ORA_DISK_1: finished piece 1 at 11-20-2020 08:17:18
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_annnn_TAG20201120T081715_hvhjgvgv_.bkp tag=TAG20201120T081715 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-20-2020 08:17:18

Sauvegarde de la PDB orcl : on le sait en voyant le nom du répertoire contenant les datafiles sauvegardés. Le datafile de mon tbs de test a le numéro 21.
Starting backup at 11-20-2020 08:17:18
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=00021 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 11-20-2020 08:17:19
channel ORA_DISK_1: finished piece 1 at 11-20-2020 08:18:14
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp tag=TAG20201120T081718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Sauvegarde du cdb$root.
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/orcl12c/undotbs2.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-20-2020 08:18:14
channel ORA_DISK_1: finished piece 1 at 11-20-2020 08:18:49
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjjpsq_.bkp tag=TAG20201120T081718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Sauvegarde de la PDB ords qui contient le catalogue RMAN.
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00019 name=/u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf
input datafile file number=00017 name=/u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/orcl12c/ORDS/undotbs01.dbf
input datafile file number=00020 name=/u01/app/oracle/oradata/orcl12c/ORDS/catalog_rman.dbf
channel ORA_DISK_1: starting piece 1 at 11-20-2020 08:18:49
channel ORA_DISK_1: finished piece 1 at 11-20-2020 08:19:34
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/B4769D74F37C125EE0530100007F6F20/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjktgb_.bkp tag=TAG20201120T081718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Sauvegarde de la pdb pdb$seed.
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 11-20-2020 08:19:35
channel ORA_DISK_1: finished piece 1 at 11-20-2020 08:19:50
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjm747_.bkp tag=TAG20201120T081718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 11-20-2020 08:19:50

Autre sauvegarde des archived redo logs.
Starting backup at 11-20-2020 08:19:50
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=3 STAMP=1056961190
channel ORA_DISK_1: starting piece 1 at 11-20-2020 08:19:51
channel ORA_DISK_1: finished piece 1 at 11-20-2020 08:19:52
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_annnn_TAG20201120T081951_hvhjmq8o_.bkp tag=TAG20201120T081951 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-20-2020 08:19:52

Sauvegarde du control file et du spfile.
Starting Control File and SPFILE Autobackup at 11-20-2020 08:19:52
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_20/o1_mf_s_1056961192_hvhjms7t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-20-2020 08:19:53

Contenu du backup RMAN
On voit que le datafile du tablespace ZZTBS a été sauvegardé : BS Key 414 file n°21.
RMAN> list backup;

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

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
398 100.08M DISK 00:00:00 11-20-2020 08:17:15
BP Key: 399 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081715
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_annnn_TAG20201120T081715_hvhjgvgv_.bkp

List of Archived Logs in backup set 398
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 18 1961784 11-19-2020 08:25:28 2071681 11-20-2020 07:28:44
1 19 2071681 11-20-2020 07:28:44 2078032 11-20-2020 08:17:14

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
414 Full 1.33G DISK 00:00:44 11-20-2020 08:18:03
BP Key: 420 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp

List of Datafiles in backup set 414
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
9 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
21 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
415 Full 1.00G DISK 00:00:26 11-20-2020 08:18:40
BP Key: 421 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjjpsq_.bkp

List of Datafiles in backup set 415
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2078131 11-20-2020 08:18:14 NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 2078131 11-20-2020 08:18:14 NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 2078131 11-20-2020 08:18:14 NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 2078131 11-20-2020 08:18:14 NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
416 Full 976.91M DISK 00:00:34 11-20-2020 08:19:23
BP Key: 422 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/B4769D74F37C125EE0530100007F6F20/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjktgb_.bkp

List of Datafiles in backup set 416
Container ID: 4, PDB Name: ORDS
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
16 Full 2078144 11-20-2020 08:18:49 NO /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
17 Full 2078144 11-20-2020 08:18:49 NO /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
18 Full 2078144 11-20-2020 08:18:49 NO /u01/app/oracle/oradata/orcl12c/ORDS/undotbs01.dbf
19 Full 2078144 11-20-2020 08:18:49 NO /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf
20 Full 2078144 11-20-2020 08:18:49 NO /u01/app/oracle/oradata/orcl12c/ORDS/catalog_rman.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
417 Full 512.80M DISK 00:00:06 11-20-2020 08:19:41
BP Key: 423 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjm747_.bkp

List of Datafiles in backup set 417
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
5 Full 1443131 03-02-2017 07:57:03 NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 03-02-2017 07:57:03 NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 03-02-2017 07:57:03 NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
454 135.00K DISK 00:00:00 11-20-2020 08:19:51
BP Key: 458 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081951
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_annnn_TAG20201120T081951_hvhjmq8o_.bkp

List of Archived Logs in backup set 454
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 20 2078032 11-20-2020 08:17:14 2078182 11-20-2020 08:19:50

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
471 Full 17.94M DISK 00:00:01 11-20-2020 08:19:53
BP Key: 473 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081952
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_20/o1_mf_s_1056961192_hvhjms7t_.bkp
SPFILE Included: Modification time: 11-20-2020 08:09:06
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 2078251 Ckp time: 11-20-2020 08:19:52

Lister les infos RMAN sur la sauvegarde de ZZTBS; attention à bien penser à préfixer le nom du tablespace par le nom de sa pdb sinon vous aurez un message d'erreur laissant croire que ce tablespace est inconnu car Oracle le cherchera dans le CDB$ROOT. Lire cet article : "RMAN-06019 "could not translate tablespace name" tablespace inconnu"
RMAN> LIST BACKUP OF TABLESPACE ORCL:ZZTBS;

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
414 Full 1.33G DISK 00:00:44 11-20-2020 08:18:03
BP Key: 420 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp

List of Datafiles in backup set 414
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
21 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf

Faire un PREVIEW du restore de ZZTBS pour voir ce qui va se passer; avec cette commande Oracle ne restaure rien mais vous dit si l'opération est possible. Sur l'avant dernière ligne, je lis "clear datafile fuzziness"; sa traduction est "nettoyer le flou des fichiers de données"; allez savoir ce que Oracle entends par là...
RMAN> RESTORE TABLESPACE ORCL:ZZTBS PREVIEW;
Starting restore at 11-20-2020 08:21:49
using channel ORA_DISK_1

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
414 Full 1.33G DISK 00:00:44 11-20-2020 08:17:20
BP Key: 420 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp

List of Datafiles in backup set 414
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
21 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf

List of Archived Log Copies for database with db_unique_name ORCL12C
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
413 1 20 A 11-20-2020 08:17:14
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_20/o1_mf_1_20_hvhjmp6k_.arc
recovery will be done up to SCN 2078105
Media recovery start SCN is 2078105
Recovery must be done beyond SCN 2078105 to clear datafile fuzziness
Finished restore at 11-20-2020 08:21:50

============================================================================================
Suppression du tablespace
============================================================================================
Je supprime maintenant le tbs ZZTBS de ma PDB, avec les datafiles.
SQL> drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME;
TABLESPACE_NAME
------------------------------
APEX_1991375173370654
APEX_1993195660370985
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS
7 rows selected.

Je crée un autre tbs applicatif ZZTBS02 dans la même PDB avec une table HR.T2; l'objectif est de voir que le restore se fera à la date du tbs supprimé mais que les objets créés après le restore ne seront pas eux supprimés. En clair je ne veux pas faire un RECOVER DATABASE dans le passé au moment de la suppression du tbs mais juste un RECOVER TABLESPACE...
SQL> CREATE TABLESPACE ZZTBS02 DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/ZZTBS02.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
SQL> CREATE TABLE HR.T2 (id number) TABLESPACE ZZTBS02;
SQL> INSERT INTO HR.T2(id) SELECT rownum FROM dual CONNECT BY LEVEL <= 2;
SQL> COMMIT;

Je me connecte dans la PDB orcl qui avait le tbs droppé et j'affiche le schéma de ma base. Le nouveau tbs ZZTBS02 (file 22) est là et le tbs ZZTBS est absent car il a été supprimé.
[oracle@vbgeneric ~]$ rman target SYS@orcl
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Nov 20 08:25:14 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL12C:ORCL (DBID=2846920952)

RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL12C

List of Permanent Datafiles
===========================

File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
9 340 SYSTEM NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 1160 SYSAUX NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 460 UNDOTBS1 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 73 USERS NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 7 APEX_1991375173370654 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 2 APEX_1993195660370985 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
22 10 ZZTBS02 NO /u01/app/oracle/oradata/orcl12c/orcl/ZZTBS02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3 64 TEMP 32767 /u01/app/oracle/oradata/orcl12c/orcl/temp01.dbf

Si on affiche tous les backups, on voit que la colonne Name n'est pas vide pour la sauvegarde du tbs zztbs (BS Key 414 file 21) grâce au catalogue RMAN, alors que cette colonne est vide si on n'utilise pas de catalogue.Le nouveau tbs ZZTBS02 n'est pas là car je ne l'ai pas sauvegardé.
RMAN> LIST BACKUP;

List of Backup Sets
===================
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
414 Full 1.33G DISK 00:00:44 11-20-2020 08:18:03
BP Key: 420 Status: AVAILABLE Compressed: NO Tag: TAG20201120T081718
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp

List of Datafiles in backup set 414
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
9 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
21 Full 2078105 11-20-2020 08:17:19 NO /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf

============================================================================================
Récupération avec RMAN (avec catalogue) d'un tablespace supprimé
============================================================================================
Identifier le SCN pour le recover
Je récupère maintenant dans le fichier alert.log la date de suppression du tbs pour savoir à quelle date il faut faire un recover du tbs dans le passé : le terme consacré est TS PITR (TableSpace Point In Time Recovery). On récupère d'abord le répertoire de l'alert_<SID>.log.
SQL> select name, value from v$diag_info where name = 'Diag Trace';
NAME VALUE
-----------------------------------------------------------------
Diag Trace /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace

Identifier le SCN à utiliser avec le contenu du fichier alert_orcl12c.log.
[oracle@vbgeneric ~]$ cd /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace
[oracle@vbgeneric ~]$ view alert_orcl12c.log
...
2020-11-20T08:22:11.992933-05:00
drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES
ORA-959 signalled during: drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES...
2020-11-20T08:22:43.351271-05:00
ORCL(3):drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES
2020-11-20T08:22:48.095788-05:00
ORCL(3):Deleted file /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf
ORCL(3):Completed: drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES
...

On calcule le SCN avant le DROP TABLESPACE en se basant sur l'info dans l'alert.log.
SQL> select timestamp_to_scn(to_timestamp('2020-11-20 08:22:48','YYYY-MM-DD HH24:MI:SS')) as scn from dual;
SCN
----------
2078507

Je crée le répertoire de la base auxilliaire qui sera utilisée par RMAN pour faire un restore du tbs droppé.
SQL> ! mkdir /u01/oraaux/

Via RMAN on se connecte au CDB$ROOT.
[oracle@vbgeneric ~]$ rman target SYS@orcl12c catalog usercat@ords

ATTENTION : pour faire un RECOVER d'un TBS d'une PDB, il faut préfixer le nom du tbs par celui de la PDB. Faites aussi attention à ne pas dépasser le SCN du DROP TABLESPACE sinon Oracle va restaurer le tbs puis le dropper... On affiche le schéma de la base pour être sur qu'on est dans le CDB : tous les tbs et tous les datafiles de ma base sont affichés donc on est dans le CDB$ROOT; petite astuce : si vous voyez PDB$SEED dans la colonne Tablespace, vous êtes dans le CDB$ROOT.
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 800 SYSTEM YES /u01/app/oracle/oradata/orcl12c/system01.dbf
3 470 SYSAUX NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
5 250 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/orcl12c/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf
9 340 ORCL:SYSTEM YES /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 1160 ORCL:SYSAUX NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 460 ORCL:UNDOTBS1 YES /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 73 ORCL:USERS NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 7 ORCL:APEX_1991375173370654 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 2 ORCL:APEX_1993195660370985 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
15 50 UNDOTBS2 YES /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
16 260 ORDS:SYSTEM YES /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
17 360 ORDS:SYSAUX NO /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
18 100 ORDS:UNDOTBS1 YES /u01/app/oracle/oradata/orcl12c/ORDS/undotbs01.dbf
19 547 ORDS:USERS NO /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf
20 32 ORDS:CATALOG_RMAN NO /u01/app/oracle/oradata/orcl12c/ORDS/catalog_rman.dbf
22 10 ORCL:ZZTBS02 NO /u01/app/oracle/oradata/orcl12c/orcl/ZZTBS02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 33 TEMP 32767 /u01/app/oracle/oradata/orcl12c/temp01.dbf
2 64 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/orcl12c/pdbseed/temp012017-03-02_07-53-20-031-AM.dbf
3 64 ORCL:TEMP 32767 /u01/app/oracle/oradata/orcl12c/orcl/temp01.dbf
4 64 ORDS:TEMP 32767 /u01/app/oracle/oradata/orcl12c/ORDS/temp012017-03-02_07-53-20-031-AM.dbf

Lancement du Recover
Je lance le recover du tablespace ZZTBS en préfixant son nom par celui de sa PDB : comme vous le voyez, on fait un RECOVER, pas un RESTORE; le RESTORE sera fait en automatique sur le datafile quand RMAN créera la base auxiliiaire.
Allez, c'est partiiiiiiiiiiiiiiiiiiiiii!
RMAN> RECOVER TABLESPACE ORCL:ZZTBS until SCN 2078507 auxiliary destination '/u01/oraaux/';
Starting recover at 11-20-2020 08:36:47
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

Création par RMAN d'une instance pour faire le TSPITR, dans le répertoire donné ci-dessus.
Creating automatic instance, with SID='Cbnt'
initialization parameters used for automatic instance:
db_name=ORCL12C
db_unique_name=Cbnt_pitr_ORCL_ORCL12C
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=800M
processes=200
db_create_file_dest=/u01/oraaux/
log_archive_dest_1='location=/u01/oraaux/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance ORCL12C
Oracle instance started
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 226496408 bytes
Database Buffers 595591168 bytes
Redo Buffers 7974912 bytes
Automatic instance created

Restauration du control file existant avant le drop du tbs, pour récupérer le lien entre le tbs et le datafile car ce lien est absent du ctrl file actuel.
contents of Memory Script:
{
# set requested point in time
set until scn 2078507;
# 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 11-20-2020 08:37:02
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=180 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/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_20/o1_mf_s_1056961192_hvhjms7t_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_20/o1_mf_s_1056961192_hvhjms7t_.bkp tag=TAG20201120T081952
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_hvhkmyjm_.ctl
Finished restore at 11-20-2020 08:37:03
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

Opérations de RMAN sur les datafiles avant le recover du tbs.
contents of Memory Script:
{
# set requested point in time
set until scn 2078507;
# 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 15 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 21 to
"/u01/app/oracle/oradata/orcl12c/orcl/zztbs.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, 15, 3, 10, 21;

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/oraaux/ORCL12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_temp_%u_.tmp in control file

Le restore démarre et là, on a bien le tbs de test supprimé ZZTBS!
Starting restore at 11-20-2020 08:37:10
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 00009 to /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjgzob_.bkp tag=TAG20201120T081718
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
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/oraaux/ORCL12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/oraaux/ORCL12C/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oraaux/ORCL12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjjpsq_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T081718_hvhjjpsq_.bkp tag=TAG20201120T081718
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:47
Finished restore at 11-20-2020 08:39:02

datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1056962342 file name=/u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_hvhkn6s1_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=1056962342 file name=/u01/oraaux/ORCL12C/datafile/o1_mf_system_hvhkp7wd_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1056962342 file name=/u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_hvhkn6ry_.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=13 STAMP=1056962342 file name=/u01/oraaux/ORCL12C/datafile/o1_mf_undotbs2_hvhkp802_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1056962342 file name=/u01/oraaux/ORCL12C/datafile/o1_mf_sysaux_hvhkp7xj_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1056962342 file name=/u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_hvhkn6q7_.dbf

Après le Restore, le Recover du tbs droppé ZZTBS, avec aussi les tbs du cdb$root et les tbs système de la pdb orcl; à noter qu'il n'y a rien sur la PDB$SEED; Oracle crée une base auxiliaire qui ne contient que l'indispensable, à savoir le CDB avec un CDB$ROOT et la PDB du tbs à restaurer.
contents of Memory Script:
{
# set requested point in time
set until scn 2078507;
# online the datafiles restored or switched
sql clone 'ORCL' "alter database datafile
9 online";
sql clone "alter database datafile 1 online";
sql clone 'ORCL' "alter database datafile
11 online";
sql clone "alter database datafile 15 online";
sql clone "alter database datafile 3 online";
sql clone 'ORCL' "alter database datafile
10 online";
sql clone 'ORCL' "alter database datafile
21 online";
# recover and open resetlogs
recover clone database tablespace "ORCL":"ZZTBS", "ORCL":"SYSTEM", "SYSTEM", "ORCL":"UNDOTBS1", "UNDOTBS2", "SYSAUX", "ORCL":"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 15 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
sql statement: alter database datafile 21 online
Starting recover at 11-20-2020 08:39:02
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_20/o1_mf_1_20_hvhjmp6k_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_20/o1_mf_1_21_hvhkmhlk_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_20/o1_mf_1_20_hvhjmp6k_.arc thread=1 sequence=20
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_20/o1_mf_1_21_hvhkmhlk_.arc thread=1 sequence=21
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-20-2020 08:39:04
database opened

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

executing Memory Script
sql statement: alter pluggable database ORCL open

RMAN fait un export Datapump du tbs qui a été restauré.
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'ORCL' 'alter tablespace
ZZTBS read only';
# create directory for datapump import
sql 'ORCL' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/oraaux/''";
# create directory for datapump export
sql clone 'ORCL' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/oraaux/''";
}

executing Memory Script
sql statement: alter tablespace ZZTBS read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oraaux/''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oraaux/''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_Cbnt_BCjs":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
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_Cbnt_BCjs" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Cbnt_BCjs is:
EXPDP> /u01/oraaux/tspitr_Cbnt_16394.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace ZZTBS:
EXPDP> /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf
EXPDP> Job "SYS"."TSPITR_EXP_Cbnt_BCjs" successfully completed at Fri Nov 20 08:40:40 2020 elapsed 0 00:00:56
Export completed

RMAN fait ensuite un import Datapump dans la base source du tbs exporté.
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_Cbnt_akqB" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_Cbnt_akqB":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_Cbnt_akqB" successfully completed at Fri Nov 20 08:41:12 2020 elapsed 0 00:00:21
Import completed

Quelques opérations sur le tbs restauré.
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'ORCL' 'alter tablespace
ZZTBS read write';
sql 'ORCL' 'alter tablespace
ZZTBS 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 ZZTBS read write
sql statement: alter tablespace ZZTBS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

RMAN synchronise les backups de la base source avec son catalogue.
starting full resync of recovery catalog
full resync complete

La base auxiliaire est maintenant supprimée.
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_temp_hvhkr983_.tmp deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_temp_hvhkr6r2_.tmp deleted
auxiliary instance file /u01/oraaux/ORCL12C/onlinelog/o1_mf_3_hvhkqrpb_.log deleted
auxiliary instance file /u01/oraaux/ORCL12C/onlinelog/o1_mf_2_hvhkqroq_.log deleted
auxiliary instance file /u01/oraaux/ORCL12C/onlinelog/o1_mf_1_hvhkqrof_.log deleted
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_hvhkn6q7_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_sysaux_hvhkp7xj_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_undotbs2_hvhkp802_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_hvhkn6ry_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_system_hvhkp7wd_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_hvhkn6s1_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/controlfile/o1_mf_hvhkmyjm_.ctl deleted
auxiliary instance file tspitr_Cbnt_16394.dmp deleted
Finished recover at 11-20-2020 08:41:18

============================================================================================
Vérification que le tablespace a été récupéré
============================================================================================
Et voilà, le tbs ZZTBS a bien été restauré et, tout aussi important, le tbs créé après le SCN de la dite suppression n'a pas été supprimé : c'était un point important.
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME;
TABLESPACE_NAME
------------------------------
APEX_1991375173370654
APEX_1993195660370985
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS
ZZTBS
ZZTBS02
9 rows selected.

Avons-nous accès aux données de la table du tbs restauré?
Arghhhhhhhhhhh, non!
SQL> SELECT * from HR.T1;
SELECT * from HR.T1
*
ERROR at line 1:
ORA-00376: file 23 cannot be read at this time
ORA-01110: data file 23: '/u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf'

Ah oui, même si le datafile est disponible, il faut mettre ONLINE le tbs restauré...
SQL> select STATUS from dba_data_files where FILE_NAME = '/u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf';
STATUS
---------
AVAILABLE
SQL> select status from dba_tablespaces where tablespace_name = 'ZZTBS';
STATUS
---------
OFFLINE

SQL> ALTER TABLESPACE ZZTBS ONLINE;
Tablespace altered.

Et là c'est OK :-)
SQL> SELECT * from HR.T1;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.