Wednesday, May 25, 2022

Multitenant: DBCA PDB Remote Clone or Relocate in Oracle 19c

In Oracle 19c, you can clone or relocate a pluggable database (PDB) from one CDB (multitenant container database) to another using the DBCA (Database Configuration Assistant).

The following pre-requisites must met

• The local and remote PDBs must be in the archive log mode and local undo mode.
• It must have the CREATE PLUGGABLE DATABASE privilege in the local CDB root container.
• The database user in the remote PDB that the database link connects must have the CREATE       PLUGGABLE DATABASE, SYSOPER and SESSION privileges.
• The same database options installed on local and remote PDB’s

You can use below query to verify database has local undo mode and archive log mode enabled

SQL> select property_name, property_value from database_properties
where property_name = 'local_undo_enabled';
SQL> select log_mode from v$database;

First, create a user that is used in the database link automatically to connect during the cloning operation. When using DBCA we need to supply the credentials only and no need to create database link

SQL> CREATE USER c##remote_user1 IDENTIFIED BY password CONTAINER=ALL;
SQL> GRANT create session, create pluggable database TO c##remote_user1 CONTAINER=ALL;


You can launch DBCA in silent mode to clone PDB1 from CDB1 as PDB11 in CDB11

$dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName PDB1 -remoteDBConnString CDB1 -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword xxxxxxxx -sysDBAUserName sys -sysDBAPassword xxxxxxxx -dbLinkUsername c##remote_user1 -dbLinkUserPassword xxxxxxxx -sourceDB CDB11 -pdbName PDB11

Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "PDB11" plugged successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

You can connect to CDB11 and check the status.

$sqlplus sys@CDB11 as sysdba

SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 READ WRITE NO

If you have cloned PDB11 as part of some testing and need to cleanup then use below commands by connecting to CDB11 as SYS

SQL> alter pluggable database PDB11 close;
SQL> drop pluggable database PDB11 including datafiles;



You can also use DBCA to delete the pluggable database that was cloned.

$dbca -silent -deletePluggableDatabase -sourceDB CDB11 -pdbName PDB11

Prepare for db operation
25% complete
Deleting Pluggable Database
40% complete
82% complete
94% complete
100% complete
Pluggable database "PDB11" deleted successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

You can also use DBCA delete the instance using below command in silent mode

$dbca -silent -deleteDatabase -sourceDB CDB11 -sysDBAUserName sys -sysDBAPassword xxxxxxxxx

The relocatePDB command relocates a PDB from a remote CDB to a local CDB.

$dbca -relocatePDB
-pdbName name_of_the_local_pdb_to_create
-sourceDB database_name_of_the_local_pdb
-remotePDBName name_of_the_remote_pdb_to_relocate
-remoteDBConnString db_connection_string_of_the_remote_pdb
-sysDBAUserName name_of_the_sysdba_user
-sysDBAPassword password_of_the_sysdba_user
-dbLinkUsername name_of_the_dblink_user_of_the_remote_pdb
-dbLinkUserPassword password_of_the_dblink_user_of_the_remote_pdb

Example:
$ dbca -silent -relocatePDB -pdbName PDB11 -sourceDB CDB11  -remotePDBName PDB1 -remoteDBConnString TESTDB -remoteDBSYSDBAUserName sys  -remoteDBSYSDBAUserPassword xxxxxxx  -dbLinkUsername c##remote_user1 -dbLinkUserPassword xxxxxxx

Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "PDB11" plugged successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.


Refer Oracle documentation for more details and syntax

When running the DBCA in silent mode the outcome will be reported as exit codes. These exit codes helps to identify the command is successful or failed.

Exit Code Description
0  : Command execution successful
6  : Command execution successful but with warnings
-1 : Command execution failed
-2 : Invalid input from user
-4 :  Command canceled by user

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE