PDB RMAN backups available after plugging in on a new CDB with Oracle 18c

With Oracle 18c, it is possible to use PDB rman backups created on the source CDB (they are called PREPLUGIN backups) when the PDB has been relocated to a target CDB.

In my environment, my original CDB is DB18, with the PDB named pdborig. The target CDB is PSI18.

The first step consist in running a rman backup on pdborig:

oracle@localhost:/u00/app/oracle/ [DB18] rman target sys/manager@pdborig
Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 15 10:57:38 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB18:PDBORIG (DBID=3031125269)

RMAN> backup pluggable database pdborig plus archivelog;

Starting backup at 15-MAY-2018 10:57:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 device type=DISK
skipping archived logs when connected to a PDB
backup cancelled because there are no files to backup
Finished backup at 15-MAY-2018 10:57:56

Starting backup at 15-MAY-2018 10:57:56
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=00029 name=/u00/app/oracle/oradata/DB18/pdborig/undotbs01.dbf
input datafile file number=00027 name=/u00/app/oracle/oradata/DB18/pdborig/system01.dbf
input datafile file number=00028 name=/u00/app/oracle/oradata/DB18/pdborig/sysaux01.dbf
input datafile file number=00030 name=/u00/app/oracle/oradata/DB18/pdborig/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-MAY-2018 10:57:56
channel ORA_DISK_1: finished piece 1 at 15-MAY-2018 10:57:59
piece handle=/u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/
2018_05_15/o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp 
tag=TAG20180515T105756 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-MAY-2018 10:57:59

Starting backup at 15-MAY-2018 10:57:59
using channel ORA_DISK_1
skipping archived logs when connected to a PDB
backup cancelled because there are no files to backup
Finished backup at 15-MAY-2018 10:57:59

Then we have to export the RMAN backup metadata for the non CDB into its dictionary using dbms_pdb.exportrmanbackup()

oracle@localhost:/u00/app/oracle/ [DB18] sqlplus sys/manager@pdborig as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:00:38 2018
Version 18.1.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> execute dbms_pdb.exportrmanbackup();

PL/SQL procedure successfully completed.

As my environment is configured with TDE, in order to unplug the pdborig, we need to export the master key of the container database otherwise we will receive the following error message:

SQL> alter pluggable database pdborig 
unplug into '/home/oracle/pdborig.xml';
alter pluggable database pdborig unplug into '/home/oracle/pdborig.xml'
*
ERROR at line 1:
ORA-46680: master keys of the container database must be exported

We export the master key:

SQL> alter session set container=PDBORIG;

Session altered

SQL> administer key management 
  2  export encryption keys with secret "manager_cdb" 
  3  to '/home/oracle/pdborig.p12'
  4  identified by manager_cdb;

keystore altered.

SQL> alter pluggable database PDBORIG close immediate;

Pluggable database altered.

SQL> alter pluggable database PDBORIG unplug into '/home/oracle/pdborig.xml';

Pluggable database altered.

Finallly on the target CDB named PSI18, we first have to create a wallet and 
open the keystore. Just remember you have to define wallet_root and 
tde_configuration in your CDB environment in order to use TDE:

SQL> show parameter wallet

NAME		       TYPE	    VALUE
-------------------- ----------- -------------------------------------
wallet_root	       string	 /u00/app/oracle/admin/PSI18/walletcdb

SQL> alter system set tde_configuration="keystore_configuration=file";
System altered.

We create and open the keystore on the target CDB and we import the master key:

SQL> administer key management create keystore identified by manager_cdb;

keystore altered.

SQL> administer key management set keystore open 
identified by manager_cdb container=all;

keystore altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> administer key management import encryption keys 
     with secret "manager_cdb" from '/home/oracle/pdborig.p12'
     identified by "manager_cdb" with backup;

keystore altered.

We create pdbnew on the target CDB using pdborig.xml:

SQL> create pluggable database pdbnew using '/home/oracle/pdborig.xml'
file_name_convert=
('/u00/app/oracle/oradata/DB18/pdborig','/home/oracle/oradata/PSI18/pdbnew');

Pluggable database created.

We open the pluggable database pdbnew:

SQL> alter pluggable database pdbnew open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBNEW			  READ WRITE NO

And now the non CDB PDBNEW has been plugged in the target CDB, we can ask if the rman backups are visible because we had exported the rman metadata backup. To visualize that we have to use the preplugin clause:

RMAN> list preplugin backup of pluggable database pdbnew;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
9       Full    463.15M    DISK        00:00:01     15-MAY-2018 10:56:51
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105650
        Piece Name: /u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105650_fho86ltx_.bkp
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
10      Full    463.15M    DISK        00:00:01     15-MAY-2018 10:57:57
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105756
        Piece Name: /u00/app/oracle/fast_recovery_area/DB18/DB18/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

We can also display the archive logs preplugin backups:

RMAN> list preplugin archivelog all;

List of Archived Log Copies for database with db_unique_name PSI18
=====================================================================

Key     Thrd Seq     S Low Time            
------- ---- ------- - --------------------
.....

6       1    16      A 15-MAY-2018 10:08:53
/u00/app/oracle/fast_recovery_area/archivelog/2018_05_15/o1_mf_1_16_fho5r944_.a
...

So let’s see if we can make a restore and recover test:

We delete the user01.dbf datafile:

oracle@localhost:/u00/app/oracle/ [PSI18] rm /home/oracle/oradata/PSI18/pdbnew/users01.dbf

oracle@localhost:/u00/app/oracle/oradata/DB18/pdbseed/ [PSI18] sq

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:20:47 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> alter pluggable database pdbnew close;

Pluggable database altered.

SQL> alter pluggable database pdbnew open;
alter pluggable database pdbnew open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 23 - see DBWR trace file

Now we try to restore: we connect with rman to the target CDB and we set the PDB that needs to be restored with the command set preplugin container=pdbnew:

oracle@localhost:/u00/app/oracle/ [PSI18] rman target sys/manager@psi18

Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 15 11:25:06 2018
Version 18.1.0.0.0

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

connected to target database: PSI18 (DBID=3896993764)

RMAN> set preplugin container=pdbnew;

executing command: SET PREPLUGIN CONTAINER
using target database control file instead of recovery catalog

RMAN> list preplugin backup of pluggable database pdbnew;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
9       Full    463.15M    DISK        00:00:01     15-MAY-2018 10:56:51
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105650
        Piece Name: /u00/app/oracle/fast_recovery_area/
/6C3BAD3B7C73354AE0530100007F9AD9/backupset/
2018_05_15/o1_mf_nnndf_TAG20180515T105650_fho86ltx_.bkp
  List of Datafiles in backup set 9
  Container ID: 4, PDB Name: PDBNEW
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
10      Full    463.15M    DISK        00:00:01     15-MAY-2018 10:57:57
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105756
        Piece Name: /u00/app/oracle/fast_recovery_area
/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
  List of Datafiles in backup set 10
  Container ID: 4, PDB Name: PDBNEW
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

We run the restore command with the preplugin clause:

RMAN> restore pluggable database pdbnew from preplugin;

Starting restore at 15-MAY-2018 11:26:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 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 00020 to 
/home/oracle/oradata/PSI18/pdbnew/system01.dbf
channel ORA_DISK_1: restoring datafile 00021 to 
/home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00022 to 
/home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00023 to 
/home/oracle/oradata/PSI18/pdbnew/users01.dbf
channel ORA_DISK_1: reading from backup piece /u00/app/oracle/fast_recovery_area/
DB18/DB18/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
channel ORA_DISK_1: piece handle=/u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp tag=TAG20180515T105756
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-MAY-2018 11:26:28

We run the recover command with the preplugin clause:

RMAN> recover pluggable database pdbnew from preplugin;

Starting recover at 15-MAY-2018 11:27:02
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/15/2018 11:27:03
RMAN-06054: media recovery requesting unknown archived log 
for thread 1 with sequence 17 and starting SCN of 1081326

We have to catalog the archive logs generated after the backup into the target CDB by issuing the catalog preplugin archivelog command :

RMAN> catalog preplugin archivelog '/u00/app/oracle/fast_recovery_area/
DB18/DB18/archivelog/2018_05_15/o1_mf_1_17_fhob69t7_.arc';

cataloged archived log
archived log file name=/u00/app/oracle/fast_recovery_area/DB18/DB18/
archivelog/2018_05_15/o1_mf_1_17_fhob69t7_.arc RECID=7 STAMP=0

Finally the recover command runs successfully:

RMAN> recover pluggable database pdbnew from preplugin;

Starting recover at 15-MAY-2018 11:32:25
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file 
/u00/app/oracle/fast_recovery_area/DB18/DB18/archivelog/2018_05_15/
o1_mf_1_17_fhob69t7_.arc
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-MAY-2018 11:32:26

We finish the recover and open the target PDB:

RMAN> recover pluggable database pdbnew;

Starting recover at 15-MAY-2018 11:33:10
using channel ORA_DISK_1

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

Finished recover at 15-MAY-2018 11:33:10

RMAN> alter pluggable database pdbnew open;

Statement processed

RMAN> exit


Recovery Manager complete.

As far we can see, the target PDB has been successfully restored and recovered:

oracle@localhost:/u00/app/oracle/oradata/DB18// [PSI18] sq

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:33:37 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBNEW			  READ WRITE NO

The preplugin clause can be used on the target CDB with rman commands like restore, recover, crosscheck, list and delete.

This new feature helps to maintain the backup compliance after moving to a new target CDB.