Clone PDB on 19c

1. Check Free space on server

SQL> col name for a10
col percentage 999.99
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

NAME          FREE_MB   TOTAL_MB PERCENTAGE
---------- ---------- ---------- ----------
TSTDB          129716    1351560 9.59750215

2. Check the size if exisitng PDBS on server

SQL> col name for a10
  select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;

    CON_ID NAME       OPEN_MODE  PDB_SIZE_GB
---------- ---------- ---------- -----------
         2 PDB$SEED   READ ONLY   2.29394531
         3 GISDEV     READ WRITE   189.53125
         4 GISDB      READ WRITE  160.301758
3 rows selected.

Clone on same server

1. Start Source PDB in readonly mode

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
GISDEV     READ WRITE
GISDB      READ WRITE

SQL> alter session set container=GISDEV;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------
/u01/oracle/oradata/cdb1/tstdb/system01.dbf
/u01/oracle/oradata/cdb1/tstdb/sysaux01.dbf

SQL> shutdown immediate;
Pluggable Database closed.

SQL> startup open read only
Pluggable Database opened.

2. Connect to container database and clone from source PDB

sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 16 12:32:58 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show con_name

CON_NAME
---------------------
CDB$ROOT

SQL> create pluggable database GISTEST from GISDEV FILE_NAME_CONVERT=('/u01/oracle/oradata/cdb1/tstdb','/u01/oracle/oradata/cdb1/tstdb1');

Pluggable database created.

3. Start the both Source and target PDB's

SQL> sho pdbs;

    CON_ID CON_NAME               OPEN MODE  RESTRICTED
---------- ---------------------- ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GISDEV                         READ ONLY NO
         4 GISDB                          READ WRITE NO
         5 GISTEST						  READ ONLY NO

SQL> ALTER PLUGGABLE DATABASE GISDEV OPEN;
SQL> ALTER PLUGGABLE DATABASE GISTEST OPEN;

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ---------------------- ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GISDEV                         READ WRITE NO
         4 GISDB                          READ WRITE NO
         5 GISTEST						  READ WRITE NO

Clone from Remote server

1. Connect to Source PDB

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
GISPROD     READ WRITE
DBPROD     READ WRITE

SQL> alter session set container=GISPROD;

Session altered.

2. Create user and grant

create user clone_pdb identified by clone_pdb;

SQL> grant create session,create pluggable database to clone_pdb;

3. Create DB link on Target db pointing to source DB, You can use either way to create DB link

Method-1

SQL> CREATE DATABASE LINK prodDB 
    CONNECT TO clone_pdb IDENTIFIED BY clone_pdb
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=GISPROD))
            )';

Method-2

vi /u01/app/oracle/product/19.0.0.0/db_1/network/admin/tnsnames.ora
  ORCLPDB =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.example.com)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = GISPROD))
)

SQL> CREATE DATABASE LINK prodDB CONNECT TO clone_pdb IDENTIFIED BY clone_pdb using 'ORCLPDB';

4. Create pluggable DB using DB link

SQL> create pluggable database orclpdbnew from  orclpdb@dblink file_name_convert='/u01/app/oracle/oradata/orcl/orclpdb/','/u01/app/ oracle/oradata/orcl/orclpdbnew/');