PDB Cloning with Data Guard

The Challenge

There are some anomalies when using Multitenant Database with Data Guard. In the following example I will describe how to clone a pluggable database and synchronize Data Guard. The base is Oracle 12 Release 1 (12.1.0.2) and Data Guard without Real Time Query (Active Data Guard).

Why is this a Challenge?

If you create a Pluggable Database from the seed (PDB$SEEE) Data Guard is able to handle this so there is no outage or failure for the configuration. All Datafiles are created and in sync.

The command:

SQL> CREATE PLUGGABLE DATABASE <PDBNAME> 
     ADMIN USER ... IDENTIFIED BY ... FILE_NAME_CONVERT=...;

But if you create a Pluggable Database from an existing one (Clone) the datafiles will not be transfered or created but Data Guard will fail:

SQL> CREATE PLUGGABLE DATABASE <PDBNEU> 
      FROM  <PDBALT> FILE_NAME_CONVERT=...;

Dataguard: <Standby DBNAME> - Physical standby database 
Error: ORA-16766: Redo Apply is stopped

Due to the Oracle documentation you should use the Option “STANDBYS=NONE” while cloning PDBs in a Data Guard environment. In this case some dummy datafiles are created in the Standby Database and you have to copy the missing data files over to the Standby Database and use the command “ALTER DATABASE CREATE DATAFILE” to resync the configuration. For this command the Standby Database has to be opened read only. Don’t worry this does not imply the Active Data Guard license because the Apply process has been stopped due to the failure.

If you omit the option “STANDBYS=NONE” you need to initiate a recovery procedure. And this is where my tests are beginning and I would appreciate any comment on the following procedure:

Clone PDB

First the PDB will be created on the Primary database as usual. BTW: with the October 2016 patch set (I believe) it is no longer required to open the source PDB read only but the database will freeze for the duration of the cloning process.

SQL> CREATE PLUGGABLE DATABASE pdbneu 
       FROM pdbalt FILE_NAME_CONVERT=('pdbold','pdbnew');
 SELECT d.name   
   FROM v$datafile d, v$pdbs p  
  WHERE d.con_id = p.con_id    
    AND p.name = 'PDBNEU';
...
/u02/oradata/LUDWIG/pdbnew/system01.dbf 
/u02/oradata/LUDWIG/pdbnew/sysaux01.dbf 
/u02/oradata/LUDWIG/pdbnew/users01.dbf

Don’t open the PDB yet because we need to copy the data files to the Standby Database:

oracle% scp -r /u02/oradata/LUDWIG/pdbneu bach:/u02/oradata/LUDWIG

And this is the trick:

If you simply enable the apply process as many times as the PDB has data files Data Guard will start synchronizing the Standby Database again. In this example I need to start the apply process three times. After Data Guard is in sync again you  can open the PDB on the primary database.

dgmgrl /
DGMGRL> show configuration

Configuration - LUDWIG_DG

  Protection Mode: MaxAvailability
  Members:
  LUDWIG_S1 - Primary database
    LUDWIG_S2 - Physical standby database
      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 35 seconds ago)

DGMGRL> edit database "LUDWIG_S2" set state='APPLY-ON';
Succeeded.
DGMGRL> edit database "LUDWIG_S2" set state='APPLY-ON';
Succeeded.
DGMGRL> edit database "LUDWIG_S2" set state='APPLY-ON';
Succeeded.
DGMGRL> show database "LUDWIG_S2"

Database - LUDWIG_S2

  Enterprise Manager Name: LUDWIG_S2
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 0 seconds ago)
  Average Apply Rate:      18.00 KByte/s
  Real Time Query:         OFF
  Instance(s):
    LUDWIG1 (apply instance)
    LUDWIG2

Database Status:
SUCCESS

Is this a supported solution? It works and it’s easy to script it.