
PDBs and role transitions
Now, we currently do not have per-PDB role transitions. Nevertheless, it is possible to move one PDB to another CDB. However, what if the other CDB is located at the DR site and you don’t want to copy all the data over the network again?
Well, this is possible!
For this to work, we need 2 CDB’s. One primary in Site A protected by a Standby CDB in Site B. The second CDB is primary in Site B and protected with a standby CDB in Site A.

Or if we put this in text:
Site A
– cdgdemovm1a -> primary for db_name cdgdemoa running on site A and protected by cdgdemo1b on site B
– cdgdemo2b -> standby for db_name cdgdemob running primary on site B with instance name cdgdemovm2a
Site B
– cdgdemovm2a -> primary for db_name cdgdemob running on site B and protected by cdgdemo2b on site A
– cdgdemo1b -> standby for db_name cdgdemoa running primary on site A with instance name cdgdemovm1a
For this method to work, there are also some prerquisites.
- The source CDB and destination CDB have to be the same version
- The CDB’s per site, share the same disk group as asm aliases cannot span over disk groups
- The db_create_file_dest parameter in the destination CDB standby database must be set and should be set to the diskgroup name being used by the standby database
- The db_file_name_convert parameter in the destination CDB standby database must be set
As my demo setup are 2 identical machines, it would not make sense to set the db_file_name_convert parameter, but it is really necessary. So this leads to another good practice to always set this parameters.
For example, my parameters looks like this, I just need it to trigger the scanning of the headers of the data files later in this procedure. If this parameter is not set, the scanning of the header won’t happen automatically.
1 2 3 4 5 6 7 |
SQL> show parameter db_file_name_convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string ff, ff pdb_file_name_convert string SQL> |
Environment description
Database cdgdemoa (primary site A running in cdgdemovm1a) has following PDBs:
1 2 3 4 5 6 7 |
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDGDEMOVM1 READ WRITE NO SQL> |
Database cdgdemob (primary site B running in cdgdemovm2a) has following PDBs
1 2 3 4 5 6 7 |
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDGDEMOVM2 READ WRITE NO SQL> |
In order to be sure that we have a succesfull “role transition”, we need to create a table in the PDB so we are 100% sure the PDB has been moved to the other CDB correctly.
Both PDBs, create a test table.
On Site A in the primary cdgdemovm1a database in the pdb pdgdemovm1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[oracle@oelvm1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 12 07:55:33 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Alter session set container = PDGDEMOVM1; Session altered. SQL> create table whoami (whoami varchar2(100)); Table created. SQL> insert into whoami(whoami) values ('I have been inserted on PDGDEMOVM1 in cdgdemovm1a' ); 1 row created. SQL> commit; Commit complete. SQL> select * from whoami; WHOAMI -------------------------------------------------------------------------------- I have been inserted on PDGDEMOVM1 in cdgdemovm1a SQL> show con_name CON_NAME ------------------------------ PDGDEMOVM1 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@oelvm1 ~]$ |
On Site B in the primary cdgdemovm2a database in the pdb pdgdemovm2:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
[oracle@oelvm2 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 12 07:56:57 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Alter session set container = PDGDEMOVM2; Session altered. SQL> SQL> create table whoami (whoami varchar2(100)); Table created. SQL> insert into whoami(whoami) values ('I have been inserted on PDGDEMOVM2 in cdgdemovm2a' ); 1 row created. SQL> SQL> commit; Commit complete. SQL> select * from whoami 2 ; WHOAMI -------------------------------------------------------------------------------- I have been inserted on PDGDEMOVM2 in cdgdemovm2a SQL> show con_name; CON_NAME ------------------------------ PDGDEMOVM2 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@oelvm2 ~]$ |
Purpose: Role switch the pdbs.
We want to
- unplug PDGDEMOVM1 from cdgdemo1b (standby database on Site B) and make it active on cdgdemovm2a
- unplug PDGDEMOVM2 from cdgdemo2b (standby database on Site A) and make it active on cdgdemovm1a
The databases cdgdemovm1a and cdgdemovm2a REMAIN PRIMARY and the databases cdgdemo1b and cdgdemo2b REMAIN PHYSICAL STANDBY databases.
We just mimic a role transition by unplugging the pdbs from their primary and plugging them in, in another primary database.
As the verification, we expect to see
- PDGDEMOVM2 in instance cdgdemovm1a
- PDGDEMOVM1 in instance cdgdemovm2a
- in PDGDEMOVM2 in instance cdgdemovm1a we want to see the text in the whoami table ‘I have been inserted on PDGDEMOVM2 in cdgdemovm2a’
- in PDGDEMOVM1 in instance cdgdemovm2a we want to see the text in the whoami table ‘I have been inserted on PDGDEMOVM1 in cdgdemovm1a’
- Data Guard must remain in sync without other filecopies and not break.
Sounds like quite a challenge, isn’t it?
The steps
Let me first highlight, that our support guys do a great job and have documented a more or less similar scenario in MOS-note 2273829.1. Only that note assumes that both primaries are located at and sharing the same disk groups on the same site.
The difference with this blogpost is that we assume that the primaries are located on different sites.
I will take one PDB for the role transition in this example. We will move PDGDEMOVM1 from cdgdemovm1a to cdgdemovm2a in the illustrated example. Take into account that in the lab, I simultaneously do the second PDB (PDGDEMOVM2 from cdgdemovm2a to cdgdemovm1a).For clarity, I left that output out of this blog post, but at the very end for the verification if all worked out, it will be displayed which hopefully doesn’t create any confusion.
First, we need to determine the GUID for PDGDEMOVM1 to create the new directory location for the destination standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> select instance_name, database_role from v$instance, v$database; INSTANCE_NAME DATABASE_ROLE ---------------- ---------------- cdgdemovm1a PRIMARY SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDGDEMOVM1 READ WRITE NO SQL> select guid from v$pdbs where name = 'PDGDEMOVM1'; GUID -------------------------------- 8B0CFA84C8BC3B7AE0536538A8C005AF SQL> |
Next, we need a script. We could just unplug a PDB using a manifest file and override the location of the data files with source_file_directory. However, this would break Data Guard. In order to help Data Guard a little, we will create aliases in ASM to help it find its files. By doing it this way, we can reuse the data files, which are already on the same site so we don’t have to copy over all the data again.
To create the aliases, create a SQL Script with the name crt_pdb_alias_for_stby.sql and following content:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set newpage 0 set linesize 999 set pagesize 0 set feedback off set heading off set echo off set space 0 set tab off set trimspool on set ver off Prompt Run this on the CURRENT PRIMARY for which the PDB will become a standby PDB. Afterwards run the created script in the ASM instance Hosting the new standby. spool crt_pdb_alias_for_stby.sql prompt set echo on select 'ALTER DISKGROUP &&diskgrp_name_without_plus_sign add directory '||''''||'+&&diskgrp_name_without_plus_sign/&&new_stby_cdb_name_in_upper_case/'||guid||''''||';' from v$containers where name='&&pdb_name_in_upper_case'; select 'ALTER DISKGROUP &&diskgrp_name_without_plus_sign add directory '||''''||'+&&diskgrp_name_without_plus_sign/&&new_stby_cdb_name_in_upper_case/'||guid||'/DATAFILE'||''''||';' from v$containers where name='&&pdb_name_in_upper_case'; select 'ALTER DISKGROUP &&diskgrp_name_without_plus_sign add alias '||''''||replace(replace(name,'.','_'),'&&old_primary_cdb_name_in_upper_case','&&new_stby_cdb_name_in_upper_case')||''''||' for '||''''||name||''''||';' from v$datafile where con_id=(select con_id from v$pdbs pdb where pdb.name='&&pdb_name_in_upper_case'); exit |
Execute this SQL file on the primary database and give the correct CDB and PDB names for your environment:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[oracle@oelvm1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 14 09:32:29 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> @crt_pdb_alias_for_stby.sql Run this on the CURRENT PRIMARY for which the PDB will become a standby PDB. Afterwards run the created script in the ASM instance Hosting the new standby. set echo on Enter value for diskgrp_name_without_plus_sign: DATA Enter value for new_stby_cdb_name_in_upper_case: CDGDEMO2B Enter value for pdb_name_in_upper_case: PDGDEMOVM1 ALTER DISKGROUP DATA add directory '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF'; ALTER DISKGROUP DATA add directory '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE'; Enter value for old_primary_cdb_name_in_upper_case: CDGDEMOVM1A ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/system_272_1010653649' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/system.272.1010653649'; ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/sysaux_271_1010653649' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/sysaux.271.1010653649'; ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/undotbs1_273_1010653649' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/undotbs1.273.1010653649'; ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/users_275_1010653677' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/users.275.1010653677'; Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@oelvm1 ~]$ |
In this example case:
– We share the disk group with name DATA
– We will move the PDB to a standby CDB with name CDGDEMO2B (on this host)
– We will move PDB with name PDGDEMOVM1
– And we will move it away from the primary CDB CDGDEMOVM1A
Next step is to execute the generated sql file ( crt_pdb_alias_for_stby.sql ) on the ASM instance. For that, set your ASM environment and execute the script to generate the aliases (take into account if you want to remove aliases, you need to use remove alias as normal rm follows the alias and removes the file itself):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[oracle@oelvm1 ~]$ . oraenv ORACLE_SID = [oracle] ? +ASM The Oracle base has been set to /u01/app/grid [oracle@oelvm1 ~]$ sqlplus / as sysasm @crt_pdb_alias_for_stby.sql SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 14 09:37:41 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 SQL> ALTER DISKGROUP DATA add directory '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF'; Diskgroup altered. SQL> ALTER DISKGROUP DATA add directory '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE'; Diskgroup altered. SQL> Enter value for old_primary_cdb_name_in_upper_case: CDGDEMOVM1A SP2-0734: unknown command beginning "Enter valu..." - rest of line ignored. SQL> ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/system_272_1010653649' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/system.272.1010653649'; Diskgroup altered. SQL> ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/sysaux_271_1010653649' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/sysaux.271.1010653649'; Diskgroup altered. SQL> ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/undotbs1_273_1010653649' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/undotbs1.273.1010653649'; Diskgroup altered. SQL> ALTER DISKGROUP DATA add alias '+DATA/CDGDEMO2B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/users_275_1010653677' for '+DATA/CDGDEMOVM1A/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/users.275.1010653677'; Diskgroup altered. SQL> |
Then it is time to unplug the primary pdb.
First close it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[oracle@oelvm1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 11 17:35:25 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select instance_name, database_role from v$instance, v$database; INSTANCE_NAME DATABASE_ROLE ---------------- ---------------- cdgdemovm1a PRIMARY SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDGDEMOVM1 READ WRITE NO SQL> SQL> alter pluggable database PDGDEMOVM1 close instances=all; Pluggable database altered. SQL> |
Create the manifest file that is used for unplugging it:
1 2 3 4 5 |
SQL> alter pluggable database PDGDEMOVM1 unplug into '/home/oracle/pdb_PDGDEMOVM1_role_trans.xml'; Pluggable database altered. SQL> |
And finally drop the pdb, but keep the data files:
1 2 3 4 5 |
SQL> drop pluggable database PDGDEMOVM1 keep datafiles; Pluggable database dropped. SQL> |
Now wait!!! until the standby db’s have received the redo.
you must see messages like this in alert log of the standby database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
... MRP0 (PID:5161): Media Recovery Log +RECO/CDGDEMO1B/ARCHIVELOG/2019_06_12/thread_1_seq_30.306.1010741745 PDGDEMOVM1(3):Recovery deleting file #11:'+DATA/CDGDEMO1B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/users.293.1010657169' from controlfile. PDGDEMOVM1(3):Recovery dropped tablespace 'USERS' PDGDEMOVM1(3):WARNING: Cannot delete file +DATA PDGDEMOVM1(3):Recovery deleting tempfile #3:'+DATA' PDGDEMOVM1(3):Recovery dropped temporary tablespace 'TEMP' PDGDEMOVM1(3):Recovery deleting file #10:'+DATA/CDGDEMO1B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/undotbs1.292.1010657167' from controlfile. PDGDEMOVM1(3):Recovery dropped tablespace 'UNDOTBS1' PDGDEMOVM1(3):Recovery deleting file #9:'+DATA/CDGDEMO1B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/sysaux.291.1010657167' from controlfile. PDGDEMOVM1(3):Recovery dropped tablespace 'SYSAUX' PDGDEMOVM1(3):Recovery deleting file #8:'+DATA/CDGDEMO1B/8B0CFA84C8BC3B7AE0536538A8C005AF/DATAFILE/system.290.1010657159' from controlfile. PDGDEMOVM1(3):Recovery dropped tablespace 'SYSTEM' PDGDEMOVM1(3):Recovery dropped pluggable database 'PDGDEMOVM1' ... |
Do not continue until these messages have passed.
Once the alert messages have passed, you can finally plug the new one in. Please take into account that you will need the manifest file.
/home/oracle is not shared, so I copied this over using ssh.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> !ls -l *xml -rw-r--r--. 1 oracle dba 7360 Jun 12 10:00 pdb_PDGDEMOVM1_role_trans.xml SQL> !scp *xml 192.168.56.101:$PWD pdb_PDGDEMOVM1_role_trans.xml 100% 7360 4.1MB/s 00:00 SQL> !scp *xml 192.168.56.102:$PWD pdb_PDGDEMOVM1_role_trans.xml 100% 7360 2.9MB/s 00:00 pdb_PDGDEMOVM2_role_trans.xml 100% 7384 3.2MB/s 00:00 SQL> !ls -l *xml total 32 -rw-r--r--. 1 oracle dba 7360 Jun 12 10:04 pdb_PDGDEMOVM1_role_trans.xml -rw-r--r--. 1 oracle oinstall 7384 Jun 12 10:04 pdb_PDGDEMOVM2_role_trans.xml SQL> |
Then plug in the PDB which was a former “standby” pdb.
1 2 3 4 5 |
SQL> create pluggable database PDGDEMOVM2 using '/home/oracle/pdb_PDGDEMOVM2_role_trans.xml' source_file_directory='+DATA/CDGDEMO2B' tempfile reuse nocopy; Pluggable database created. SQL> |
in alert log, you will see that the database is scanning the location for the right data files
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
2019-06-14T09:44:38.158778-04:00 create pluggable database PDGDEMOVM2 using '/home/oracle/pdb_PDGDEMOVM2_role_trans.xml' source_file_directory='+DATA/CDGDEMO2B' tempfile reuse nocopy 2019-06-14T09:44:38.637294-04:00 Scanning plugin datafile directory - +DATA/CDGDEMO2B for file originally created as +DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/system.273.1010653869 with afn -8 Using file-+DATA/CDGDEMO2B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/system.290.1010657173 for original file-+DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/system.273.1010653869 with afn-8 Scanning plugin datafile directory - +DATA/CDGDEMO2B for file originally created as +DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/sysaux.271.1010653867 with afn -9 Using file-+DATA/CDGDEMO2B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/sysaux.291.1010657175 for original file-+DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/sysaux.271.1010653867 with afn-9 Scanning plugin datafile directory - +DATA/CDGDEMO2B for file originally created as +DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/undotbs1.272.1010653867 with afn -10 Using file-+DATA/CDGDEMO2B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/undotbs1.292.1010657179 for original file-+DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/undotbs1.272.1010653867 with afn-10 Creating new file-+DATA/CDGDEMO2B/temp.274.1010653873 for original file-+DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/TEMPFILE/temp.274.1010653873 Scanning plugin datafile directory - +DATA/CDGDEMO2B for file originally created as +DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/users.275.1010653891 with afn -11 Using file-+DATA/CDGDEMO2B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/users.293.1010657183 for original file-+DATA/CDGDEMOVM2A/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/users.275.1010653891 with afn-11 PDGDEMOVM2(5):Endian type of dictionary set to little ... PDGDEMOVM2(5):Buffer Cache flush started: 5 PDGDEMOVM2(5):Buffer Cache flush finished: 5 Completed: create pluggable database PDGDEMOVM2 using '/home/oracle/pdb_PDGDEMOVM2_role_trans.xml' source_file_directory='+DATA/CDGDEMO2B' tempfile reuse nocopy ... |
Then it is time to perform a log switch (or just wait) to force the redo being sent to the standby database and the standby database will then see that it has to add the pdb.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
... 2019-06-14T09:46:54.099367-04:00 MRP0 (PID:7795): Media Recovery Log +RECO/CDGDEMO1B/ARCHIVELOG/2019_06_14/thread_1_seq_39.326.1010914959 Recovery created pluggable database PDGDEMOVM2 PDGDEMOVM2(5):Recovery scanning directory +DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE for any matching files PDGDEMOVM2(5):Recovery created file +DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/system_273_1010653869 PDGDEMOVM2(5):Successfully added datafile 12 to media recovery PDGDEMOVM2(5):Datafile #12: '+DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/system_273_1010653869' PDGDEMOVM2(5):Recovery created file +DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/sysaux_271_1010653867 PDGDEMOVM2(5):Successfully added datafile 13 to media recovery PDGDEMOVM2(5):Datafile #13: '+DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/sysaux_271_1010653867' PDGDEMOVM2(5):Recovery created file +DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/undotbs1_272_1010653867 PDGDEMOVM2(5):Successfully added datafile 14 to media recovery PDGDEMOVM2(5):Datafile #14: '+DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/undotbs1_272_1010653867' 2019-06-14T09:46:55.162633-04:00 PDGDEMOVM2(5):Recovery created file +DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/users_275_1010653891 PDGDEMOVM2(5):Successfully added datafile 15 to media recovery PDGDEMOVM2(5):Datafile #15: '+DATA/CDGDEMO1B/8B0D0787E7F1402CE0536638A8C0B9A4/DATAFILE/users_275_1010653891' MRP0 (PID:7795): Media Recovery Waiting for T-1.S-40 (in transit) ... |
To show the full picture, a screenshot is maybe better

Then it is time for the real test. Open the PDBs and check if all went correctly:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> alter pluggable database all open; Pluggable database altered. SQL> alter session set container = PDGDEMOVM2; Session altered. SQL> select instance_name, database_role from v$instance, v$database; INSTANCE_NAME DATABASE_ROLE ---------------- ---------------- cdgdemovm1a PRIMARY SQL> select * from whoami; WHOAMI -------------------------------------------------------------------------------- I have been inserted on PDGDEMOVM2 in cdgdemovm2a SQL> |
But of course … we want to open the corresponding Active Standby PDB as well:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> alter pluggable database all open; Pluggable database altered. SQL> alter session set container = PDGDEMOVM2; Session altered. SQL> select instance_name, database_role from v$instance, v$database; INSTANCE_NAME DATABASE_ROLE ---------------- ---------------- cdgdemo1b PHYSICAL STANDBY SQL> select * from whoami; WHOAMI -------------------------------------------------------------------------------- I have been inserted on PDGDEMOVM2 in cdgdemovm2a SQL> |
So yes! This is the correct PDB which has now become a Active Standby PDB.
And to show it is just a fully working primary <-> standby we create a table in the new primary pdb
1 2 3 4 5 6 7 8 9 10 |
SQL> show con_name CON_NAME ------------------------------ PDGDEMOVM2 SQL> create table test(id number); Table created. SQL> |
and it just replicates to the standby pdb as you would expect
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select instance_name, database_role from v$instance, v$database; INSTANCE_NAME DATABASE_ROLE ---------------- ---------------- cdgdemo1b PHYSICAL STANDBY SQL> show con_name CON_NAME ------------------------------ PDGDEMOVM2 SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER SQL> |
Conclusion
Despite we currently do not have per-pdb switchover or failover, it is possible to do a cross-site per-pdb protection without having to copy over all the data again and keep Data Guard happily working. I agree it is a little tricky, so if you decide to go for this approach, perform very extensive testing on small databases first which suit your environment.
What about the broker? The Broker doesn’t care about the pdbs being plugged out or plugged in. For Data Guard, this operation is purely a redo stream. Yes indeed “migrate pluggable database” does the move as well. Take into account that using the migrate command, the destination container for the MIGRATE command is always a primary database.
As always, questions, remarks? find me on twitter @vanpupi