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 [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.