Update: 09/March/2015 .- I wrote a second part and followed up on this on my personal blog RMAN 12cR1 : Say goodbye to your backup when dropping your PDB – Part II
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/adminoracle $ 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=DISKchannel 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:48RMAN> RECOVER PLUGGABLE DATABASE PDB1;
Starting recover at 12/02/2014 19:21:06
using channel ORA_DISK_1starting media recovery
media recovery complete, elapsed time: 00:00:00Finished 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 PDB1RMAN> 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_.dbfBS 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 ?
Interested in working with Rene? Schedule a tech call.
8 Comments. Leave new
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.
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.
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
Yes, that will persist unless you restore your controlfile to before the drop of the pluggable database.
Hi, i think is not a bug.
remember PDB is like a schema (logical) or tablespace (physical), in a database suppose you have a rman backup, if you drop a user defined tablespace, the backup for this tablespace is removed from the list of backed up tablespace because the information of the tablespace is removed from control file.
Hi Raul, thank you for your feedbak, I actually did a follow up on my personal blog on this, sorry for not mentioning it before.
http://reneantunez.blogspot.mx/2014/06/rman-12c-say-goodbye-to-your-backup.html
Regards
René
Hi I came across something like this…
I created a third pluggable database, then dropped it. Next I tried to do an incomplete recovery, past the point where the dropped pluggable database had been created, but before the point where it had been dropped. The recovery crashed at this stage, pointing to a missing datafile from the dropped PDB and I could only recover the database to a point before the dropped pluggable database had been created.
hi ,, i have tested the senario,,, i dropped PDB and restored, but we have different steps to restore, its not like normal DB.
u hav eto create Auxiliary, set password file, startup no mount
Duplicate database to ‘testcdb’ noopen backup location ‘/data/u01/orlcdb01/rman/’;
SYS @ testcdb > alter database open resetlogs;
Database altered.
SYS @ testcdb > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 MYPDB1 MOUNTED
now from aux CDB we have to move it to orignal target CDB
SYS @ testcdb > alter pluggable database MYPDB1 close immediate;
Pluggable database altered.
SYS @ testcdb > alter pluggable database MYPDB1 unplug into ‘/tmp/MYPDB1.xml’;
Pluggable database altered.
SYS @ orlcdb01 > create pluggable database MYPDB1 using ‘/tmp/MYPDB1.xml’;
Pluggable database created.
SYS @ orlcdb01 > alter pluggable database mypdb1 open;
Pluggable database altered.