Remote PDB Cloning Between Two OCI Base Database Service DB Systems Using a Database Link

SUMMARY:

Learn how to hot-clone an Oracle PDB between two OCI Base Database Service DB Systems using a database link, TDE wallet handling, and OMF.

SUMMARY:
Introduction
Environment and Naming
Prerequisites
Task 1 — Prepare the Source CDB
Task 2 — Prepare the Destination CDB
Task 3 — Clone the PDB
Troubleshooting
Key Principles
Conclusion
References

Introduction

Cloning a Pluggable Database (PDB) between two OCI Base Database Service DB Systems is a common request — refreshing a test environment from production, standing up a UAT copy, or migrating a workload between compartments. Each DB System hosts one CDB, so this is a remote clone. While recent versions of the OCI Console have added a remote-clone workflow (within the same availability domain, to the same or a later database version, across compartments, DB systems, or VCNs), the classic SQL-over-database-link method remains the most flexible approach — it works everywhere, is fully scriptable, and gives you complete control over the operation.

This post walks through the full database-link procedure on Oracle Database 19c, including the TDE wallet handling and the OMF gotchas that trip people up on Base Database Service.

Environment and Naming

Item	Value used in this walkthrough
Source DB System	SOURCE
Destination DB System	TARGET
Source PDB	SOURCE_PDB1
Clone (new) PDB	CLONE_PDB1
Common clone user	C##CLONE_ADMIN
Database link	clone_pdb_dblink
TNS alias to source CDB	SOURCE_CDB

Applies to: Oracle Base Database Service (ASM / OMF storage), Oracle Database 19c.

Prerequisites

Network path — the two DB Systems are in the same VCN, or in peered VCNs (set up VCN peering if separate). The destination node must reach the source node on the listener port (default 1521).
ARCHIVELOG mode and LOCAL UNDO mode — this enables hot cloning, meaning the source PDB can stay open read/write during the clone. Per the 19c documentation, if the source CDB is not in local undo mode or not in ARCHIVELOG mode, the source PDB must be opened read-only for the clone, so verify both modes on the source as well as the destination.
Archive log retention during the clone — archive logs generated from the start to the end of the cloning process must be preserved in a valid archive destination, or the clone can fail.
Same endianness and compatible database options on source and destination. Compatible character sets are also required unless the destination CDB uses AL32UTF8.
TDE wallet/keystore open on both sides — Base Database Service databases are TDE-encrypted by default, so this step is not optional. The keystore password is required even if the source uses an auto-login keystore; you can check for encrypted data via DBA_ENCRYPTED_COLUMNS.

Task 1 — Prepare the Source CDB

Run everything in this section connected to CDB$ROOT on the source.

-- Confirm you are in the root
SHOW CON_NAME

-- Create the common clone user (CONTAINER=ALL is required)
CREATE USER c##clone_admin IDENTIFIED BY <PASSWORD> CONTAINER=ALL;

-- Grant privileges across all containers
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##clone_admin CONTAINER=ALL;

Verify the user and privileges

-- Confirm the common user exists in every container
col username for a20
set lines 300
SELECT username, common, con_id
FROM   cdb_users
WHERE  username LIKE 'C##CLONE_ADMIN%'
ORDER BY con_id;

-- Confirm CREATE PLUGGABLE DATABASE is effective INSIDE the source PDB
ALTER SESSION SET CONTAINER = SOURCE_PDB1;

SELECT grantee, privilege, common
FROM   dba_sys_privs
WHERE  grantee LIKE 'C##CLONE_ADMIN%'
AND    privilege = 'CREATE PLUGGABLE DATABASE';

Key point: the CREATE PLUGGABLE DATABASE privilege must be effective in the source PDB itself, not just in the root. (Per the 19c documentation, granting SYSOPER to the link user is an accepted alternative, but the least-privilege approach shown here is preferred.) If the query above returns no rows, grant it while connected to the PDB:

-- While connected to SOURCE_PDB1
GRANT CREATE PLUGGABLE DATABASE TO c##clone_admin;

Task 2 — Prepare the Destination CDB

Run on the destination.

1. Confirm ARCHIVELOG and LOCAL UNDO

SELECT log_mode FROM v$database;        -- expect ARCHIVELOG
SHOW PARAMETER local_undo_enabled       -- expect TRUE

2. Add the source CDB to tnsnames.ora

Edit $ORACLE_HOME/network/admin/tnsnames.ora on the destination node. Take a backup first:

export MY_DATE=$(date +"%m.%d.%Y_%H:%M")
cp tnsnames.ora "tnsnames.ora_$MY_DATE"

Then add an entry pointing at the source CDB (you can copy the connect descriptor from the source node’s own tnsnames.ora):

SOURCE_CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <source_host>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <source_service_name>)
    )
  )

SOURCE_CDB here is a TNS net service name (an alias) — the same kind of name you’d reference as @alias. It is the exact string used in the database link’s USING clause. It is not a hostname or a literal database name.

3. Create the database link (destination → source)

CREATE PUBLIC DATABASE LINK clone_pdb_dblink
  CONNECT TO c##clone_admin IDENTIFIED BY <PASSWORD>
  USING 'SOURCE_CDB';

The password must match the actual password of C##CLONE_ADMIN on the source.

4. Test the link

ALTER SESSION SET global_names = FALSE;   -- avoids ORA-02085 (link name != db name)
SELECT * FROM dual@clone_pdb_dblink;      -- expect 'X'

Optionally, verify the link user’s privileges through the link:

SELECT privilege
FROM   dba_sys_privs@clone_pdb_dblink
WHERE  grantee LIKE 'C##CLONE_ADMIN%';

Task 3 — Clone the PDB

Run in CDB$ROOT on the destination.

Important: storage and file placement on Base Database Service

Base Database Service uses ASM with Oracle Managed Files (OMF). Do not use FILE_NAME_CONVERT — OMF handles file placement automatically. Specifying FILE_NAME_CONVERT (or PDB_FILE_NAME_CONVERT) against OMF-named source files raises ORA-01276.

Confirm OMF is configured:

SHOW PARAMETER db_create_file_dest      -- expect +DATA (or your disk group)

If not set:

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

Verify the TDE wallet password (before the clone)

Test the destination’s TDE wallet password up front rather than discovering a mismatch mid-clone. Find your unique name and list the wallet contents:

SHOW PARAMETER db_unique_name

# Replace <DB_UNIQUE_NAME> with the value from the query above.
# Enter the TDE wallet password when prompted.
# If the ORACLE.SECURITY entries are listed, the password is correct.
mkstore -wrl /opt/oracle/dcs/commonstore/wallets/<DB_UNIQUE_NAME>/tde/ -list

Run the clone

ALTER SESSION SET global_names = FALSE;

-- 1. Prompt for the wallet password securely (not echoed, not in history)
ACCEPT TARGET_TDE_WALLET_PASSWORD CHAR PROMPT 'Enter TDE Wallet Password: ' HIDE

-- 2. Run the clone
CREATE PLUGGABLE DATABASE CLONE_PDB1 FROM SOURCE_PDB1@clone_pdb_dblink
  KEYSTORE IDENTIFIED BY "&TARGET_TDE_WALLET_PASSWORD";

-- 3. Clear the variable from session memory (good security practice)
UNDEFINE TARGET_TDE_WALLET_PASSWORD

KEYSTORE IDENTIFIED BY takes the destination’s TDE wallet password. It is required whenever the source has encrypted data or a keystore configured — which is effectively always on Base Database Service.

Open the clone

ALTER PLUGGABLE DATABASE CLONE_PDB1 OPEN READ WRITE;

After creation, the PDB sits in mounted mode (status NEW). Opening it read/write completes integration into the destination CDB (status becomes NORMAL).

Verify

SELECT name, open_mode FROM v$pdbs WHERE name = 'CLONE_PDB1';

ALTER SESSION SET CONTAINER = CLONE_PDB1;
-- Spot-check cloned objects, e.g.:
-- SELECT COUNT(*) FROM <schema>.<table>;

Finally — back up the new PDB once validated. A freshly cloned PDB has no backup history on the destination.

Troubleshooting

Error	Cause	Resolution
ORA-02085	Link name differs from the DB it connects to	ALTER SESSION SET global_names = FALSE; before using the link
ORA-17628 / ORA-01031 (insufficient privileges)	Link user lacks CREATE PLUGGABLE DATABASE in the source PDB	Grant with CONTAINER=ALL in the source root, or locally inside SOURCE_PDB1. Verify via dba_sys_privs while connected to the source PDB
No rows for C##CLONE_ADMIN in cdb_users	User not created on source, wrong container, or wrong DB	Confirm SHOW CON_NAME = CDB$ROOT on the source; recreate the user with CONTAINER=ALL; ensure the link password matches
ORA-01276 (“Cannot add file … has an Oracle Managed Files file name”)	FILE_NAME_CONVERT used against OMF-managed source files	Remove FILE_NAME_CONVERT; let OMF place files (ensure db_create_file_dest is set)

Key Principles

Never use FILE_NAME_CONVERT for remote clones on Base Database Service — ASM/OMF manages placement via db_create_file_dest.
The USING ‘SOURCE_CDB’ clause is a TNS net service name alias, not a hostname or literal database name.
Common users must be created in CDB$ROOT with CONTAINER=ALL to be usable across the container hierarchy.
The database link password must match the source common user’s actual password.
CREATE PLUGGABLE DATABASE must be effective in the source PDB itself, not just the root.

Conclusion

Remote PDB cloning over a database link is one of those procedures that looks intimidating on paper but reduces to a handful of well-ordered steps once the prerequisites are locked down: a properly scoped common user, a working link, open keystores on both sides, and — on Base Database Service specifically — the discipline to let OMF place the files rather than reaching for FILE_NAME_CONVERT. Get those right, and the clone itself is a single SQL statement. The Console alternative is convenient for one-off copies, but use the database-link method when clones need to be repeatable, scheduled, or embedded in automation. As always: verify the privilege inside the source PDB, test the link before you trust it, and back up the new PDB the moment it’s validated.