One of the most talked new features of Oracle 12c is multitenant databases,Multitenant architecture where the multiple databases share same SGA and all databases are interlinked to a master database called Root databases(CDB$root)and the whole is called as CDB.They have also come to be known as pluggable databases. The c in 12c stands for cloud.
Root Container:
Identified as CDB$ROOT.
Stores Oracle-supplied metadata and common users.
There is only one root container for a CDB.
Control files, REDO Logs, and UNDO files are owned by ROOT Container as these are Instance specific.
CDB is a container database which is similar like standalone database. Called CDB$ROOT.
Seed Database (Seed PDB):
Identified as PDB$SEED.
By default in READ-ONLY mode and cannot be altered or modified.
It is a default PDB.
It is a template which can be used to create new PDBs, Oracle copies mandatory datafiles (common datafiles) for creation of PDB from this SEED Database.
You cannot add objects to or modify objects in the seed.
A CDB has exactly one seed.
User Defined PDB:
It is the custom PDBs which may be created for various applications, teams or functionalities.
A CDB can contain 253 PDBs including the SEED Container.
Every PDB has its own set of SYSTEM/SYSAUX/TEMP tablespaces. If we don’t assign any TEMP table space to PDB it will use the TEMP of ROOT Container.
When a Container database instance is started all PDB’s comes automatically in mounted state. We will need to manually open the PDB’s into Read-Write mode.
The all above is called a container, Therefore, the root is a container, the seed is a container, and each PDB is a container. Each container has a unique container ID and name within a CDB.
In the earlier version of Databases we have 3 level of dictionary view relationship (USER_,ALL_,DBA_) but with the introduction of the CDB one more layer of view has increased (CDB_).
Representation of databases will be managed by CON_ID column in all the dynamic views or base tables, and the root database is always 0.
Oracle introduced a concept called Common Users and Local Users, CDB_DBA or PDB_DBA:
Common Users:
A common user is a user exist in the root database and all the PDB databases.
Contains super privileges to manage whole database (CDB).
A common user can perform administrative tasks specific to the root or PDBs, such as plugging and unplugging PDBs.
Example of Common Users: SYS, SYSTEM.
Common users can perform the following operations across multiple PDBs, Granting privs, Alter database command that effects pdb, Alter pluggable database etc.
common user must have a special prefix of "C##" to differentiate itself from local users.
Local Users:
Local users exists only in PDB .
With the appropriate privileges, a local user can access objects in a common user's schema. For example, a local user can access a table within the schema of a common user if the common user has granted the local user privileges to access it.
Important views
CDB_USERS, DBA_USERS,ALL_USERS, DBA_PDBS
Let’s go through some Basic Commands.
Connect to CDB
sqlplus “/as sysdba”
select name, cdb from v$database;
select con_id, name, open_mode from v$pdbs;
Connect to PDB:
alter session set container=pdb3;
Either using TNS Entries SQLPLUS sys/****@pdb3 as sysdba
set linesi 200
set pagesi 200
select NAME,CON_ID,CON_UID,OPEN_MODE,OPEN_TIME from V$CONTAINERS;
To start the individual pluggable database.
ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ WRITE;
To shutdown the individual pluggable database.
ALTER PLUGGABLE DATABASE <pdb_name> CLOSE IMMEDIATE;
select file_name from cdb_data_files where con_id=4;
select tablespace_name from cdb_tablespaces where con_id=4;
select name from v$active_services where con_id = 4;
The pluggable database has four different open modes:
READ WRITE
A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs.
READ ONLY
A PDB in open read-only mode allows queries but does not allow user changes.
MIGRATE
When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB.
MOUNTED
When a PDB is in mounted mode, it behaves like a non-CDB in mounted mode. It does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Cold backups of the PDB are possible.
