OCP 12C – Basics of Multitenant Container Database (CDB)

The multitenant architecture

  • Needs enterprise edition and the multitenant option.
  • Consists of a CDB (Container database) and zero, one or up to 252 PDBs (pluggable databases).
  • Has a root container (the CDB itself) and a seed container (template to create PDBs)
  • There is only one instance per CDB.
  • A PDB doesn’t have :
    • background processes
    • undo tablespace, it uses the CDB’s.
    • redologs, it uses the CDB’s.
    • controlfiles
  • A PDB have :
    • It’s own tablespaces
    • It’s owner local users and local roles
    • It’s own metadata
  • A PDB CAN have :
    • a temporary tablespace or can use the CDB temporary tablespace.
    • it’s own Resource Manager Plan or can user the default CDB one.
  • Users can be common users (at the CDB level) or local users (local to a PDB)

Benefits of the multitenant database architecture

Multitenant architecture

  • Reduce storage and resources utilization.
  • Reduce provisioning time with the plug/unplug feature
  • Patching and Upgrading is done only at the CDB level, this reduce maintenance time and lower management cost.
  • Backups of all PDBs can be done at the same time with RMAN
  • There is only one instance to manage, this implies only one instance to tune.
  • This doesn’t change the way to connect to the database, the multitenant architecture is made for application isolation and you can easily restrict access to a specific PDB to a user.

Create and configure CDBs and PDBs

  • In order to create a CDB you must set the following initialization parameter : ENABLE_PLUGGABLE_DATABASE=TRUE
  • By default only the CDB$ROOT and PDB$SEED are created. Oui and DBCA let you create additional PDBs through the interface.
  • The creation of a PDB can be done in serveral ways :
    • By pluging it to the container
    • By cloning another PDB inside the same CDB : CREATE PLUGGABLE DATABASE NEW_PDB FROM OTHER_PDB;
    • By issuing the : CREATE PLUGGABLE DATABASE NEW_PDB;.
  • When you create a PDB you can choose the admin username and password, this user is granted the PDB_DBA role equivalent to the DBA role in a standard database.

New views, package and parameters

  • The CDB$ROOT, PDB$SEED and each PDB is identified in new views by a CON_ID column. The CON_ID=1 state for the CDB$ROOT and CON_ID=2 for the PDB$SEED.
  • The V$CONTAINERS database list the CDB$ROOT, the PDB$SEED and all the PDBs created. You can view their OPEN_MODE, OPEN_TIME and TOTAL_SIZE.
  • All DBA_ views exist also as CDB_, the CDB_ views include a CON_ID column to identify to which container it belongs to.
  • The PDB_FILE_NAME_CONVERT initialization parameter allows you to convert filename when you clone an existing PDB for exemple.
  • The DBMS_PDB package can be used to create a metadata xml file which can be used to convert a standard database to a PDB by plugging it into an existing CDB.

Plug, Unplug, Drop a PDB

  • To unplug a PDB you must follow two steps :
    • close the PDB : alter pluggable database mypdb close;
    • unplug the pdb : alter pluggable database mypdb unplug into ‘mypdb.xml';
  • To plug a PDB you must issue the following command : create pluggable database new_pdb using ‘/tmp/unplugged_pdb.xml';
  • To drop a PDB you must follow two steps  :
    • close the PDB : alter pluggable database mypdb close;
    • drop the PDB : drop pluggable database mypdb including datafiles;

Convert standard database to PDB

  • Conversion from standard database to PDB is done by using the DBMS_PDB package.
  • The dbms_pdb.describe procedure is used to create a metadata file containing information about TABLESPACE, DATAFILES, OPTIONS, PARAMETERS … used by the standard database.
  • Create a PDB in the destination CDB by specifying the XML metadata file generated by previous command.
email

OCP 12C – Enterprise Manager and Other Tools

EM Database Express

  • The old Enterprise Manager database control is replaced by Enterprise Manager database express in Oracle 12c.
  • It contains only basic administration capabilities as the advanced ones are included in Enterprise Manager cloud control
  • With EM database Express you can manage Security (Users, roles, profiles), Configuration (Instance Parameters, memory, database features), Storage (tablespaces, undo, redo, archive logs, control files), Performance.
  • Enterprise Manager needs the XML DB component.
  • You can configure the port used by EM database Express using :  exec DBMS_XDB_CONFIG.setHTTPsPort(5500);
  • You can get the port configured for EM database Express using :  select DBMS_XDB_CONFIG.getHTTPsPort from dual;
  • The EM_EXPRESS_BASIC (read-only role)  and EM_EXPRESS_ALL (access all functionnalities) roles can be used to grant access to EM Database Express to non-administrative users.
  • You can’t start or stop your DB with EM database express, this means that you database is accessible through EM database express only when you opened it.

 

Datafile space reclaimable report.

This script will help you find the space reclaimable in your datafiles, it finds the High Water Mark of all your datafiles (the minimum size) and then report the following information:

  • Datafile Size
  • Datafile HWM
  • Percentage of space reclaimable
  • Command to resize the datafile
  • Total space reclaimable in your datafiles
  • Percentage of space reclaimable in your datafiles

Find space reclaimable in the datafiles