RMAN-06019 "could not translate tablespace name" tablespace inconnu de RMAN mais non droppé - tablespace not dropped but unknow
Introduction
Sous RMAN, vous verrez parfois les messages d'erreur suivants :
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TBS1"
C'est typique lorsqu'on veut utiliser la sauvegarde d'un tablespace qui a été ensuite droppé. Le problème c'est que, si on a pas de catalog RMAN, lors de le destruction de ce tablespace, le control file a été mis à jour et ce tablespace a disparu de celui-ci. Donc quand on utilise la commande LIST, RMAN affiche bien la sauvegarde mais il ne peut plus la rapprocher d'un tablespace existant dans son ctrl file.
C'est une erreur classique sous RMAN mais là où ça devient vicieux, c'est quand on obtient le même message alors que le tablespace n'a pas été droppé... Que se passe t-il? C'est simple, avec l'architecture muti-tenants, vous essayez, depuis le CDB$ROOT de lister des infos des backups sur un tablespace d'une PDB et, allez savoir pourquoi, il n'y arrive pas, c'est comme s'il ne pouvait chercher si ce tablespace existe que dans la base connectée. La solution est donc de se connecter à la PDB pour relancer la commande LIST et là, miracle, c'est OK :-).
Points d'attention
N/A.
Base de tests
Une base Oracle 18c multi-tenants.
Exemples
============================================================================================
La base de test
============================================================================================
Je me connecte à une PDB.
[oracle@vbgeneric ~]$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
------------------------------
ORCL
Je crée un tbs applicatif TBS1 dans la PDB. Vous noterez le chemin du fichier, il est dans le répertoire orcl du répertoire orcl12c (celui du CDB$ROOT) : /u01/app/oracle/oradata/orcl12c/orcl/tbs1.dbf
SQL> CREATE TABLESPACE TBS1 DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tbs1.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Je crée une table HR.T1 de dix éléments dans TBS1.
SQL> CREATE TABLE HR.T1 (id number) TABLESPACE TBS1;
SQL> INSERT INTO HR.T1(id) SELECT rownum FROM dual CONNECT BY LEVEL <= 10;
10 rows created.
SQL> COMMIT;
============================================================================================
Les sauvegardes RMAN
============================================================================================
Connexion RMAN au CDB$ROOT. Notez que quand on se connecte au CDB$ROOT, un seul nom de base est affiché, ici orcl12c.
[oracle@vbgeneric ~]$ rman target SYS@orcl12c
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Oct 31 06:24:44 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL12C (DBID=768045447)
Ma base n'a aucun backup.
RMAN> list backup summary;
specification does not match any backup in the repository
Je fais une sauvegarde RMAN complète du container et de la pdb : c'est important, TOUJOURS sauvegarder complètement le CDB$ROOT, surtout si vous devez, à un moment ou à un autre, créer une base auxilliaire pour récupérer un tbs ou une table droppée. Sans la sauvegarde du CDB$ROOT, cette création échouera.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 31-OCT-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=16 RECID=1 STAMP=1055226391
input archived log thread=1 sequence=17 RECID=2 STAMP=1055226499
input archived log thread=1 sequence=18 RECID=3 STAMP=1055226613
channel ORA_DISK_1: starting piece 1 at 31-OCT-20
channel ORA_DISK_1: finished piece 1 at 31-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_annnn_TAG20201031T063013_hsth5owh_.bkp tag=TAG20201031T063013 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-OCT-20
Sauvegarde de la PDB orcl : on le voit avec les chemins des fichiers sauvés.
Starting backup at 31-OCT-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/tbs1.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 31-OCT-20
channel ORA_DISK_1: finished piece 1 at 31-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_hsth5qbk_.bkp tag=TAG20201031T063015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Sauvegarde du CDB$ROOT : les fichiers sont sous /u01/app/oracle/oradata/orcl12c/.
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 31-OCT-20
channel ORA_DISK_1: finished piece 1 at 31-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_hsth6vo7_.bkp tag=TAG20201031T063015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Sauvegarde de la 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 31-OCT-20
channel ORA_DISK_1: finished piece 1 at 31-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T06 3015_hsth7mnh_.bkp tag=TAG20201031T063015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 31-OCT-20
Sauvegarde des redo logs.
Starting backup at 31-OCT-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=19 RECID=4 STAMP=1055226703
channel ORA_DISK_1: starting piece 1 at 31-OCT-20
channel ORA_DISK_1: finished piece 1 at 31-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_annnn_TAG20201031T063143_hsth8hp4_.bkp tag=TAG20201031T063143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-OCT-20
Sauvegarde du control file et du spfile.
Starting Control File and SPFILE Autobackup at 31-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_10_31/o1_mf_s_1055226704_hsth8jy3_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-OCT-20
On voit que le datafile du tablespace tbs1 a bien été sauvegardé, il est dans le BS (Backup Set) numéro 8.
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -----------------------------
7 17.24M DISK 00:00:00 31-october -2020 06:30:13 am
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063013
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_annnn_TAG20201031T063013_hsth5owh_.bkp
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ----------------------------- ---------- ---------
1 16 2161508 31-october -2020 06:11:29 am 2166086 31-october -2020 06:26:31 am
1 17 2166086 31-october -2020 06:26:31 am 2166141 31-october -2020 06:28:19 am
1 18 2166141 31-october -2020 06:28:19 am 2166834 31-october -2020 06:30:13 am
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
8 Full 1.41G DISK 00:00:18 31-october -2020 06:30:33 am
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063015
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_h sth5qbk_.bkp
List of Datafiles in backup set 8
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
9 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf
16 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/tbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
9 Full 1.01G DISK 00:00:18 31-october -2020 06:31:08 am
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063015
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_hsth6vo7_.bkp
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
1 Full 2166858 31-october -2020 06:30:50 am NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 2166858 31-october -2020 06:30:50 am NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 2166858 31-october -2020 06:30:50 am NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 2166858 31-october -2020 06:30:50 am NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
10 Full 512.80M DISK 00:00:10 31-october -2020 06:31:25 am
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063015
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_hsth7mnh_.bkp
List of Datafiles in backup set 10
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
5 Full 1443131 02-march -2017 07:57:03 am NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 02-march -2017 07:57:03 am NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 02-march -2017 07:57:03 am NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -----------------------------
11 39.00K DISK 00:00:00 31-october -2020 06:31:43 am
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063143
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_annnn_TAG20201031T063143_hsth8hp4_.bkp
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ----------------------------- ---------- ---------
1 19 2166834 31-october -2020 06:30:13 am 2166886 31-october -2020 06:31:43 am
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
12 Full 17.94M DISK 00:00:00 31-october -2020 06:31:44 am
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063144
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_10_31/o1_mf_s_1055226704_hsth8jy3_.bkp
SPFILE Included: Modification time: 31-october -2020 06:29:30 am
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 2166895 Ckp time: 31-october -2020 06:31:44 am
Et maintenant je veux lister les infos RMAN sur la sauvegarde du TBS1. Aïe, gros problème... RMAN-06019: could not translate tablespace name "TBS1"; mon tbs est inconnu de RMAN...
RMAN> LIST BACKUP OF TABLESPACE TBS1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 10/31/2020 06:37:40
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TBS1"
Pourtant ce tablespace existe bien dans ma PDB.
SQL> select tablespace_name from dba_tablespaces order by 1;
TABLESPACE_NAME
------------------------------
APEX_1991375173370654
APEX_1993195660370985
SYSAUX
SYSTEM
TBS1
TEMP
UNDOTBS1
USERS
8 rows selected.
La syntaxe du LIST est bonne car le résultat est OK pour le tbs USERS... hé, mais il me liste le backup du tbs USERS du CDB$ROOT, pas de la PDB, il devrait y avoir les deux tbs USERS normalement... En effet, le chemin du tbs USERS affiché est /u01/app/oracle/oradata/orcl12c/users01.dbf et non pas /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf.
RMAN> LIST BACKUP OF TABLESPACE USERS;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
9 Full 1.01G DISK 00:00:18 31-october -2020 06:31:08 am
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063015
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_hsth6vo7_.bkp
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
7 Full 2166858 31-october -2020 06:30:50 am NO /u01/app/oracle/oradata/orcl12c/users01.dbf
OK, j'ai compris, il faut que je me connecte à la PDB sous RMAN pour accéder aux infos des backups de la PDB. C'est bizarre, je ne comprends pas pourquoi ces infos ne sont pas disponibles depuis le CDB$ROOT puisque RMAN peut sauvegarder toutes les bases du container.
Notez qu'on est sur d'être bien connecté à une pdb car il y a deux noms de base affichés : orcl12c et orcl.
[oracle@vbgeneric ~]$ rman target SYS/oracle@orcl
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Oct 31 07:39:21 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL12C:ORCL (DBID=2846920952)
Bingo, c'est bon, cette fois on affiche bien les infos de la sauvegarde de mon tbs :-)
RMAN> LIST BACKUP OF TABLESPACE TBS1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------------------
8 Full 1.41G DISK 00:00:18 31-october -2020 06:30:33 am
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20201031T063015
Piece Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T063015_hsth5qbk_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ----------------------------- ----------- ------ ----
16 Full 2166841 31-october -2020 06:30:15 am NO /u01/app/oracle/oradata/orcl12c/orcl/tbs1.dbf
Une autre solution est de lancer la commande suivante, qui permet, depuis le CDB$ROOT, d'accéder à la PDB : on met le nom de la PDB avec : (deux points).
RMAN> LIST BACKUP OF TABLESPACE ORCL:TBS1;