Multitenent

1. What is multi tenant architecture?

Multi tenant architecture is the concept of the Container Database (CDB) and Pluggable Database (PDB). In this architecture multiple pluggable database are using the same SGA and background process of CDB. If you see the architecture, 2 pluggable databases ABC and XYZ are using the same sga and same background process. i.e there will be only one instance for a CDB. ( only pmon process)

2. What is CDB

The CDB represents the database as a whole.It contains multiple PDBs. but in EBS , only one PDB Supported. It contains the metadata of all the PDBs. This seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (control files, datafiles, undo, tempfiles, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.

3. What is PDB Like a normal database , a PDB contains physical datafiles that store the data contained within that PDB. The datafiles also contain a local copy of the data dictionary, which contains the metadata associated with that PDB. The data in the PDB is isolated to the PDB itself, and is not shared across other PDBs. All PDBs are owned by the parent CDB SYS user. A given CDB can have up to 252 PDBs plugged into it at any one time. Each PDB has, at a minimum, its own SYSTEM, SYSAUX, and USERS tablespaces. You can create additional tablespaces in a PDB just as in any other database. Each pdb has an associated service name assigned to it. So if you want to connect to PDB directly use the service name(tns name) to connect like. A PDB doesn't have background processes,undo tablespace, redo logs and control files.

4. Root container Each CDB has exactly one root container. This container provides a location for schemas, schema objects, and non-schema objects that belong to the CDB as a whole. System metadata required for the database to manage the individual PDBs are stored in the root container. The root container is named CDB$ROOT.

5. Seed PDB Each CDB has an Oracle supplied PDB called the seed. This PDB is called PDB$SEED and it’s used to create new PDBs within the CDB. You cannot add or modify objects in PDB$SEED.

6. Benifit and When it is required

Suppose, assuming a situation, where we need to create two schemas with the same name in the same database. Is it possible???? No, it is not possible......

When we want to install two applications like PeopleSoft/EBS or any other custom application in the same database because some application which has a specific schema name -SYSADMIN in PeopleSoft or AP/AR/GL ect..ect required for EBS Case.

7. What was the option in prior 12c?

We could create two different databases but with two different databases comes two different sets of overhead - two instances which consume memory CPU The more databases you have, the more the CPU and memory usage - all because you want to create multiple schemas in the same name.

In 12c we can achieve the above goal.

In 12c, Oracle introduces a multi-tenancy option. Instead of creating a physical database for each SYSADM schema you want to create, you can create a virtual database for each schema.

Each virtual database behaves like an independent database; but runs on the top of a real, physical database which may be hidden from the end users.

These virtual databases are called PDB. The physical database that houses these containers is known as a Container Database (CDB). You can pull out (or "unplug") a PDB from one CDB and place it (or, "plug" it) into another CDB.

Clone

1. Clone PDB in same container

2. Clone PDB in another container

How To

1. How to check whether the database is multi tenant or not:

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME Multitenant Option ? OPEN_MODE CON_ID

--------- -------------------------- -------------------- ----------

DBATEST Multitenant Option enabled READ WRITE 0

2. Find the list of PDBs present in multitenant database:

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID NAME OPEN_MODE

---------- ------------------------------ ----------

2 PDB$SEED READ ONLY

3 PDB1 READ WRITE

4 PDB2 READ WRITE

3. What is this CON_ID:

CON_ID – 0 = The data pertains to the entire CDB, i.e for a complete database.

CON_ID – 1= The data pertains to the root container (CDB$ROOT)

CON_ID – 2= The data pertains to the seed ( PDB$SEED)

CON_ID – 3 onwards = The data pertains to a PDB, Each PDB has its own container ID.(PDB1,PDB2 etc)

4. Chek Container name SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> sho con_id

CON_ID

------------------------------

1

5. Set Contener session

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB$SEED

SQL> show con_id

CON_ID

------------------------------

2

6. Find to which pdb we are currently connected:

SQL> show con_name

CON_NAME

------------------------------

PDB1

SQL> select sys_context ( 'Userenv', 'Con_Name') "Container DB" from dual;

Container DB

--------------------------------------------------------------------------------

PDB1

7. How to connect to container database:(CDB) SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

8. How to connect to pluggable database ( PDB)

sqlplus username/password@pdbname

SYS@ISPSOA> show con_name

CON_NAME

------------------------------

ISPSOA

SYS@ISPSOA> select con_id, name, open_mode, restricted from v$pdbs order by 1;

CON_ID NAME OPEN_MODE RES

---------- ------------------------------ ---------- ---

4 ISPSOA READ WRITE NO

Else

ALTER SESSION SET CONTAINER=PDB1;