Oracle 19c PDB Remote Clone and Relocation using DBCA
Contents
The Oracle 12.2 Database is happening via a database link – a new feature in Oracle 19c is the ability to use DBCA to not only perform a remote clone of a PDB (pluggable database) but also to relocate a PDB from one Container Database to another.
The graph below elaborates on the relocation process of one CDB to another.
You can read more about PDB relocating using this link:
In this article, I will elaborate on how we can relocate PDB from one CDB to another. In this testing, we are using RWHSE01 as a local CDB and we are relocating RMAN_CAT_PDB PDB from TWHSE01 CDB to RWHSE01 CDB within the same server.
For more clarity, please find local, remote, and remote PDB database details:
local - RWHSE01 (local database use for relocation)
remote - TWHSE01 (current pdb running cdb database)
remote pdb - RMAN_CAT_PDB (relocating pdb)
After the relocation database, this is the intended final environment
TWHSE01 : TWHSE_PDB
RWHSE01 : RWHSE_PDB, RMAN_CAT_PDB
First, verify the current settings before starting the activity.
-- RMAN_CAT_PDB currently resides in TWHSE01 cdb database
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TWHSE01
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TWHSE_PDB READ WRITE NO
4 RMAN_CAT_PDB READ WRITE NO
The Prerequisites
We need to create a common user with the below-mentioned privileges to access the PDB database in a remote database. Unlike a regular relocation, 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.
The following are the prerequisites for running the relocate PDB command:
- The database user in the local PDB must have the CREATE PLUGGABLE DATABASE privilege in the local CDB root container
- The remote CDB must be in the local undo mode
- The remote and local PDBs must be in the archive log mode
- The database user in the remote PDB that the database link connects to must have the CREATE PLUGGABLE DATABASE, SESSION, and SYSOPER privileges
- The local and remote PDBs must have the same options installed, or the remote PDB must have a subset of the options installed on the local 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;
Sample Output
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TWHSE01
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TWHSE_PDB READ WRITE NO
4 RMAN_CAT_PDB READ WRITE NO
SQL> CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
User created.
SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SYSOPER TO c##remote_clone_user CONTAINER=ALL;
Grant succeeded.
We need to check local and remote database undo settings and archive log mode.
Note: No need to put the database on read-only mode, because a database with archive log mode fills that requirement.
### Check the remote CDB is in local undo mode and archivelog mode.
-- remote
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TWHSE01
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';SQL> SQL> 2 3
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
SQL> SELECT log_mode
FROM v$database; 2
LOG_MODE
------------
ARCHIVELOG
-- local
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string RWHSE01
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';SQL> SQL> SQL> 2 3
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
SELECT log_mode
FROM v$database;
SQL> SELECT log_mode
FROM v$database; 2
LOG_MODE
------------
ARCHIVELOG
Let’s verify the connectivity c##remote_clone_user connection check in the remote database.
[oracle@crs01 ~]$ sqlplus c##remote_clone_user/remote_clone_user@192.168.56.200:1525/TWHSE01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 13 16:56:50 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "C##REMOTE_CLONE_USER"
SQL>
Sys connection verification
[oracle@crs01 ~]$ sqlplus sys/sys123@192.168.56.200:1525/TWHSE01 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 13 16:58:21 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TWHSE01
SQL>
The Relocation
Relocate a PDB with the DBCA In 19c the DBCA -relocate PDB command has been introduced, allowing us to relocate a PDB to a different container database (CDB).
Here is the link with all the syntaxes:
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/creating-and-configuring-an-oracle-database.html#GUID-8DD80A8A-DDE1-471F-8CBB-013D85CFE28F
-relocatePDB - Command to Relocate a pluggable database.
-remotePDBName Name of the pluggable database to clone/relocate
-pdbName Pluggable database name
-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"
-sourceDB Database unique name for RAC database or SID for Single Instance database
[-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.
[-useWalletForDBCredentials true | false Specify true to load database credentials from wallet
-dbCredentialsWalletLocation Path of the directory containing the wallet files
[-dbCredentialsWalletPassword Password to open wallet with auto login disabled
[-remoteDBSYSDBAUserPassword Password for remoteDBSYSDBAUserName user of remote database.
[-sysDBAUserName User name with SYSDBA privileges
[-sysDBAPassword Password for sysDBAUserName user name
As per this example, we are using the mentioned DBCA command to perform the relocation.
[oracle@crs01 admin]$ . oraenv
ORACLE_SID = [RWHSE01] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@crs01 admin]$
dbca -silent \
-relocatePDB \
-pdbName RMAN_CAT_PDB \
-sourceDB RWHSE01 \
-remotePDBName RMAN_CAT_PDB \
-remoteDBConnString localhost:1525/TWHSE01 \
-remoteDBSYSDBAUserName sys \
-remoteDBSYSDBAUserPassword sys123 \
-dbLinkUsername c##remote_clone_user \
-dbLinkUserPassword remote_clone_user
The verification of the log output
#################### output
[oracle@crs01 admin]$ dbca -silent \
> -relocatePDB \
> -pdbName RMAN_CAT_PDB \
> -sourceDB RWHSE01 \
> -remotePDBName RMAN_CAT_PDB \
> -remoteDBConnString localhost:1525/TWHSE01 \
> -remoteDBSYSDBAUserName sys \
> -remoteDBSYSDBAUserPassword sys123 \
> -dbLinkUsername c##remote_clone_user \
> -dbLinkUserPassword remote_clone_user
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "RMAN_CAT_PDB" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/RWHSE01/RMAN_CAT_PDB/RWHSE01.log" for further details.
[oracle@crs01 admin]$
The verification of the PDB relocation to RWHSE01
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string RWHSE01
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 RWHSE_PDB MOUNTED
4 RMAN_CAT_PDB READ WRITE NO
SQL>