19c – Clone PDB to another CDB using DBLink

Identify the Source PDB, which needs to be Cloned:

SQL> show pdbs

CON_ID CON_NAME	OPEN MODE	RESTRICTED
2 PDB$SEED	READ ONLY	NO
3 TESTTST01	READ WRITE	NO

You may receive the following ERROR during PDB Creation/Clone: ORA-17628: Oracle error 1031 returned by remote Oracle server ORA-01031: insufficient privileges

What ORA-01031 alerted that the remote account has no CREATE PLUGGABLE DATABASE system privileges to clone the PDB.

In Source Environment, Grant the required privilege to the user, which would be used to clone the PDB.

From Target – Check the Connectivity using telnet:

$ telnet test-training-db.rawishAviation.com 1521

Escape character is ‘^]’.

Add Connection String to tnsnames.ora

[oracle@testinterface ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /applic/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TESTTST01 =   (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-training-db.rawishAviation.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testtst01.rawishAviation.com) ) )

Connect to Target SQL*Plus and Create Database Link:

create database link link_to_testtst01_pdb connect to system identified by testadmin2022 using 'testtst01';

Verify Connection is Working from Target to Source using DBLink:

select * from tab@link_to_testtst01_pdb;

Clone the target PDB from the remote database via database link:

SQL> create pluggable database TESTTST01 from TESTTST01@link_to_testtst01_pdb;

SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

PDB_ID	PDB_NAME	STATUS
2	PDB$SEED	NORMAL
3	TESTUAT01	NORMAL

SQL>  create pluggable database TESTTST01 from TESTTST01@link_to_testtst01_pdb;

Pluggable database created.

SQL> show pdbs

CON_ID	CON_NAME	OPEN MODE	RESTRICTED
2	PDB$SEED	READ ONLY	NO
3	TESTUAT01	READ WRITE	NO
4	TESTTST01	MOUNTED

Pluggable Databases is now Cloned and Currently in Mounted State. Set the OPEN MODE to READ WRITE and save the STATE:

SQL> show pdbs

CON_ID	CON_NAME	OPEN MODE	RESTRICTED
2	PDB$SEED	READ ONLY	NO
3	TESTUAT01	READ WRITE	NO
4	TESTTST01	READ WRITE	NO

Now newly Cloned PDB is ready to be used.

---

You might be Interested in Reading of:

• Data Warehouse, Data Lake & Data Vault
• NoSQL Column-Oriented Database – Columnar Database
• Data Modeling – Metrics and Checklist
• Big Data – Distributed File-Based Databases
• Data Steward – Stewardship Activities
• Data Governance Committees / Bodies
• Data Modeling – Metrics and Checklist
• How to Measure the Value of Data