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.

2 thoughts on “PDB Cloning with Data Guard”

  1. Hi Johannes,

    I can’t comment whether that behaviour is a supported solution, but I came across it when testing plugging (not cloning) PBDs to a CDB with a physical standby some time ago. I emphasize plugging vs cloning because of the distinction in behaviour described in MOS Doc ID 2049127.1 . If it is useful, I’ll post the steps below (hopefully the fixed width font formatting will work):

    Primary:
    [code]
    SQL> CREATE PLUGGABLE DATABASE testplug
    USING ‘/tmp/testplug.xml’
    NOCOPY
    TEMPFILE REUSE;

    SQL> alter pluggable database testplug open;
    [/code]

    As you wrote, on standby the redo apply (MRP0 process) stops. Checking v$datafile and v$datafile_header on the standby for the plugged PDB returns only one row, for the SYSTEM tablespace. So, I copy all the PDB’s datafiles from primary to standby, eg:

    [code]
    $ rman target sys@orcl auxiliary sys@dg01orcl
    RMAN> backup as copy datafile ‘/u01/app/oracle/oradata/orcl/testplug/system01.dbf’ auxiliary format ‘/u01/app/oracle/oradata/dg01orcl/testplug/system01.dbf’;
    [/code]
    And repeat the same for SYSAUX and USERS tablespace’s datafiles.

    When trying to enable redo apply on the standby (I didn’t use the Broker), the same behaviour as you experienced appears – the MRP0 process stops and the alert log reports:
    [code]
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

    Thu Jul 21 16:15:46 2016
    Errors in file /u01/app/oracle/diag/rdbms/dg01orcl/dg01orcl/trace/dg01orcl_mrp0_12237.trc:
    ORA-01157: cannot identify/lock data file 12 – see DBWR trace file
    ORA-01110: data file 12: ‘/u01/app/oracle/oradata/dg01orcl/testplug/sysaux01.dbf’
    [/code]

    However v$datafile and v$datafile_header now have an additional row for the sysaux tablespace, so the datafiles are being gradually added to the standby’s controlfile. And when enabling redo apply for two more times, the redo apply works.

    Regards,
    Jure Bratina

  2. Johannes Ahrends

    Thanks for this comment. Looks like you came to the same solution than me. Funny though that the creation of a PDB from the PDB$SEED works without any issues (except that temp will not be created).

Leave a Comment

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

Scroll to Top