Show Menu
Cheatography

Oracle Multitenant Cheat Sheet by

Quick Reference Guide to Oracle Multitenant commands

Essential Termin­ology

Multit­enant
The Oracle Archit­ecture that consists of a CDB, and one or more PDBs
Container Database (CDB)
A tradit­ional database instance, but has the ability to support PDBs
Pluggable Database (PDB)
A collection of Tables­paces which supports it's own indepe­ndant Role and User security, and can be easily moved between CDBs
Root Database
The instance admini­str­ative layer that sets above PDBs. Users and Roles here must be preceded by c##
Seed Database
A PDB that remains offline to be used as a template for creating new blank PDBs

Daily Use Commands (from SQL command line)

Connect to Contaner or PDB
CONN <u­ser­><p­wd>­@/­/<­hos­t>­:<­lis­tener port>­/<­ser­vic­e> {as sysdba};
CONN <u­ser­><p­wd>­@/­/<­tns­_en­try­> {as sysdba};
Display Current Container or PDB
SHOW CON_NAME;
SELECT SYS_CO­NTE­XT(­'US­ERE­NV'­,'C­ON_­NAME')
SHOW CON_ID;
FROM DUAL;
List Containers and PDBs on Instance
SELECT PDB_NAME, Status
SELECT Name, Open_Mode
SELECT Name,PDB
FROM DBA_PDBS
FROM V$PDBS
FROM V$SERV­ICES
ORDER BY PDB_Name;
ORDER BY Name;
ORDER BY Name;
Change Container or PDB
ALTER SESSION SET contai­ner­=<­nam­e>;
ALTER SESSION SET contai­ner­=cd­b$r­oot;

Clonin­g/C­reating a PDB

First, set your source and target datafile paths...
ALTER SESSION SET PDB_FI­LE_­NAM­E_C­ONV­ERT­='­</seed path/>­',­'<­/target path/>';
Then run the create command from the target root contai­ner...
CREATE PLUGGABLE DATABASE <New PDB Name>
                          ADMIN USER <U­ser­nam­e>
                                ID­ENT­IFIED BY <P­ass­wor­d>
                          FROM <S­ource PDB[@d­bli­nk]­>
Finally, Open the newly created databa­se...
ALTER PLUGGABLE DATABASE <t­arget pdb> OPEN;
NOTE: Creating a PDB is just cloning from the seed db.
 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­[@­dbl­ink] is optional and used when creating PDB from existing PDB on another instance.
 ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ If using dblink, the link user should be an admini­str­ative user on the source PDB

Managing a Multit­enant Database

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 <n­ame­>OPEN READ WRITE­{RE­STR­ICT­ED}­{FO­RCE};
ALTER PLUGGABLE DATABASE <n­ame­> OPEN READ ONLY {REST­RIC­TED­}{F­ORC­E};
ALTER PLUGGABLE DATABASE <n­ame­> OPEN UPGRADE {REST­RIC­TED};
ALTER PLUGGABLE DATABASE <n­ame­> CLOSE {IMME­DIA­TE};
To retain state as startup state of contai­ner...
ALTER PLUGGABLE DATABASE <n­ame­> 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 Privil­eges
GRANT <p­riv­ele­ge/­rol­e> TO <u­ser­> CONTAI­NER­=<PDB name or ALL>;
If local only, grant from pdb and omit container argument.

Backup and Recovery

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 <p­db1­,pd­b2,­pdb­3>

RMAN connection to pluggable database will only work on that pdb

Rest­ore
Connect to root container. Normal restore is full instance. For pdb...
RUN {ALTER PLUGGABLE DATABASE <p­db> CLOSE;
 ­ ­ ­ ­  SET UNTIL TIME "­<t­imeset value>­";
 ­ ­ ­ ­  RESTORE PLUGGABLE DATABASE <p­db>;
 ­ ­ ­ ­  RECOVER PLUGGABLE DATABASE <p­db>;
 ­ ­ ­ ­  ALTER PLUGGABLE DATABASE <p­db>­OPEN; }

Moving PDB's (Unplu­ggi­ng/­Plu­gging in PDB)

Expo­rti­ng/­Unp­lugging 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 <p­db_­nam­e> CLOSE;
ALTER PLUGGABLE DATABASE <p­db_­nam­e> UNPLUG INTO '<­/pa­th/­><n­ame­>.x­ml';
DROP PLUGGABLE DATABASE <p­db_­nam­e> KEEP DATAFI­LES;

Impo­rti­ng/­Plu­gging 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_­result BOOLEAN;
BEGIN
     l_­result := DBMS_P­DB.C­HE­CK_­PLU­G_C­OMP­ATI­BIL­ITY(
                          PD­B_D­ESC­R_FILE => '<­/pa­th/­><n­ame­>.x­ml',
                          PD­B_N­AME­ ­ ­ ­ ­ ­ ­ ­=> '<­nam­e>');
     IF l_result THEN
          DB­MS_­OUT­PUT.PU­T_L­INE­('C­omp­atible, OK to Procee­d');
     ELSE
          DB­MS_­OUT­PUT.PU­T_L­INE­('I­nco­mpa­tible, See PDB_PL­UG_­IN_­VIO­LATIONS for detail­s');
     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 <n­ew_­pdb­_na­me> USING '<­/pa­th/­><n­ame­>.x­ml'
       FI­LE_­NAM­E_C­ONV­ERT­=('­</­source path/>­',­'<­/dest path/>­');
ALTER PLUGGABLE DATABASE <n­ew_­pdb­_na­me> OPEN;

PROXY Database Functi­onality

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 <t­arget pdb><d­bli­nk>;
NOTE: dblink may be dropped after proxy db is created
In a proxy DB the alter Database and Alter Pluggable Database commands apply to the proxy db. All other DDL applies to the target db.

Misc Other Multit­enant Management Commands

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 <n­ew_­pdb­> FROM NON$CD­B@­<db­lin­k>
       FI­LE_­NAM­E_C­ONV­ERT­=('­</­source datafile path/>­,'­</­target datafile path/>­');
@ORAC­LE_­HOM­E/r­dbm­s/a­dmi­n/n­onc­db_­to_­pdb.sql
ALTER PLUGGABLE DATABASE <t­arget pdb> OPEN;

Moving a PDB
CREATE PLUGGABLE DATABASE <new pdb> FROM <old pdb><d­bli­nk> RELOCATE;
ALTER PLUGGABLE DATABASE <new pdb> OPEN;

Removing a PDB
ALTER PLUGGABLE DATABASE <n­ame­> CLOSE;
DROP PLUGGABLE DATABASE <n­ame­> INCLUDING DATAFI­LES;

Expo­rti­ng/­Unp­lugging a pdb to a single compressed file
ALTER PLUGGABLE DATABASE <p­db_­nam­e> UNPLUG INTO '<­/pa­th/­><f­ile­nam­e>.pdb';

Impo­rti­ng/­Plu­gging in a pdb from a single compressed file
CREATE PLUGGABLE DATABASE <new pdb name> USING '<­/pa­th/­><f­ile­nam­e>.pdb';
Note that compressed pdb files for export and import are suffixed by .pdb and are a zip fle format.
               

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          MySQL Cheat Sheet
          SQL Server Cheat Sheet