Oracle 19C PDB Management Cloning and Moving

1] Oracle 19C PDB Cloning

1.0 PDB Create with a different Charset than CDB

Since Oracle 19C, 12.2, we can create PDBs with a different Charset than the root Container, as long as CDB is in Unicode Charset.

Let's say, we want to create a PDB WE8MSWIN1252 in a CDB AL32UTF8

1/ First create Template PDB in a CDB WE8MSWIN1252 , then unplug it

* source

$ cat cre_PDB_template_MSWIN.sql

SET VERIFY OFF

connect / as SYSDBA

set echo on

select d.name||'|'||t.name from v$datafile d,V$TABLESPACE t where d.con_id=2 and d.ts#=t.ts# and d.con_id=t.con_id;

select d.name||'|'||t.name from v$tempfile d,V$TABLESPACE t where d.con_id=2 and d.ts#=t.ts# and d.con_id=t.con_id;

-- '/data/cdb_mswin/pdbseed','/data/cdb_mswin/pdbs'

CREATE PLUGGABLE DATABASE PDBMSWIN ADMIN USER PDBMSWIN_ADM IDENTIFIED BY "AC#HML3zebestln12#" ROLES=(CONNECT)

file_name_convert=('/data/cdb_mswin/pdbseed','/data/cdb_mswin/pdbs/PDBMSWIN')

STORAGE ( MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED);

alter pluggable database PDBMSWIN open;

alter pluggable database PDBMSWIN save state;

alter system register;

host /oracle/12CDB/OPatch/datapatch -skip_upgrade_check -db PELUN -pdbs PDBMSWIN;

alter session set container=PDBMSWIN;

CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/data/cdb_mswin/pdbs/PDBMSWIN/users01.dbf' SIZE 8M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;

ALTER DATABASE DEFAULT TABLESPACE "USERS";

$ sqlplus /nolog @cre_PDB_template_MSWIN.sql |tee cre_PDB_template_MSWIN.log

$ sqlplus / as sysdba
ALTER PLUGGABLE DATABASE PDBMSWIN CLOSE;
ALTER PLUGGABLE DATABASE PDBMSWIN UNPLUG INTO '/work/oracle/cdb_mswin/create/PDBS_REP/Unplug/PDBMSWIN.xml';

2/ Copy DBfs

$ scp -p /data/cdb_mswin/pdbs/PDBMSWIN/* target_srv:/data/pdb_mswin ; scp -p /work/oracle/cdb_mswin/create/PDBS_REP/Unplug/PDBMSWIN.xml target_srv:/work/oracle/cdb_utf8/create/PDB

3/ Target Server : create the new PDB in MSWIN in a Unicode CDB

$ sqlplus / as sysdba

CREATE PLUGGABLE DATABASE PDB_MSWIN USING '/work/oracle/cdb_utf8/create/PDB/PDBMSWIN.xml' SOURCE_FILE_NAME_CONVERT=('/data/cdb_mswin/pdbs/PDBMSWIN/','/data/pdb_mswin/')  NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> alter pluggable database PDB_MSWIN open;
Pluggable database altered.
SQL> alter pluggable database PDB_MSWIN save state;
Pluggable database altered.
SQL> alter session set container=PDB_MSWIN ;
Session altered.
SQL> SELECT * FROM   nls_database_parameters WHERE  parameter = 'NLS_CHARACTERSET';

WE8MSWIN1252

4/ Remove template PDB on Source CDB, connected as SYSDBA

DROP PLUGGABLE DATABASE PDBMSWIN INCLUDING DATAFILES;

1.1 PDB Hot Cloning within the same CDB

Oracle 19C

CDB can be either in Archivelog or NoArchivelog mode.

CDB has LOCAL UNDO Enabled :

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

LOCAL_UNDO_ENABLED FALSE

conn / as sysdba

shutdown immediate;

startup upgrade;

alter database local undo on;

shutdown immediate;

startup; 

Connect to CDB as SYSDBA :

$ sqlplus / as sysdba

 

create pluggable database PDBTARGET  from PDBSOURCE FILE_NAME_CONVERT=('/data/pdbsource/','/data/pdbtarget/') ;

Pluggable database created.

 

alter pluggable database PDBTARGET open;

alter pluggable database PDBTARGET save state;

1.2 PDB Cold Cloning from one CDB to another CDB, using database link to source PDB

Oracle 19C

CDB can be either in Archivelog or NoArchivelog mode. 

Connect to Source Environment to CDB as SYSDBA to put Source PDB in READ ONLY Mode 

$ sqlplus / as sysdba

 

ALTER PROFILE ora_stig_profile LIMIT SESSIONS_PER_USER UNLIMITED;

alter session set container=PDBSOURCE ;

grant CREATE PLUGGABLE DATABASE to SYSTEM ;

ALTER PLUGGABLE DATABASE PDBSOURCE CLOSE immediate;

ALTER PLUGGABLE DATABASE PDBSOURCE OPEN READ ONLY; 

Connect to Target Environment to CDB as SYSTEM 

$ sqlplus system/$psw

 

CREATE DATABASE LINK DBL_CDBSOURCE CONNECT TO SYSTEM IDENTIFIED BY <psw> USING 'CDBSOURCE';

CREATE PLUGGABLE DATABASE PDBTARGET FROM PDBSOURCE@DBL_CDBSOURCE   FILE_NAME_CONVERT=('/data/pdbsource/','/data/pdbtarget/');

 

DROP DATABASE LINK DBL_CDBSOURCE ; 

Connect to Target Environment to CDB as SYSDBA 

ALTER PLUGGABLE DATABASE PDBTARGET OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDBTARGET SAVE STATE; 

Connect to Source Environment to CDB as SYSDBA to put Source PDB back in READ WRITE Mode 

$ sqlplus / as sysdba

 

ALTER PLUGGABLE DATABASE PDBSOURCE CLOSE immediate ;

ALTER PLUGGABLE DATABASE PDBSOURCE OPEN READ WRITE;

ALTER PROFILE ora_stig_profile LIMIT SESSIONS_PER_USER 10;

1.2b PDB Cold Cloning from one CDB to another CDB, using database link to source PDB - with a Standby Database

Oracle 19C

CDB can be either in Archivelog or NoArchivelog mode. 

Target CDB has a DataGuard configuration with a Standby DB.

Connect to Source Environment to CDB as SYSDBA to put Source PDB in READ ONLY Mode 

$ sqlplus / as sysdba

 

ALTER PROFILE ora_stig_profile LIMIT SESSIONS_PER_USER UNLIMITED;

alter session set container=PDBSOURCE ;

grant CREATE PLUGGABLE DATABASE to SYSTEM ;

ALTER PLUGGABLE DATABASE PDBSOURCE CLOSE immediate;

ALTER PLUGGABLE DATABASE PDBSOURCE OPEN READ ONLY; 

Connect to Target Environment to PRIMARY CDB as SYSDBA 

$ sqlplus system/$psw

 

CREATE DATABASE LINK DBL_CDBSOURCE CONNECT TO SYSTEM IDENTIFIED BY <psw> USING 'CDBSOURCE';

SQL> SELECT * FROM dual@DBL_CDBSOURCE;

D

-

X

Connect to Target Environment to STANDBY CDB as SYSDBA,

should be in the ADG Active Dataguard configuration :

# Standby - Active Data Guard (ADG).

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

EXIT;

EOF

$ sqlplus / as sysdba

SELECT * FROM dual@DBL_CDBSOURCE;

D

-

X

Use this parameter for the Standby CDB to be aware of Source PDB creation :

ALTER SYSTEM SET STANDBY_PDB_SOURCE_FILE_DBLINK='DBL_CDBSOURCE';

Connect to Target Environment to PRIMARY CDB as SYSDBA 

$ sqlplus / as sysdba

CREATE PLUGGABLE DATABASE PDBTARGET FROM PDBSOURCE@DBL_CDBSOURCE   FILE_NAME_CONVERT=('/data/pdbsource/','/data/pdbtarget/');

 

ALTER PLUGGABLE DATABASE PDBTARGET OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDBTARGET SAVE STATE; 

Wait for the PDB to be fully created on Standby environment, and then :

Connect to Source Environment to CDB as SYSDBA to put Source PDB back in READ WRITE Mode 

$ sqlplus / as sysdba

 

ALTER PLUGGABLE DATABASE PDBSOURCE CLOSE immediate ;

ALTER PLUGGABLE DATABASE PDBSOURCE OPEN READ WRITE;

ALTER PROFILE ora_stig_profile LIMIT SESSIONS_PER_USER 10;

Alert.log is showing the correct dbf creation

Recovery copied files for tablespace SYSAUX

Recovery successfully copied file /data/<cdb>/<target_pdb>/SAS_sysaux01.dbf from /data/<cdb>/<source_PDB>/SAS_sysaux01.dbf

SHA_PRO(5):Recovery created file /data/<cdb>/<target_pdb>/SAS_sysaux01.dbf

SHA_PRO(5):Successfully added datafile 35 to media recovery

SHA_PRO(5):Datafile #35: '/data/<cdb>/<target_pdb>/SAS_sysaux01.dbf'

    In case of premature reset RW of source PDB, the following errors occur : 

Recovery copied files for tablespace DONNEES

Recovery successfully copied file /data/<cdb>/<target_pdb>/SAS_donnees.dbf from /data/<cdb>/<source_pdb>/SAS_donnees.dbf

PR00 (PID:50201012): MRP0: Background Media Recovery terminated with error 1274

2025-10-07T18:19:25.139771+02:00

Errors in file /work/oracle/<target_cdb_stdby>/diag/rdbms/<target_cdb_stdby>/trace/<target_cdb_stdby>_pr00_50201012.trc:

ORA-01274: cannot add data file that was originally created as '/data/<primary_cdb>/<primary_pdb>/SAS_donnees.dbf'

ORA-19729: File 39 is not the initial version of the plugged in datafile

Connect to Target Environment to STANDBY CDB as SYSDBA, to put back the previous configuration : 

sqlplus / as sysdba <<EOF

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

EXIT;

EOF

1.3 PDB HOT Cloning from one CDB to another CDB, using database link to source PDB

Oracle 19C

Source CDB should be in Archivelog mode and had LOCAL UNDO Enabled.

Same as 1.2 except the Source PDB closing and opening :

Connect to Source Environment to CDB as SYSDBA to put Source PDB in READ ONLY Mode

 

$ sqlplus / as sysdba

 

ALTER PROFILE ora_stig_profile LIMIT SESSIONS_PER_USER UNLIMITED;

alter session set container=PDBSOURCE ;

grant CREATE PLUGGABLE DATABASE to SYSTEM ;

Connect to Target Environment to CDB as SYSTEM 

$ sqlplus system/$psw

 

CREATE DATABASE LINK DBL_CDBSOURCE CONNECT TO SYSTEM IDENTIFIED BY <psw> USING 'CDBSOURCE';

CREATE PLUGGABLE DATABASE PDBTARGET FROM PDBSOURCE@DBL_CDBSOURCE   FILE_NAME_CONVERT=('/data/pdbsource/','/data/pdbtarget/');

 

DROP DATABASE LINK DBL_CDBSOURCE ; 

Connect to Target Environment to CDB as SYSDBA 

ALTER PLUGGABLE DATABASE PDBTARGET OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDBTARGET SAVE STATE; 

Connect to Source Environment to CDB as SYSDBA to put Source PDB back in READ WRITE Mode 

$ sqlplus / as sysdba

ALTER PROFILE ora_stig_profile LIMIT SESSIONS_PER_USER 10;

2] Oracle 19C PDB Moving 

Pre requisite : find out PDB's Datafiles

conn / as sysdba

set pages 5000 lines 150

col file_name for a60

col PDB for a20

select NAME PDB , file_name , ceil(bytes/1024/1024) MB_Sz from cdb_data_files a , v$containers b where a.CON_ID=b.CON_ID --and b.name = 'PDBNAME' 

order by a.con_id , file_name ;

2.1 PDB Moving using Unplug feature and XML file

Source Environment

ALTER PLUGGABLE DATABASE PDBNAME CLOSE immediate;

ALTER PLUGGABLE DATABASE PDBNAME UNPLUG INTO '/work/oracle/cdbname/create/PDBS_REP/Unplug/PDBNAME.xml';

Copy the datafiles

scp -p /data/cdbname/pdbs/PDBNAME/*.dbf <target_srv>:/data/cdbname/PDBNAME ; scp -p /work/oracle/cdbname/create/PDBS_REP/Unplug/PDBNAME.xml cdbname:/work/oracle/cdbname/create/PDBs

Target PDB

CREATE PLUGGABLE DATABASE PDBNAME USING '/work/oracle/cdbname/create/PDBs/PDBNAME.xml' SOURCE_FILE_NAME_CONVERT=('/data/cdbname/pdbs/PDBNAME/','/data/cdbname/PDBNAME/')  NOCOPY TEMPFILE REUSE;

alter pluggable database PDBNAME open;

alter pluggable database PDBNAME save state;

alter session set container=PDBNAME;

alter pluggable database PDBNAME close;

alter pluggable database PDBNAME open;

When OK , Source Environment

DROP PLUGGABLE DATABASE PDBNAME INCLUDING DATAFILES;

2.2 Failback : 

Target PDB

alter pluggable database PDBNAME close;

DROP PLUGGABLE DATABASE PDBNAME INCLUDING DATAFILES;

Source PDB

plug back the PDB in source

DROP PLUGGABLE DATABASE PDBNAME KEEP DATAFILES;

CREATE PLUGGABLE DATABASE PDBNAME USING '/work/oracle/cdbname/create/PDBS_REP/Unplug/PDBNAME.xml'    NOCOPY   TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE PDBNAME OPEN READ WRITE;

PDB Moving and RENAME 

Same as previous sections, one just had to put a new PDB NAME :

CREATE PLUGGABLE DATABASE NEWPDBNAME USING '/work/oracle/pdbname/create/PDBNAME.xml'  ...