Repository navigation
51. Multi Tenant Database
From oracle 12c onwards this multi-tenant database concept becomes mandatory. Up until 11g all database were standalone databases. Before if we needed to take the database from one server to another server in a different location we needed to take the database's backup and the restore it in another server. In industry the databases which were 1 TB Size it would have taken a lot of time. So oracle introduced this concept of multi-tenant database.
Remember in DBCA Graphical interface there was an option called container database which we always unselect while creating th database, we need to select it to make it a multi-tenant database. There is a container database and a pluggable database. It it as it sounds, the pluggable database can easily be unplugged and taken to a new server and plug into another container database.
Now the architecture is the same in parts of the System Global Area and background processes.
Look at the diagram below.
'
In the image there are 3 pluggable databases, but there can be more.
The outer whole structure which is container database (CDB) is like a super database which holds several child databases or pluggable databases (PDB). Every PDB uses the shared global area and the background processes of the CDB. Shared globa area is nothing but a huge system global area used by every PDBs. Imagine a single building used by multiple tenants. Every tenant has a dedicated space available to them in the building. It is just like this.
Now as in RAM SGA, different databases's user's queries exist in shared global area's sql area, different databases's data block's exist in the shared global area's database buffer cache and ifferent databases's user's DDL and DML queries's exist in the shared global area's redo buffer cache. So how will the server process recognize which one belongs to which database?
The answer is by a level, every query or datablock has a level attached to them which tells the database server process this one belongs to this PDB's.
All the pluggable databases shares and uses the same background processes of the container database.
Now lets talk about the physical part. Every PDBs has their own system01.dbf, sysaux01.dbf, user01.dbf, undo01.dbf(this is optional). Control file, spfile and redo log don't exist within individual PDBs.
The CDB has its own system01.dbf, sysaux01.dbf, undo01.dbf, tem01.dbf (there is no users01.dbf in CDB).
All the PDBs use the centralized control file and redo log file of the CDB. Similarly to the shared global area in the redo log files, in the DDL and DML queries of the files, they will have their database id (called container ID) to identify which pluggable database it belongs to.
We can decide as a DBA whether in my Pluggable Database, they will have individual undo data file to use, or they will not have individual undo data file and will use the centralize undo datafile of Container Database.
Under one container there can be maximum 254 pluggable databases.
In an industry there can be 100s of databases, so manually managing all of them is difficult like we want to take a backup of 200 databases. So there is a single super database which can manage all others together. Also 200 standalone database will cost much RAM (for separate 200 SGAs) and computing power (for separate background processes). This architecture make those costs much lower.
This is an old concept. We can register many databases inside this catalog databases. Then we can use this to monitor all those registered databases together to see whose backup is done and whose backup is not done. But this old concept is not used anymore and is shifted to container database.
Different between the system data file of the container database vs the system data file of the pluggable database:
To understand that let's first review what system data file of a standalone database contains:
The sql syntax, base functions like (sum, max), built in packages and data types basically a library of basic sql.
The user's in the database, their metadata like (name, id, password, creation timestamp etc), roles, system and objects privileges of each user and roles.
Objects details (like table name and definition, their column details, view definitions etc)
In the Container database's system datafile the oracle engine definitions part stays. Along with it the common user (c##admin) details stays.
In the pluggable database the database specific user and security data and Application metadata stays. Along with a metadata link which point to the container database's system datafile.
For example hr database's system datafile will only contain its specific user, roles metadata and object metadata etc. It will not have the basic sql library.
Another PDB sales database's system datafile will also only contain its specific user, roles metadata and object metadata etc and it will not have the basic sql library.
But they both will contain a metadata link to the container database's system datafile and it will use the basic sql library from there.
So if the hr PDB' user runs select * from employees; it will first do the syntax check via the link, then do the sementic checks on its specific system data file and then only find those specific data block with it ( as we said before each data block has a level attached to it container id, it will use that to get that specific datablock for that specific PDB) and give the answer to that user.
As we know pfile is necessity for a database to even start and getting in nomount state. PDBs don't have individual spfile. So how do they start up.
Answer is they use the pfile of the Container database to start up.
They also store the parameter values which is normal stored in pfile for a standalone database inside a special dictionary table inside container database's system datafile called PDB_SPFILES$. When a PDB starts up the system takes the specific parameter values of that specific PDB (it checks against that container id) from the table and got the database in nomount state.
Initially when we create this there will be one pluggable database by default that will be there called PDB$SEED. This is basically the template which will help creation of other pluggable databases. The name of the container database is CDB$ROOT.
Step 2: This which we used to uncheck the container database, we have to enable this container database option.
'
See according to this configuration our PDBs will have separate undo data files. And this will also create an extra Pluggable Database named pdb along with the PDB$SEED template pluggable database.
Create with no listener now. We will create listener for this later as we will need this.
This will create the database.
See. It seems PDBs doesn't show up in oratab file.
Now lets open the container database.
Now lets open the Pluggable database. It seems I need to set up oracle home for this manually. And its showing error in reading its spfile.
