Skip to content

Controlling PDB Replication in Data Guard

    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 12cR1

    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 in 12cR2

    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="*";