How to remotely clone a PDB or non-CDB in Oracle Database 12.1

Comment

Last updated on November 26th, 2018 at 05:13 pm

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