Not your typical full recovery – PDB (style) recovery?

Ok, I’ve been working on a few writings as of late.  As I was working on one writing, I came across an issue with RMAN.  Well, I should say an issue that I think is a problem, yet Oracle says it is fine.  I’m wondering if anyone else has run into this issues.  The issue I’m talking about is a full recovery of a PDB. This is not a recovery of a CDB or a PIT recovery of a PDB.  I have tested this over and over again with the same results. 

When we do a restore of a database (non-CDB), we use the latest backup and roll forward using using archive logs and online redo files.  So, when doing a full restore of a PDB inside of a CDB, I would expect the datafile to be in the backup set before the restore.  What I’m seeing is that when the tablespace is dropped, the datafile is no longer in the backupset.  How is that possible?  Is this really a control file issue? That is what I’ve been trying got figure out. 

Lets take a look at what I did.

Start RMAN and do a full backup of the CDB and associated PDBs.  The tablespace that I’m concern with is the BEERS tablespace.

[oracle@oel6 Desktop]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Aug 25 20:25:11 2013

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

connected to target database: ORA12C (DBID=201446454)

RMAN> list backup;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> backup database tag=FULL_BACKUP;

Starting backup at 25-AUG-13
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=00001 name=/opt/oracle/oradata/ora12c/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora12c/sysaux01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora12c/undotbs01.dbf
input datafile file number=00006 name=/opt/oracle/oradata/ora12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-13
channel ORA_DISK_1: finished piece 1 at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/backupset/2013_08_25/o1_mf_nnndf_FULL_BACKUP_91o9hfvw_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
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=/opt/oracle/oradata/ora12c/bcpdb1/sysaux01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/ora12c/bcpdb1/system01.dbf
BEER ==> input datafile file number=00018 name=/opt/oracle/oradata/ora12c/bcpdb1/beers.dbf
input datafile file number=00010 name=/opt/oracle/oradata/ora12c/bcpdb1/bcpdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-13
channel ORA_DISK_1: finished piece 1 at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_FULL_BACKUP_91o9kh03_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/opt/oracle/oradata/ora12c/bcpdb2/sysaux01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/ora12c/bcpdb2/system01.dbf
input datafile file number=00013 name=/opt/oracle/oradata/ora12c/bcpdb2/bcpdb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-13
channel ORA_DISK_1: finished piece 1 at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/E41CE4D4E63312ACE043933EA8C04FD9/backupset/2013_08_25/o1_mf_nnndf_FULL_BACKUP_91o9l839_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/opt/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora12c/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-13
channel ORA_DISK_1: finished piece 1 at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/E41CD3850ECB0FCBE043933EA8C0C8A1/backupset/2013_08_25/o1_mf_nnndf_FULL_BACKUP_91o9mz63_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 25-AUG-13

Starting Control File and SPFILE Autobackup at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/autobackup/2013_08_25/o1_mf_s_824417526_91o9opcc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-AUG-13

Now that I have a complete backup of the CDB with PDBs, I want to take just a backup of a PDB (bcpdb1).  In order to do this, I needed to use the PLUGGABLE DATABASE option in RMAN.

RMAN> backup pluggable database bcpdb1 tag=BCPDB1_BACKUP;

Starting backup at 25-AUG-13
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=00009 name=/opt/oracle/oradata/ora12c/bcpdb1/sysaux01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/ora12c/bcpdb1/system01.dbf
BEERS ==> input datafile file number=00018 name=/opt/oracle/oradata/ora12c/bcpdb1/beers.dbf
input datafile file number=00010 name=/opt/oracle/oradata/ora12c/bcpdb1/bcpdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-13
channel ORA_DISK_1: finished piece 1 at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_BCPDB1_BACKUP_91o9yk0r_.bkp tag=BCPDB1_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 25-AUG-13

Starting Control File and SPFILE Autobackup at 25-AUG-13
piece handle=/opt/oracle/fast_recovery_area/ORA12C/autobackup/2013_08_25/o1_mf_s_824417824_91o9z04q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-AUG-13

Although the output says that the datafiles associated with the BEERS tablespace has been backed up, lets verify.  The datafile we are looking for is file_id of 18.

RMAN> list backup of datafile 18;

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56 Full 696.98M DISK 00:00:21 25-AUG-13 
 BP Key: 56 Status: AVAILABLE Compressed: NO Tag: FULL_BACKUP
 Piece Name: /opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_FULL_BACKUP_91o9kh03_.bkp
 List of Datafiles in backup set 56
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- --------- ----
 18 Full 1976408 25-AUG-13 /opt/oracle/oradata/ora12c/bcpdb1/beers.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
60 Full 696.98M DISK 00:00:12 25-AUG-13 
 BP Key: 60 Status: AVAILABLE Compressed: NO Tag: BCPDB1_BACKUP
 Piece Name: /opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_BCPDB1_BACKUP_91o9yk0r_.bkp
 List of Datafiles in backup set 60
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- --------- ----
 18 Full 1976685 25-AUG-13 /opt/oracle/oradata/ora12c/bcpdb1/beers.dbf

Ok, so now we have two backups of the PDB (bcpdb1) and verified that the datafile needed for recovery is there.  Lets drop the tablespace and try to recovery the tablespace by doing a full restore of the PDB using the backup tagged as BCPDB1_BACKUP.

[oracle@oel6 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Aug 25 21:03:53 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

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> alter session set container=bcpdb1;

Session altered.

SQL> select file_id, tablespace_name, file_name from dba_data_files;

FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
 8 SYSTEM
/opt/oracle/oradata/ora12c/bcpdb1/system01.dbf

9 SYSAUX
/opt/oracle/oradata/ora12c/bcpdb1/sysaux01.dbf

10 USERS
/opt/oracle/oradata/ora12c/bcpdb1/bcpdb1_users01.dbf

 FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
 18 BEERS
/opt/oracle/oradata/ora12c/bcpdb1/beers.dbf

As you can see, I switched to the PDB with an alter session command.  Then I identified the file_id and file_name for the tablespaces using the DBA_DATA_FILES view.  Now, I’m dropping the tablespace BEERS.

SQL> drop tablespace BEERS including contents and datafiles;

Tablespace dropped.

SQL> select file_id, tablespace_name, file_name from dba_data_files;

FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
 9 SYSAUX
/opt/oracle/oradata/ora12c/bcpdb1/sysaux01.dbf

8 SYSTEM
/opt/oracle/oradata/ora12c/bcpdb1/system01.dbf

10 USERS
/opt/oracle/oradata/ora12c/bcpdb1/bcpdb1_users01.dbf

Querying the DBA_DATA_FILES view again, you can see that the BEERS tablespace and associated datafile are gone.  How can I get this tablespace/data file back?  Lets identify the backup that has this datafile.  To do this, I’m going to list all backups that have datafile 18 in them.

RMAN> list backup of datafile 18;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 08/25/2013 21:10:56
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 18

What an error?!  Where did datafile 18 go?  If I go an look up the RMAN-20201 & RMAN-06010 error, I find that these errors reference the fact that the datafile is not found in the recovery catalog/control file.   Earlier, when I checked the backups for the existence of data file 18, it was there.  Lets try validating the PDB.

RMAN> restore pluggable database bcpdb1 validate;

Starting restore at 25-AUG-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_BCPDB1_BACKUP_91o9yk0r_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_BCPDB1_BACKUP_91o9yk0r_.bkp tag=BCPDB1_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 25-AUG-13

Validating the restore for PDB (bcpdb1), the restore would be successful.  How is this possible since we are missing datafile 18? Lets try the restore and see.

RMAN> run
2> {
3> restore pluggable database bcpdb1;
4> recover pluggable database bcpdb1;
5> }

Starting restore at Aug 25 2013 21:39:50
using channel ORA_DISK_1

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 /opt/oracle/oradata/ora12c/bcpdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ora12c/bcpdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ora12c/bcpdb1/bcpdb1_users01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_BCPDB1_BACKUP_91o9yk0r_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/fast_recovery_area/ORA12C/E41CE41B06DE128CE043933EA8C092EA/backupset/2013_08_25/o1_mf_nnndf_BCPDB1_BACKUP_91o9yk0r_.bkp tag=BCPDB1_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at Aug 25 2013 21:40:05

Starting recover at Aug 25 2013 21:40:05
using channel ORA_DISK_1

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

Finished recover at Aug 25 2013 21:40:06

As you can see, the restore of PDB (bcpdb1) was successful.  However, the datafile for the BEERS tablespace were not included in the restore/recover process.  With this being a full restore of the PDB (bcpdb1), why wasn’t the datafile for BEERS restored to the PDB?  When I addressed this with Oracle through an SR; Oracle said that this is due to the control file (at CDB) level having information that the tablespace/datafile doesn’t exist in the PDB and will not be restored.  I don’t know about you, but I think this is a problem.  If I wanted to do a full restore of a PDB, shouldn’t I get all the datafiles in the last full backup of the PDB or CDB?

I’m open to discuss this; however, I’m still puzzled by this and will keep working Oracle to define exactly why a full restore of a PDB doesn’t include tablespaces/datafiles that are dropped. At this point, I have not found a work around to this issue.

Maybe, point-in-time recovery will resolve this issue.  Going to try that next.

Enjoy!

twitter: @curtisbl

blog: http://dbasolved.com

Please follow and like: