8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : Controlling PDB Replication in Data Guard Environments

This article describes two methods for controlling the replication of PDBs in standby databases when using Data Guard.

The examples here assume the new PDB is created from the seed database, or from a local read-only PDB in the primary database. The read-only source PDB limitation is still true up to 19c.

Assumptions
STANDBYS Clause (12.1.0.2)
ENABLED_PDBS_ON_STANDBY Parameter (12.2)

Assumptions

This articles assumes you have a Data Guard environment with a primary database and a single standby database. You can see how to create one manually here.

Alternatively, you can create a Data Guard environment with VirtualBox and Vagrant using one of the builds here.

The output below shows the current status of the pluggable databases in the primary and standby databases.

-- Primary
COLUMN name FORMAT a30

SELECT name, open_mode, recovery_status 
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY
------------------------------ ---------- --------
PDB$SEED                       READ ONLY  ENABLED
PDB1                           READ WRITE ENABLED

SQL>


-- Standby
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status 
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY
------------------------------ ---------- --------
PDB$SEED                       MOUNTED    ENABLED
PDB1                           MOUNTED    ENABLED

SQL>

The container databases are using Oracle Managed Files (OMF), so no file name conversion is needed. If you are not using OMF you will need to include the file name conversion as usual.

STANDBYS Clause (12.1.0.2)

The STANDBYS clause of the CREATE PLUGGABLE DATABASE statement was introduced in 12.1.0.2 to allow us to indicate which standby databases should protect the new PDB being created. The syntax is as follows.

CREATE PLUGGABLE DATABASE ... STANDBYS={('cdb_name', 'cdb_name', ...) | NONE | ALL [EXCEPT ('cdb_name', 'cdb_name', ...)]}

Here are some examples of the usage.

STANDBYS clause omitted : The pluggable database is protected by all standby databases.
STANDBYS=NONE : The pluggable database is not protected on any of the standby databases.
STANDBYS=ALL : The pluggable database is protected on all of the standby databases.
STANDBYS=ALL EXCEPT ('cdb1_stby_1','cdb1_stby_2') : The pluggable database is protected by all standby databases, except those with a DB_UNIQUE_NAME of 'cdb1_stby_1' and 'cdb1_stby_2'.
STANDBYS=('cdb1_stby_1') : The pluggable database is only protected by the standby database with a DB_UNIQUE_NAME of 'cdb1_stby_1'.

Create a new pluggable database on the primary database, which won't be protected by any standby database

-- Primary
CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY Password1
  STANDBYS=NONE;

ALTER PLUGGABLE DATABASE pdb2 OPEN;

As expected, we see the PDB is created in the primary database, but is not protected by the standby database.

-- Primary
COLUMN name FORMAT a30

SELECT name, open_mode, recovery_status 
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY
------------------------------ ---------- --------
PDB$SEED                       READ ONLY  ENABLED
PDB1                           READ WRITE ENABLED
PDB2                           READ WRITE ENABLED

SQL>


-- Standby
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status 
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY
------------------------------ ---------- --------
PDB$SEED                       MOUNTED    ENABLED
PDB1                           MOUNTED    ENABLED
PDB2                           MOUNTED    DISABLED

SQL>

We only have a single standby database, so we could achieve the same result by excluding it using the DB_UNIQUE_NAME along with ALL EXCEPT as shown below.

-- Primary

-- Remove the PDB.
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

-- Recreate it using ALL ACCEPT.
CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY Password1
  STANDBYS=ALL EXCEPT ('cdb1_stby');

ALTER PLUGGABLE DATABASE pdb2 OPEN;

As we only have a single standby database, any of the following would result in the PDB being protected by the standby database.

-- Primary

-- Remove the PDB.
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

-- Recreate it with one of these.
CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY Password1;

CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY Password1
  STANDBYS=ALL;

CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY Password1
  STANDBYS=('cdb1_stby');

-- Open it.
ALTER PLUGGABLE DATABASE pdb2 OPEN;

Remove the new PDB before trying the examples in the following section.

-- Primary
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

ENABLED_PDBS_ON_STANDBY Parameter (12.2)

The ENABLED_PDBS_ON_STANDBY initialisation parameter was introduced in this form in 12.2 to control which pluggable databases are protected by a specific standby database. The parameter can be set on a primary or standby database, but it is only used by standby databases. Here are some examples of how the parameter might be used with wildcard and exclusions.

"*" : All PDBs are protected.
"PDB1", "PDB2" : Only pluggable databases called "PDB1" and "PDB2" are protected.
"PDB*" : Only pluggable databases with a name beginning with "PDB" are protected.
"*", "-PDB*" : All pluggable databases are protected, except those with a name beginning with "PDB".
"*", "-PDB1" : All pluggable databases are protected, except if the name is "PDB1".

On the standby database we issue the following command to prevent a pluggable database called PDB2 from being replicated to the standby database. All other PDBs will be protected as normal.

-- Standby
ALTER SYSTEM SET enabled_pdbs_on_standby="*", "-PDB2";

On primary database create pluggable databases called PDB2 and PDB3.

-- Primary
CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE pdb2 OPEN;

CREATE PLUGGABLE DATABASE pdb3
  ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE pdb3 OPEN;

Now check the status of the pluggable databases on both the primary and standby databases.

-- Primary
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status 
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY
------------------------------ ---------- --------
PDB$SEED                       READ ONLY  ENABLED
PDB1                           READ WRITE ENABLED
PDB2                           READ WRITE ENABLED
PDB3                           READ WRITE ENABLED

SQL>


-- Standby
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status 
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY
------------------------------ ---------- --------
PDB$SEED                       MOUNTED    ENABLED
PDB1                           MOUNTED    ENABLED
PDB2                           MOUNTED    DISABLED
PDB3                           MOUNTED    ENABLED

SQL>

As expected, the pluggable database called PDB2 is not protected by the standby database.

To clean up, remove the new pluggable databases and reset the ENABLED_PDBS_ON_STANDBY parameter on the standby database.

-- Primary
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

-- Standby
ALTER SYSTEM SET enabled_pdbs_on_standby="*";

For more information see:

Creating a PDB in a Primary Database
ENABLED_PDBS_ON_STANDBY
Data Guard Quick Links : 11gR2, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Multitenant : All Articles

Hope this helps. Regards Tim...

Back to the Top.