Copy pluggable database from one container to another container

 Source Container Name: CHRMSP1

PDB Name: P_HRDP

Target Container: CHRMSP2

Target PDB Name: P_HRDP

Memory: 8G

Version: 19.17.0.0.0

NLS Character Set: AL32UTF8

Assumption: Oracle database software versions and patch level should be same on both the containers.

** Preparation Phase ** 

1) Check characterset in source container and PDB 

CDB: 

SQL> select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

PARAMETER VALUE

---------- ----------

NLS_CHARACTERSET AL32UTF8

PDB:

SQL> alter session set container = P_HRDP;

SQL> select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

PARAMETER VALUE

---------- ----------

NLS_CHARACTERSET AL32UTF8

2) Create Target Container CHRMSP2, (In my case, I was working on ODA system to I used odacli 

           command to create container)

a) Execute odacli command to create container

odacli create-database --dbname CHRMSP2 --databaseUniqueName CHRMSP2 --pdbname P_ODA1901 --cdb --dbtype RAC --dbclass OLTP --dbhomeid cf868ba0-6fff-4b9d-84e2-53cc1a95ca1b --bshape odb1 --dbstorage ASM --dbdomainname db.test.com --associated-networks Public-network --characterset AL32UTF8 --nationalscharacterset AL16UTF16 --dblanguage american --dbterritory america

Note: When creating a CDB, the create database command requires a PDB Name. 

   This is a temporary database that the DBA has to drop it, once container is created 

                   successfully.

b) Check create container job status

odacli describe-job -i <jobid>

c) srvctl status database -d CHRMSP2 -v

d) srvctl config database -d CHRMSP2

e) Drop P_ODA1901 PDB

SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                       READ ONLY  NO

3 P_ODA1901                      READ WRITE NO

SQL> ALTER PLUGGABLE DATABASE P_ODA1901 CLOSE IMMEDIATE INSTANCES=ALL;

     SQL> DROP PLUGGABLE DATABASE P_ODA1901 INCLUDING DATAFILES;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                       READ ONLY  NO

3) Compare initialization parameters(init.ora) of CHRMSP1 and CHRMSP2 containers and do the 

           necessary parameter changes.

4) Add newly created container CHRMSP2 into OEM and schedule RMAN backup jobs

5) Create a TNS entry for source PDB (to be used during migration)

P_HRDP_MIG =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = indodap1-scan)(PORT = 1521))

             (CONNECT_DATA =

         (SERVER = DEDICATED)

    (SERVICE_NAME = P_HRDP.db.test.com)

)

     )

** Migration Phase **

Referred MOS Doc ID: 2297470.1 - How to clone PDB (Remote Clone) across CDB using Database Link 

1) Take Full Export backup of P_HRDP PDB

           Directory Location:  /acfs01/backup/CHRMSP1/P_HRDP

nohup expdp <username>/<password>@P_HRDP_MIG DIRECTORY=DATADUMP DUMPFILE=EXPDP_P_HRDP_FULL_%U.dmp LOGFILE=EXPDP_P_HRDP_FULL.log FULL=Y PARALLEL=4 EXCLUDE=STATISTICS,MARKER &

2) Stop P_HRDP_SVC service from source container CHRMSP1

   srvctl stop service -d CHRMSP1 -s P_HRDP_SVC

        srvctl disable service -d CHRMSP1 -s P_HRDP_SVC

3) clone P_HRDP database from source container(CHRMSP1) to target container(CHRMSP2)   

** Source Container (CHRMSP1) **

ALTER SESSION SET CONTAINER=P_HRDP;

     CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;

             GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

     Grant select any dictionary to remote_clone_user;

      ALTER PLUGGABLE DATABASE P_HRDP CLOSE IMMEDIATE instances=all;

      ALTER PLUGGABLE DATABASE P_HRDP OPEN READ ONLY instances=all; <<< Restart database in read only mode

SHOW PDBS

** Target Container (CHRMSP2) **   

** Create database link pointing to source PDB   

     CREATE DATABASE LINK clone_link CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'P_HRDP_MIG'; <<< Use TNS Entry which was created in above step

           ** Test Database link (check users list and confirm it is connecting to correct database)

DESC user_tables@clone_link

Select username from dba_users@clone_link where(oracle_maintained='N');

** Create a shell script to clone PDB

cd /home/oracle

vi clone_P_HRDP_pdb.sh

#!/bin/sh  

sqlplus /nolog << ! >> /dev/null

connect / as sysdba

spool clone_P_HRDP_pdb_02Jan2024.log

select username from dba_users@clone_link where(oracle_maintained='N' and username not like 'C##%');

CREATE PLUGGABLE DATABASE P_HRDP FROM P_HRDP@clone_link;

alter pluggable database P_HRDP open instances=all services=all;

spool off;

!

** Before executing a script make sure environment is set for target container CHRMSP2

$ export ORACLE_SID=CHRMSP2

$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_2

$ nohup clone_P_HRDP_pdb.sh &

** Logs

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 22 17:58:42 2023

Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> show pdbs          

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                       READ ONLY  NO

3 P_HRDP                         READ WRITE NO

SQL> select name from v$services;

NAME

----------------------------------------------------------------

CHRMSP2XDB

CHRMSP2.db.test.com

SYS$BACKGROUND

P_HRDP_SVC

SYS$USERS

P_HRDP

Note: Here, we can see that cloning the pdb will also copy all the services associate with source PDB

4.Create a high availability service for the new PDB in CHRMSP2

  srvctl add service -db CHRMSP2 -pdb P_HRDP -service P_HRDP_SVC -failovertype SELECT -failovermethod BASIC -preferred "CHRMSP21,CHRMSP22"

  srvctl start service -db CHRMSP2 -service P_HRDP_SVC

  srvctl status service -db CHRMSP2

5. Rename global name of Source PDB(P_HRDP) to P_HRDP_OLD in source container CHRMSP1 (This step 

           is required to avoid conflict of pdb with same name on same server)

   Login to CHRMSP1 container    

   alter pluggable database P_HRDP close immediate instances=all;

   alter pluggable database P_HRDP open restricted;

   select inst_id, name, OPEN_MODE from gv$pdbs where name = 'P_HRDP'; 

   alter session set container=P_HRDP;    

   alter database rename global_name to P_HRDP_OLD;        

   alter pluggable database P_HRDP_OLD open restricted instances=all;

   alter pluggable database P_HRDP_OLD save state;

   alter pluggable database P_HRDP_OLD close immediate;