Oracle introduced multitenant architecture in Oracle 12c. This architecture enables the management of multiple Pluggable Databases (PDBs) inside a single Container Database (CDB).

Every CDB has the following containers:

Exactly one CDB root container (also called simply the root)
Exactly one system container
Zero or more application containers
Zero or more user-created PDBs
Exactly one seed PDB

CDB with No Application Containers

Types of Containers in Oracle:

Container Database (CDB):

A CDB is the main database that contains multiple pluggable databases (PDBs).

It contains three primary components:

Root container (CDB$ROOT): Contains common metadata and system resources that are shared across all the PDBs in the CDB.
Pluggable Databases (PDBs): These are user-created, self-contained databases that run within the CDB. Each PDB is like an independent database with its own data, schema, and settings, but they share the infrastructure provided by the CDB (e.g., memory, processes).
Seed PDB (PDB$SEED): This is a template PDB that Oracle provides to create new PDBs. You can't modify or delete this database.0

Pluggable Database (PDB):

A PDB is a portable, pluggable database within the CDB. It behaves like a normal Oracle database and can be moved (plugged and unplugged) between CDBs.
Each PDB contains its own set of user schemas, objects, and non-system tablespaces.

Application Container:

Application Containers were introduced in Oracle 12.2.
An Application Container is a specialized type of container that hosts Application PDBs.
It allows multiple PDBs to share the same metadata, schema definitions, or common application data, making it easier to manage multi-tenant applications.
Application containers are particularly useful in SaaS (Software as a Service) environments, where multiple tenants (PDBs) may share application logic but have isolated data.

Root Container (CDB$ROOT):

The Root Container contains the Oracle system metadata and configuration information shared across all the PDBs within the CDB.
It stores common objects, such as the data dictionary, system schemas, and administrative users.
This container is always present in any CDB and cannot be removed or modified like a regular PDB.

Application PDB:

These are PDBs within an Application Container that share application-specific metadata, but each can have its own isolated data.
This helps in managing multi-tenant application environments.

Challenges for a Non-CDB Architecture

Large enterprises may use hundreds or thousands of databases. Often these databases run on different platforms on multiple physical servers.
For example, 100 servers may have one database each, with each database using 10% of hardware resources and 10% of an administrator's time. A team of DBAs must manage the SGA, database files, accounts, security, and so on of each database separately, while system administrators must maintain 100 different computers.

Benefits of the Multitenant Architecture for Database Consolidation:

Database consolidation is the process of consolidating data from multiple databases into one database on one computer. The Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications.

Cost Reduction:

Consolidating hardware and database infrastructure into one set of background processes allows 100 PDBs on a single server to share one database instance, cutting hardware and maintenance costs.

Faster Data and Code Movement:

You can easily plug and unplug PDBs between CDBs. Cloning PDBs is possible without downtime, and different character sets can coexist without conversion if the CDB uses AL32UTF8.

Simplified Management:

The CDB administrator can manage the entire environment (like backups or patching) in a single operation, simplifying disaster recovery.

Data and Code Separation:

Each PDB works independently within a CDB, so recovery of one PDB doesn’t affect others. Flashback or point-in-time recovery is available for individual PDBs.

Secure Administration:

Common users can manage the entire CDB, while local users only manage specific PDBs, providing secure role separation.

Easier Performance Tuning:

You can optimize performance and collect metrics for one database instead of managing multiple instances.

Fewer Patches and Upgrades:

Patching and upgrading is simpler, as you only need to do it once for the entire CDB instead of for multiple databases.
Benefits of the Multitenant Architecture for Manageability:

Easier Upgrades:

Instead of upgrading an entire CDB, you can quickly unplug a PDB from one CDB and plug it into a new CDB with a higher release.

Easier Migration:

You can migrate a PDB between servers or from on-premise to the cloud to balance load.

Data Corruption Protection:

Flash back a PDB to a previous point without affecting other PDBs, similar to Flashback for non-CDBs.

Centralized Application Management:

An application container allows you to manage multiple PDBs with the same structure. Changes to the application are applied to all PDBs that use the same definition.

Resource Management:

Oracle Database Resource Manager helps manage system resources for consistent performance across multiple PDBs running on the same server.

CDB_ : All objects in all containers * (root and all PDBs).
--DBA_ : All objects in the current container (root or PDB).
--ALL_ : Objects accessible by the current user in the current container (root or PDB), including those owned by the current user.
--USER_ : Objects owned by the current user in the current container (root or PDB).

Create a New Database with CDB Command mode:

cd /u02
mkdir tada
cd tada
mkdir data redo control log arch diag
mkdir -p /u02/tada/data/pdbseed
chown -R oracle:oinstall /u02/tada/
chmod -R 775 /u02/tada/

vi /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/inittada.ora

*.compatible='19.3.0.0.0'
*.control_files='/u02/tada/control/control01.ctl'
*.db_block_size=8192
*.db_domain="192.168.31.201"
*.db_name='tada'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tadaXDB)'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
enable_pluggable_database=true

Start the Database:

startup nomount pfile="/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/inittada.ora";

Run the Create database statement:

CREATE DATABASE tada
USER SYS IDENTIFIED BY sysdba
USER SYSTEM IDENTIFIED BY sysdba
LOGFILE GROUP 1 ('/u02/tada/data/redo01.log') SIZE 100M,
GROUP 2 ('/u02/tada/data/redo02.log') SIZE 100M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u02/tada/data/system01.dbf' SIZE 700M REUSE
SYSAUX DATAFILE '/u02/tada/data/sysaux01.dbf' SIZE 550M REUSE
UNDO TABLESPACE undotbs1 DATAFILE '/u02/tada/data/undotbs01.dbf' SIZE 200M REUSE
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u02/tada/data/temp01.dbf' SIZE 20M REUSE
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/u02/tada/data/', '/u02/tada/data/pdbseed/');

Run the below SQL files for system catatalog creations:

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
set serveroutput off;
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

shut immediate

startup pfile="/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/inittada.ora";

SELECT name, cdb FROM v$database;

show con_name;
show pdbs;

Create a New PDB:

CREATE PLUGGABLE DATABASE pdbdev ADMIN USER pdbdevadmin IDENTIFIED BY DevAdmin#123
FILE_NAME_CONVERT = ('/u02/tada/data/pdbseed/', '/u02/tada/data/pdbdev/');

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS;

ALTER PLUGGABLE DATABASE PDBDEV1 open;

ALTER SESSION SET CONTAINER =PDBDEV;

ALTER SESSION SET CONTAINER = CDB$ROOT;

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS;

SELECT name, cdb FROM v$database;

CREATE PLUGGABLE DATABASE pdbdev1 ADMIN USER pdbdevadmin IDENTIFIED BY dev123
FILE_NAME_CONVERT = ('/u02/tada/data/pdbseed/', '/u02/tada/data/pdbdev1/');

COLUMN name FORMAT A20
SELECT name, open_mode FROM v$pdbs ORDER BY name;

Unplug a Pluggable Database (PDB) Manually:

Check Compatibility:

SET SERVEROUTPUT ON
DECLARE
l_result BOOLEAN;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility(
pdb_descr_file => '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml',
pdb_name => 'pdb2');
IF l_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/

The PDB is compatible, create a new PDB using it as the source. If we were creating, it with a new name
CREATE PLUGGABLE DATABASE pdb5 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb5/');

plug the database back into the same container:

CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml' NOCOPY TEMPFILE REUSE;
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
SELECT name, open_mode FROM v$pdbs ORDER BY name;

Clone a Pluggable Database (PDB) Manually:

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY;
CREATE PLUGGABLE DATABASE pdb4 FROM pdb3 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/');
ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

-- Switch the source PDB back to read/write if you made it read-only.

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

Delete a Pluggable Database (PDB) Manually:

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;

SELECT name, open_mode FROM v$pdbs ORDER BY name;

Metadata Clone:

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb11 FROM pdb10 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb10/','/u01/app/oracle/oradata/cdb1/pdb11/') NO DATA;

ALTER PLUGGABLE DATABASE pdb11 OPEN READ WRITE;

-- Switch the source PDB back to read/write

ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ WRITE;

Connection:

PDB =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
)
TESTPDB =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testpdb)
    )
)

alter system set local_listener='(address=(protocol=tcp)(host=192.168.31.201) (port=1521))' scope=both;

alter system register;

alter pluggable database testpdb close immediate;

alter pluggable database testpdb open;

exit;

lsnrctl start

lsnrctl status

lsnrctl service

Common User Management (Applicable at CDB Level):

CREATE USER C##adminuser IDENTIFIED BY AdminPass#123 CONTAINER=ALL;
Grant connect,resource,unlimited tablespace,create session to C##adminuser CONTAINER=ALL;

Create a User in the PDB:

ALTER SESSION SET CONTAINER=PDB1;
CREATE USER okok IDENTIFIED BY ok123 container=current;
Grant connect,resource,unlimited tablespace,create session to okok;
ALTER USER okok ACCOUNT UNLOCK;
conn okok/ok123@localhost:1521/pdb1

COLUMN USERNAME FORMAT A13
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A11
COLUMN OBJECT_NAME FORMAT A11
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT A6

SELECT USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME, ALL_CONTAINERS, CONTAINER_NAME, CON_ID FROM CDB_CONTAINER_DATA ORDER BY OBJECT_NAME;

Oracle Multitenant Architecture