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.

1) Check characterset in source container and PDB

• CDB:

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET';
 
PARAMETER	VALUE
	----------	----------
	NLS_CHARACTERSET AL32UTF8	

• PDB:

ALTER session SET container = P_HRDP;	
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>			
srvctl status database -d CHRMSP2 -v	
srvctl config database -d CHRMSP2		
<code PLSQL>
 
  * 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	   
<code PLSQL>
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;