Monday, September 16, 2013

Understanding Oracle Database 12c Multitenant Architecture

Until Oracle Database 11g, Oracle only supported non-CDB. Oracle refers to the old architecture as the non-CDB architecture which requires its own instance and, therefore, its own background processes, memory allocation for the SGA, and needs to store the Oracle metadata in its data dictionary. Old architecture is also referred to as pre 12.1 architecture.

In pre 12.1 architecture multiple databases had to be created for multiple applications. This resulted in multiple databases and instances including increasing number of Oracle background process, more memory for individual databases and more storage to store data dictionaries of the database.

During application upgrades, each databases had to be upgraded which is tedious and time consuming tasks for the DBAs.

The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

A CDB can contain upto 253 PDBs including the SEED so there will be 252 user defined PDBs. In a CDB 512 services could be created.



A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application. A PDB is fully backward compatible with Oracle Database releases before Oracle Database 12c.


Common Users and Local Users

A CDB supports common users. A common user is a user that has the same identity in the root and in every existing and future PDB. The operations that a common user can perform depend on the privileges granted to the common user. Some administrative tasks, such as creating a PDB or unplugging a PDB, must be performed by a common user.

A common user is defined in the root’s data dictionary.
·         Only common users can be defined in the root: Creating a common user allows the CDB administrator to create at once a user that is replicated in each PDB.
·         A common user is known, not only where it is defined in the root, but also in every PDB that belongs to the CDB.

·         A common user can perform administrative tasks specific to the root or PDBs, such as plugging and unplugging PDBs, starting up the CDB, or opening a PDB when granted the proper privileges.

A CDB also supports local users. A local user is a user that exists in exactly one PDB. A user in a non-CDB maps to a local user in a PDB. A local user is defined in the PDB’s own data dictionary—and so is not known outside of that PDB.  According to the privileges granted, a user can work on the application data within the PDB or with other PDBs’ application using database links. Moreover, there cannot be any local users defined in the root.


Common and Local Privileges and Roles

Local Roles
A role in a non-CDB maps to a local role in a PDB. A local role is defined in the PDB’s own data dictionary—and so it is not known outside of that PDB and can only be used within its PDB.

Common Roles
Besides the local role, you can create common roles that are defined in every container. This way, it is easy to create at once a role that is replicated in all PDBs. It is at the creation time that you specify the nature of the role: local or common. Common roles as well as common users can only be created in the root by common users. Moreover, there cannot be any local roles defined in the root. All Oracle-supplied predefined roles are common roles.

Local and Common Privileges
The privileges are commonly referred to as local or common privileges, but to be more precise a privilege is either granted locally with the clause CONTAINER=CURRENT or commonly with the clause CONTAINER=ALL.


The same rule applies to roles: common roles can be granted commonly or locally to common users or roles. Common roles may contain privileges that apply across the CDB, that is, commonly granted for all containers, and can also contain locally granted privileges that apply only to an individual PDB, whereas local roles do not contain any commonly granted privileges.


S. No.
CDB
PDB
1
Control files and redo log files belong to the CDB and not to a specific container.

2
Shared UNDO and default database temporary tablespace.
Application tablespaces and Local temporary tablespaces can be created at PDB level.
3
Oracle-supplied metadata.
Non-shared local metadata.
4
Shared Oracle-supplied data.
Non-shared application data with other PDBs.
5
CDB views providing information across PDBs.

6
CDB resource manager plan allowing resource management between PDBs within a CDB.
PDB resource manager plan allowing resource management within PDB
7
Global users and global roles.
Local users and local roles.





Data Dictionary Views

Following are the data dictionary views related to CDB and/or PDB

View
Description
Container data objects, including:

V$ views
GV$ views
CDB_ views
DBA_HIST* views
Container data objects can display information about multiple PDBs. Each container data object includes aCON_ID column to identify containers.

There is a CDB_ view for each corresponding DBA_ view.


{CDB|DBA}_PDBS
Displays information about the PDBs associated with the CDB, including the status of each PDB.
CDB_PROPERTIES
Displays the permanent properties of each container in a CDB.
{CDB|DBA}_PDB_HISTORY
Displays the history of each PDB.
{CDB|DBA}_CONTAINER_DATA
Displays information about the user-level and object-level CONTAINER_DATA attributes specified in the CDB.
{USER|ALL|DBA|CDB}_OBJECTS
Displays information about database objects, and the SHARING column shows whether a database object is a metadata-linked object, an object-linked object, or a standalone object that is not linked to another object.
{ALL|DBA|CDB}_SERVICES
Displays information about database services, and the PDB column shows the name of the PDB associated with each service.
{USER|ALL|DBA|CDB}_VIEWS
{USER|ALL|DBA|CDB}_TABLES
The CONTAINER_DATA column shows whether the view or table is a container data object.
{USER|ALL|DBA|CDB}_USERS
The COMMON column shows whether a user is a common user or a local user.
{USER|ALL|DBA|CDB}_ROLES
{USER|ALL|DBA|CDB}_COL_PRIVS
{USER|DBA|CDB}_SYS_PRIVS
{USER|DBA|CDB}_ROLE_PRIVS
The COMMON column shows whether a role or privilege is commonly granted or locally granted.
{USER|ALL|DBA|CDB}_ARGUMENTS
{USER|ALL|DBA|CDB}_CLUSTERS
{USER|ALL|DBA|CDB}_CONSTRAINTS
{ALL|DBA|CDB}_DIRECTORIES
{USER|ALL|DBA|CDB}_IDENTIFIERS
{USER|ALL|DBA|CDB}_LIBRARIES
{USER|ALL|DBA|CDB}_PROCEDURES
{USER|ALL|DBA|CDB}_SOURCE
{USER|ALL|DBA|CDB}_SYNONYMS
{USER|ALL|DBA|CDB}_VIEWS
The ORIGIN_CON_ID column shows the ID of the container from which the row originates.
[G]V$PDBS
Displays information about the PDBs associated with the current CDB, including the open mode of each PDB.
[G]V$SYSTEM_PARAMETER
[G]V$PARAMETER
Displays information about initialization parameters, and the ISPDB_MODIFIABLE column shows whether a parameter can be modified for a PDB.



Oracle Multitenant is a new option in Oracle Database 12c. The multitenant architecture enables an Oracle database to contain a portable set of schemas, objects, and related structures that appears logically to an application as a separate database.

You can easily plug a PDB into a CDB and unplug a PDB from a CDB. When you plug in a PDB, you associate the PDB with a CDB. When you unplug a PDB, you disassociate the PDB from a CDB. An unplugged PDB consists of an XML file that describes the PDB and the PDB's files (such as the data files and wallet file).

You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time.

Each PDB has a globally unique identifier (GUID). The PDB GUID is primarily used to generate names for directories that store the PDB’s files, including both Oracle Managed Files directories and non-Oracle Managed Files directories.

In summary, you can think of your laptop or PC as a CDB, your hard disks as root container and your USB stick as pluggable database which can be plugged into your laptop or PC. The hard disk stores information of your laptop or PC and metadata about all your pluggable USB sticks.


References:
Oracle® Database
Administrator's Guide
12c Release 1 (12.1)
E17636-17

Hope this helps.


2 comments:

  1. According to http://www.oracle.com/technetwork/database/multitenant/overview/multitenant-wp-12c-2078248.pdf , Oracle is recommending one application backend (usually stored in one schema) per PDB. Do you agree? Has anyone attempted this with a large number of applications and users? Since most user accounts access multiple applications this would involve a lot more effort maintaining accounts and database links would also be required (which aren't necessary if multiple application back ends exist in a PDB).

    ReplyDelete
  2. Hi Gus,
    Oracle is just trying to show various ways of database consolidation in the white paper, one being schema consolidation and the other being multi-tenancy option. For applications which use different schemas sharing data between the schemas can reside in same database (either non-CDB or a PDB). For e.g. in case of ERP systems you will have schemas like GL, AR and AP e.t.c to store data for General Ledger, Accounts Receivable and Accounts Payable modules which share data with-in. But if you take example of banking systems which have Core Banking System Software (CBS) and Card Management System (CMS) can have two backend schemas in two PDBs.
    If you have databases with multiple schemas and want to upgrade to DB 12c then identify the dependent schemas and segregate them accordingly into different PDBs else you can convert the non-CDB into a single PDB. But you have to consider the security aspects as mentioned in the white paper.
    Hope this helps.
    Regards,
    Ashish Man Baisyet

    ReplyDelete