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

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.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'  ...