CDB/PDB - 12.2, 18c, 19c new features

Create CDB

==========

CREATE DATABASE ... ENABLE PLUGGABLE DATABASE;

It will create CDB$ROOT and PDB$SEED

SELECT NAME, CDB, CON_ID FROM V$DATABASE;

Create Application root

=======================

connect to the CDB root

CREATE PLUGGABLE DATABASE .. AS APPLICATION CONTAINER;

Create Application seed

=======================

make sure that the current container is the application root

CREATE PLUGGABLE DATABASE AS SEED

Create a PDB from Seeds

=========================

- Alter session set container=cdb$root;

- Create pluggable database mypdbnew ADMIN user MY_DBA identified by #### storage (...) default tablespace mypdbnew_tbs datafile '..' size 100M   file_name_convert =('...','...');

- Alter pluggable database mypdbnew open;

- select con_id,username,default_tablespace,common from cdb_users;

Move PDB/Plugging In a PDB

==========================

- Alter pluggable database mypdb close immediate;

- alter pluggable database mypdb unplug into '/backup_location/';

It will create mypdb.xml file

Move files (system.dbf,sysaux.dbf,mypdb.pdf,mypdb.xml) to a new location

- check compitability 

- create pluggable database mypdb using '/../mypdb.xml' SOURCE_FILE_NAME_CONVERT=('..','..') NOCOPY;

- Alter pluggable database mypdb open; (if RAC -> on both node)

Cold clone from another PDB's

========================

 - Alter pluggable database mypdb open read only force;  [Hot clone - we don't require this step]

 - Create pluggable database mypdb_test from mypdb file_name_convert =('...','...');

 - Alter pluggable database mypdb_test open;

Cold clone from non-CDB's

========================

- DBMS_PDB.DESCRIBE function to generate XML

- Create pluggable database 

- run noncdb_to_pdb.sql

Drop pluggable database

========================

- Alter pluggable database mypdb close;

- drop pluggable database mypdb including datafiles;

Hot Clone a Remote PDB or Non-CDB (12.2) 
===========================

-- Source must use local undo mode

- CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user USING 'remotedb';
- CREATE PLUGGABLE DATABASE newpdb FROM remotedb@db_link;

- Alter pluggable database newpdb open;
- run noncdb_to_pdb.sql (If it's from non-cdb)

Relocate PDB (12.2)

=====================
- CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user USING 'remotedb';
- CREATE PLUGGABLE DATABASE remotedb FROM remotedb@db_link relocate;

- Alter pluggable database remotedb open;

PDB archive files (12.2)

===============

- alter pluggable database mypdb unplug into '/backup_location/mypdb.pdb';

mypdb.pdb is an archive and it contains .xml file and datafiles.

- Create pluggable database mypdb_test using '/backup_location/mypdb.pdb';

PDB refresh (12.2)

===============

- CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user USING 'remotedb';
- CREATE PLUGGABLE DATABASE newpdb FROM remotedb@db_link refresh mode manual;

- Alter pluggable database newpdb read only;

- Alter pluggable database newpdb close immediate;

- Alter pluggable database refresh;

- Alter pluggable database newpdb read only;

Auto-refresh -> - Alter PLUGGABLE DATABASE newpdb refresh mode every 120 minutes; (If only PDB is closed)

PROXY PDB (12.2)

===============

Benefits:

- Existing client connection unchanged

- A single entry point for could DB

- Share an application root container between multiple containers

-- CDB 1 instance 

- CREATE DATABASE LINK db_clone_link CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user USING 'CDB2'; 

- CREATE PLUGGABLE DATABASE PDB2_PROXY as PROXY FROM PDB2@db_clone_link; 

- Select pdb_name,is_proxy_pdb,status from dba_pdbs;

--No longer DB link & link user required

- alter session set container=PDB2_proxy;

-- it will show CDB2 instance

-- create table XYZ (DDL & DML)

-- CDB 3 instance 

- alter session set container=PDB2;

-- it will show the XYZ table

Snapshot carousel (PDB archives) (18C)

================================

It is a repository for periodic point-in-time copies of a PDB.

- Create pluggable database pdb_snap ADMIN user MY_DBA identified by #### snapshot mode every 24 hours;

- Alter pluggable database pdb_snap open;

-- MAX_PDB_SNAPSHOT can be changed between 0 to 8 (0 will delete existing snapshot)

- Alter pluggable database snapshot xyz_snap; (manual snap creation)

-- new PDB from the archives

- Create pluggable database pdb_from_snap  from pdb_snap using snapshot xyz_snap;

Transportable Backups (18C)

==================

support the use of backups performed on a PDB prior to it being unplugged and plugged into a new container. This facilitates agile relocation of PDBs between CDBs, perhaps for load balancing or migration between on-premises and cloud, without requiring backups to be taken immediately before, and after, each PDB relocation

Switchover Refreshable clone PDB between CDB's (migration) (18C)

========================================  

   Planned :

          alter pluggable database XXXX refresh mode every 2 minutes from remotedb@dblink switchover;

  Unplanned:

         alter pluggable database XXXX refresh; 

         alter pluggable database XXXX refresh mode none;

         alter pluggable database XXXX open read-write;

Transient no-standby PDB's (clone) (18C)

=========================    

          Hot clone to transient PDB using standby=none parameter

          Cold clone of this transient PDB with standby

        drop transient PDB

12.2 onwards AWR for Pluggable Database

==================================

alter system set awr_pdb_autoflush_enabled=true;

select * from awr_pdb_snapshot;

Avamar rman backup generates large amount of trace files

1. Avamar team needs to set 'TRACE 0' from GUI.

2. DBA needs to set the following event : (need DB restarts to take effect)

Alter system set event='trace[krb.*] disk disable, memory disable' scope=spfile sid='*';

or  temporary fix on the live instance without db restart :

Alter system set event 'trace[krb.*] disk disable, memory disable' ;