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: 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 MINUTESclause during creation or modification of the clone. - Manual Refresh Mode: synchronization with the source PDB occurs only when explicitly initiated by the DBA. The clone is created using the
REFRESH MODE MANUALclause and refreshed on demand using theALTER PLUGGABLE DATABASE pdb_name REFRESHcommand.
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 applies regardless of refresh mode.
Prerequisites
- Connectivity between the source and target database servers.
Environment Used in this Guide
| Hostname | srcdb.oraeasy.com |
|---|---|
| Database Name | SRCDB |
| PDB Name | ORPDB |
| Database Version | 19c (19.28) |
| Hostname | trcdb.oraeasy.com |
|---|---|
| Database Name | TRCDB |
| Database Version | 19c (19.28) |
Configuring the Refreshable Clone PDB Step by Step
Step 1: Create a Table at the Source
Set up some test data at the source first, so there's something real to verify the clone against once it's created and refreshed later.
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
Step 2: Create a User in the CDB at the Source
The refresh mechanism connects across the network via a database link, so create a common user in the source CDB with the privilege to be pulled from remotely.
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 Your@Password container=all;
User created.
SQL> grant create session, create pluggable database to C##REFRESH_USER container=all;
Grant succeeded.
Step 3: Create Directories on the Target and Test Connectivity
Create the destination directory for the clone's datafiles, and confirm the target can reach the source over the network before attempting the actual clone.
[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)
Step 4: Create a Database Link on the Target
The CREATE PLUGGABLE DATABASE ... FROM ... @dblink syntax used in the next step requires this link to already exist and be working.
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 Your@Password using 'srcdb';
Database link created.
SQL> select sysdate from dual@PDBREFRESH_DB_LINK;
SYSDATE
---------
14-FEB-26
Step 5: Create the Refreshable Clone PDB (Auto Mode)
With everything in place, create the clone using REFRESH MODE EVERY 5 MINUTES. A refreshable clone is created in mounted state and must be opened read-only explicitly afterward.
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.
Step 6: Verify the Cloned PDB
Confirm the clone's refresh configuration and its current sync point before moving on. Closing the PDB at the end of this step is what actually lets the next scheduled refresh happen.
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.
STATUS showing REFRESHING and REFRESH_MODE showing AUTO with a 5-minute interval confirm the clone is correctly configured for automatic refresh.
Step 7: Insert Data at the Source and Verify on the Target
With the clone closed and refreshing automatically, insert new rows at the source and wait for the next scheduled refresh cycle to confirm the clone picks them up on its own.
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
The LAST_REFRESH_SCN advancing on its own, and the row count rising from 3 to 5 without any manual refresh command, confirms automatic refresh mode is working as configured.
Step 8: Create a Second Clone with Manual Refresh Mode
Repeat the clone creation, this time with REFRESH MODE MANUAL, so the two clones can be compared side by side.
[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.
REFRESH_MODE showing MANUAL with an empty REFRESH_INTERVAL confirms this clone won't sync on any schedule of its own.
Step 9: Insert Data at the Source and Manually Refresh the New PDB
Unlike Step 7, this clone won't pick up the change on its own. Confirm that, then issue the refresh command explicitly.
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
The row count only updates to 6 after the explicit alter pluggable database refresh command, confirming manual mode requires this step every time.
Step 10: Compare Auto vs Manual Refresh Side by Side
With one more insert at the source, this step highlights the practical difference directly: the auto clone reflects it immediately after its next refresh cycle, while the manual clone stays stale until refreshed on purpose.
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
This is the clearest side-by-side proof of the difference: ORDPDB1 (auto) already shows 7 rows with no manual action, while ORDPDB2 (manual) is still at 6 until explicitly refreshed and closed/reopened.
Step 11: Convert the Clone PDB to a Regular Read/Write PDB
A refreshable clone can be converted into an ordinary PDB using REFRESH MODE NONE, but this conversion cannot be rolled back. The PDB must be fully closed on all instances first, which is why the initial attempt below fails.
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
ORDPDB2 now showing READ WRITE instead of READ ONLY confirms it has been permanently converted to a regular pluggable database and is no longer a refreshable clone.
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
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Well explained
ReplyDelete