19c PDB remote clone and relocation using dbca

Database world consolidation and managing resources plays a key role. To ease the administration of databases  and efficiently use the hardware resources dbas use concept of database consolidation . To make this process easier oracle introduced cool feature of relocating pluggable database from one cdb to another. 

12.2 database this is happening via db link , A new feature in Oracle 19c is the ability to use DBCA to not only perform a remote clone of a PDB, but also to relocate a PDB from one Container Database to another.

This below mention graph elaborates the relocation process of one cdb to another.

Can read more about pdb relocationg using below mention link

http://ora-srv.wlv.ac.uk/oracle19c_doc/multi/relocating-a-pdb.html#GUID-75519361-3DA2-4558-A7E5-64BC16FAFC7D

This article I will elaborate how we can relocate pdb from one cdb to another. In this testing we are using RWHSE01 as local cdb and we are relocating RMAN_CAT_PDB pdb from TWHSE01 cdb to RWHSE01 cdb within same server.

Just for more clarity please and 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 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

Prerequisites

We need create common user with below mention privileges to access the pdb database in remote database. Unlike a regular relocate, 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 archivelog 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 database on read only mode, because database with archive log mode
full fill 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 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>

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 below mention 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

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

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>