How to clone PDB ( Remote Clone ) across CDB using Database Link (Doc ID 2297470.1)

In this Document

Goal

Solution

References

---

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Goal

This article provides an example by going through the the steps how to clone PDB from CDB to another CDB

Existing Database information:
Source Multitenant  Database : CDB1 with Pdb1 , Pdb2
Destination Multitenant Database : CDB2 with Pdb2

 Point to Note :
  During cloning pdb of source database should be READ ONLY. But from 12.2, Source PDB can be kept in READ WRITE mode also

Solution

Let we see how to clone pdb1 from cdb1 to cdb2 using remote link method.

Source Database: ( CDB1 )

SQL> select CON_ID, dbid, NAME, OPEN_MODE,open_time,create_scn from V$PDBS ORDER BY name;
CON_ID DBID             NAME           OPEN_MODE     OPEN_TIME                                      CREATE_SCN
---------- ---------- ------------------------------ ---------- ------------------------------------------------------------
2        3998669976    PDB$SEED     READ ONLY      08-AUG-17 06.02.49.899 PM +05:30      227
3        2537019739    PDB1            MOUNTED         08-AUG-17 06.09.57.759 PM +05:30      1965762
4        2868907633    PDB2            MOUNTED         08-AUG-17 06.11.14.177 PM +05:30      2018955

Destination Database ( CDB2)

SQL> select CON_ID, dbid, NAME, OPEN_MODE,open_time,create_scn from V$PDBS ORDER BY name;
CON_ID   DBID            NAME       OPEN_MODE OPEN_TIME                                       CREATE_SCN
---------- ---------- --------------- ---------- ------------------------------------------------------------------
2            3798668876 PDB$SEED READ ONLY   08-AUG-17 05.55.05.936 PM +05:30     227
4            2568907633 PDB2         MOUNTED     08-AUG-17 05.55.26.840 PM +05:30     3013955

Phase 1:  At Source Database: ( CDB1 )
 1.1 Create user at pdb which going to clone for this activity 
 1.2 Assign grants  'CREATE SESSION and CREATE PLUGGABLE DATABASE' to that user.

 To explain this case, i have created user, named 'remote_user_for_clone' 

Example:

At CDB1: 
SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> ALTER PLUGGABLE DATABASE pdb1 open;

SQL> CREATE USER remote_user_for_clone identified by remote_user_for_clone;

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_user_for_clone;

SQL> ALTER PLUGGABLE DATABASE pdb1 close;

SQL> ALTER PLUGGABLE DATABASE pdb1 open read only; 

Phase-2
Create net service name ( tnsalias ) in tnsnames.ora of  Destination Database ( CDB2 ) server 

tnsnames.ora
~~~~~~~~~~
getpdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = <Source_Database_ CDB1_host > )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)

Phase-3
At Destination Database  ( CDB2 )

From cdb$root
 3.1 Create database link using user which created for this activity 
 3.2 Create pluggable database using dblink. Optionally if folder structure of source and destination database are different then we can provide FILE_NAME_CONVERT parameter approprietely
 3.3 Open cloned pdb database at destination database.

SQL> CREATE DATABASE LINK getpdb_link CONNECT TO remote_user_for_clone identified by remote_user_for_clone using 'getpdb1';
SQL> create pluggable database pdb1new from pdb1@getpdb_link file_name_convert=('D:\ORADATA\dbcdb\DATA\pdb1\','D:\ORADATA\dbcdbaux\DATA\pdb1\');
SQL> alter pluggable database pdb1new open;             <======= to make read write from mount state

Note: We can test database  link prior to creation of pluggable database at Destination database
      Assuming we have below object at source ( CDB1) Database
             SQL> select count(*) from usrpdb1.allobj;
                  COUNT(*)
                 ----------
                   78779
      Provide "select on" privs on above object to user which we created at 1.1
                  SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE , [ select on usrpdb1.allobj] TO remote_user_for_clone;
      Now, after database link creation, we can test the connectivity like below at Destination Database ( CDB2)
           ASQL> select count(*) from usrpdb1.allobj@getpdb_link;
                 COUNT(*)
                ----------
                 78779
            Above output implies , database link which we created works fine.

3.4  Verify and check the objects in pdb1new which cloned from source database
        SQL> alter session set container=pdb1new ;
        SQL> select username,password from dba_users;

 SQL> select CON_ID, dbid, NAME, OPEN_MODE,open_time,create_scn from V$PDBS ORDER BY name;
CON_ID DBID             NAME          OPEN_MODE        OPEN_TIME                                         CREATE_SCN
---------- ---------- ------------------- ---------- --------------------------------------------- ----------------------
2           3798668876 PDB$SEED    READ ONLY          08-AUG-17 05.55.05.936 PM +05:30            227
4           2568907633 PDB2            MOUNTED            08-AUG-17 05.55.26.840 PM +05:30            3013955
3           2156111281 PDB1NEW     READ WRITE        08-AUG-17 07.11.36.419 PM +05:30            2951277                     <<<<< Read write
SQL> select count(*) from usrpdb1.allobj;
COUNT(*)
----------
78779