Oracle Database 12c version 2 (12.2) introduces the ability to perform remote pluggable database (PDB) hot cloning using the CREATE PLUGGABLE DATABASE command. In Oracle 19c, remote cloning of pluggable databases (PDBs) can be performed using the database configuration assistant (DBCA).

1, Prerequisites

Connect to the remote CDB and prepare for cloning:

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Create a user in the remote database to use with the database link. In this case, we will use a normal user in the remote PDB.

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

Unlike conventional remote cloning, we do not need to create database links. We only need to provide the credentials used to create the link. DBCA completes the rest of the work.

Check whether the remote CDB is in local undo mode and Archive Mode:

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Because the remote CDB is local undo mode and archive mode, we do not need to change the remote database into read-only mode.

Connect to the local CDB and prepare for cloning:

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Check whether the local CDB is in local undo mode and Archive Mode:

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

2, Remote cloning of PDB using DBCA

In 19c, the DBCA createpluggabledatabase command has a newly named parameter - createfromremoteddb, which allows us to create a new PDB by remotely cloning an existing PDB.

 [-createFromRemotePDB <Create a pluggable database from Remote PDB clone operation.>]
      -remotePDBName <Name of the pluggable database to clone/relocate>
      -dbLinkUsername <Common user of a remote CDB, used by database link to connect to remote CDB.>
      -remoteDBConnString <EZCONNECT string to connect to Source database for example "host:port/servicename">
      [-remoteDBSYSDBAUserName <User name with SYSDBA privileges of remote database>]
      [-dbLinkUserPassword <Common user password of a remote CDB, used by database link to connect to remote CDB.>]
      [-remoteDBSYSDBAUserPassword <Password for remoteDBSYSDBAUserName user of remote database.>]
      [-sysDBAUserName <User name  with SYSDBA privileges>]
      [-sysDBAPassword <Password for sysDBAUserName user name>]

You can here View the complete syntax of the - createPluggableDatabase command. Although it does not match the utility usage provided by the command when writing the document, you can also use dbca -createPluggableDatabase -help to view the help.

Ensure that the environment is set to point to the local instance "cdb1" and create a new PDB named "pdb5new" as a clone of the remote PDB named "pdb5" in the "cdb3" instance.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES


dbca -silent \
  -createPluggableDatabase \
    -pdbName pdb5new \
    -sourceDB cdb1 \
  -createFromRemotePDB \
    -remotePDBName pdb5 \
    -remoteDBConnString localhost:1521/pdb5 \
    -remoteDBSYSDBAUserName sys \
    -remoteDBSYSDBAUserPassword SysPassword1 \
    -dbLinkUsername c##remote_clone_user \
    -dbLinkUserPassword remote_clone_user 
Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "pdb5new" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb5new/cdb1.log" for further details.
$

Connect to the local root container and check the status of the new PDB:

COLUMN name FORMAT A30

SELECT con_id, name, open_mode
FROM   v$pdbs
ORDER BY 1;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB5NEW                        READ WRITE

SQL>

We can clean up by deleting a new pluggable database using the following command:

dbca -silent \
     -deletePluggableDatabase \
     -sourceDB cdb1 \
     -pdbName pdb5new
Prepare for db operation
25% complete
Deleting Pluggable Database
40% complete
85% complete
92% complete
100% complete
Pluggable database "pdb5new" deleted successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb5new/cdb14.log" for further details.
$

3, Appendix

The instances and pluggable databases used in these examples were created using the following command.

# Local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

# Remote container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

 

# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
#dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1

Oracle Managed Files (OMF) is enabled for the database and switched to archive log mode:

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb5 OPEN;
ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;

EXIT;
EOF


export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

EXIT;
EOF