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 (184.108.40.206) 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.
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:
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.