Managing Refreshable Clone Pluggable Databases with Oracle 21c

A refreshable clone PDB is a way to refresh a single PDB instead of refreshing all PDBs in a container as in a Data Guard environment. It consists to make a clone of a source PDB and the clone PDB is updated with redo accumulated since the last redo log apply
In this blog I did some tests of this feature Refreshable pluggable databases.
I am doing my test with Oracle 21c but this feature exists since Oracle 12.2.

The configuration I use in the following

An Oracle 21c source CDB : DB21 with a source pluggable database PDB1
An Oracle 21c target CDB : TEST21 which will contain the refreshable clone of PDB1. The clone will be named PDB1FRES

Note that the refreshable clone can be created in the same container.

The first step is to create a user in the source CDB DB21 for database link purpose

SQL> create user c##clone_user identified by rootroot2016 temporary tablespace temp container=ALL;

User created.

SQL>

SQL> grant create session, create pluggable database, sysoper to c##clone_user container=ALL ;

Grant succeeded.

SQL>

In the target CDB TEST21 let’s create a database link to the source CDB. We will use the user c##clone

SQL> create database link clonesource connect to c##clone_user identified by rootroot2016 using 'DB21';

Database link created.

SQL>

SQL> select * from [email protected];

D
-
X

SQL>

Now we can create a refreshable clone PDB1FRES of PDB1 in the database TEST21.

First we will create a manual refreshable clone

SQL> create pluggable database PDB1FRES from P[email protected] refresh mode manual;

Pluggable database created.

SQL>

When created the new clone is mounted

SQL> show pdbs;

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

We can see the refresh mode

SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PDB1FRES';

PDB_NAME        REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
--------------- ------ ---------------- ----------------
PDB1FRES        MANUAL                          39266271

SQL>

Ok now let’s do some change on PDB1 and let’s see how to propagate these changes on PDB1FRES

SQL> show con_name

CON_NAME
------------------------------
PDB1


SQL> create table test(id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>

PDB1FRES must be closed (mounted) to be refreshed with changes in PDB1. As the clause REFRESH MANUAL was used during it’s creation, we have to manually do the refresh

SQL> show con_name

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

SQL> alter pluggable database PDB1FRES refresh;

Pluggable database altered.

SQL>

Let’s now open PDB1FRES in Read Only mode to verify the refresh

SQL> alter pluggable database PDB1FRES open read only;

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1FRES                       MOUNTED
SQL> alter pluggable database PDB1FRES open read only;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1FRES                       READ ONLY  NO
SQL> alter session set container=PDB1FRES;

Session altered.

SQL> select * from test;

        ID
----------
         1

SQL>

SQL> alter pluggable database PDB1FRES close immediate;

Pluggable database altered.

As seen, the manual refresh works fine.

Can we change the manual refresh mode to an automatic one?

Let’s try

SQL> alter pluggable database PDB1FRES  refresh mode every 4 minutes;

Pluggable database altered.

SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PDB1FRES';

PDB_NAME        REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
--------------- ------ ---------------- ----------------
PDB1FRES        AUTO                  4         39272240

SQL>

Now let’s again do some changes in PDB1

SQL> insert into test values (10);

1 row created.

SQL> insert into test values (20);

1 row created.

SQL> commit;

Commit complete.

SQL>

4 minutes after we can see the the last LAST_REFRESH_SCN has changed on PDB1FRES

SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PDB1FRES';

PDB_NAME        REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
--------------- ------ ---------------- ----------------
PDB1FRES        AUTO                  4         39272403

SQL>

Let’s open PDB1FRES on read only mode and let’s verify that the latest changes are replicated

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1FRES                       READ ONLY  NO

SQL> alter session set container=PDB1FRES ;

Session altered.

SQL> select * from test;

        ID
----------
         1
        10
        20

SQL>

Note that the automatic refresh will success only if the PDB clone is mounted. Note also that a manual refresh can be done even if the auto refresh is configured.

Another question may be if we can open PDB1FRES in read write mode.
Let’s try

SQL> alter pluggable database PDB1FRES open read write;

Pluggable database altered.

SQL> show pdbs;

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

What? The command open read write returns SUCCESS but the database is real openend in read only mode.

To open the database in a read write mode, we have to set the refresh mode to none

SQL> alter pluggable database PDB1FRES  refresh mode none;
alter pluggable database PDB1FRES  refresh mode none
*
ERROR at line 1:
ORA-65025: Pluggable database PDB1FRES is not closed on all instances.


SQL> alter pluggable database PDB1FRES  close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB1FRES  refresh mode none;

Pluggable database altered.

SQL> col pdb_name for a15
SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PDB1FRES';

PDB_NAME        REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
--------------- ------ ---------------- ----------------
PDB1FRES        NONE                            39272683

SQL> alter pluggable database PDB1FRES open read write;

Pluggable database altered.

SQL> show pdbs;

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

Now that PDB1FRES is opened in read write mode, let’s close it and let’s try to transform it again in refreshable clone

SQL> alter pluggable database PDB1FRES close immediate;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1FRES                       MOUNTED
SQL> alter pluggable database PDB1FRES  refresh mode manual;
alter pluggable database PDB1FRES  refresh mode manual
*
ERROR at line 1:
ORA-65261: pluggable database PDB1FRES not enabled for refresh


SQL>

It’s not possible to convert back an opened R/W PDB to a refreshable PDB. It’s clearly specified in the documentation
You cannot change an ordinary PDB into a refreshable clone PDB. After a refreshable clone PDB is converted to an ordinary PDB, you cannot change it back into a refreshable clone PDB.

Conclusion

One usage of refreshable PDB is that the clone can be used as a golden master for snapshots at PDB level. And these snapshots can be used for cloning environments for developers.