Multitenant

Refreshable PDB Clones in Data Guard Environments On-premises and on Oracle Cloud

Introduction

Refreshable PDB Clones enable you to copy a PDB and keep the copy (the clone) up-to-date with the source PDB (refresh the clone). The clone can be on the same CDB (local) or on another CDB (remote).

If the target database is a primary database in a Data Guard configuration, then the refreshable PDB clone will always be created on the primary ONLY and never on the standby. The reason is described in the MOS note “Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)”

NOTE: When using 12.2 hot clone, PDB refresh and PDB relocate functionality, the new PDBs should always be created with the STANDBYS=NONE clause. Currently there is no way for the standby to be able to exactly repeat the same operation as the primary (blocks at the source have changed by the time redo is received at the standby, it is not possible to run multiple media recovery sessions on the standby to apply redo from the source generated during the hot clone, etc.), so you must defer recovery of the PDB and enable recovery at some point in the future. This applies to both remote (source PDB is in another container database) and local (source PDB is in the same container database) clones. PDBs created as refreshable should never have recovery enabled as long as there may be a refresh operation that can be performed. If the refreshable PDB has been transitioned into a standalone PDB via the alter pluggable database….refresh none operation, at that point it can have recovery enabled at any corresponding standby.

This blog post shows the steps and implications when creating the refreshable PDB clone manually (on-premises) and how the process is automated when using Cloud Tooling in Oracle Cloud.

Spoiler Alert: Cloud Tooling automates the process of creating the PDB on standby when the refreshable clone is converted to a regular PDB.

The Environment

We will use the following:

  • Source: Single Instance Database version 19.21 on VM DB System on Oracle Cloud with a PDB called PDBFRA.
  • Target: Single Instance Database version 19.21 in Active Data Guard configuration created via Cloud Tooling on VM DB Systems in Oracle Cloud.

Manual Configuration

For on-premises configurations, or when using manual PDB creation in Oracle Cloud:

Step 1: Prepare the C##SYSOPER user on the source database

Grant the C##SYSOPER user the required privileges to be used for the database link:

SQL> grant create session, sysoper, create pluggable database to C##SYSOPER identified by VerySecretPW__2023 container=all;
 
Grant succeeded.

Step 2: On target primary CDB, create a database link to the source CDB

Create a database link on the target primary database pointing to the source database:

SQL> create database link DBLINK_TO_SRC connect to C##SYSOPER identified by VerySecretPW__2023 using '<connection_string_to_source_cdb>';
 
Database link created.

Step 3: Create the refreshable PDB clone on the target primary

Use the database link from the previous step to create a refreshable remote clone from the source PDB, providing the STANDBYS=NONE clause:

SQL> create pluggable database MCLONE from PDBFRA@DBLINK_TO_SRC keystore identified by "VerySecretPW__2023" refresh mode every 1 minutes STANDBYS=NONE;

Pluggable database created.

Step 4: Check the metadata of the newly created refreshable clone

On primary:

set lines 300
col pdb_name for a20
col status for a20
col refresh_mode for a20
col refresh_interval for 999
SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='MCLONE';

    CON_ID PDB_NAME             STATUS               REFRESH_MODE         REFRESH_INTERVAL
---------- -------------------- -------------------- -------------------- ----------------
         5 MCLONE               REFRESHING           AUTO                                1

Executing the same query on standby will give you the same results, as the metadata is replicated in the standby. However, the standby PDB does not contain any datafiles:

col name for a60
SQL> select status, name from v$datafile where con_id = 5;

STATUS               NAME
-------------------- ------------------------------------------------------------
SYSOFF               /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00017
RECOVER              /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00018
RECOVER              /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00019
RECOVER              /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00020

Step 5: Convert the refreshable clone to a regular PDB

To open the refreshable clone in read/write mode, you need to convert it to a regular PDB. On primary:

SQL> alter pluggable database MCLONE refresh mode none;

Pluggable database altered.

SQL> alter pluggable database MCLONE open;

Pluggable database altered.

Check the recovery status on the standby. It will show DISABLED:

SQL> select con_id, open_mode, recovery_status from v$pdbs where name='MCLONE';

    CON_ID OPEN_MODE  RECOVERY_STATUS
---------- ---------- --------------------------
         5 MOUNTED    DISABLED

Step 6: Restore and recover the PDB on standby

Follow the steps as described in the MOS note “Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)” or the example provided in this blog post, starting at step 2:

Using Cloud Tooling on Oracle Cloud

Create a refreshable PDB clone

For BaseDB and ExaDB-D you can create the refreshable PDB clone using the automation provided by the service, e.g., using the Cloud Console (the web interface).

From the source database details page, choose “Pluggable Databases” in the resources section, then click on the dotted menu on the right and choose “Clone”:

Choose “Refreshable clone”. In the Destination section, choose the primary database system and database. Enter a name for the new refreshable clone and the SYS and TDE wallet passwords:

In the Source section, enter the SYS password. In the Database link section, enter a common user name and password that will be used for the database link from the primary pointing to the source database:

Make sure the user provided has the CREATE PLUGGABLE DATABASE privilege. See step 1 above.

Finally, click on “Create pluggable database”.

On the primary database details page, you will see a new refreshable PDB being provisioned:

Note: the PDBFRA here is the one already created in the Data Guard target environment. I wasn’t very creative when it came to the naming of PDBs.

You can also check the alert log of the primary database. You will see:

[oracle@fraad2 ~]$ view /u01/app/oracle/diag/rdbms/cdbfra_ad2/CDBFRA/trace/alert_CDBFRA.log
...
CREATE PLUGGABLE DATABASE "ACLONE" FROM "PDBFRA"@"DBLINK_CLONE_EPHEMERAL_ACLONE_20231215143656.SUBNETPUBLIC.VCNFRA.ORACLEVCN.COM" PARALLEL  STORAGE ( MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED)  file_name_convert=NONE KEYSTORE identified by * 
refresh mode manual  STANDBYS=NONE
...

The refreshable clone is created with manual refresh mode and STANDBYS=NONE. You can change the refresh mode to automatic via SQL command if needed.

The standby database will not show the refreshable PDB clone in the OCi Console (named ACLONE):

If you check the standby on the DB System, you will see the same behavior as manual creation:

SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='ACLONE';

    CON_ID PDB_NAME             STATUS               REFRESH_MODE         REFRESH_INTERVAL
---------- -------------------- -------------------- -------------------- ----------------
         4 ACLONE               REFRESHING           MANUAL

SQL> select status, name from v$datafile where con_id = 4;

STATUS               NAME
-------------------- ------------------------------------------------------------
SYSOFF               /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00021
RECOVER              /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00022
RECOVER              /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00023
RECOVER              /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00024

Convert the refreshable PDB to a regular PDB

From the primary database details page, choose “Pluggable Databases” in the resources section, and move the support icon so you can see the dotted menu on the right side:

Click on the dotted menu and choose “Convert to regular PDB”:

Enter the SYS and TDE wallet passwords and click on “Submit”:

On the primary, you will see the PDB in Updating state:

On standby, the PDB will be now created. This process happens automatically so you don’t need to execute any additional steps manually as described in the linked resources in step 6 (copy the TDE wallet file, restore and recover the PDB, and create a TEMP file):

In the primary alert log you will find:

...
ACLONE(4): ALTER PLUGGABLE DATABASE REFRESH MODE NONE
...

In the standby alert log:

...
ACLONE(4):alter tablespace TEMP add tempfile  size 151552K  AUTOEXTEND ON NEXT 65536K MAXSIZE UNLIMITED
...

So, a TEMP file is also automatically created for you in the standby PDB.

Check the status and datafiles on the standby DB System:

SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='ACLONE';

    CON_ID PDB_NAME             STATUS               REFRESH_MODE         REFRESH_INTERVAL
---------- -------------------- -------------------- -------------------- ----------------
         4 ACLONE               NORMAL               NONE

SQL> select status, name from v$datafile where con_id = 4;

STATUS               NAME
-------------------- ----------------------------------------------------------------------------------------------------------------------------------
SYSTEM               /u02/app/oracle/oradata/CDBFRA_5rp_fra/CDBFRA_5RP_FRA/0C8E550F16030505E06367000A0A0D8A/datafile/o1_mf_system_lqrtj38r_.dbf
ONLINE               /u02/app/oracle/oradata/CDBFRA_5rp_fra/CDBFRA_5RP_FRA/0C8E550F16030505E06367000A0A0D8A/datafile/o1_mf_sysaux_lqrtj49b_.dbf
ONLINE               /u02/app/oracle/oradata/CDBFRA_5rp_fra/CDBFRA_5RP_FRA/0C8E550F16030505E06367000A0A0D8A/datafile/o1_mf_undotbs1_lqrtjbds_.dbf
ONLINE               /u02/app/oracle/oradata/CDBFRA_5rp_fra/CDBFRA_5RP_FRA/0C8E550F16030505E06367000A0A0D8A/datafile/o1_mf_users_lqrtjbf7_.dbf

Tracing in case of failures

If a work request is not completed successfully, check the root cause by using the dbcli command on the VM DB System:

[root@fraad2 ~]# dbcli list-jobs
[root@fraad2 ~]# dbcli describe-job -i <job_id>

Depending on the work request, remember to execute the command on the standby system as well.

Conclusion

Refreshable PDB clones in Data Guard configurations will always be created on the primary database only. If you create the refreshable clone manually via SQL commands, you will need to restore and recover the PDB on the standby after converting the refreshable clone to a regular PDB.

Oracle Cloud provides automation to create refreshable PDB clones. Here again, the refreshable PDB clone will be created on the primary database only. When you convert the refreshable clone to a regular PDB using Cloud Tooling, the cloud automation will take care of restoring and recovering the PDB on standby, eliminating the manual process for you.

Further Reading

Would you like to get notified when the next post is published?