🏗️ Module 21: Multitenant Architecture

Container Databases (CDB) & Pluggable Databases (PDB)

🏛️ Multitenant Architecture Overview

Architecture Components

Component	Description	Purpose
CDB (Container DB)	Root container	Shared memory & processes
PDB (Pluggable DB)	Isolated database	Application data & schemas
CDB$ROOT	Root container	System metadata
PDB$SEED	Template PDB	Fast provisioning

Check CDB Status

-- Verify database is CDB
SELECT CDB, CON_ID, NAME
FROM V$DATABASE;

-- View all containers
SELECT CON_ID, NAME, OPEN_MODE, RESTRICTED
FROM V$PDBS;

-- Current container
SHOW CON_NAME;
SHOW CON_ID;

🔨 Creating & Managing PDBs

Create PDB from Seed

-- Create PDB from seed template
CREATE PLUGGABLE DATABASE pdb1
  ADMIN USER pdb_admin IDENTIFIED BY "SecurePass123"
  FILE_NAME_CONVERT = ('/pdbseed/', '/pdb1/');

-- Create with tablespace
CREATE PLUGGABLE DATABASE pdb2
  ADMIN USER pdb_admin IDENTIFIED BY "Pass456"
  DEFAULT TABLESPACE users
  DATAFILE '/u01/oradata/pdb2/users01.dbf' SIZE 100M
  FILE_NAME_CONVERT = ('/pdbseed/', '/pdb2/');

Clone Existing PDB

-- Clone PDB (source must be READ ONLY)
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb1_clone
  FROM pdb1
  FILE_NAME_CONVERT = ('/pdb1/', '/pdb1_clone/');

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1_clone OPEN;

Unplug & Plug PDB

-- Unplug PDB (export metadata)
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
ALTER PLUGGABLE DATABASE pdb2 
  UNPLUG INTO '/tmp/pdb2.xml';

-- Drop unplugged PDB
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

-- Plug PDB into another CDB
CREATE PLUGGABLE DATABASE pdb2
  USING '/tmp/pdb2.xml'
  NOCOPY
  TEMPFILE REUSE;

⚙️ PDB Operations

Open/Close Operations

-- Open PDB
ALTER PLUGGABLE DATABASE pdb1 OPEN;

-- Open all PDBs
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Close PDB
ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

-- Open in restricted mode
ALTER PLUGGABLE DATABASE pdb1 OPEN RESTRICTED;

-- Open in read-only
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

Switch Between Containers

-- Switch to PDB
ALTER SESSION SET CONTAINER = pdb1;

-- Switch to root
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Connect directly to PDB
CONNECT username/password@localhost:1521/pdb1

Save PDB State

-- Save state (auto-open on startup)
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

-- Discard saved state
ALTER PLUGGABLE DATABASE pdb1 DISCARD STATE;

-- View saved states
SELECT CON_NAME, STATE
FROM DBA_PDB_SAVED_STATES;

📊 Resource Management

CDB Resource Plans

-- Create CDB resource plan
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
    plan    => 'cdb_plan',
    comment => 'CDB resource plan'
  );
END;
/

-- Create directives (allocate shares)
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'cdb_plan',
    pluggable_database    => 'pdb1',
    shares                => 3,
    utilization_limit     => 80,  -- Max 80% CPU
    parallel_server_limit => 50   -- Max 50% parallel
  );
END;
/

-- Activate plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'cdb_plan';

Monitor Resources

-- View directives
SELECT PLAN, PLUGGABLE_DATABASE, SHARES, UTILIZATION_LIMIT
FROM DBA_CDB_RSRC_PLAN_DIRECTIVES;

-- Monitor CPU usage
SELECT p.NAME, s.CPU_TIME, s.CPU_WAIT_TIME
FROM V$RSRC_CONS_GROUP_HISTORY s
JOIN V$PDBS p ON s.CON_ID = p.CON_ID;

🚀 Advanced Features

PDB Relocation (18c+)

-- Relocate PDB online
CREATE PLUGGABLE DATABASE pdb_relocated
  FROM pdb1@source_db_link
  RELOCATE
  FILE_NAME_CONVERT = ('/source/', '/target/');

PDB Refresh (21c+)

-- Create refreshable clone
CREATE PLUGGABLE DATABASE pdb_refresh
  FROM pdb_prod@prod_link
  REFRESH MODE MANUAL
  FILE_NAME_CONVERT = ('/prod/', '/refresh/');

-- Manually refresh
ALTER PLUGGABLE DATABASE pdb_refresh REFRESH;

Common vs Local Users

-- Create common user (C## prefix)
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE USER C##admin IDENTIFIED BY "Pass123" 
  CONTAINER = ALL;
GRANT DBA TO C##admin CONTAINER = ALL;

-- Create local user (in PDB)
ALTER SESSION SET CONTAINER = pdb1;
CREATE USER app_user IDENTIFIED BY "AppPass";
GRANT CONNECT, RESOURCE TO app_user;

🔍 Monitoring Queries

PDB Status & Information

-- List all PDBs
SELECT PDB_ID, PDB_NAME, STATUS, OPEN_MODE, RESTRICTED
FROM DBA_PDBS;

-- PDB datafiles
SELECT p.NAME, d.FILE_NAME, d.BYTES/1024/1024 AS size_mb
FROM CDB_DATA_FILES d
JOIN V$PDBS p ON d.CON_ID = p.CON_ID
ORDER BY p.NAME;

-- PDB size
SELECT p.NAME, ROUND(SUM(s.BYTES)/1024/1024/1024, 2) AS size_gb
FROM CDB_SEGMENTS s
JOIN V$PDBS p ON s.CON_ID = p.CON_ID
GROUP BY p.NAME;

-- Common vs local users
SELECT USERNAME, COMMON, CON_ID, ACCOUNT_STATUS
FROM CDB_USERS
WHERE USERNAME NOT IN ('SYS', 'SYSTEM')
ORDER BY COMMON DESC;

🎯 Best Practices

💡 Multitenant Best Practices:

• Use meaningful PDB names (app_dev, app_prod)
• Set resource plans to prevent monopolization
• Backup entire CDB or individual PDBs per SLA
• Use C## prefix for common users only
• Monitor PDB states and resource usage
• Document PDB purpose and ownership
• Test operations in dev before production
• Automate provisioning with scripts