How to configure Refreshable Clone PDB

Introduction: A Refreshable Clone PDB is a special type of Pluggable Database introduced in Oracle Database 12.2 that allows you to periodically synchronize a cloned PDB with its source by applying redo data. In this article, we will configure the Refreshable Clone PDB.
Refresh Mode:
- Automatic Refresh Mode: This refresh mode is a configuration of a refreshable clone PDB in Oracle Database where the clone is automatically synchronized with its source PDB at fixed, predefined time intervals. The refresh occurs based on the interval specified in the REFRESH MODE EVERY n MINUTES clause during creation or modification of the clone.
- Manual Refresh Mode: This refresh mode is a configuration of a refreshable clone PDB in Oracle Database where synchronization with the source PDB occurs only when explicitly initiated by the DBA. The clone is created using the REFRESH MODE MANUAL clause and refreshed on demand using the ALTER PLUGGABLE DATABASE pdb_name REFRESH command.
Please note the refreshable clone PDB must be closed (mount state) during the refresh process, it cannot be open, not even in read only mode. This is irrespective of refresh mode.

Prerequisites:

Connectivity between Source & Target database server.

Environment:

Source Database
Hostname	srcdb.oraeasy.com
Database Name	SRCDB
PDB Name	ORPDB
Database Version	19c (19.28)

Target Database
Hostname	trcdb.oraeasy.com
Database Name	TRCDB
Database Version	19c (19.28)

Now let's proceed to configure Refreshable Clone PDB step by step:

1. First create a table at source side.


SQL> def
DEFINE _DATE           = "14-FEB-26" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "srcdb" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1928000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1928000000" (CHAR)

SQL> show pdbs

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

Session altered.

SQL> CREATE TABLE COMPANY (EMP_ID INT,NAME VARCHAR(255), COMPANY VARCHAR(255));

Table created.

SQL> INSERT INTO COMPANY VALUES (101,'Yash','WIPRO');

1 row created.

SQL> INSERT INTO COMPANY VALUES (102,'Vijay','AIRTEL');

1 row created.

SQL> INSERT INTO COMPANY VALUES (103,'Riya','TCS');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select count(*) from COMPANY;

  COUNT(*)
----------
         3

2. Now at source create a user in CDB and provide required privileges.

 
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORPDB                          READ WRITE NO
         
SQL> create user C##REFRESH_USER identified by India#123 container=all;

User created.

SQL> grant create session, create pluggable database to C##REFRESH_USER container=all;

Grant succeeded.

3. On target create directories for datafiles. Also check TNSPING for source database.

 
[oracle@trcdb ~]$ mkdir -p /u01/app/oracle/oradata/TRCDB/orpdb/

[oracle@trcdb ~]$ tnsping srcdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 14-FEB-2026 20:08:07

Copyright (c) 1997, 2025, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srcdb.oraeasy.com)(PORT = 1521))) 
 (CONNECT_DATA = (SERVICE_NAME = srcdb)))
OK (30 msec)

4. Now in target, create a database link.

  
SQL> show pdbs

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

SQL> create database link PDBREFRESH_DB_LINK connect to C##REFRESH_USER identified by India#123 using 'srcdb';

Database link created.

SQL> select sysdate from dual@PDBREFRESH_DB_LINK;

SYSDATE
---------
14-FEB-26

5. Now we will create the refreshable clone PDB. Here we will use refresh mode AUTO.


SQL> CREATE PLUGGABLE DATABASE ORDPDB1 FROM ORPDB@PDBREFRESH_DB_LINK
     REFRESH MODE EVERY 5 MINUTES
     FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/SRCDB/','/u01/app/oracle/oradata/TRCDB/'); 

Pluggable database created.

SQL> show pdbs

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

Pluggable database altered.

6. Now verify the cloned PDB.


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

    CON_ID PDB_NAME             STATUS               REFRESH_MODE         REFRESH_INTERVAL
---------- -------------------- -------------------- -------------------- ----------------
         5 ORDPDB1              REFRESHING           AUTO                                5

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

    CON_ID OPEN_MODE  RECOVERY
---------- ---------- --------
         5 READ ONLY  ENABLED

SQL> SELECT last_refresh_scn FROM dba_pdbs WHERE  pdb_name = 'ORDPDB1';

LAST_REFRESH_SCN
----------------
         2523167
		 
SQL> select count(*) from COMPANY;

  COUNT(*)
----------
         3
==>Close the PDB to get it refreshed.

SQL> alter pluggable database ORDPDB1 close;

Pluggable database altered.

7. Now do the DML at source and verify on target.


==>At Source

SQL> show pdbs

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

Session altered.

SQL> INSERT INTO COMPANY VALUES (104,'Arnab','Airtel');

1 row created.

SQL> INSERT INTO COMPANY VALUES (105,'Amar','LG');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------
14-FEB-26 08.38.11.886138 PM +05:30

==>At Target wait for 5 mins and then check.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 ORDPDB1                        MOUNTED
         
SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-FEB-26 08.44.18.221846 PM +05:30

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

PDB_NAME             REFRESH_MODE         REFRESH_INTERVAL LAST_REFRESH_SCN
-------------------- -------------------- ---------------- ----------------
ORDPDB1              AUTO                                5          2526658

SQL> alter session set container=ORDPDB1;

Session altered.

SQL> alter pluggable database ORDPDB1 open read only;

Pluggable database altered.

SQL> select count(*) from COMPANY;

  COUNT(*)
----------
         5

8. Now let's create another clone PDB with manual refresh mode.


[oracle@trcdb ~]$ mkdir -p /u01/app/oracle/oradata/TRCDB/orpdb2

SQL> CREATE PLUGGABLE DATABASE ORDPDB2 FROM ORPDB@PDBREFRESH_DB_LINK
REFRESH MODE MANUAL
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/SRCDB/orpdb/','/u01/app/oracle/oradata/TRCDB/orpdb2/');  2    3

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        READ ONLY  NO
         7 ORDPDB2                        MOUNTED
         
SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='ORDPDB2';

PDB_NAME             REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
-------------------- ------ ---------------- ----------------
ORDPDB2              MANUAL                           2532336

SQL> alter pluggable database ORDPDB2 open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        READ ONLY  NO
         7 ORDPDB2                        READ ONLY  NO
SQL> alter session set container=ORDPDB2;

Session altered.

SQL> select count(*) from COMPANY;

  COUNT(*)
----------
         5

==>Close the PDB to get it refreshed.

SQL> alter pluggable database ORDPDB2 close;

Pluggable database altered.

9. Now do the DML at source and verify on target for this new PDB.


==>Source
SQL> show pdbs

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

Session altered.

SQL> INSERT INTO COMPANY VALUES (106,'DK','TATA');

1 row created.

SQL> commit;

Commit complete.

SQL>  select count(*) from COMPANY;

  COUNT(*)
----------
         6
         
==>Target

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 ORDPDB2                        MOUNTED
         
SQL> alter pluggable database refresh;

Pluggable database altered.

SQL> alter pluggable database ORDPDB2 open read only;

Pluggable database altered.

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

PDB_NAME             REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
-------------------- ------ ---------------- ----------------
ORDPDB2              MANUAL                           2535474

SQL>  select count(*) from COMPANY;

  COUNT(*)
----------
         6

10. Now let's verify the difference between Auto & Manual refresh mode.


==>Source

SQL> show pdbs

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

Session altered.

SQL> INSERT INTO COMPANY VALUES (107,'VK','VI');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from company;

  COUNT(*)
----------
         7

==>Target
AUTO

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        MOUNTED
         7 ORDPDB2                        MOUNTED
         
SQL> alter session set container=ORDPDB1;

Session altered.

SQL> alter pluggable database ORDPDB1 open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 ORDPDB1                        READ ONLY  NO
SQL> select count(*) from company;

  COUNT(*)
----------
         7

==>Manual

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        READ ONLY  NO
         7 ORDPDB2                        MOUNTED
SQL> alter session set container=ORDPDB2;

Session altered.
		 
SQL> alter pluggable database ORDPDB2 open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 ORDPDB2                        READ ONLY  NO
         
SQL> select count(*) from company;

  COUNT(*)
----------
         6
==>As per above data is not refreshed, so let's run refresh command.
SQL> alter pluggable database ORDPDB2 close immediate;

Pluggable database altered.

SQL>  alter pluggable database refresh;

Pluggable database altered.

SQL> alter pluggable database ORDPDB2 open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 ORDPDB2                        READ ONLY  NO
SQL> select count(*) from company;

  COUNT(*)
----------
         7

11. We can also convert the clone PDB into regular R/W PDB, although its rollback is not possible.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        READ ONLY  NO
         7 ORDPDB2                        READ ONLY  NO

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


SQL> alter pluggable database ORDPDB2 close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        READ ONLY  NO
         7 ORDPDB2                        MOUNTED

SQL> alter pluggable database ORDPDB2 refresh mode none;

Pluggable database altered.

SQL> alter pluggable database ORDPDB2 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 ORDPDB1                        READ ONLY  NO
         7 ORDPDB2                        READ WRITE NO

Thank you for reading!

I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.

Abhishek Shrivastava

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

OraEasy

Search This Blog

How to configure Refreshable Clone PDB

Thank you for reading!

Follow Us

Labels

Comments

Post a Comment