Oracle Multitenant Database Architecture

The Oracle Multitenant Database Architecture is designed to host multiple “PLUGGABLE DATABASE” PDB databases within a single CDB “CONTAINER DATABASE” container instance.
The architecture of the CDB instance maintains more PDBs within it, using the same set of processes and the same SGA (System Global Area).
Instead for the system components (control file, redo log, spfile, …) they are relevant only to the CDB.

The creation of a user is local to the DB to which you are connected, but it is permissible to create general users in the CDB for all PDBs. In a PDB the creation of temporary tablespaces is allowed, but if this is not done, the temporary table space of the CDB is used. Finally, the data files are collected in a directory with the same name as the name of the PDB and can use the OMF (Oracle Managed Files) mode. Creating pluggable databases, moving pluggable databases between containers, and cloning pluggable databases are done with SQL commands, and these are done in just seconds. The startup of a CDB occurs like that of a normal instance with the startup command from SQL * Plus. The PDBs must instead be opened with an OPEN command, unlike the CDB, by default they are in the MOUNTED state and therefore not accessible. We can also create our own PLUGGABLE database during database installation. Below we will create a PDB with a db already installed. Create and open a PDB Database: First we will create our PLUGGABLE DATABASE named PDB1 with the following command:

CREATE PLUGGABLE DATABASE pdb1;

Now we can access our PDB with the following command:

ALTER SESSION SET CONTAINER=pdb1;

The newly created PDBs are in the MOUNT state and are opened like all normal Oracle instances with the following command:

ALTER PLUGGABLE DATABASE PDB1 OPEN;

1. Using the container session VAR:

AlTER SESSION SET CONTAINER=pdb1;

ATER SESSION SET CONTAINER=cdb$root;

2. Using a service on listener:

conn sys/xxx@pdb1 as sysdba;

3. Using the environment variable TWO TASK:

export TWO_TASK=PDB1;

sqlplus / as sysdba;

How to get where we are connected to:

It seems trivial but the first few times it is not easy to understand where you are connected, the following command will show the database we are in:

show con_name;

How to check the name of CONTAINER and PLUGGABLE database:

select name from V$CONTAINERS;

Checking the PLUGGABLE database name:

select pdb_name, status from DBA_PDBS order by 1;

How to activate PDB in automatic way:

PAY ATTENTION: please do not run following commands in PDB, then in CONTAINER only:

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

To delete a PDB

The removal command shared in the example will delete totally the PDB and related datafile.

DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;

Roles and privileges assigmement

GRANT CREATE TABLESPACE TO PIPPO;
GRANT CONNECT TO PIPPO;
GRANT CREATE TABLE TO PIPPO;
GRANT CREATE ANY DIRECTORY TO PIPPO;