There is no item in the cart. If you want to buy, Please click here.
The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB). in this article describe CDB and PDB in details.
Multi-tenant refers to a kind of architecture where a single instance of software runs on a server and serves multiple customers. In a multi-tenant environment,
CDB and PDB:
A container is either a PDB or the root.
A CDB includes zero, one, or many customer-created pluggable databases (PDBs). The root stores Oracle-supplied metadata and common users. The root container is named CDB$ROOT.
Pluggable Database or PDB is
The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED. a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.
Oracle has termed "multi-tenancy" to describe the process of creating a CDB that contains many "tenant" PDB's.
There are several benefits to pluggable databases:
1 - Easy fast cloning: Simply copy the PDB very quickly
2 - Easy upgrades: Just copy/move the PDB to a container running a higher release of Oracle
connect database using SQLPlus with sys user as sysdba
sqlplus / as sysdba
and now you will see all PDBS using following command
SQL> show pdbs
this output will be as like
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORAPDB READ WRITE NO
To view root database:
SQL> select name ,cdb , con_id from v$database;
Output:
NAME CDB CON_ID --------- --- ---------- ORCL YES 0
To view instance:
SQL> select instance_name , status , con_id from v$instance ;
Output:
INSTANCE_NAME STATUS CON_ID ---------------- ------------ ---------- oradb OPEN 0
To check services automatically created for each container
SQL> select name , con_id from v$services;
Output:
NAME CON_ID ---------------------------------------------------------------- ---------- SYS$BACKGROUND 1 SYS$USERS 1 mfipdb 5 orapdb 3 orcl.mfi.com 1 oradbXDB 1
6 rows selected.
To show Log files :
SQL> select group# , con_id , member from v$logfile;
Control files :
SQL> select name ,con_id from v$controlfile;
Output:
GROUP# CON_ID MEMBER ---------- ------------------------------------------------------------------------------------------ 3 0 /u01/app/oracle/oradata/ORCL/redo03.log 2 0 /u01/app/oracle/oradata/ORCL/redo02.log 1 0 /u01/app/oracle/oradata/ORCL/redo01.log
DATAFILES BOTH CDB AND PDBS
SQL> select file_name , tablespace_name , con_id from cdb_data_files order by con_id;
Output:
FILE_NAME TABLESPACE_NAME CON_ID ------------------------------------------------ ------------------------------------- ---------- /u01/app/oracle/oradata/ORCL/users01.dbf USERS 1 /u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 1 /u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM 1 /u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/ORCL/orapdb/system01.dbf SYSTEM 3 /u01/app/oracle/oradata/ORCL/orapdb/users01.dbf USERS 3 /u01/app/oracle/oradata/ORCL/orapdb/undotbs01.dbf UNDOTBS1 3 /u01/app/oracle/oradata/ORCL/orapdb/sysaux01.dbf SYSAUX 3 /u01/app/oracle/oradata/ORCL/mfipdb/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/ORCL/mfipdb/undotbs01.dbf UNDOTBS1 5 /u01/app/oracle/oradata/ORCL/mfipdb/sysaux01.dbf SYSAUX 5
11 rows selected.
There are the system , sysaux and undo datafile and a tempfile for the CDB seed .
now let use the views tablespace and datafile .
SQL> col name format a12 SQL> select file#,ts.name,ts.ts#,ts.con_id from v$datafile d , v$tablespace ts where d.ts#=ts.ts# and d.con_id = ts.con_id order by 4,3;
Output:
FILE# NAME TS# CON_ID ---------- ------------ ---------- ---------- 1 SYSTEM 0 1 3 SYSAUX 1 1 4 UNDOTBS1 2 1 7 USERS 4 1 5 SYSTEM 0 2 6 SYSAUX 1 2 8 UNDOTBS1 2 2 9 SYSTEM 0 3 10 SYSAUX 1 3 11 UNDOTBS1 2 3 12 USERS 5 3 13 SYSTEM 0 5 14 SYSAUX 1 5 15 UNDOTBS1 2 5
14 rows selected.
To list tempfiles of CDB.
SQL> col file_name for a57 SQL> select file_name , tablespace_name from cdb_temp_files;
Output:
FILE_NAME TABLESPACE_NAME --------------------------------------------------------- ------------------------------ /u01/app/oracle/oradata/ORCL/mfipdb/temp012020-11-19_14-5 TEMP 0-30-807-PM.dbf /u01/app/oracle/oradata/ORCL/orapdb/temp01.dbf TEMP /u01/app/oracle/oradata/ORCL/temp01.dbf TEMP
1. system user is created
SQL> select username , common ,con_id from cdb_users where username='SYSTEM';
Output:
USERNAME COM CON_ID ------------------------------------- --- ---------- SYSTEM YES 5 SYSTEM YES 1 SYSTEM YES 3
If we see the output system is in all containers as a common user .
2. To list all the common users of the CDB
SQL> select distinct username from cdb_users where common='YES' order by 1;
Output:
USERNAME -------------------------------------------------------------------------------- ANONYMOUS APPQOSSYS AUDSYS CTXSYS DBSFWUSER DBSNMP DIP DVF DVSYS GGSYS GSMADMIN_INTERNAL GSMCATUSER GSMROOTUSER GSMUSER LBACSYS MDDATA MDSYS OJVMSYS OLAPSYS ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN REMOTE_SCHEDULER_AGENT SI_INFORMTN_SCHEMA SYS SYS$UMF SYSBACKUP SYSDG SYSKM SYSRAC SYSTEM WMSYS XDB XS$NULL 36 rows selected.
Let us check with local users in the CDB
SQL> select username , con_id from cdb_users where common= 'NO';
Output:
USERNAME CON_ID --------------------------------------------- ---------- MPDBADMIN 5 TESTDB 5 PDBADMIN 3
If we see the output there is no local user in CDB , because it is impossible to create local user in CDB root .
To list roles and privilege's of the CDB .
SQL> select role,common , con_id from cdb_roles order by 3;
Output:
ROLE COM CON_ID -------------------------- --- ---------- DV_DATAPUMP_NETWORK_LINK YES 1 DV_AUDIT_CLEANUP YES 1 DV_GOLDENGATE_REDO_ACCESS YES 1 DV_XSTREAM_ADMIN YES 1 DV_GOLDENGATE_ADMIN YES 1 DV_STREAMS_ADMIN YES 1 ................ 267 rows selected.
if we see the out put there is no local role in the root container . we can not create local role in the root CDB .
TO SEE THE COMMONLY OR LOCALLY GRANTED .
SQL> select grantee , granted_role , common , con_id from cdb_role_privs where grantee='SYSTEM';
Output:
GRANTEE GRANTED_ROLE COM CON_ID -------------------------------------- ----------------------- --- ---------- SYSTEM DBA YES 3 SYSTEM AQ_ADMINISTRATOR_ROLE YES 3 SYSTEM DBA YES 5 SYSTEM AQ_ADMINISTRATOR_ROLE YES 5 SYSTEM AQ_ADMINISTRATOR_ROLE YES 1 6 rows selected.
