dbi Blog

Introduction

In Oracle Database 19c, if you have a Dataguard Configuration of a multitenant database, and you want to clone a pluggable database from a remote server to your primary, then you have to mind that the dataguard mechanism is about applying redo, not copying datafiles : After you execute the clone, the new cloned pluggable will be useless on the standby site.

you can verify that the clone’s standby contains no datafiles by quering the v$datafile view.

You would get something like this :

SQL> select name from v$datafile where con_id=4;
 
NAME
--------------------------------------------------------------
/opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00044
/opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00045
/opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00046

The tricky part is that you might not be aware of this, because the dataguard broker will tell you the configuration is fine, and the validate command will confirm that the standby is ready for switchover. (The swithover will indeed work, but you won’t be able to open the new cloned pluggable in the standby site).

One possible solution, if you don’t want to rebuild your standby database after the clone, is to use a transient pluggable copy.

The idea is to :
– First do a clone to a temporary pluggable (or transient) in your primary, using a database link to your remote database.
– Define a “self-refering” database link in your primary database.
– Use this database link to create your pluggable database, which will be usable also on the standby.

It’s not complicated but it requires many steps, so let’s see how it’s done :

The procedure

Let’s consider this example :

– We have a multitenant database called QUALCDB :

SQL> select name from v$database;

NAME
---------
QUALCDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 QUAL_PDB1                      READ WRITE NO
SQL>

This database is protected by a dataguard configuration :

Configuration - qualcdb

  Protection Mode: MaxPerformance
  Members:
  QUALCDB_SITE1 - Primary database
    QUALCDB_SITE2 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 14 seconds ago)

– And a multitenant database called PRODCDB on a remote server, this would be the source of our clone :

SQL> select name from v$database;

NAME
---------
PRODCDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PROD_PDB1                      READ WRITE NO
         5 PROD_PDB2                      READ WRITE NO
SQL>

Our objective is to clone PROD_PDB2 into the primary QUALCDB database as QUAL_PDB2, bellow we will execute the following procedure :

– Clone the pluggable PROD_PDB2 to TEMP_QUAL2 on the primary QUALCDB, so this is the clone that is fine on the primary, but useless on the standby
– Create a Database link from the QUALCDB Primary to itself.
– Give this database link as a parameter to the standby database.
– Use this database link to clone TEMP_QUAL2 to our final pluggable QUAL_PDB2, this would our final clone that is fine on the standby.

Let’s do it step by step :

1 – In the source PRODCDB, we create a user that would execute the clone :

-- On the Remote prodcdb database
SQL> grant create session, sysoper to C##PDBCLONE identified by Welcome__001 container=all;

Grant succeeded.

SQL>

2- In the destination QUALCDB, we create a database link to the PRODCDB database :

-- On the Primary
SQL> CREATE DATABASE LINK "DBLINK_TO_REMOTEDB" CONNECT TO "C##PDBCLONE" IDENTIFIED BY "Welcome__001" USING 'your_tns_string_to_PRODCDB';

Database link created.

3- We open the root standby in read only :

Please note that in 19c, the Active Data Guard option license is not required when only the CDB$ROOT and PDB$SEED are opened in read only, as long as you ensure that the other users PDBs are always closed when apply is on.

You can find valuable details and ways to test that in this blog : dbi-services blog

-- On the standby
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 QUAL_PDB1                      MOUNTED

SQL> alter database open read only;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 QUAL_PDB1                      MOUNTED
SQL>

4- We clone prod_pdb2 to a transient database, using the STANDBYS=NONE clause, let’s name the clone TEMP_QUAL2

SQL> select name from v$database;

NAME
---------
QUALCDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

SQL> create pluggable database TEMP_QUAL2 from PROD_PDB2@DBLINK_TO_REMOTEDB STANDBYS=NONE;

Pluggable database created.

SQL> alter pluggable database TEMP_QUAL2 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 QUAL_PDB1                      READ WRITE NO
         4 TEMP_QUAL2                     READ WRITE NO

5- We want to create our QUAL_PDB2 from TEMP_QUAL2, but The temp_qual2 datafiles do not exist on the standby server, so we need something beyond the dataguard redo apply to make it work, this is provided by the standby_pdb_source_file_dblink parameter.

First, we define a “self-db-link” from the primary container to “itself” :

-- On the Primary 
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> grant create session, sysoper to C##PDBCLONE identified by Welcome__001 container=all;

Grant succeeded.

SQL> CREATE DATABASE LINK "SELF_DBLINK" CONNECT TO "C##PDBCLONE" IDENTIFIED BY Welcome__001 USING 'your_primary_database_tns';

Database link created.

SQL>

Then we set the standby_pdb_source_file_dblink parameter to this dblink on the standby :

--On the standby
SQL> alter system set standby_pdb_source_file_dblink='SELF_DBLINK' scope=both;

System altered.

SQL>

Now we can create our pluggable using the STANDBYS=ALL clause, please note it is important in this case to restart temp_qual2 in read only mode even if you are not updating it

--On the Primary
SQL> alter pluggable database TEMP_QUAL2 close;

Pluggable database altered.

SQL> alter pluggable database TEMP_QUAL2 open read only;

Pluggable database altered.

SQL> create pluggable database QUAL_PDB2 from TEMP_QUAL2 STANDBYS=ALL;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 QUAL_PDB1                      READ WRITE NO
         4 TEMP_QUAL2                     READ WRITE NO
         5 QUAL_PDB2                      MOUNTED
SQL> alter pluggable database QUAL_PDB2 open;

Pluggable database altered.

And we are done ! the pluggable standby is OK and contains the files :

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 QUAL_PDB1                      MOUNTED
         4 TEMP_QUAL2                     MOUNTED      
         5 QUAL_PDB2                      MOUNTED      

SQL> select name from v$datafile where con_id=5;

NAME
--------------------------------------------------------------------------------
/u02/oradata/QUALCDB/QUALCDB_SITE2/0436816C267F1805E06571B1CAD248EB/datafile/o1_
mf_system_mbd8pzd9_.dbf

/u02/oradata/QUALCDB/QUALCDB_SITE2/1E4D5E04CFDD140AE06573A0AEC465A5/datafile/o1_
mf_sysaux_mbd8pzd9_.dbf

/u02/oradata/QUALCDB/QUALCDB_SITE2/1E4D5E04CFDD140AE06573A0AEC465A5/datafile/o1_
mf_undotbs1_mbd8pzdb_.dbf

At the end, you might want to delete the temporary TEMP_QUAL2 database, and to restart your standby in mount mode if you don’t have the Active Dataguard Licence :

--On the Primary
SQL> alter pluggable database TEMP_QUAL2 close;

Pluggable database altered.

SQL> drop pluggable database TEMP_QUAL2 including datafiles;

Pluggable database dropped.

SQL>

-- On the standby

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1157627856 bytes
Fixed Size                  9134032 bytes
Variable Size             318767104 bytes
Database Buffers          822083584 bytes
Redo Buffers                7643136 bytes
Database mounted.
SQL>

Final Considerations

– You might prefer to clone your pluggable, then re-create the standby database. This is indeed more straightforward. However, you might use the procedure of this blog if you have Disaster Recovery obligations that force you to keep your standby up and ready all the time, or if your database is too big.

– Starting from 21c, A new feature “PDB Recovery Isolation” solves the problem, a “normal hot clone” is enough. BUT, this feature is only available within the Active Dataguard Licence. Please check this for more details :
Oracle Database Documentation

Post Views: 586

Hot Clone of a Remote PDB in a Dataguard Environment 19c

Introduction

The procedure

Final Considerations

Abdessamed El Ahmar

Leave a Reply:

Related blog articles

Introduction

The procedure

Final Considerations

Abdessamed El Ahmar

Leave a Reply:

Related blog articles

Automatic Transaction Rollback in Oracle Database 23ai

And what about cloning a PDB in a Data Guard environment on ExaCC?

Create new PDB in a Data Guard environment on ExaCC

Issue running Dynamic Scaling on hardened system