28. Multi Tenant Database Architecture - sayanbhattacharya1234/Oracle_DBA GitHub Wiki
Multi Tenant Database Architecture :
Basically at first from ORACLE 12c only still we are able to maintain the architecture of 11G ,That architecture we have rate eralier of 11G architecture , but after ORACLE vertion 12c ,11G architecture was changed or modified little-bit but now from ORACLE 21 onwords , ORACLE made it mandatory , to maintain multi-tanent architecture.
Oracle 12c onwards this multi-tenant database concept becomes mandatory. Up until 11G all databases were standalone databases. Before if we need to take the database, from one server to another server in a different locationc, we need to take the database's backups and the restore it in another server. In industry the databases which were 1 TB ,2 TB ,10 TB Sizes , so it would have taken a lot of time. So oracle introduced with this concept of multi-tenant database Architecture .
Remember in DBCA Graphical interface, there was an option called CONTAINER database which we always unselect while creating the database, NOW we need to select it & make it a multi-tenant database. There is a container database and a pluggable databases. 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.
Multi Tanent Database Architecture :
Under one Container database who can have max 254 plugable databases.
Diagram
Here there 2 pluggable databases, there can be more PDBs
How multiple database can use same memory space in RAM:
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.
Physical Files for the multi-tenant architecture:
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.
Architecture Basics:
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.
Additional detail:
In the Container Redolog files of all PDBs , MAX 254 databases we can accomodate in one container database.
What is the purpose of the Container database:
The Purpose of the Container database > under one container we can have max 254 pluggable databases , so The container basically holds system files ( system file ,sysaux file ,undo file ,temp file , datafile) , control files , redo log files.
Control file for which database ?
: In this container database , for all the pluggable databases, all and each database's , 1 control file will be present is the container database , and redolog file is 1 only .
The name of the container :
CDB$ROOT , under CDB i may have multiple PDBs, in PDB we have system files , users file , undo and temp files also there but undo and temp are optional.
As a DBA , decide in my db in my database only container database should have the undo datafile , temp file, indivudial PDBs should not have these files . and vise varsa .only PDBs have seprate undo,temp files
Max number of PDB in one CDB:
Under one container there can be maximum 254 pluggable databases.
Creating a container database and Pluggable Database:
Initially When we create a container database that time , that will be one pluggable database bydefault created . that is PDB$SEED : that is basically the template that will help to create for all other Pluggable databases .
Reason for this architecture:
In an industry there can be multiple database like 100 or 200 or 300 of databases, so manually managing all of them is difficult like we want to take a backup of 200 databases , so we have to take backup individual databases takes hugh time . 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.
Catalog Database:
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 , what is the sizes . 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:
Oracle Engine definitions: The sql syntax, base functions like (sum, max), built in packages and data types basically a library of basic sql.
User and security data: 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.
Application metadata: Objects details (like table name and definition, their column details, view definitions etc)
Steps for Creating a CDB and PDB:
Step 1: Do the steps we already know.
>
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.
Step 3: Then follow the same steps which we used to follow to create database previously.
>
Create with no listener now. We will create listener for this later as we will need this.