1 Introduction to Multitenant Administration

You can create and administer multitenant container databases (CDBs), pluggable databases (PDBs), and application containers.

Multitenant Architecture

The multitenant architecture enables an Oracle AI Database to be a CDB.

Every Oracle AI Database must contain or be able to be contained by another database. For example, a CDB contains PDBs, and an application container contains application PDBs. A PDB is contained by a CDB or application container, and an application container is contained by a CDB.

Starting in Oracle Database 21c, a multitenant container database is the only supported architecture. In previous releases, Oracle supported non-container databases (non-CDBs).

  • CDBs
    A CDB contains one or more user-created PDBs and application containers.
  • PDBs
    A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an application as a separate database.
  • Application Containers
    An application container is an optional, user-created container within a CDB that stores data and metadata for one or more applications.

Parent topic: Introduction to Multitenant Administration

CDBs

A CDB contains one or more user-created PDBs and application containers.

At the physical level, a CDB is a set of files: control file, online redo log files, and data files. The database instance manages the files that make up the CDB.

The following figure shows a CDB and an associated database instance.

Figure 1-1 Database Instance and CDB

Description of Figure 1-1 follows
Description of "Figure 1-1 Database Instance and CDB"

Parent topic: Multitenant Architecture

PDBs

A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an application as a separate database.

At the physical level, each PDB has its own set of data files that store the data for the PDB. The CDB includes all the data files for the PDBs contained within it, and a set of system data files that store metadata for the CDB itself.

To move or archive a PDB, you can unplug it. An unplugged PDB consists of the PDB data files and a metadata file. An unplugged PDB is not usable until it is plugged in to a CDB.

The following figure shows a CDB named MYCDB.

Figure 1-2 PDBs in a CDB

Description of Figure 1-2 follows
Description of "Figure 1-2 PDBs in a CDB"

Physically, MYCDB is an Oracle AI Database, in the sense of a set of data files associated with an instance. MYCDB has one database instance, although multiple instances are possible in Oracle Real Application Clusters, and one set of database files.

MYCDB contains two PDBs: hrpdb and salespdb. As shown in Figure 1-2, these PDBs appear to their respective applications as separate, independent databases. An application has no knowledge of whether it is connecting to a CDB or PDB.

To administer the CDB itself or any PDB within it, you can connect to the CDB root. The root is a collection of schemas, schema objects, and nonschema objects to which all PDBs and application containers belong.

Parent topic: Multitenant Architecture

Application Containers

An application container is an optional, user-created container within a CDB that stores data and metadata for one or more applications.

In this context, an application (also called the master application definition) is a named, versioned set of common data and metadata stored in the application root. For example, the application might include definitions of tables, views, user accounts, and PL/SQL packages that are common to a set of PDBs.

In some ways, an application container functions as an application-specific CDB within a CDB. An application container, like the CDB itself, can include multiple application PDBs, and enables these PDBs to share metadata and data. At the physical level, an application container has its own set of data files, just like a PDB.

For example, a SaaS deployment can use multiple application PDBs, each for a separate customer, which share application metadata and data. For example, in the following figure, sales_app is the application model in the application root. The application PDB named cust1_pdb contains sales data only for customer 1, whereas the application PDB named cust2_pdb contains sales data only for customer 2. Plugging, unplugging, cloning, and other PDB-level operations are available for individual customer PDBs.

Figure 1-3 SaaS Use Case

Description of Figure 1-3 follows
Description of "Figure 1-3 SaaS Use Case"

Parent topic: Multitenant Architecture

Benefits of the Multitenant Architecture

Creating separate PDBs and application containers within a single CDB provides benefits for manageability and performance.

Parent topic: Introduction to Multitenant Administration

Benefits of Consolidating Data into a Single CDB

Database consolidation is the process of consolidating data from multiple databases on separate hosts into one CDB on one host. The multitenant architecture enables you to consolidate data and code without altering existing schemas or applications.

Consolidating data into a single CDB has the following benefits:

  • Cost reduction

    By consolidating hardware and database infrastructure to a single set of background processes, and efficiently sharing computational and memory resources, you reduce costs for hardware and maintenance. For example, 100 PDBs in a single CDB on a single host can share one database instance.

  • Easier and more rapid movement of data and code

    By design, you can quickly plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. You can also clone PDBs while they remain available. You can plug in a PDB with any character set and access it without character set conversion. If the character set of the CDB is AL32UTF8, then PDBs with different database character sets can exist in the same CDB.

  • Easier management and monitoring of the physical database

    The CDB administrator can manage the environment as an aggregate by executing a single operation, such as patching or performing an RMAN backup, for all hosted tenants and the CDB root. Backup strategies and disaster recovery are simplified.

  • Separation of data and code

    Although consolidated into a single physical CDB, PDBs appears to applications as separate databases. For example, if user error loses critical data in a single PDB, then the PDB administrator can use Oracle Flashback or point-in-time recovery to retrieve the lost data without affecting other PDBs.

  • Secure separation of administrative duties

    A common user account can connect to any container on which it has sufficient privileges, whereas a local user account is restricted to a specific PDB. Administrators can divide duties as follows:

    • An administrator uses a common user account to manage a CDB or application container.

    • A PDB administrator uses a local user account to manage an individual PDB. Because a privilege is contained within the container in which it is granted, a local user on one PDB does not have privileges on other PDBs within the same CDB.

  • Ease of performance tuning

    It is easier to collect performance metrics for a single CDB on one host than for multiple databases on multiple hosts. For example, it is easier to size one SGA than 100 SGAs.

  • Fewer database patches and upgrades

    It is easier to apply a patch to one CDB than to 100 databases, and to upgrade one CDB than to upgrade 100 databases.

See Also:

Parent topic: Benefits of the Multitenant Architecture

Benefits of the Multitenant Architecture for Manageability

The multitenant architecture improves manageability by storing the data and metadata specific to a PDB in the PDB itself.

By storing its own dictionary metadata, a PDB becomes easier to manage as a unit. This benefit occurs even when only one PDB resides in a CDB. Grouping PDBs into a separately managed application container increases manageability even further.

In a CDB, the data dictionary metadata is split between the CDB root and the PDBs. Benefits of data dictionary separation include the following:

  • Easier upgrade of data and code

    For example, instead of upgrading a CDB from one database release to another, you can rapidly unplug a PDB from the existing CDB, and then plug it into a newly created CDB from a higher release.

  • Easier migration between servers

    To perform load balancing or to meet SLAs, you can migrate an application database from an on-premise data center to the Oracle Cloud, or between two servers in the same environment.

  • Protection against data corruption within a PDB

    You can flash back a PDB to an SCN or PDB-specific restore point, without affecting other PDBs.

  • Ability to install, administer, and upgrade application-specific data and metadata in a single place

    You can define a set of application-specific PDBs as a single component, called an application container. You can then define one or more applications within this container. Each application definition is a named, versioned set of common metadata and data shared within this application container.

    For example, each customer of a SaaS vendor could have its own application PDB. Each application PDB might have identically defined tables named sales_mlt, with different data in each PDB. The PDBs could share a data-linked common object named countries_olt, which has identical data in each PDB. As an application administrator, you could manage the master application definition so that every new customer gets a PDB with the same objects, and every change to existing schemas (for example, the addition of a new table, or a change in the definition of a table) applies to all PDBs that share the application definition.

  • Integration with Oracle Database Resource Manager (the Resource Manager)

    In the multitenant environment, PDBs contend for shared resources. To address resource contention, usage, and monitoring issues, use the Resource Manager.

See Also:

Parent topic: Benefits of the Multitenant Architecture

Overview of Multitenant Administration

Become familiar with basic concepts related to configuring and managing a multitenant environment.

Parent topic: Introduction to Multitenant Administration

Users, Roles, and Objects in a Multitenant Environment

The container architecture enables database administrators to assume different roles. The key to the separation of duties is the distinction between common and local users, roles, and objects.

Parent topic: Overview of Multitenant Administration

About Commonality in a CDB

A common phenomenon defined in a CDB or application root is the same in all containers plugged in to this root.

  • Principles of Commonality
    In a CDB, a phenomenon can be common within either the system container (the CDB itself), or within a specific application container.
  • Namespaces in a CDB
    In a CDB, the namespace for every object is scoped to its container.

Parent topic: Users, Roles, and Objects in a Multitenant Environment

Principles of Commonality

In a CDB, a phenomenon can be common within either the system container (the CDB itself), or within a specific application container.

For example, if you create a common user account while connected to CDB$ROOT, then this user account is common to all PDBs and application roots in the CDB. If you create an application common user account while connected to an application root, however, then this user account is common only to the PDBs in this application container.

Within the context of CDB$ROOT or an application root, the principles of commonality are as follows:

  • A common phenomenon is the same in every existing and future container.

    Therefore, a common user defined in the CDB root has the same identity in every PDB plugged in to the CDB root; a common user defined in an application root has the same identity in every application PDB plugged in to this application root. In contrast, a local phenomenon is scoped to exactly one existing container.

  • Only a common user can alter the existence of common phenomena.

    More precisely, only a common user logged in to either the CDB root or an application root can create, destroy, or modify attributes of a user, role, or object that is common to the current container.

Namespaces in a CDB

In a CDB, the namespace for every object is scoped to its container.

The following principles summarize the scoping rules:

  • From an application perspective, a PDB is a separate database that is distinct from any other PDBs.

  • Local phenomena are created within and restricted to a single container.

    Note:

    In this topic, the word “phenomenon” means “user account, role, or database object.”

  • Common phenomena are defined in a CDB root or application root, and exist in all PDBs that are or will be plugged into this root.

The preceding principles have implications for local and common phenomena.

Local Phenomena

A local phenomenon must be uniquely named within a container, but not across all containers in the CDB. Identically named local phenomena in different containers are distinct. For example, local user sh in one PDB does not conflict with local user sh in another PDB.

CDB$ROOT Common Phenomena

Common phenomena defined in CDB$ROOT exist in multiple containers and must be unique within each of these namespaces. For example, the CDB root includes predefined common users such as SYSTEM and SYS. To ensure namespace separation, Oracle AI Database prevents creation of a SYSTEM user within another container.

To ensure namespace separation, the name of user-created common phenomena in the CDB root must begin with the value specified by the COMMON_USER_PREFIX initialization parameter. The default prefix is c## or C##. The names of all other user-created phenomena must not begin with c## or C##. For example, you cannot create a local user in hrpdb named c##hr, nor can you create a common user in the CDB root named hr.

Application Common Phenomena

Within an application container, names for local and application common phenomena must not conflict.

  • Application common users and roles

    The same principles apply to application common users as to CDB common users. The difference is that for CDB common users, the default value for the common user prefix is c## or C##, whereas in application root the default value for the common user prefix is the empty string.

    The multitenant architecture assumes that you create application PDBs from an application root, or convert a single-tenant application to a multitenant application.

  • Application common objects

    The multitenant architecture assumes that you create application common objects in the application root. Later, you add data locally within the application PDBs. However, Oracle AI Database supports creation of local tables within an application PDB. In this case, the local tables reside in the same namespace as application common objects within the application PDB.

See Also:

Oracle AI Database Security Guide to learn more about common users and roles

Parent topic: About Commonality in a CDB

About Common and Local User Accounts

A database user account has a password and specific database privileges.

User Accounts and Schemas

Each user account owns a single schema, which has the same name as the user. The schema contains the data for the user owning the schema. For example, the hr user account owns the hr schema, which contains schema objects such as the employees table. In a production database, the schema owner usually represents a database application rather than a person.

Within a schema, each schema object of a particular type has a unique name. For example, hr.employees refers to the table employees in the hr schema. The following figure depicts a schema owner named hr and schema objects within the hr schema.

Figure 1-4 HR Schema


Description of Figure 1-4 follows
Description of "Figure 1-4 HR Schema"

Common and Local User Accounts

If a user account owns objects that define the database, then this user account is common. User accounts that are not Oracle-supplied are either local or common. A CDB common user is a common user that is created in the CDB root. An application common user is a user that is created in an application root, and is common only within this application container.

The following graphic shows the possible user account types in a CDB.

Figure 1-5 User Accounts in a CDB

Description of Figure 1-5 follows
Description of "Figure 1-5 User Accounts in a CDB"

A CDB common user can connect to any container in the CDB to which it has sufficient privileges. In contrast, an application common user can only connect to the application root in which it was created, or a PDB that is plugged in to this application root, depending on its privileges.

  • Common User Accounts
    Within the context of either the system container (CDB) or an application container, a common user is a database user that has the same identity in the root and in every existing and future PDB within this container.
  • Local User Accounts
    A local user is a database user that is not common and can operate only within a single PDB.

Parent topic: Users, Roles, and Objects in a Multitenant Environment

Common User Accounts

Within the context of either the system container (CDB) or an application container, a common user is a database user that has the same identity in the root and in every existing and future PDB within this container.

Every common user can connect to and perform operations within the root of its container, and within any PDB in which it has sufficient privileges. Some administrative tasks must be performed by a common user. Examples include creating a PDB and unplugging a PDB.

For example, SYSTEM is a CDB common user with DBA privileges. Thus, SYSTEM can connect to the CDB root and any PDB in the database. You might create a common user saas_sales_admin in the saas_sales application container. In this case, the saas_sales_admin user could only connect to the saas_sales application root or to an application PDB within the saas_sales application container.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM. Every user-created common user is either a CDB common user, or an application common user.

The following figure shows sample users and schemas in two PDBs: hrpdb and salespdb. SYS and c##dba are CDB common users who have schemas in CDB$ROOT, hrpdb, and salespdb. Local users hr and rep exist in hrpdb. Local users hr and rep also exist in salespdb.

Figure 1-6 Users and Schemas in a CDB

Description of Figure 1-6 follows
Description of "Figure 1-6 Users and Schemas in a CDB"

Common users have the following characteristics:

  • A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.

    A common user need not have the same privileges in every container. For example, the c##dba user may have the privilege to create a session in hrpdb and in the root, but not to create a session in salespdb. Because a common user with the appropriate privileges can switch between containers, a common user in the root can administer PDBs

  • An application common user does not have the CREATE SESSION privilege in any container outside its own application container.

    Thus, an application common user is restricted to its own application container. For example, the application common user created in the saas_sales application can connect only to the application root and the PDBs in the saas_sales application container.

  • The names of user-created CDB common users must follow the naming rules for other database users. Additionally, the names must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default. Oracle-supplied common user names and user-created application common user names do not have this restriction.

    No local user name may begin with the characters c## or C##.

  • Every common user is uniquely named across all PDBs within the container (either the system container or a specific application container) in which it was created.

    A CDB common user is defined in the CDB root, but must be able to connect to every PDB with the same identity. An application common user resides in the application root, and may connect to every application PDB in its container with the same identity.

Parent topic: About Common and Local User Accounts

Characteristics of Common Users

Every common user is either Oracle-supplied or user-created.

Common user accounts have the following characteristics:

  • A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.

    A common user need not have the same privileges in every container. For example, the c##dba user may have the privilege to create a session in hrpdb and in the root, but not to create a session in salespdb. Because a common user with the appropriate privileges can switch between containers, a common user in the root can administer PDBs.

  • An application common user does not have the CREATE SESSION privilege in any container outside its own application container.

    Thus, an application common user is restricted to its own application container. For example, the application common user created in the saas_sales application can connect only to the application root and the PDBs in the saas_sales application container.

  • The names of user-created CDB common users must follow the naming rules for other database users. Additionally, the names must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default. Oracle-supplied common user names and user-created application common user names do not have this restriction.

    No local user name may begin with the characters c## or C##.

  • Every common user is uniquely named across all PDBs within the container (either the system container or a specific application container) in which it was created.

    A CDB common user is defined in the CDB root, but must be able to connect to every PDB with the same identity. An application common user resides in the application root, and may connect to every application PDB in its container with the same identity.

The following figure shows sample users and schemas in two PDBs: hrpdb and salespdb. SYS and c##dba are CDB common users who have schemas in CDB$ROOT, hrpdb, and salespdb. Local users hr and rep exist in hrpdb. Local users hr and rep also exist in salespdb.

Figure 1-7 Users and Schemas in a CDB

Description of Figure 1-7 follows
Description of "Figure 1-7 Users and Schemas in a CDB"

See Also:

Parent topic: Common User Accounts

SYS and SYSTEM Accounts

All Oracle databases include default common user accounts with administrative privileges.

Administrative accounts are highly privileged and are intended only for DBAs authorized to perform tasks such as starting and stopping the database, managing memory and storage, creating and managing database users, and so on.

The SYS common user account is automatically created when a database is created. This account can perform all database administrative functions. The SYS schema stores the base tables and views for the data dictionary. These base tables and views are critical for the operation of Oracle AI Database. Tables in the SYS schema are manipulated only by the database and must never be modified by any user.

The SYSTEM administrative account is also automatically created when a database is created. The SYSTEM schema stores additional tables and views that display administrative information, and internal tables and views used by various Oracle AI Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users.

See Also:

Parent topic: Common User Accounts

Local User Accounts

A local user is a database user that is not common and can operate only within a single PDB.

Local users have the following characteristics:

  • A local user is specific to a PDB and may own a schema in this PDB.

    In the example shown in "Characteristics of Common Users", local user hr on hrpdb owns the hr schema. On salespdb, local user rep owns the rep schema, and local user hr owns the hr schema.

  • A local user can administer a PDB, including opening and closing it.

    A common user with SYSDBA privileges can grant SYSDBA privileges to a local user. In this case, the privileged user remains local.

  • A local user in one PDB cannot log in to another PDB or to the CDB root.

    For example, when local user hr connects to hrpdb, hr cannot access objects in the sh schema that reside in the salespdb database without using a database link. In the same way, when local user sh connects to the salespdb PDB, sh cannot access objects in the hr schema that resides in hrpdb without using a database link.

  • The name of a local user must not begin with the characters c## or C##.

  • The name of a local user must only be unique within its PDB.

    The user name and the PDB in which that user schema is contained determine a unique local user. "Characteristics of Common Users" shows that a local user and schema named rep exist on hrpdb. A completely independent local user and schema named rep exist on the salespdb PDB.

The following table describes a scenario involving the CDB in "Characteristics of Common Users". Each row describes an action that occurs after the action in the preceding row. Common user SYSTEM creates local users in two PDBs.

Table 1-1 Local Users in a CDB

Operation Description 
SQL> CONNECT SYSTEM@hrpdb
Enter password: ********
Connected.

SYSTEM connects to the hrpdb container using the service name hrpdb. 

SQL> CREATE USER rep IDENTIFIED BY password; 

User created.

SQL> GRANT CREATE SESSION TO rep;

Grant succeeded.

SYSTEM now creates a local user rep and grants the CREATE SESSION privilege in this PDB to this user. The user is local because common users can only be created by a common user connected to the root. 

SQL> CONNECT rep@salespdb
Enter password: *******
ERROR:
ORA-01017: invalid username/password; logon denied

The rep user, which is local to hrpdb, attempts to connect to salespdb. The attempt fails because rep does not exist in PDB salespdb. 

SQL> CONNECT SYSTEM@salespdb
Enter password: ********
Connected.

SYSTEM connects to the salespdb container using the service name salespdb. 

SQL> CREATE USER rep IDENTIFIED BY password; 

User created.

SQL> GRANT CREATE SESSION TO rep;

Grant succeeded.

SYSTEM creates a local user rep in salespdb and grants the CREATE SESSION privilege in this PDB to this user. Because the name of a local user must only be unique within its PDB, a user named rep can exist in both salespdb and hrpdb. 

SQL> CONNECT rep@salespdb
Enter password: *******
Connected.

The rep user successfully logs in to salespdb.

See Also:

Oracle AI Database Security Guide to learn about local user accounts

Parent topic: About Common and Local User Accounts

Overview of Common and Local Roles in a CDB

User-created roles are either local or common. Common roles are either common to the CDB itself or to a specific application container.

Every Oracle-supplied role is common, for example, the predefined DBA role. In Oracle-supplied scripts, every privilege or role granted to Oracle-supplied users and roles is granted commonly, with one exception: system privileges are granted locally to the common role PUBLIC.

  • Common Roles in a CDB
    A common role exists either in the CDB root or an application root, and applies to every PDB within the root container (either the CDB or the application container).
  • Local Roles in a CDB
    A local role exists only in a single PDB, and is thus completely independent of local roles in any other PDBs.

Parent topic: Users, Roles, and Objects in a Multitenant Environment

Common Roles in a CDB

A common role exists either in the CDB root or an application root, and applies to every PDB within the root container (either the CDB or the application container).

Common roles are useful for cross-container operations, ensuring that a common user has a role in every PDB. Every common role is one of the following types:

  • Oracle-supplied

    All Oracle-supplied roles, such as DBA and PUBLIC, are common to the CDB.

  • User-created

    Create a common role by executing CREATE ROLE ... CONTAINER=ALL in either the CDB root or application root, which determines the container to which the role is common. The standard naming conventions apply. Additionally, the names of CDB common roles must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default.

The scope of the role is the scope of the root within which it is defined. If you define the role in CDB$ROOT, then its scope is the entire CDB. If you define the role within application root, then its scope is the application container.

Local Roles in a CDB

A local role exists only in a single PDB, and is thus completely independent of local roles in any other PDBs.

A local role can only contain roles and privileges that apply within the container in which the role exists. For example, if you create the local role pdbadmin in hrpdb, then the scope of this role is restricted to this PDB.

PDBs in the same CDB, or in the same application container, may contain local roles with the same name. For example, the user-created role pdbadmin may exist in both hrpdb and salespdb. However, these roles are completely independent of each other.

Common and Local Objects

A common object is defined in either the CDB root or an application root, and can be referenced using metadata links or object links. A local object is every object that is not a common object.

Database-supplied common objects are defined in CDB$ROOT and cannot be changed. Oracle Database does not support creation of common objects in CDB$ROOT.

You can create most schema objects—such as tables, views, PL/SQL and Java program units, sequences, and so on—as common objects in an application root. If the object exists in an application root, then it is called an application common object.

A local user can own a common object. Also, a common user can own a local object, but only when the object is not data-linked or metadata-linked, and is also neither a metadata link nor a data link.

See Also:

Oracle AI Database Security Guide to learn more about privilege management for common objects

Parent topic: Users, Roles, and Objects in a Multitenant Environment

Separation of Duties in CDB and PDB Administration

Some database administrators manage an entire CDB, while others manage individual PDBs.

DBAs who manage an entire CDB connect to the CDB as common users, and manage attributes of the entire CDB and the root, as well as some attributes of PDBs. For example, these CDB DBAs can create, unplug, plug in, and drop PDBs. They can also specify the temporary tablespace and the default tablespace for the CDB root, and they can change the open mode of PDBs.

DBAs can also connect to a specific PDB as a local PDB administrator. The PDB DBA performs tasks required for the PDB to support an application. For example, tasks can include management of tablespaces and schemas in a PDB, specification of storage parameters for that PDB, changing the open mode of the current PDB, and setting PDB-level initialization parameters.

Tasks and Tools for a Multitenant Environment

This manual explains how to create and perform operations on containers using command-line tools such as SQL*Plus or SQL Developer.

Parent topic: Overview of Multitenant Administration

Tasks for a Multitenant Environment

This section summarizes the tasks required to manage a multitenant environment.

This manual explains how to administer containers as containers, for example, how to create CDBs and PDBs, start them up and shut them down, and perform cross-container operations. Oracle AI Database Administrator’s Guide describes traditional administrative tasks that you perform within an existing container, including managing database storage, schema objects, resources, and task scheduling.

To achieve the goals described in "Benefits of the Multitenant Architecture", you must complete the following general tasks:

Task 1   Plan for the Multitenant Environment

Creating and configuring any database requires careful planning. A CDB requires special considerations. For example, consider the following factors when you plan for a CDB:

  • The number of PDBs that will be plugged into each CDB

  • The resources required to support the planned CDB

  • Container management policies run as an aggregate on the entire CDB or run locally on individual PDBs

  • Container database topology, which could consist of application containers with application PDBs or a CDB with PDBs, or a combination of both

See "Preparing to Create a CDB" for detailed information about planning for a CDB.

Task 2   Create One or More CDBs

When you have completed the necessary planning, you can create one or more CDBs using either the Database Configuration Assistant (DBCA) or the CREATE DATABASE ... ENABLE PLUGGABLE DATABASE command. In either case, you must specify the configuration details for each CDB.

See "Creating a CDB with DBCA" and "Creating a Database with the CREATE DATABASE Statement" for detailed information about creating a CDB.

After a CDB is created, it consists of the root and PDB$SEED, as shown in the following figure. The CDB root contains only Oracle maintained objects and data structures, and PDB$SEED is a generic seed database for cloning purposes.

Figure 1-8 A Newly Created CDB

Description of Figure 1-8 follows
Description of "Figure 1-8 A Newly Created CDB"
Task 3   Optionally, Create Application Containers

An application container is an optional component of a CDB that consists of an application root and the application PDBs associated with it. An application container stores data for one or more applications.

The following graphic shows a CDB with one empty application container.

Figure 1-9 An Application Container

Description of Figure 1-9 follows
Description of "Figure 1-9 An Application Container"

See "About Application Containers".

Task 4   Create, Plug In, and Unplug PDBs

PDBs contain user data. After creating a CDB, you can create PDBs, plug unplugged PDBs into it, and unplug PDBs from it whenever necessary. You can unplug a PDB from a CDB and plug this PDB into a different CDB. You might move a PDB from one CDB to another if, for example, you want to move the workload for the PDB from one server to another.

See "Creating PDBs and Application Containers" for information about creating PDBs, plugging in PDBs, and unplugging PDBs.

The following figure shows a CDB with several PDBs.

Figure 1-10 A CDB with PDBs

Description of Figure 1-10 follows
Description of "Figure 1-10 A CDB with PDBs"

Figure 1-11 shows a CDB with PDBs, application containers, and application PDBs.

Figure 1-11 A CDB with PDBs, Application Containers, and Application PDBs

Description of Figure 1-11 follows
Description of "Figure 1-11 A CDB with PDBs, Application Containers, and Application PDBs"
Task 5   Administer and Monitor the CDB and Application Containers

Administering and monitoring a CDB involves managing the entire CDB, the CDB root, and some attributes of PDBs.. Administering and monitoring an application container is similar to administering and monitoring a CDB, but your actions only affect the application root and the application PDBs that are part of the application container.

See "After Creating a CDB" for descriptions of tasks that are similar and tasks that are different. Also, see "Administering a CDB" and "Monitoring Containers in a CDB".

You can use Oracle Resource Manager to allocate and manage resources among PDBs hosted in a CDB, and you can use it to allocate and manage resource use among user processes within a PDB.

You can also use Oracle Scheduler to schedule jobs in a CDB and in individual PDBs. See Oracle AI Database Administrator’s Guide.

Task 6   Administer and Monitor PDBs and Application PDBs

See "Administering PDBs" and "Monitoring Containers in a CDB".

Tools for a Multitenant Environment

You can use various tools to configure and administer a multitenant environment.

Table 1-2 Tools for a Multitenant Environment

Tool Description See Also

SQL*Plus

SQL*Plus is a command-line tool that enables you to create, manage, and monitor CDBs and PDBs. You use SQL statements and Oracle-supplied PL/SQL packages to complete these tasks in SQL*Plus.

SQL*Plus User's Guide and Reference

Oracle Database Configuration Assistant (DBCA)

DBCA is a utility with a graphical user interface that enables you to create and duplicate CDBs. It also enables you to create, relocate, clone, plug in, and unplug PDBs.

Oracle AI Database Installation Guide and the DBCA online help

Oracle Enterprise Manager Cloud Control

Cloud Control is a system management tool with a graphical user interface that enables you to manage and monitor a CDB and its PDBs.

Cloud Control online help

Oracle SQL Developer

Oracle SQL Developer is a client application with a graphical user interface that enables you to configure a CDB, create PDBs, plug and unplug PDBs, modify the state of a PDB, clone a PDB to the Oracle Cloud, hot clone/refresh a PDB, relocate a PDB between application roots, and more.

Additionally, Oracle SQL Developer has graphical interfaces for resource management, storage, security, configuration, and reporting of performance metrics on containers and pluggable databases in a CDB.

Oracle SQL Developer User's Guide

The Server Control (SRVCTL) utility

The SRVCTL utility can create and manage services for PDBs.

"Managing Services for PDBs"

Overview of Container Creation

You create a CDB using CREATE DATABASE, and then create PDBs and application containers using CREATE PLUGGABLE DATABASE.

Parent topic: Overview of Multitenant Administration

Creation of a CDB

The CREATE DATABASE statement creates a new CDB.

When you create a CDB, Oracle Database automatically creates a root container (CDB$ROOT) and a seed PDB (PDB$SEED). The following graphic shows a newly created CDB:

Figure 1-12 CDB with Seed PDB

Description of Figure 1-12 follows
Description of "Figure 1-12 CDB with Seed PDB"

See Also:

Parent topic: Overview of Container Creation

Creation of a PDB or Application Container

The CREATE PLUGGABLE DATABASE SQL statement creates a PDB. Specifying the AS APPLICATION CONTAINER clause creates an application container.

A created PDB automatically includes a full data dictionary, including metadata and internal links to system-supplied objects in the CDB root or application root. You must define every PDB from a single root: either the CDB root or an application root. A PDB created in an application container is called an application PDB.

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

Note:

In the following topics, the term "PDB" refers to a PDB, application container, or application PDB.

See Also:

"Creating PDBs and Application Containers"

Parent topic: Overview of Container Creation

Creation of a PDB by Cloning

One technique for creating a PDB is called cloning.

You can clone a PDB from PDB$SEED, an application seed, or a remote or local PDB.

Parent topic: Creation of a PDB or Application Container

Creation of a PDB from a Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a PDB from a seed.

A seed is a PDB that serves as a template for creation of another PDB. Creating a PDB from a seed copies some or all of the contents of a PDB, and then assigns a new unique identifier.

A seed PDB is either of the following:

  • The PDB seed (PDB$SEED), which is a system-supplied template for creating PDBs

    Every CDB has exactly one PDB$SEED, which cannot be modified or dropped.

  • An application seed, which is a user-created PDB for a specified application root

    Within an application container, you can create an application seed using the CREATE PLUGGABLE DATABASE AS SEED statement, which you can then use to accelerate creation of new application PDBs.

Figure 1-13 Creation from PDB$SEED

Description of Figure 1-13 follows
Description of "Figure 1-13 Creation from PDB$SEED"

Example 1-1 Creation of a PDB from PDB$SEED

The following SQL statement creates a PDB named hrpdb from PDB$SEED using Oracle Managed Files: 

CREATE PLUGGABLE DATABASE hrpdb
 ADMIN USER dba1 IDENTIFIED BY password;
Creation of a PDB by Cloning a PDB

To clone a PDB from another PDB, use the CREATE PLUGGABLE DATABASE statement with the FROM clause.

In this technique, the source is a PDB in a local or remote CDB. The target is the PDB copied from the source. The cloning operation copies the files associated with the source to a new location, and then assigns a new GUID to create the PDB.

This technique is useful for quickly creating PDBs for testing and development. For example, you might test a new or modified application on a cloned PDB before deploying the application in a production PDB. If a PDB is in local undo mode, then the source PDB can be open in read/write mode during the operation, referred to as hot cloning.

Note:

If you clone a PDB from a remote CDB, then you must use a database link.

If you run CREATE PLUGGABLE DATABASE statement in an application root, then you create the cloned PDB in the application container. In this case, the application name and version of the source PDB must be compatible with the application name and version of the application container.

The following graphic illustrates cloning a PDB when both source and target are in the same CDB.

Figure 1-14 Cloning a PDB

Description of Figure 1-14 follows
Description of "Figure 1-14 Cloning a PDB"

Starting in Oracle Database 19c, you can clone a remote PDB using DBCA.

Example 1-2 Cloning a PDB

The following SQL statement clones a PDB named salespdb from the plugged-in PDB named hrpdb: 

CREATE PLUGGABLE DATABASE salespdb FROM hrpdb;
  • Clones from PDB Snapshots
    Create a clone from a PDB snapshot by specifying USING SNAPSHOT clause of the CREATE PLUGGABLE DATABASE command.
  • Snapshot Copy PDBs
    A snapshot copy PDB is based on a copy of the underlying storage system. Snapshot copy PDBs reduce the amount of storage required for testing purposes and reduce creation time significantly.
  • Refreshable Clone PDBs
    A refreshable clone PDB is a read-only clone that can periodically synchronize with its source PDB.

See Also:

Parent topic: Creation of a PDB by Cloning

Clones from PDB Snapshots

Create a clone from a PDB snapshot by specifying USING SNAPSHOT clause of the CREATE PLUGGABLE DATABASE command.

Creation of PDB Snapshots with the SNAPSHOT Clause

A PDB snapshot is a point-in-time copy of a PDB. The source PDB can be open read-only or read/write while the snapshot is created. A PDB snapshot taken while the source PDB is open is called a hot clone. You can create clones from PDB snapshots. These clone PDBs are useful in development and testing.

You can create snapshots manually using the SNAPSHOT clause of CREATE PLUGGABLE DATABASE (or ALTER PLUGGABLE DATABASE), or automatically using the EVERY interval clause. The following statement creates a PDB snapshot with the name pdb1_wed_4_1201: 

ALTER PLUGGABLE DATABASE SNAPSHOT pdb1_wed_4_1201;

If the storage system supports sparse clones, then the preceding command creates a sparse copy. Otherwise, the command creates a full copy.

Every PDB snapshot is associated with a snapshot name and the SCN and timestamp at snapshot creation.

Creation of a PDB Clone with the USING SNAPSHOT Clause

If you create a clone from a PDB snapshot using the SNAPSHOT COPY clause, then the PDB is a snapshot copy PDB and is based on a copy of the underlying storage system.

To create a clone from a PDB snapshot, specify the USING SNAPSHOT clause of the CREATE PLUGGABLE DATABASE statement. For example, the following statement clones a PDB named pdb1_copy from the PDB-level snapshot named pdb1_wed_4_1201: 

CREATE PLUGGABLE DATABASE pdb1_copy FROM pdb1 
  USING SNAPSHOT pdb1_wed_4_1201;

See Also:

Parent topic: Creation of a PDB by Cloning a PDB

Snapshot Copy PDBs

A snapshot copy PDB is based on a copy of the underlying storage system. Snapshot copy PDBs reduce the amount of storage required for testing purposes and reduce creation time significantly.

If the file system supports storage snapshots, then CREATE PLUGGABLE DATABASE ... FROM ... SNAPSHOT COPY copies a PDB from a source PDB, which can be read/write during the operation. The snapshot copy PDB files use copy-on-write technology. Only modified blocks require extra storage on disk. If the file system does not support storage snapshots or use Oracle Exadata sparse files, then the CLONEDB initialization parameter must be true, and the source PDB must be read-only for as long as the snapshot copy PDB exists.

Because a snapshot copy PDB depends on storage-managed snapshots, you cannot unplug a snapshot copy PDB from the CDB root or application root. You cannot drop the storage snapshot on which a snapshot copy PDB is based.

You can transform a snapshot copy PDB, which uses sparse files, into a full PDB. This process is known as materializing the snapshot copy PDB. Because a materialized PDB does not depend on the source PDB, you can drop it. Materialize a PDB by running the ALTER PLUGGABLE DATABASE MATERIALIZE command.

Note:

A PDB created with the USING SNAPSHOT clause and a PDB created with the SNAPSHOT COPY clause have different properties. You cannot specify both clauses in a single CREATE PLUGGABLE DATABASE command. The CREATE PLUGGABLE DATABASE … FROM … USING SNAPSHOT clause creates a full, standalone PDB that does not need to be materialized. The CREATE PLUGGABLE DATABASE … FROM … SNAPSHOT COPY clause creates a sparse PDB that must be materialized if you want to drop the storage-level snapshot on which it is based.

Note:

"Creating and Materializing Snapshot Copy PDBs"

Refreshable Clone PDBs

A refreshable clone PDB is a read-only clone that can periodically synchronize with its source PDB.

Depending on the value specified in the REFRESH MODE clause, synchronization occurs automatically or manually. For example, if hrpdb_re_clone is a clone of hrpdb, then every month you could manually refresh hrpdb_re_clone with changes from hrpdb. Alternatively, you could configure hrpdb to propagate changes to hrpdb_re_clone automatically every 24 hours.

You can switch the roles of a source PDB and its refreshable clone. This switchover can be useful for load balancing between CDBs, and when the source PDB suffers a failure.

Note:

"About Cloning a PDB" to learn how to clone a PDB using the REFRESH MODE clause

Creation of a PDB by Plugging In an Unplugged PDB

An unplugged PDB is a self-contained set of data files, and an XML metadata file that specifies the locations of the PDB files. To plug in an unplugged PDB, use the CREATE PLUGGABLE DATABASE statement with the USING clause.

When plugging in an unplugged PDB, you have the following options:

  • Specify the XML metadata file that describes the PDB and the files associated with the PDB.

  • Specify a PDB archive file, which is a compressed file that contains both the XML file and PDB data files. You can create a PDB by specifying the archive file, and thereby avoid copying the XML file and the data files separately.

The following graphic illustrates plugging in an unplugged PDB using the XML file.

Figure 1-15 Plugging In an Unplugged PDB

Description of Figure 1-15 follows
Description of "Figure 1-15 Plugging In an Unplugged PDB"

Example 1-3 Plugging In a PDB

The following SQL statement plugs in a PDB named salespdb based on the metadata stored in the named XML file, and specifies NOCOPY because the files of the unplugged PDB do not need to be moved to a new location: 

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' NOCOPY;
Creation of a PDB by Relocating

To relocate a PDB from one CDB to another, use either the CREATE PLUGGABLE DATABASE ... RELOCATE statement or DBCA.

This technique has the following advantages:

  • The relocation occurs with minimal downtime.

  • The technique keeps the PDB being relocated open in read/write mode during the relocation, and then brings the PDB online in its new location.

You must create a database link at the target CDB, which is the CDB that will contain the relocated PDB. Also, the source PDB must use local undo data.

The following graphic depicts a PDB relocation.

Figure 1-16 Relocating a PDB

Description of Figure 1-16 follows
Description of "Figure 1-16 Relocating a PDB"

Starting in Oracle Database 19c, you can relocate a remote PDB using DBCA in silent mode.

Example 1-4 PDB Relocation

The following statement, which is issued at a target CDB, relocates hrpdb from the source CDB to the target CDB: 

CREATE PLUGGABLE DATABASE hrpdb FROM hrpdb@lnk_to_source RELOCATE;
Creation of a PDB as a Proxy PDB

A proxy PDB provides access to different PDB, called the referenced PDB, in a remote CDB.

Proxy PDBs enable you to aggregate data from multiple sources. A SQL statement submitted for execution in a proxy PDB executes within the referenced PDB.

A typical use case is a proxy PDB that references an application root replica. If multiple CDBs have the same application definition (for example, same tables and PL/SQL packages), then you can create a proxy PDB in the application container of the master application root. The referenced PDB for the proxy PDB is the application root in a different CDB. By running installation scripts in the master root, the application roots in the other CDBs become replicas of the master application root.

To create a proxy PDB, use the CREATE PLUGGABLE DATABASE statement with the FROM clause, which must specify a database link to the referenced PDB in the remote CDB, and the AS PROXY clause.

Note:

If you plug a proxy PDB directly into CDB$ROOT, then you must have created the proxy in CDB$ROOT. A proxy of an application PDB must both be plugged in to an application root.

The following graphic shows the creation of a proxy PDB that references a PDB in a remote CDB.

Figure 1-17 Creating a Proxy PDB

Description of Figure 1-17 follows
Description of "Figure 1-17 Creating a Proxy PDB"

Example 1-5 Creation of a Proxy PDB

This example creates a proxy PDB named pdb1. The referenced PDB is specified using a database link. 

CREATE PLUGGABLE DATABASE pdb1 AS PROXY FROM pdb1@pdb1_link;

Note:

"Creating a PDB as a Proxy PDB"