Many times, we may need to copy the remote PDB to Local PDB for testing purpose. The case would be copying Production PDB to Development PDB box.
Some times , we may want to migrate the non-CDB to PDB to make sure Multitenant features in the Oracle database
Restriction for doing this
1)The default tablespaces for each common user in the remote PDB must exist in local CDB. If this is not true, create the missing tablespaces in the root container of the local PDB. If you don’t do this your new PDB will only be able to open in restricted mode (Bug 19174942).
2) When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher
Summary of the Clone process
1) Open the source database in Read only mode
2) create database link in Target database
3) create the clone . The clone process involves moving data across the database link.
Now lets see the methods
Remote clone a PDB
Suppose we want to create PDB(test2_tech) in CDB1 from existing db test1_tech in CDB2,
a) Close the test1_tech database and open read only
alter pluggable database test1_tech close;
Pluggable database altered.
SQL> alter pluggable database test1_tech open read only;
Pluggable database altered.
b) Create tnsnames entry in Destination Server
Switch to the Destination server and create a “tnsnames.ora” entry pointing to the remote database for use in the USING clause of the database link.
TEST1_TECH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tech.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1_TECH)
)
)
c) Create database link to the Source database
CREATE DATABASE LINK tech_clone_link
CONNECT TO <remote user> IDENTIFIED BY <password> USING ‘TEST1_TECH’;
remote user must be having create pluggable database privelege
— Test link.
DESC user_tables@tech_clone_link
b) If OMF is set i.e DB_CREATE_FILE_DEST or CREATE_FILE_DEST,
CREATE PLUGGABLE DATABASE
test2_tech FROM test1_tech@tech_clone_link;
if OMF not set
CREATE PLUGGABLE DATABASE
test2_tech FROM test1_tech@tech_clone_link
FILE_NAME_CONVERT= (‘/u01/oracle/CDB1/datafile/techdata’,
‘/u01/oracle/CDB1/datafile/tech2data’);
;
c) ReOpen the source PDB
alter pluggable database test1_tech open;
d) We can see the new PDB has been created, but it is in the MOUNTED state.
COLUMN name FORMAT A30
SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;
NAME OPEN_MODE
TEST2_TECH MOUNTED
SQL>
The PDB is opened in read-write mode to complete the process.
ALTER PLUGGABLE DATABASE TEST2_TECH OPEN;
SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;
NAME OPEN_MODE
TEST2_TECH READ WRITE
SQL>
Remote clone a non-CDB
Suppose we want to create PDB(test2_tech) in CDB1 from existing non-db test1_tech,
a) Close the test1_tech database and open read only
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
EXIT;
b) Create tnsnames entry in Destination Server
Switch to the Destination server and create a “tnsnames.ora” entry pointing to the remote database for use in the USING clause of the database link.
TEST1_TECH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tech.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1_TECH)
)
)
c) Create database link to the Source database
CREATE DATABASE LINK tech_clone_link
CONNECT TO <remote user> IDENTIFIED BY <password> USING ‘TEST1_TECH’;
remote user must be having create pluggable database privelege
— Test link.
DESC user_tables@tech_clone_link
b) If OMF is set i.e DB_CREATE_FILE_DEST or CREATE_FILE_DEST,
CREATE PLUGGABLE DATABASE
test2_tech FROM test1_tech@tech_clone_link;
if OMF not set
CREATE PLUGGABLE DATABASE
test2_tech FROM test1_tech@tech_clone_link
FILE_NAME_CONVERT= (‘/u01/oracle/CDB1/datafile/techdata’,
‘/u01/oracle/CDB1/datafile/tech2data’);
;
c) Re-Open the source PDB
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN ;
EXIT;
d) We can see the new PDB has been created, but it is in the MOUNTED state.
COLUMN name FORMAT A30
SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;
NAME OPEN_MODE
TEST2_TECH MOUNTED
SQL>
Since this PDB was created as a clone of a non-CDB, before it can be opened we need to run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean it up.
ALTER SESSION SET CONTAINER=’TEST2_TECH’;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
The PDB is opened in read-write mode to complete the process.
ALTER PLUGGABLE DATABASE TEST2_TECH OPEN;
SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;
NAME OPEN_MODE
TEST2_TECH READ WRITE
SQL>
With Oracle database Release 12.2, we no longer need to keep the source PDB is read-only mode. This can be done while in read -write mode also.
Hope you like this article on Remote PDB cloning
Related Articles
Oracle database 12c:How to create Pluggable database in 12c database
Oracle database 12c: Container Database (CDB) and Pluggable Database (PDB)
Local Undo in Oracle Database 12c R2(12.2)
Oracle Database Interactive Quick Reference Guide | 11g & 12c
Online move of active datafile in 12c
Top 10 oracle 12c new features
Bestselling Udemy Courses
Oracle DBA 11g/12c – Database Administration for Junior DBA
Oracle Database 12c SQL Certified Associate 1Z0-071
Learning Oracle 12c – A Beginners Oracle Training Video