RMAN 12c : Say goodbye to your backup when dropping your PDB

Feb 14, 2014 / By Rene Antunez

Tags: ,

I was working on my presentations for IOUG Collaborate, and I came upon this strange behaviour in RMAN 12c (12.1.0.1.0) which to me, shouldn’t happen. Seems that when you do a DROP PLUGGABLE DATABASE , it is the equivalent of DROP DATABASE INCLUDING BACKUPS. This means that if you need to restore your PDB later on, you won’t have this registered – just be careful when dropping them.

Here we go: So I took a backup of my CDB and all of its PDBs, and kept an eye on this TAG 20140212T191237  (I removed a couple of lines for better reading)


oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rman target sys/oracle@cdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:12:06 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=808250731)

RMAN> backup database plus archivelog ;

Starting backup at 12/02/2014 19:12:31

current log archived

...

Starting backup at 12/02/2014 19:12:37

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=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf

channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:12:38

channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:15:23

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/<strong>o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_</strong>.bkp tag=TAG20140212T191237 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:15:23

channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:16:08

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=<b>TAG20140212T191237</b> comment=NONE

..

Finished backup at 12/02/2014 19:16:37

Starting Control File and SPFILE Autobackup at 12/02/2014 19:16:37

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_02_12/o1_mf_s_839358997_9hrbr5vr_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 12/02/2014 19:16:38

So First I want to show you that I was able to recoup the DB in case I were to lose my datafiles of my PDB, so I will first delete them

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ sqlplus / as sysdba

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set pages 999

SQL> COLUMN PDB_ID FORMAT 999

COLUMN PDB_NAME FORMAT A8

COLUMN FILE_ID FORMAT 9999

COLUMN TABLESPACE_NAME FORMAT A10

COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME

FROM DBA_PDBS p, CDB_DATA_FILES d

WHERE p.PDB_ID = d.CON_ID

ORDER BY p.PDB_ID;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4

PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME

------ -------- ------- ---------- ---------------------------------------------

2 PDB$SEED       5 SYSTEM   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf

2 PDB$SEED       7 SYSAUX   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

3 PDB1      9 SYSAUX   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

3 PDB1      8 SYSTEM   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

3 PDB1     10 USERS   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

Now that I have deleted the datafiles, I will now proceed to restore and recover the PDB, and keep an eye on the tag 20140212T191237  used for the restore.

oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
oracle $ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:19:46 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDB1 (DBID=808250731)

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 12/02/2014 19:20:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=TAG20140212T191237
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 12/02/2014 19:20:48

RMAN> RECOVER PLUGGABLE DATABASE PDB1;

Starting recover at 12/02/2014 19:21:06
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 12/02/2014 19:21:07

RMAN> alter pluggable database pdb1 open;

Statement processed

As you can see, I was able to restore and recover my PDB without a problem. But what happens if I decide to drop my PDB, and later on decided that the PDB was needed? So I tried to go back to my backup, it will no longer be there, and it doesn’t report on the backup tag 20140212T191237


RMAN> alter pluggable database pdb1 close;

Statement processed

RMAN> drop pluggable database PDB1;

Statement processed

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 13/02/2014 11:18:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/13/2014 11:18:27
RMAN-06813: could not translate pluggable database PDB1

RMAN> list backup tag TAG20140212T191237;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
67 Full 1.88G DISK 00:02:41 12/02/2014 19:15:19
 BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_.bkp
 List of Datafiles in backup set 67
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 1 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf
 3 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf
 4 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf
 6 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
69 Full 680.13M DISK 00:00:24 12/02/2014 19:16:32
 BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/EDDDB886A1191F07E043344EB2C0BE27/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbq8lm_.bkp
 List of Datafiles in backup set 69
 Container ID: 2, PDB Name: PDB$SEED
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 5 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf
 7 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

As you can see, that backup is no longer registered. I still don’t know if this is normal behaviour for PDBs backup, or a bug – but for now just be careful when dropping a PDB. Your backup will not be reliable. Scary stuff isn’t it ?

4 Responses to “RMAN 12c : Say goodbye to your backup when dropping your PDB”

  • Amar says:

    If you have a valid RMAN backups then do the following and it will work:
    1) Create a new Auxiliary instance, and do a RMAN restore for the following PDBS only : ROOT, PDB$SEED and the Dropped PDB that needs to be restored.

    2) Open this newly created Auxiliary instance.

    3) You should be able to see all the 3 PDB (root, pdb$seed and dropped PDB) with all the datafiles.

    4) Follow the process of unplugging and then plugging in this dropped PDB from the restored auxiliary instance into the original source CDB and you should be all set.

    5) Drop the Auxiliary instance now.

    • Rene Antunez says:

      Nice workaround and yes,its because the information is in the restored controlfile, not in the current controlfile, in the current controlfile the PDB metadata no longer exists.

  • Manish says:

    Will it do the same if you use RMAN repository. I can imagine it could do that because all references to the PDB are deleted from the controlfile

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>