Multitenant : DBCA PDB Remote Clone in Oracle Database 19c

Oracle Database 12c Release 2 (12.2) introduced the ability to perform a hot clone of a remote pluggable database (PDB) using the CREATE PLUGGABLE DATABASE command. In Oracle 19c it’s possible to perform a remote clone of a pluggable database (PDB) using the Database Configuration Assistant (DBCA).

Prerequisites

Connect to the remote CDB and prepare it 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 for use with the database link. In this case, we will use a common 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 a regular remote clone, we don’t need to create a database link. We just need to supply the credentials we would use to create the link. The DBCA does the rest.

Check the remote CDB is in local undo mode and archivelog 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 in local undo mode and archivelog mode, we don’t need to turn the remote database into read-only mode.

Connect to the local CDB and prepare it for cloning.

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

sqlplus / as sysdba

Check the local CDB is in local undo mode and archivelog 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>

Remote Clone a PDB with the DBCA

In 19c the DBCA -createPluggableDatabase command has a new parameter called -createFromRemotePDB, allowing us to create a new PDB by remote 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 see the full syntax for the -createPluggableDatabase command here, although at the time of writing the documentation doesn’t match the utility usage presented by the dbca -createPluggableDatabase -help command.

Make sure the environment is set up to point to the local instance “cdb1” and create a new PDB called “pdb5new” as a clone of the remote PDB called “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 the new pluggable database using the command below.

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.
$

Appendix

The instances and pluggable databases used in these examples are created using the following commands.

# 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

The databases have Oracle Managed Files (OMF) enabled and are switched to archivelog 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

credit: https://oracle-base.com/articles/19c/multitenant-dbca-pdb-remote-clone-19c

Prerequisites

Remote Clone a PDB with the DBCA

Appendix

Related posts: