Search notes:

Oracle Multitenant Architecture

The multitenant architecture enables a database to be a CDB.
The multitenant architecture allows for a separation of duties between a PDB administrator and a CDB administrator.
Starting with 21c, Oracle supports multitenant architecture only.

Container Database CDB

A container database (CDB) consists of containers:
A CDB also has
The value of CDB in v$database shows if the database is a container database (YES or NO).

Container

A container is either
Each container has a separate data dictionary (and can therefore have different database objects). The data dictionary internally links to objects in the CDB root.
The CDB root and all containers share an instance (or, when using RAC, multiple concurrent instances).

CDB root (Root container)

The CDB root (root container) stores the metadata which is required to manage the PDBs.
The name of the root container is CDB$ROOT.
Oracle recommends to use AL32UTF8 for the root character set.

Application container

An application container is an optional component of the CDB which stores stores data and metadata for «application back-ends».
An application container consists of
  • Exactly one application root and
  • the PDBs plugged into this root.

System container

The system container is the CDB's logical container and contains
  • The root container (CDB root)
  • The CDB's PDBs.

Current container

A session operates on exactly one container, referred to as current container, at a given point in time.
The current container can be determined with select sys_context('userenv', 'con_name') from dual;
The container can be switched with alter session set container ….
cdb$root can be the current container only for common users.

Portable Database (PDB)

A portable database (PDB) is a collection of
A PDB is a container.
With regard to Oracle Net, a PDB feels like an ordinary database (This behavior is referred to as «PDB/non-CDB compatibility guarantee»).
A PDB belongs to zero or one application container.
If it belongs to an application container, it is a application PDB.
A PDB is created with create pluggable database …;
A PDB is associated with a GUID whose main purpose is to make sure that directory names for files with PDB related data are unique.
When a PDB is created, the the PDB belongs to the current container.

Seed

A seed serves as a template from which PDBs are created (create pluggable database).
When a PDB is created, some or all of the contents of the seed is copied to the new PDB.
A seed is either

Seed PDB

The seed PDB is a system-supplied, read-only template from which new PDBs are created.
There is exactly on seed PDB in a CDB, its name is PDB$SEED.

TODO

dbms_pdb
The procedure dbms_pdb_num ($ORACLE_HOME/rdbms/admin/catfusrg.sql) checks if Multitenant is used and counts user-created PDBs.
pdb_plug_in_violations
pdb_history$, pdb_sync$
Is the current database a CDB: 
select cdb from v$database;

select * from v$database where cdb = 'YES';

select
   sys_context('USERENV', 'CDB_NAME'),
   sys_context('USERENV', 'CON_NAME'),
   sys_context('USERENV', 'APPLICATION_NAME')
from
   dual;

select * from containers(table_name);
select * from containers(table_name) where con_id <> sys_context('userenv', 'con_id');
approot_to_pdb.sql 
set lines 290
column name  format a12
column name_ format a12
select
   con.con_id,
   con_dbid_to_id    (con.dbid   ) con_id_,
   con_guid_to_id    (con.guid   ) con_id__,
   con_uid_to_id     (con.con_uid) con_id___,
   --
   con.con_uid,
   con.dbid,
   con_id_to_dbid    (con.con_id)  dbid_,
   --
   con.name,
   con_id_to_con_name(con.con_id) name_,
   --
   con.guid
from
   v$containers con;


select * from cdb_properties;

alter user … account unlock container=current';

select * from sys.container$;

select
                                        sys.dbms_registry.get_con_id(),
   sys.dbms_registry.get_container_name(sys.dbms_registry.get_con_id())
from
   dual;

select
   property_name, property_value
from database_properties
where
   property_name in (
     'CON_VSN',
     'MAX_PDB_SNAPSHOTS',
     'PDB_UPGRADE_SYNC',
     'UPGRADE_PDB_ON_OPEN'
);

alter pluggable database prod priority 1;
alter pluggable database uat  priority 2;
alter pluggable database dev  priority 3;

alter session set container = …;
alter session set container = … service = …;
The container hint.
select statements using the containers() clause are executed in each queried PDB.
_exclude_seed_cdb_view
Is the con_id always 1 for cdb$root and/or 2 for pdb$seed?
Functions that are useful in a container database.
The dbms_service package can be used to create services and associate them with PDBs.

Creating PDBs

There are multiple ways to create a PDB:
  • From scratch, by copying the files of the PDB seed or application seed to a different directory.
  • Cloning an existing PDB, which copies the files from the existing PDB to a different directory
  • Relocating an existing PDB, which moves the files from the existing PDB to a different directory
  • Plugging in an unplugged PDB, which requires an XML that describes the PDB (create pluggable database pluggedInPDB using '/path/to/config.xml'
  • Creating a proxy PDB
  • Adopting a non-CDB as a PDB, which uses Replay Upgrade

CREATE PLUGGABLE DATABASE

The create pluggable database statement is used to
  • create PDBs
  • application containers
  • application seeds
  • application PDBs
If executed in a CDB root, the statement creates a PDB, if executed in an application root, it creates an application PDB.
An application root is created in a CDB root container with create pluggable database … as application container.
An application seed is created in an application container with create pluggable database … as seed. 
SQL> show pdbs
--
--     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
-- ---------- ------------------------------ ---------- ----------
--          2 PDB$SEED                       READ ONLY  NO

SQL> create pluggable database tq84_pdb
--   as application container
     admin user rene_pdb identified by theSecret
--   file_name_convert = ('/pdbseed/', '/mydatabase/')
;

SQL> show pdbs
--
--     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
-- ---------- ------------------------------ ---------- ----------
--          2 PDB$SEED                       READ ONLY  NO
--          3 TQ84_PDB                       MOUNTED

SQL> alter pluggable database tq84_pdb open;
SQL> show pdbs
--
--     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
-- ---------- ------------------------------ ---------- ----------
--          2 PDB$SEED                       READ ONLY  NO
--          3 TQ84_PDB                       READ WRITE NO

SQL> select name from v$services;
SQL> select name from cdb_services;
--
-- …
-- tq84_pdb
-- …

SQL> select * from cdb_pdbs;
SQL> select * from dba_pdbs;

SQL> alter session set container = tq84_pdb;
SQL> alter session set container = pdb$seed;

lsrnctl service
EZCONNECT? 
sqlplus sys@//localhost:1521/tq84_pdb as sysdba
SQL> show con_name
When a PDB is created, common users are synchronized to the new PDB. If the new PDB does not have tablespaces with the same name as the common users's default tablespaces, the PDB can only be opened in restricted mode, see also
  • ORA-01035: Login denied. Database is in RESTRICTED mode
  • pdb_plug_in_violations, message *"Sync PDB failed with ORA-959 during create user … identified by * default tablespace …*.

CONTAINER clause in SQL statements

Some SQL statements can be executed in the CDB root with the container = all clause in which case the affect all containers in the CDB:
  • create and alter user
  • create role
  • grant, revoke
  • alter system

Objects related to application container

Tables related to the application container seem to include:
  • sys.fed$apps
  • sys.fed$patches
  • sys.fed$versions
  • sys.fed$statement$errors
  • sys.fed$app$status
  • sys.fed$binds
  • sys.fed$editions
  • sys.fed$dependency
Sequence
  • app$con$seq

CDB Fleet

A CDB fleet is a collection of CDBs that can be managed as one logical CDB (the lead CDB?)

Lead CDB

Determine current CDB Lead Database: 
select
   property_value
from
   database_properties
where
   property_name='LEAD_CDB';

alter session  set container = cdb$root;
alter database set lead_cdb  = true;

Data links, metadata links, extended data links

DL = data link (formerly OBL); MDL = metadata link; EDL = extended data link
A data link allows to reference data stored in the root from a PDB.
For example, AWR data is stored in the root, but is accessible from dba_hist_… views in each container.
The plan operation EXTENDED DATA LINK FULL
Columns
  • SHARING in DBA_OBJECTS
  • EXTENDED_DATA_LINK and EXTENDED_DATA_LINK_MAP in DBA_TABLES and DBA_VIEWS
  • PDB_LOCAL_ONLY in DBA_VIEWS
dbms_pdb_alter_sharing.set_metadata_linked, dbms_pdb_alter_sharing.set_data_linked, dbms_pdb_alter_sharing.set_ext_data_linked
See also database link.

See also

The init parameter enable_pluggable_database
v$pdbs, v$containers.
The init parameters max_pdbs and enable_pluggable_database

Index