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

12 novembre 2020

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

Introduction
Cet article fait suite à celui où j'expliquais comment récupérer via RMAN une table applicative droppée par erreur : "RMAN 12 "RECOVER TABLE" : comment restaurer avec RMAN une table supprimée". Dans celui-ci je passe au niveau supèrieur : comment récupérer un tablespace applicatif droppé par erreur sans catalogue RMAN.

Points d'attention
N/A.

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

Exemples

============================================================================================
Configuration de la base
============================================================================================
Configuration RMAN
Je me connecte à la PDB via le service orcl.
[oracle@vbgeneric ~]$ sqlplus SYS/oracle@orcl as sysdba
SQL> show con_name user
CON_NAME
------------------------------
ORCL
USER is "SYS"

Il faut modifier sous Linux ou Oracle la variable NLS_DATE_FORMAT pour que les sauvegardes RMAN incorporent l'heure.
SQL> alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS';

Création du tablespace de test 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;
Tablespace created.

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.
[oracle@vbgeneric ~]$ rman target SYS@orcl12c
connected to target database: ORCL12C (DBID=768045447)

Vérifier la configuration RMAN : il faut que l'autobackup du control file soit à ON.
RMAN> show all;
using target database control file instead of recovery catalog
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
============================================================================================
Actuellement ma base n'a aucun backup.
RMAN> list backup summary;
specification does not match any backup in the repository

Faire une sauvegarde RMAN complète du container et des pdbs : pour cela il faut bien être dans le CDB$ROOT.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Sauvegarde des redo logs archivés.
Starting backup at 07-NOV-20
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=179 device type=DISK
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=17 RECID=1 STAMP=1055832125
channel ORA_DISK_1: starting piece 1 at 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_annnn_TAG20201107T064206_htf20go6_.bkp tag=TAG20201107T064206 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 07-NOV-20

Sauvegarde de la PDB orcl avec mon tbs applicatif.
Starting backup at 07-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 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf20ln3_.bkp tag=TAG20201107T064209 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 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf22b3h_.bkp tag=TAG20201107T064209 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

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 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf23f69_.bkp tag=TAG20201107T064209 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 07-NOV-20

Tiens, une deuxième sauvegarde des archived redo logs.
Starting backup at 07-NOV-20
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=2 STAMP=1055832236
channel ORA_DISK_1: starting piece 1 at 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_annnn_TAG20201107T064356_htf23wtr_.bkp tag=TAG20201107T064356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-20

Sauvegarde du control file et du spfile.
Starting Control File and SPFILE Autobackup at 07-NOV-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_07/o1_mf_s_1055832237_htf23ypk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-NOV-20

On voit que le datafile du tablespace ZZTBS a été sauvegardé : BS Key 2 et file n°16.
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 60.81M DISK 00:00:00 07-NOV-20

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064206
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_annnn_TAG20201107T064206_htf20go6_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 17 2265187 07-NOV-20 2289444 07-NOV-20
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.35G DISK 00:00:34 07-NOV-20

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064209
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_h tf20ln3_.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 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
16 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.02G DISK 00:00:24 07-NOV-20
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064209
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf22b3h_.bkp

List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2289513 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 2289513 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 2289513 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 2289513 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 512.80M DISK 00:00:06 07-NOV-20
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064209
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf23f69_.bkp

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

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5 79.50K DISK 00:00:00 07-NOV-20
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064356
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_annnn_TAG20201107T064356_htf23wtr_.bkp

List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 18 2289444 07-NOV-20 2289546 07-NOV-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 17.94M DISK 00:00:01 07-NOV-20
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064357
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_07/o1_mf_s_1055832237_htf23ypk_.bkp
SPFILE Included: Modification time: 07-NOV-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 2289555 Ckp time: 07-NOV-20

Lister les infos RMAN sur la sauvegarde de ZZTBS.
Aïe : ça commence mal...
RMAN> LIST BACKUP OF TABLESPACE ZZTBS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/07/2020 06:48:35
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "ZZTBS"

OK, j'ai compris, il faut que je me connecte à la PDB sous RMAN pour accéder aux backups de la PDB, ou bien que je préfixe le nom du tbs par le nom de ma PDB si je suis dans le CDB$ROOT.
RMAN> LIST BACKUP OF TABLESPACE ORCL:ZZTBS;
List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.35G DISK 00:00:34 07-NOV-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064209
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf20ln3_.bkp

List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
16 Full 2289490 07-NOV-20 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 dis si l'opération est possible.
RMAN> RESTORE TABLESPACE ORCL:ZZTBS PREVIEW;
Starting restore at 07-NOV-20
using channel ORA_DISK_1

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.35G DISK 00:00:34 07-NOV-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064209
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf20ln3_.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
---- -- ---- ---------- --------- ----------- ------ ----
16 Full 2289490 07-NOV-20 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
------- ---- ------- - ---------
2 1 18 A 07-NOV-20
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_07/o1_mf_1_18_htf23wcr_.arc
recovery will be done up to SCN 2289490
Media recovery start SCN is 2289490
Recovery must be done beyond SCN 2289490 to clear datafile fuzziness
Finished restore at 07-NOV-20

============================================================================================
Suppression du tablespace
============================================================================================
Je supprime donc 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 et j'affiche le schéma de ma base. Le nouveau tbs ZZTBS02 est là et le tbs ZZTBS est absent car il a été supprimé.
[oracle@vbgeneric ~]$ rman target SYS@orcl
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 1150 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
17 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

Lister les infos RMAN sur la sauvegarde de ZZTBS en étant dans le CDB. Oracle nous dit qu'il ne connait pas le tbs supprimé; normal car le ctrl file a été mis à jour donc le tbs ZZTBS n'existe plus pour Oracle ou RMAN, même si le backup est toujours là mais Oracle ne peut pas dire ce qu'il renferme.
[oracle@vbgeneric trace]$ rman target SYS/oracle@orcl12c
connected to target database: ORCL12C (DBID=768045447)

RMAN> LIST BACKUP OF TABLESPACE ORCL:ZZTBS;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/07/2020 06:53:25
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "ZZTBS"

Si on affiche tous les backups, on voit que la colonne Name est dorénavant vide pour le BS Key 2 File n°16 puisque le ctrl file ne connait plus ce tbs mais en pus le datafile du tbs a été supprimé... c'est perturbant, surtout que c'est ce backup qu'on va utiliser.
RMAN> LIST BACKUP;
List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.35G DISK 00:00:34 07-NOV-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201107T064209
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf20ln3_.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 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2289490 07-NOV-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
16 Full 2289490 07-NOV-20 NO
...

Je récupère maintenant dans le fichier alert.log la date de suppression du tbs pour savoir à quelle date il faut revenir dans le passé.
SQL> select name, value from v$diag_info where name = 'Diag Trace' order by 1;
NAME VALUE
--------------------------------------------------------------------------
Diag Trace /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace

[oracle@vbgeneric ~]$ cd /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace
[oracle@vbgeneric trace]$ view alert_orcl12c.log
...
2020-11-07T06:50:37.078193-05:00
ORCL(3):drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES
2020-11-07T06:50:40.599419-05:00
ORCL(3):Deleted file /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf
ORCL(3):Completed: drop tablespace ZZTBS INCLUDING CONTENTS AND DATAFILES

Je lance le recover du tablespace ZZTBS : 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.
D'abord je crée le répertoire de la base auxilliaire.
SQL> ! mkdir /u01/oraaux/

On lance RMAN et on se connecte au CDB$ROOT.
[oracle@vbgeneric ~]$ rman target SYS@orcl12c
connected to target database: ORCL12C (DBID=768045447)

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;
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
---- -------- -------------------- ------- ------------------------
1 810 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 1150 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
17 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

============================================================================================
Récupération avec RMAN (sans catalogue) d'un tablespace supprimé
============================================================================================
Identifier le SCN à utiliser
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-07 06:50:40','YYYY-MM-DD HH24:MI:SS')) as scn from dual;
SCN
----------
2290555

J'utilise un SCN un peu plus vieux pour être sur que le DROP TABLESPACE ne soit pas réexécuté mais normalement RMAN s'arrête AVANT le SCN passé en paramètre.
SQL> SELECT SCN_TO_TIMESTAMP(2290500) as "TIMESTAMP" from dual;
TIMESTAMP
---------------------------------------------------------------------------
07-NOV-20 06.49.20.000000000 AM

Commande RECOVER TABLE
Allez, c'est partiiiiiiiiiiiiiiiiiiiiii!
RMAN> RECOVER TABLESPACE ORCL:ZZTBS until SCN 2290550 auxiliary destination '/u01/oraaux/';
Starting recover at 07-NOV-20
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
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 ORCL:SYSTEM
Tablespace ORCL:UNDOTBS1
Tablespace UNDOTBS2

Création d'une instance auxiliaire
Oracle crée une base auxiliaire avec un SID bidon, en vrai un container avec le CDB$ROOT et une PDB, pour restaurer le tablespace et faire ensuite un export/import datapump de ce tbs vers la base source. RMAN se base sur le db_name orcl12c donc la base qui va être créée est un container avec un CDB$ROOT et une PDB.
Creating automatic instance, with SID='earc'
initialization parameters used for automatic instance:
db_name=ORCL12C
db_unique_name=earc_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

L'instance auxiliaire est démarrée.
starting up automatic instance ORCL12C
Oracle instance started
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 239079320 bytes
Database Buffers 583008256 bytes
Redo Buffers 7974912 bytes
Automatic instance created

Ah, notre tablespace, il est là :-)
List of tablespaces that have been dropped from the target database:
Tablespace ORCL:ZZTBS

Restauration du ctrl file
Oracle restaure le ctrl file où est référencé le tbs droppé pour pouvoir ensuite utiliser la sauvegarde : il nous montre le fichier de commandes RMAN qu'il va exécuter.
contents of Memory Script:
{
# set requested point in time
set until scn 2290550;
# 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

Le restore proprement dit commence : partie 1, le control file.
Starting restore at 07-NOV-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=229 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_07/o1_mf_s_1055832237_htf23ypk_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_11_07/o1_mf_s_1055832237_htf23ypk_.bkp tag=TAG20201107T064357
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_htf3f11z_.ctl
Finished restore at 07-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;

Renommage des fichiers
Le datafile 16 contenant le tbs supprimé va être restauré.
contents of Memory Script:
{
# set requested point in time
set until scn 2290550;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 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 16 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 1, 9, 11, 15, 3, 10, 16;

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
Starting restore at 07-NOV-20
using channel ORA_AUX_DISK_1

Restore de la PDB
Le restore proprement dit commence avec les fichiers de la PDB contenant le tbs ZZTBS : je me base sur le numéro du datafile pour identifier leur appartenance.
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 00016 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_07/o1_mf_nnndf_TAG20201107T064209_htf20ln3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf20ln3_.bkp tag=TAG20201107T064209
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56

Restore du CDB$ROOT
Le restore du CDB$ROOT : datafiles 1, 3, 15 mais pas le TEMP car cela ne signifie rien lors d'un RECOVER.
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_07/o1_mf_nnndf_TAG20201107T064209_htf22b3h_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T064209_htf22b3h_.bkp tag=TAG20201107T064209
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-NOV-20
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1055833660 file name=/u01/oraaux/ORCL12C/datafile/o1_mf_system_htf3gz48_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1055833660 file name=/u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_htf3f7mx_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1055833660 file name=/u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_htf3f7mt_.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=13 STAMP=1055833660 file name=/u01/oraaux/ORCL12C/datafile/o1_mf_undotbs2_htf3gz61_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1055833660 file name=/u01/oraaux/ORCL12C/datafile/o1_mf_sysaux_htf3gz4z_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1055833660 file name=/u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_htf3f7k5_.dbf

Préparation du RECOVER
Après le RESTORE, le RECOVER, notamment de mon tbs.
contents of Memory Script:
{
# set requested point in time
set until scn 2290550;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'ORCL' "alter database datafile
9 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
16 online";
# recover and open resetlogs
recover clone database tablespace "ORCL":"ZZTBS", "SYSTEM", "ORCL":"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 1 online
sql statement: alter database datafile 9 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 16 online

Le RECOVER
Starting recover at 07-NOV-20
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_07/o1_mf_1_18_htf23wcr_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_07/o1_mf_1_19_htf3dmwk_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_07/o1_mf_1_18_htf23wcr_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_11_07/o1_mf_1_19_htf3dmwk_.arc thread=1 sequence=19
media recovery complete, elapsed time: 00:00:02
Finished recover at 07-NOV-20
database opened

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

Oracle prépare le tbs restauré pour l'export datapump.
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/''

Export datapump du tablespace restauré
L'export datapump depuis la base auxiliaire : c'est bien un TSPITR qui est fait (TableSpace Point In Time Recovery) comme l'indique le nom "TSPITR_EXP_earc_ilgk".
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_earc_ilgk":
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_earc_ilgk" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_earc_ilgk is:
EXPDP> /u01/oraaux/tspitr_earc_39931.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace ZZTBS:
EXPDP> /u01/app/oracle/oradata/orcl12c/orcl/zztbs.dbf
EXPDP> Job "SYS"."TSPITR_EXP_earc_ilgk" successfully completed at Sat Nov 7 07:09:24 2020 elapsed 0 00:01:04
Export completed

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

executing Memory Script
Oracle instance shut down

Import datapump du tablespace
Maintenant, RMAN fait un import datapump dans la base source : on peut dire que c'est l'équyivalent d'un RESTORE du tablespace.
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_earc_FuuC" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_earc_FuuC":
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_earc_FuuC" successfully completed at Sat Nov 7 07:09:57 2020 elapsed 0 00:00:25
Import completed

Vous noterez que le tbs restauré est mis offline, pas online.
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;';
}

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;

Suppression de la base auxiliaire
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_temp_htf3jy4n_.tmp deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_temp_htf3jwkl_.tmp deleted
auxiliary instance file /u01/oraaux/ORCL12C/onlinelog/o1_mf_3_htf3jhq9_.log deleted
auxiliary instance file /u01/oraaux/ORCL12C/onlinelog/o1_mf_2_htf3jhpw_.log deleted
auxiliary instance file /u01/oraaux/ORCL12C/onlinelog/o1_mf_1_htf3jhph_.log deleted
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_htf3f7k5_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_sysaux_htf3gz4z_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_undotbs2_htf3gz61_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_htf3f7mt_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_htf3f7mx_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/datafile/o1_mf_system_htf3gz48_.dbf deleted
auxiliary instance file /u01/oraaux/ORCL12C/controlfile/o1_mf_htf3f11z_.ctl deleted
auxiliary instance file tspitr_earc_39931.dmp deleted
Finished recover at 07-NOV-20

============================================================================================
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.

A-t-on 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 18 cannot be read at this time
ORA-01110: data file 18: '/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.