CALL NOW: 0703 180 5732

                         Home || Services || Oracle DBA Mentoring ||Remote Support || Contact Us

 
 
Understanding oracle multi tenant architecture




Virtualisation and Database virtualisaiton using multi tenant architecture

Virtualisation has its benefits
You spend less on power saving electricity
You are able to use your existing server in full capacity
Reduces total cost of operation.
Instead of having different machines for running
differnet os / and running different applicaitons all can
be run in single machine.

Now from 12c we have Multi tenant architecture .
It is like virtualisation for the database .

 

Advantage of Mutli tenanat archicture is
In case there is a upgrade for a oracle version or patch to be applied to a version
The change needs to be applied in all the databases.
Instead of this enters mutli tenant pluggable database .
YOu do the change in on root container and the change is impacted in all the existing pluggable database.

Database consolidation is the process of consolidating data from multiple databases into one database on one computer. Starting in Oracle Database 12c, the Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications.

The advantage are
Cost reduction :- Reduce cost of hardware, storage, availability, and labor. For example, 100 PDBs on a single server share one database instance and one set of database files, thereby requiring less hardware and fewer personnel.

Easier and more rapid movement of data and code
By design, you can quickly plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. The implementation technique for plugging and unplugging is similar to the transportable tablespace technique.




Easiest way to create pluggable database is using dbca thats database configuration assistant.
When you install oracle database you can create a oracle databae which can be non multi tenant which was the older method or a single container which can have multiple plugin database
or third option whci can be mulitple containsers each with multiple plugin database.

So you need one root container database and also another seed container is created at default .
You use the seed container to max new pluggable database.

Create a pluggable database use the following command  

Master Data manage

After creating pluggable database .
You need to configure your listener.ora file and tnsnames.ora file and then you able to connect to your pdb database.

Users
Users can be Common users or Local users.
Similar to common users and local users there can also be common roles and local roles.
A comoon user can acess all pdbs while a local user can only acess its local pluggable database.


Each pdb consists of each set of data_files
cdb$root Contains users meta data and little bit of uers data.
Each cdb has one root.

The cdb seed container is named pdb$seed and there is oen per cdb.
It is just template to create other pdb.
Many plggable database can be created under each cdb.
Each pluggable database will ahve one container id ,container name
Each pluggable database will have its own system data file.

The root user is the cdba and each pluggable database can have a individual dba.
The below command shows how to identify the number of containers in a database.

1* select open_mode,name,con_id from v$containers
SQL> /

OPEN_MODE NAME CON_ID
---------- ------------------------------ ----------
READ WRITE CDB$ROOT 1
READ ONLY PDB$SEED 2
READ WRITE PDBORCL 3
READ WRITE FIRST 4
READ WRITE SECOND 5

 

CDB$ROOT is the root container database .
While pdb$seed is pluggable database template.

PDBORCL is created using dbca. First and second are also pluggable database .

The background process and memory are connect to container database .

While the pluggable database arenot connected to background process and memory area.
Each pluggable database will have its own system data files,user tablespaces

The redo logfiles,undo tablespace are all shared with container database.

 

Let us create a pluggable database manually.
There are two parameters which we need to know

FILE_NAME_CONVERT
PDB_FILE_NAME_CONVERT

1* select open_mode,name,con_id from v$containers
SQL> /

OPEN_MODE NAME CON_ID
---------- ------------------------------ ----------
READ WRITE CDB$ROOT 1
READ ONLY PDB$SEED 2
READ WRITE PDBORCL 3
READ WRITE FIRST 4
READ WRITE SECOND 5

Now i shall try to create a pluggable database manually.

All container data files are present in cdb_data_Files

C:\APP\PORACLE\ORADATA\ORCL\USERS01.DBF
C:\APP\PORACLE\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\PORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\PORACLE\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\PORACLE\ORADATA\ORCL\SECOND\SYSTEM01.DBF
C:\APP\PORACLE\ORADATA\ORCL\SECOND\SYSAUX01.DBF
C:\APP\PORACLE\ORADATA\ORCL\SECOND\FIRST_USERS01.DBF
C:\APP\PORACLE\ORADATA\ORCL\FIRST\SYSTEM01.DBF
C:\APP\PORACLE\ORADATA\ORCL\FIRST\SYSAUX01.DBF
C:\APP\PORACLE\ORADATA\ORCL\FIRST\FIRST_USERS01.DBF
C:\APP\PORACLE\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF
C:\APP\PORACLE\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
C:\APP\PORACLE\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF
C:\APP\PORACLE\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF

so we have addtional dictionary views like cdb_tablespaces,cdb_users,etc

When you start a oracle database the pluggable database are in mount state.
To start all the pluggable database fire the command

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
FIRST READ WRITE
SECOND READ WRITE

SQL> ed cr

SQL> start cr

Pluggable database created.

SQL> l
1 CREATE PLUGGABLE DATABASE third ADMIN USER pdb_adm IDENTIFIED BY third
2* FILE_NAME_CONVERT=('C:\app\poracle\oradata\orcl\pdbseed','C:\app\poracle\
oradata\orcl\third')

SQL> create user box identified by box container=current;

User created.

SQL> grant resource,connect,dba to box container=current;

Grant succeeded.


SQL> ed
Wrote file afiedt.buf

1* alter user box default tablespace users container=current
SQL> /

User altered.

Theuser which you have created is visible in dba_usres

Note the user is only avaialble for that pluggable database .
If you go to other container connecting via sql
and if we query dba_users you wont find the new user created.

 

Basic commands.
This command shall open all pluggable database for that root container.

Alter pluggable databae all open;



Some pluggable and container related tables are
v$container
v$pdbs
cdb_tablespaces
cdb_data_Filies
cdb_pdb



ment

 



 
Copyright CpiSolution.com | 2007 © all rights reserved