Requirements:

  1. The source must be opened in read-only mode.
  2. CDB that is to accommodate the remote PDB must have the database link defined to the remote database.
  3. It can point either to the CDB that the source PDB belongs to or directly to the source PDB.
  4. User in the source database that the db link points to must have the CREATE PLUGGABLE DATABASE privilege. It can be common or local user (if PDB)

Additionaly databases should have the same options installed, same endianness and character sets

When cloning from a non-CDB, both the the local and remote databases must beusing version 12.1.0.2 or higher.

What we want to do:

cloning_db_link

The goal is to clone SRCPDB from SRCDB container database to the DESTCDB container database as PDB called CLONEPDB. DESTCDB could be an empty CDB or with other tenants (mind license requirements. In my case I do not have Oracle Multitenant option included, so all I can to is to build Single Tenant CDB (one and only PDB onboard)

1. Prepare SRCPDB for cloning
Create REMOTE_USER in the SRCPDB as the user for database link to be connected from the DESTCDB.

[oracle@server ~]$ sqlplus system@SRCPDB
show pdbs

CON_ID CON_NAME   OPEN MODE    RESTRICTED
----------  -----------------  -------------------  --------------
3         SRCPDB       READ WRITE   NO

PDB is open so let’s create user and grant privileges

CREATE USER remote_user IDENTIFIED BY remote_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_user; 

2. Open source PDB in read only mode

alter pluggable database close immediate instances=all;
Pluggable database altered.

alter pluggable database open read only instances=all;
Pluggable database altered.

show pdbs
CON_ID CON_NAME   OPEN MODE    RESTRICTED
----------  -----------------  -------------------  --------------
3        SRCPDB       READ ONLY    NO

3. Create database link from DESTCDB TO SRCPDB

[oracle@server ~]$ sqlplus sys@DESTCDB as sysdba

show pdbs

CON_ID CON_NAME   OPEN MODE    RESTRICTED
----------  -----------------  -------------------  --------------
3         SRCPDB       READ WRITE   NO

CREATE DATABASE LINK clone_link CONNECT TO remote_user IDENTIFIED BY remote_user USING 'SRCPDB';
Database link created.

Check if database link is working properly

DESC user_tables@clone_link
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(128)
TABLESPACE_NAME VARCHAR2(30)
..........

Check if CDB is empty (PDB$SEED only)

show pdbs

CON_ID CON_NAME   OPEN MODE    RESTRICTED
----------  -----------------  -------------------  --------------
2        PDB$SEED    READ ONLY     NO

4. Clone PDB
Being logged in into the DESTCDB invoke cloning

CREATE PLUGGABLE DATABASE CLONEDPDB FROM SRCPDB@clone_link;
Pluggable database created.

How it looks in alert.log

CREATE PLUGGABLE DATABASE CLONEDB FROM SRCDB@clone_link
Wed Dec 1508:26:59 2015
****************************************************************
Pluggable Database CLONEDB with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
This instance was first to open pluggable database CLONEDB (container=3)
Database Characterset for CLONEDB is WE8MSWIN1252
Deleting old file#8 from file$
Deleting old file#9 from file$
Deleting old file#10 from file$
Deleting old file#11 from file$
Deleting old file#12 from file$
Deleting old file#13 from file$
Deleting old file#14 from file$
Deleting old file#15 from file$
Deleting old file#16 from file$
Deleting old file#17 from file$
Deleting old file#18 from file$
Deleting old file#19 from file$
Deleting old file#20 from file$
Deleting old file#21 from file$
Deleting old file#22 from file$
Adding new file#24 to file$(old file#8)
Adding new file#25 to file$(old file#9)
Adding new file#26 to file$(old file#10)
Adding new file#27 to file$(old file#11)
Adding new file#28 to file$(old file#12)
Adding new file#29 to file$(old file#13)
Adding new file#30 to file$(old file#14)
Adding new file#31 to file$(old file#15)
Adding new file#32 to file$(old file#16)
Adding new file#33 to file$(old file#17)
Adding new file#34 to file$(old file#18)
Adding new file#35 to file$(old file#19)
Adding new file#36 to file$(old file#20)
Adding new file#37 to file$(old file#21)
Adding new file#38 to file$(old file#22)
Successfully created internal service CLONEDB at open
ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database CLONEDB with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE CLONEDB FROM CLONEDB@clone_link

Let’s check what we got now:

show pdbs

CON_ID CON_NAME   OPEN MODE    RESTRICTED
----------  -----------------  -------------------  --------------
2         PDB$SEED    READ ONLY     NO
3         CLONEDPDB  MOUNTED

As you can see PDB has arrived :). Let’s open it

alter pluggable database CLONEDPDB open instances=all;
Pluggable database altered.

alter pluggable database CLONEDPDB save state instances=all;
Pluggable database altered.

Looks OK, what alertlog says:

alter pluggable database CLONEDB open instances=all
Wed Dec 15 08:28:04 2015
This instance was first to open pluggable database CLONEDB (container=3)
Pluggable database CLONEDB dictionary check beginning
Pluggable Database CLONEDB Dictionary check complete
Database Characterset for CLONEDB is WE8MSWIN1252
Opening pdb CLONEDB (3) with no Resource Manager plan active
Pluggable database CLONEDB opened read write
Completed: alter pluggable database CLONEDB open instances=all
alter pluggable database CLONEDB save state instances=all
Completed: alter pluggable database CLONEDB save state instances=all

No errors, what we got now:

show pdbs
CON_ID CON_NAME   OPEN MODE    RESTRICTED
----------  -----------------  -------------------  --------------
2         PDB$SEED    READ ONLY     NO
3         CLONEDPDB  READ WRITE   NO

5. Tidying up
Connect to SRCPDB and drop REMOTE_USER

[oracle@server ~]$ sqlplus system@SRCPDB
DROP USER REMOTE_USER
User dropped.

Connect to the DESTCDB and drop clone_link

[oracle@server ~]$ sqlplus sys@DESTCDB as sysdba
DROP DATABASE LINK clone_link;

That’s all Folks!!
References:

https://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1

About the author

 
maciej tokar
Maciej Tokar

An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com