📋Steps in this guide1/13

Oracle 12c multitenant architecture - DBACLASS DBACLASS

oracle clusteringintermediate

by OracleDba

17 views

Overview

The most prominent feature of oracle 12c is its multitenant feature. We can use oracle 12c as normal database as that of previous version. But if you want to use multitenant, you need to have separate license for this. So, if you are manually creating a database with the create database command, and you wish to create the database as a Multitenant Container Database, you need to include the enable pluggable database clause. If you use the Oracle Database Configuration Assistant (DBCA), it gives you an option to create the database as a CDB when you configure the database to be created. An additional option is provided to create the first PDB in that CDB.

Section 2

> you can’t later convert an existing Oracle Database 12c non-CDB to a CDB if you decide you want to take advantage of the features of the CDB and vice versa. you can’t later convert an existing Oracle Database 12c non-CDB to a CDB if you decide you want to take advantage of the features of the CDB and vice versa. This feature introduced concept of container database(CDB) and pluggable database(PDB). If you see the architecutre, 2 pluggable database SALESPDB and HRPDB are using same sga and same background process. i.e there will be only one instance for a CDB. ( only pmon process)

Section 3

The CDB: The CDB represents the database as a whole. It contains multiple PDBs. It contains the metadata off all the PDBs. PDB: A PDB is designed to provide what is essentially a unique and isolated database environment within a CDB. 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

Section 4

> A PDB don’t have background processes,undo tablespace, redo logs and control file. It uses that of CDB. A PDB don’t have background processes,undo tablespace, redo logs and control file. It uses that of CDB. 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. 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.

Section 5

CONTROL FILE: controlfile is common for CDB and all PDBS. REDO LOG: Online redo logs are common for both CDB and PDB. But make sure to keep the size of logs more. ARCHIVELOG :

Section 6

This mode itself is done at the level of the CDB. You cannot opt to disable ARCHIVELOG mode for individual PDBs. SYSTEM TABLESPACE: There is a separate SYSTEM tablespace for the root and for each PDB. SYSAUX TABLESPACE: There is a separate SYSAUX tablespace for the root and for each PDB.

Section 7

TEMP TABLESPACE: A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD. UNDO TABLESPACE: PDB can’t have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible. > Upto Oracle 12cR1- Each PDB uses shared UNDO ( undo space from CDB) and From 12cR2 onwards each PDB has it’s own UNDO and temp tablespaces. Upto Oracle 12cR1- Each PDB uses shared UNDO ( undo space from CDB) and From 12cR2 onwards each PDB has it’s own UNDO and temp tablespaces.

Section 8

CHARACTERSET: There will only one characterset for the complete CDB. All the PDBS will use same characterset. For new multitenant database installation, Oracle recommends to user AL32UTF8. ALERT LOG:

Section 9

There will only one alert log for the container database. All log info about the PDBs will be written to the same alert log. PASSWORD FILE: There will be only one password file for the container database. All PDBs will use the same password file. How to check whether the database is multitenant or not:

Section 10

Find the list of PDBs present in multitenant database: What is this CON_ID: CON_ID – 0 = The data pertains to the entire CDB, i.e for 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)

Code/Command (click line numbers to comment):

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

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

Section 11

Find to which pdb we are currently connected: How to connect to container database:(CDB)

Code/Command (click line numbers to comment):

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> sho con_id

CON_ID
------------------------------
1

SQL> alter session set container=PDB$SEED;

Session altered.

SQL>
SQL>
SQL> show con_name

CON_NAME
------------------------------
PDB$SEED
SQL> show con_id

CON_ID
------------------------------
2

SQL> show con_name

CON_NAME
------------------------------
PDB1

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

Container DB
--------------------------------------------------------------------------------
PDB1

Section 12

How to connect to pluggable database ( PDB) Else

Code/Command (click line numbers to comment):

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

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

ALTER SESSION SET CONTAINER=PDB1;

Section 13

Common user vs local user in Multitenant database How to create a multitenant database in oracle 12c: How to create a pluggable database in oracle 12c: How to clone a pluggable database in oracle 12c: How to unplug and plug PDBs in oracle 12c: How to rename a pluggable database in oracle 12c

Comments (0)

Please to add comments

No comments yet. Be the first to comment!