Introduction
Cloning PDBs in Multitenant environments is an easy way to migrate a PDB from one CDB to another or to create multiple copies of that PDB for test and development purposes. The data files are being read from the source and transferred to the target CDB. Hence, cloning a PDB also means generating workload on the source database. Consequently, in data guard environments, one of the most asked questions is whether we could offload this workload to the standby server by cloning the PDB from the standby database.
The Environment
For this blog post I’m going to use:
- Source: Oracle database release 19.10, Multitenant with Data Guard.
- Target: Oracle database release 19.10, Multitenant without Data Guard.
Preparation
On the source, primary CDB, grant privileges need to the C##SYSOPER user:
SQL> grant create session, sysoper to C##SYSOPER identified by SysOperPw__199 container=all;
Grant succeeded.
Data Guard will propagate the changes to the standby database.
On the target CDB, create a database link pointing to the source, standby CDB:
SQL> create database link DBLINK_TO_STANDBY connect to C##SYSOPER identified by SysOperPw__199 using '<connection_string_to_source_standby>';
Database link created.
PDB Cloning
On the target CDB, clone the PDB from the source standby using the database link created in the previous step:
SQL> create pluggable database PDBCLONE from PDB001@DBLINK_TO_STANDBY keystore identified by "<TDE_Password>";
*
ERROR at line 1:
ORA-17628: Oracle error 65036 returned by remote Oracle server
ORA-65036: pluggable database not open in required mode
Ok, this is expected when the source PDB is in MOUNTED state. As we are trying to clone from the standby PDB, we need to open it in READ ONLY mode. On the source, standby CDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB001 MOUNTED
SQL> alter pluggable database PDB001 open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB001 READ ONLY NO
As we need to open the standby PDB in READ ONLY mode, does we need the Active Data Guard license??? (let’s just continue for the moment).
Now back again to the target CDB, try to create the PDB clone again:
SQL> create pluggable database PDBCLONE from PDB001@DBLINK_TO_STANDBY keystore identified by "<TDE_Password>";
ERROR at line 1:
ORA-00600: internal error code, arguments: [3647], [32], [16400], [], [], [],
[], [], [], [], [], []
Ups! ORA-600 never looks good. Looking at Doc ID 2072550.1 (Create Pluggable Database From ADG errors out with ORA-600 [3647]), it exactly describes this behavior for database versions 12.1.0.2 to 18.3. But it seems to be still an issue in 19.10.
The solution described in the MOS note is:
In order to Create a Pluggable database from Source ADG (Active Data Guard) you need to first stop the Apply process.
Let’s try this using the Data Guard Broker:
DGMGRL> edit database <db_unique_name_of_source_standby> set state = 'apply-off';
Succeeded.
Now we try the cloning again after cleaning up the last failed PDB creation. On the target CDB:
SQL> drop pluggable database PDBCLONE including datafiles;
Pluggable database dropped.
SQL> create pluggable database PDBCLONE from PDB001@DBLINK_TO_STANDBY keystore identified by "<TDE_Password>";
Pluggable database created.
SQL> alter pluggable database PDBCLONE open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
8 PDBCLONE READ WRITE NO
All good things come in threes. Finally! PDB created and opened successfully!
Re-enable the redo apply and close the source standby PDB if needed:
SQL> alter pluggable database PDB001 close immediate;
Pluggable database altered.
DGMGRL> edit database <db_unique_name_of_source_standby> set state = 'apply-on';
Succeeded.
Active Data Guard (ADG) License Needed?
As the source, standby PDB has to be opened in READ ONLY mode, you’d need the ADG license. But as we need to stop the redo apply anyway as described in the MOS note, you don’t need the ADG license if you open the standby database in READ ONLY mode while redo apply is stopped.
As always, please read the licensing documents carefully before taking any actions.
Considerations
Depending on the time needed for the clone to complete (source PDB size, parallelism, and network throughput) and the transaction rate on the primary, you need to make sure to have sufficient storage space for the archived Standby Redo Logs on standby.
In case of a disaster and failover to the standby, the standby will need to apply the redo first, which would increase your recovery time.
Refreshable Clones
What about creating a refreshable PDB clone from the standby? Would that work?
Stop the redo apply and open the source PDB in READ ONLY mode as described above. Then, on the target CDB:
SQL> create pluggable database REFCLONE from PDB001@DBLINK_TO_STANDBY keystore identified by "TDE_Password"
refresh mode every 1 minutes;
Pluggable database created.
-- try manual refresh
SQL> alter pluggable database PDB1RCP refresh;
Pluggable database altered.
Cool, refreshable PDB clone is created successfully, and even the manual refresh command works.
BUT!!! After starting the redo apply again, the standby database is getting synchronized with the primary, but the refreshable PDB clone is NOT getting updated from the standby!!!
Refreshable PDB clones are not supported to be created from a standby database. Hence, it is an expected behavior. A refreshable PDB clone has to be created from the primary database to get updated.
Also, check Doc ID 2765472.1: ORA-65345 When Refreshing PDB Sourced From Active Data Guard Standby.
Conclusion
Creating PDB clones from the standby database in a Data Guard environment is possible when:
- Standby PDB (the source for cloning) is opened READ ONLY, and
- Redo Apply is stopped.
Refreshable PDB clones has to be created from the primary database to get updated. You can control the number of parallel execution servers using the parallel_pdb_creation_clause to speed up the PDB copy or to reduce the impact on the source database.
Database Heartbeat 