PDBs and role transitions

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.

Environment description

Database cdgdemoa (primary site A running in cdgdemovm1a) has following PDBs:

Database cdgdemob (primary site B running in cdgdemovm2a) has following PDBs

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:

On Site B in the primary cdgdemovm2a database in the pdb pdgdemovm2:

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.

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:

Execute this SQL file on the primary database and give the correct CDB and PDB names for your environment:

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):

Then it is time to unplug the primary pdb.
First close it:

Create the manifest file that is used for unplugging it:

And finally drop the pdb, but keep the data files:

Now wait!!! until the standby db’s have received the redo.
you must see messages like this in alert log of the standby database:

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.

Then plug in the PDB which was a former “standby” pdb.

in alert log, you will see that the database is scanning the location for the right data files

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.

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:

But of course … we want to open the corresponding Active Standby PDB as well:

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

and it just replicates to the standby pdb as you would expect


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

Leave a Reply

Your email address will not be published. Required fields are marked *

9 + 14 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: