Oracle 19c: PDB‑to‑PDB Clone & Refresh Using Database Links (SE2)



A Practical Way to Refresh DEV and TEST from PROD

Refreshing DEV or TEST databases from PROD is something every DBA does regularly.

Traditionally, this meant using RMAN duplicate or Data Pump. Both approaches work, but they take time, consume storage, and often feel heavier than the problem itself.

From Oracle 12.2 onwards, a better option exists.

Refreshable PDB allows you to clone one PDB into another using a database link and keep it updated using redo. This feature is fully supported in Oracle 19c Standard Edition 2.

In this post, I walk through PDB to PDB cloning and refresh, along with the mistakes that usually cost the most time.

Why Use Refreshable PDB

Refreshable PDB changes how you think about cloning.

Instead of rebuilding environments repeatedly, you create once and refresh when needed.

It gives you

Faster refresh compared to RMAN or Data Pump
No export or import cycle
Minimal impact on production
Ideal for DEV, TEST, QA, and reporting
Works even in Oracle 19c Standard Edition

It behaves like a simplified version of Data Guard, without continuous synchronization.

Architecture Overview

Source database

CDB
PDB_PROD

Target database

CDB
PDB_CLONE which is refreshable

The clone is created from one PDB to another using a database link. During refresh, redo from the source is applied to keep the clone updated.

Prerequisites

Source Database

Oracle 19c running in CDB architecture
Source PDB must be in ARCHIVELOG mode
LOCAL_UNDO_ENABLED must be TRUE
PDB service must be configured

Run the following to validate

ARCHIVE LOG LIST;
SHOW PARAMETER local_undo_enabled;

Target Database

Oracle 19c CDB
Enough storage for the cloned PDB

Storage can be managed using either

Oracle Managed Files
or
FILE_NAME_CONVERT mapping

The Most Common Mistake

Common User Requirement

This is where most setups fail.

For remote PDB cloning, Oracle requires a common user. A local PDB user will not work, even if it has DBA privileges.

Why this matters

PDB cloning touches container level metadata
It interacts with control files
Redo is applied at container level

Because of this, the user must exist across the entire container.

Create Common User on Source

Connect to the source database as SYS

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE USER C##PDBCLONE IDENTIFIED BY password CONTAINER = ALL;

GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE
TO C##PDBCLONE CONTAINER = ALL;

This user is now available in

CDB root
PDB seed
Source PDB

Configure TNS on Target Server

The database link must connect to the source PDB service, not the CDB.

Example entry in tnsnames.ora

MYPRD =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = prod-host)(PORT = 1521))
   (CONNECT_DATA =
     (SERVICE_NAME = pdb_prod.company.com)
   )
 )

Test connectivity

tnsping myprd

If this step fails, nothing else will work.

Create Database Link on Target

Connect to target CDB root as SYS

CREATE DATABASE LINK myprd
CONNECT TO C##PDBCLONE IDENTIFIED BY password
USING 'myprd';

Test the link

SELECT sysdate FROM dual@prod_link;

Create Refreshable PDB

Option 1 Using Oracle Managed Files

ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE = BOTH;

CREATE PLUGGABLE DATABASE PDB_CLONE
FROM PDB_PROD@myprd
REFRESH MODE MANUAL;

Option 2 Using File Name Convert

CREATE PLUGGABLE DATABASE PDB_CLONE
FROM PDB_PROD@myprd
FILE_NAME_CONVERT
('/opt/oradata/PDB_PROD/', '/opt/oradata/PDB_CLONE/')
REFRESH MODE MANUAL;

Open the Clone

ALTER PLUGGABLE DATABASE PDB_CLONE OPEN READ ONLY;

A refreshable PDB must stay in read only mode while refreshing.

Refresh the PDB

ALTER PLUGGABLE DATABASE PDB_CLONE CLOSE;

ALTER PLUGGABLE DATABASE PDB_CLONE REFRESH;

ALTER PLUGGABLE DATABASE PDB_CLONE OPEN READ ONLY;

Check refresh status

SELECT * FROM v$pdb_refresh;

Alert Log Behavior

Refresh activity appears almost immediately once it starts.

Logs are written in the CDB alert log, not the PDB alert log.

You will typically see entries related to media recovery and foreign archive logs.

If nothing appears, the refresh likely never started.

Final Cutover if Needed

If you want to make the cloned database writable

ALTER PLUGGABLE DATABASE PDB_CLONE OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDB_CLONE REFRESH MODE NONE;

Important point

Once opened in read write mode, refresh is permanently disabled.

To refresh again, the PDB must be dropped and recreated.

SE2 Limitations to Keep in Mind

Maximum of three user PDBs per CDB

Not a replacement for Data Guard

No continuous synchronization

Cannot use refresh when the PDB is read write

Lessons Learned

Database link user must be common

Database link must point to the PDB service

Local users will fail with ORA errors

Oracle Managed Files makes life easier

Most errors are not bugs, they are design rules

Closing Thought

Refreshable PDB is one of those features that quietly improves your workflow.

Once the common user and database link are set up correctly, everything else becomes predictable.

Cloning becomes lighter
Refreshing becomes faster
And your environments stay closer to reality without repeated effort.

Jay

Comments

Post a Comment

Popular posts from this blog

ASM Integrity check failed with PRCT-1225 and PRCT-1011 errors while creating database using DBCA on Exadata 3 node RAC

Image
  Error: The error message PRCT-1225: failed to verify Oracle Automatic Storage Management (Oracle ASM) user credentials using the command 'asmcmd credverify' indicates that the asmcmd credverify command failed to verify the Oracle ASM user credentials. This can happen for a number of reasons, including: The user credentials are incorrect. The user credentials file is corrupted. The user does not have the necessary permissions to run the asmcmd credverify command. There is a problem with the Oracle ASM instance. To troubleshoot this error, you can try the following: Make sure that the user credentials are correct. You can do this by trying to log in to the Oracle ASM instance using the same credentials. Check the user credentials file for errors. You can do this by using a text editor to open the file and inspect the contents. Make sure that the user has the necessary permissions to run the asmcmd credverify command. You can do this by checking the user's permissi...
Read more

Lock Tables in MariaDB

Whether or not you need to lock tables while backing up MariaDB depends on a few factors, including the type of backup you are performing and the tools you are using. If you are performing a full backup , you should lock the tables to ensure that the backup is consistent. This is because a full backup copies all of the data in the database, including any data that is currently being modified by transactions. If you do not lock the tables, it is possible that the backup could contain inconsistent data. If you are performing a differential backup or an incremental backup , you may not need to lock the tables. Differential and incremental backups only copy the data that has changed since the previous backup. This means that the data is already consistent, so there is no need to lock the tables. However, some backup tools may require you to lock the tables, even if you are performing a differential or incremental backup. This is because these tools may need to modify the database in some ...
Read more

Life is beautiful

Image
Life brings unexpected tragedies that are beyond our control. It's heartbreaking to witness people facing circumstances they never chose. We react with tears and inevitably wonder, "What if that happens to me?" As humans, we naturally focus on the positive and avoid dwelling on such thoughts—and that's healthy. But this perspective has shaped how I approach life: Am I prepared for these scenarios? Are my loved ones protected if something goes wrong? If I were to die, I couldn't be there to tell them to stay strong—they'd have to face that grief alone. But I can give them financial security through proper planning, wills, and trusts. These are tangible ways to care for them even when I'm gone. Beyond that, what else can I control? I don't waste energy worrying about an uncertain past or future. Instead, I choose to spread peace, truly listen to others, and share genuine smiles. I embrace the principle of living fully while letting others do the same. ...
Read more