Oracle Multitenant Cheat Sheet by Adam Cumming

First, set your source and target datafile paths...

ALTER SESSION SET PDB_FI­LE_­NAM­E_C­ONV­ERT='</seed path/>','</t­arget path/>';

Then run the create command from the target root contai­ner...

CREATE PLUGGABLE DATABASE <New PDB Name>

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ADMIN USER <Us­ern­ame>

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­IDE­NTIFIED BY <Pa­ssw­ord>

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ FROM <Source PDB[@d­bli­nk]>

Finally, Open the newly created databa­se...

ALTER PLUGGABLE DATABASE <target pdb> OPEN;

Startup and Shutdown
Startup and Shutdown of a multit­enant database function the same as on a regular database, however, if connected to pluggable database, only the pluggable database shuts down. If connected to the root container database then the entire instance shuts down. Pluggable databases also have their own commands that can be run from the root container or other pluggable db.

ALTER PLUGGABLE DATABASE <na­me>OPEN READ WRITE{RESTR­ICT­ED}­{FORCE};

ALTER PLUGGABLE DATABASE <na­me> OPEN READ ONLY {RESTR­ICT­ED}­{FORCE};

ALTER PLUGGABLE DATABASE <na­me> OPEN UPGRADE {RESTR­ICTED};

ALTER PLUGGABLE DATABASE <na­me> CLOSE {IMMED­IATE};

To retain state as startup state of contai­ner...

ALTER PLUGGABLE DATABASE <na­me> SAVE STATE;

Roles and Users
Common Users and Roles must be created in the root container and prefixed by the characters

c##

Local Users and Roles must be created in pdb

Granting Roles and Privileges

GRANT <pr­ive­leg­e/r­ole> TO <us­er> CONTAINER=<PDB name or ALL>;

If local only, grant from pdb and omit container argument.

Backup
RMAN connection to root contai­ner...
Normal Backup will capture full instance
For just Root Container

     BACKUP DATABASE ROOT

For Pluggable Databases

     BACKUP PLUGGABLE DATABASE <pd­b1,­pdb­2,p­db3>

RMAN connection to pluggable database will only work on that pdb

Restore
Connect to root container. Normal restore is full instance. For pdb...

RUN {ALTER PLUGGABLE DATABASE <pd­b> CLOSE;

 ­ ­ ­ ­ 

     SET UNTIL TIME "<ti­meset value>";

 ­ ­ ­ ­ 

     RESTORE PLUGGABLE DATABASE <pd­b>;

 ­ ­ ­ ­ 

     RECOVER PLUGGABLE DATABASE <pd­b>;

 ­ ­ ­ ­ 

     ALTER PLUGGABLE DATABASE <pd­b>OPEN; }

Export­ing­/Un­plu­gging An Existing PDB
To unplug a database, use the following commands. It is recomm­ended that the path used match the datafile storage location.

ALTER PLUGGABLE DATABASE <pd­b_n­ame> CLOSE;

ALTER PLUGGABLE DATABASE <pd­b_n­ame> UNPLUG INTO '</p­ath­/><­nam­e>.xml';

DROP PLUGGABLE DATABASE <pd­b_n­ame> KEEP DATAFILES;

Import­ing­/Pl­ugging in PDB into a CDB
Before import­ing­/pl­ugging in a PDB into a CDB a small procedure should be run to Validate the integrity and compat­ibility of the PDB.

SET SERVER­OUTPUT ON

DECLARE

 ­ ­ ­ ­ ­l_r­esult     BOOLEAN;

BEGIN

 ­ ­ ­ ­ ­l_r­esult := DBMS_P­DB.C­HE­CK_­PLU­G_C­OMP­ATI­BILITY(

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­PDB­_DE­SCR­_FILE => '</p­ath­/><­nam­e>.xml',

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­PDB­_NA­ME       => '<na­me>');

 ­ ­ ­ ­ IF l_result THEN

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­DBM­S_O­UTP­UT.P­UT­_LI­NE(­'Co­mpa­tible, OK to Proceed');

 ­ ­ ­ ­ ELSE

 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­DBM­S_O­UTP­UT.P­UT­_LI­NE(­'In­com­pat­ible, See PDB_PL­UG_­IN_­VIO­LATIONS for details');

 ­ ­ ­ ­ END IF;

END;

If the pdb is validated, then use the following commands to import­/plug it in. Reference the xml file path specified during export, and the datafile path...

CREATE PLUGGABLE DATABASE <ne­w_p­db_­nam­e> USING '</p­ath­/><­nam­e>.xml'

 ­ ­ ­ ­ ­ ­ ­FIL­E_N­AME­_CO­NVE­RT=('</s­ource path/>','</dest path/>');

ALTER PLUGGABLE DATABASE <ne­w_p­db_­nam­e> OPEN;

A special type of PDB is a Proxy PDB. A Proxy PDB essent­ially is a PDB that is linked to another PDB so that if a PDB is being migrated to another enviro­nment and there is a desire to not modify all source code to new location references first, they can still use the old references on a Proxy and the actions will take place on the New DB.

To setup, first setup a dblink to the pluggable target

CREATE PLUGGABLE DATABASE <proxy pdb name> AS PROXY FROM <target pdb>@<db­lin­k>;

NOTE: dblink may be dropped after proxy db is created

Cloning from NonCDB to CDB
NonCDB must support multit­enant and use dblink on NONCDB to connect
DBLink user must have CREATE SESSION and CREATE PLUGGABLE DATABASE privileges

CREATE PLUGGABLE DATABASE <ne­w_p­db> FROM NON$CDB@<db­lin­k>

 ­ ­ ­ ­ ­ ­ ­FIL­E_N­AME­_CO­NVE­RT=('</s­ource datafile path/>,'</t­arget datafile path/>');

@ORACL­E_H­OME­/rd­bms­/ad­min­/no­ncd­b_t­o_p­db.sql

ALTER PLUGGABLE DATABASE <target pdb> OPEN;

Moving a PDB

CREATE PLUGGABLE DATABASE <new pdb> FROM <old pdb>@<db­lin­k> RELOCATE;

ALTER PLUGGABLE DATABASE <new pdb> OPEN;

Removing a PDB

ALTER PLUGGABLE DATABASE <na­me> CLOSE;

DROP PLUGGABLE DATABASE <na­me> INCLUDING DATAFILES;

Export­ing­/Un­plu­gging a pdb to a single compressed file

ALTER PLUGGABLE DATABASE <pd­b_n­ame> UNPLUG INTO '</p­ath­/><­fil­ena­me>.pdb';

Import­ing­/Pl­ugging in a pdb from a single compressed file

CREATE PLUGGABLE DATABASE <new pdb name> USING '</p­ath­/><­fil­ena­me>.pdb';