Что такое pdb oracle
Oracle 12c Multitenant Architecture. Новые возможности для разработки и тестирования
Самым крупным нововведением недавно вышедшего Oracle 12c безусловно является Multitenant Architecture. Сам Oracle преподносит эту возможность в основном как средство консолидации и снижения расходов.
Суть технологии состоит в возможности запустить несколько независимых баз (pluggable database, PDB) в рамках одного инстанса (container database, CDB). Каждая база имеет свой набор схем и табличных пространств, но при этом у них общая SGA и один набор серверных процессов. Есть возможность клонировать pluggable database, как в рамках одного контейнера, так и между контейнерами. Вот эту возможность и будем использовать для создания копий тестовых баз и экономии ресурсов.
Задача — имеем большую систему, с большой базой. Нужно проводить тестирование изменений, причем изменений разрушительных — удаление/модификация таблиц. Как это делается сейчас — создаем новую базу, заливаем в нее минимально возможный набор схем и данных и проводим тестирование. Процесс сам по себе не быстрый, трудоемкий и всегда есть возможность ошибиться. Да и «минимальный набор данных» может быть не таким уж и маленьким.
Эксперимент проводился в среде Oracle Virtualbox 4.2.14. Я не буду подробно описывать инсталляцию, она хорошо освещена в документации, буду останавливаться только на важных моментах.
Инсталляция
Ставим Oracle linux 6.4 c апдейтами, зависимости для oracle и поддержку ASM:
конфигурируем ASM и создаем ASM disk:
Подключаемся к инстансу ASM и меняем режим совместимости:
В результате получаем базу-контейнер и с seed database (модельная база для создания pluggable database).
Проверяем что все получилось:
Работа с клонами
Создаем каталог на ACFS /data/oradata, и меняем владельца на oracle. Меняем параметр db_create_file_dest на /data/oradata. Cоздаем клон который будет изображать тестовую базу:
На ACFS должен появиться каталог с буквенно-цифровым именем (случайным), содержащий наш PDB:
PDB после создания или рестарта контейнера нужно открывать:
Имитируем создание тестовой базы — создадим tablespace test размером 2G:
Убеждаемся что файл данных есть и размер его 2 гигабайта:
Ради чего все это затевалось
Мы имеем тестовую базу большого объема и хотим протестировать особо крупное изменение, затрагивающее структуру таблиц и требующее для проверки всех данных.
Клонируем тестовую базу в режиме snapshot:
И смотрим что произошло на файловой системе:
Файлы данных всех табличных пространств кроме TEMP это ссылки на ACFS снапшот, и место на диске они не занимают. Узнать сколько реально занял снапшот можно так:
Чего и добивались — 286МB против более 3GB
Естественно, если активно начать изменять блоки клона со снапшотами, занимаемое им место вырастет.
После того как провели тестирование, удаляем ненужный клон:
Убеждаемся что место освободилось:
Результат
В результате нам удалось сэкономить время и ресурсы. И не только дисковые, напомню, что все базы PDB используют один комплект процессов и одну SGA.
PS. Статья не претендует на полное описание Oracle Multitenant Architecture, рассмотрен лишь частный случай применительно к конкретной задаче.
17 Introduction to the Multitenant Architecture
This chapter contains information specific to the Oracle Multitenant option.
It includes the following topics:
About the Multitenant Architecture
The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).
About Containers in a CDB
A container is either a PDB or the root. The root container is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.
Every CDB has the following containers:
Zero or more user-created PDBs
A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, 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.
The following figure shows a CDB with four containers: the root, seed, and two PDBs. Each PDB has its own dedicated application. A different PDB administrator manages each PDB. A common user exists across a CDB with a single identity. In this example, common user SYS can manage the root and every PDB. At the physical level, this CDB has a database instance and database files, just as a non-CDB does.
Figure 17-1 CDB with Two PDBs
Description of «Figure 17-1 CDB with Two PDBs»
Oracle Database Administrator’s Guide for an introduction to the multitenant architecture
About User Interfaces for the Multitenant Architecture
You can use the same administration tools for both CDBs and non-CDBs.
For example, you can use the following tools in a multitenant environment:
SQL*Plus is a command-line program that you use to submit SQL and PL/SQL statements to an Oracle database.
SQL Developer provides another GUI for accessing your Oracle database. SQL Developer supports PDB provisioning.
Oracle Enterprise Manager Cloud Control (Cloud Control)
Cloud Control is an Oracle Database administration tool that provides a graphical user interface (GUI). Cloud Control supports Oracle Database 12 c targets, including PDBs, CDBs, and non-CDBs.
See Oracle Database Administrator’s Guide to learn more about Cloud Control.
Oracle Enterprise Manager Database Express (EM Express)
EM Express is a web-based management product built into the Oracle database. EM Express enables you to provision and manage PDBs, including the following operations:
Creating and dropping PDBs
Plugging in and unplugging and PDBs
Setting resource limits for PDBs
See Oracle Database 2 Day DBA to learn more about using EM Express for managing CDBs and PDBs.
Oracle Database Configuration Assistant (DBCA)
DBCA enables you to create CDBs or non-CDBs, and create, plug, and unplug PDBs. See Oracle Database 2 Day DBA and Oracle Database Administrator’s Guide for more information about DBCA.
Oracle Multitenant Self-Service Provisioning application
This application enables the self-service provisioning of PDBs. CDB administrators control access to this self-service application and manage quotas on PDBs.
For more information about the application or to download the software, use any browser to access the OTN page for the application:
Benefits of the Multitenant Architecture
The multitenant architecture solves a number of problems posed by the traditional non-CDB architecture.
Challenges for a Non-CDB Architecture
Large enterprises may use hundreds or thousands of databases. Often these databases run on different platforms on multiple physical servers.
Because of improvements in hardware technology, especially the increase in the number of CPUs, servers are able to handle heavier workloads than before. A database may use only a fraction of the server hardware capacity. This approach wastes both hardware and human resources.
For example, 100 servers may have one database each, with each database using 10% of hardware resources and 10% of an administrator’s time. A team of DBAs must manage the SGA, database files, accounts, security, and so on of each database separately, while system administrators must maintain 100 different computers.
To show the problem in reduced scale, Figure 17-2 depicts 11 databases, each with its own application and server. A head DBA oversees a team of four DBAs, each of whom is responsible for two or three databases.
Figure 17-2 Database Environment Before Database Consolidation
Description of «Figure 17-2 Database Environment Before Database Consolidation»
Typical responses include:
Use virtual machines (VMs).
In this model, you replicate the operating infrastructure of the physical server—operating system and database—in a virtual machine. VMs are agile, but use technical resources inefficiently, and require individual management. Virtual sprawl, which is just as expensive to manage, replaces the existing physical sprawl.
Place multiple databases on each server.
Separate databases eliminate operating system replication, but do not share background processes, system and process memory, or Oracle metadata. The databases require individual management.
Separate the data logically into schemas or virtual private databases (VPDs).
This technique uses technical resources efficiently. You can manage multiple schemas or VPDs as one. However, this model is less agile than its alternatives, requiring more effort to manage, secure, and transport. Also, the logical model typically requires extensive application changes, which discourages adoption.
Benefits of the Multitenant Architecture for Database Consolidation
The PDB/non-CDB compatibility guarantee means that a PDB behaves the same as a non-CDB as seen from a client connecting with Oracle Net. The installation scheme for an application back end that runs against a non-CDB runs the same against a PDB and produces the same result. Also, the run-time behavior of client code that connects to the PDB containing the application back end is identical to the behavior of client code that connected to the non-CDB containing this back end.
Operations that act on an entire non-CDB act in the same way on an entire CDB, for example, when using Oracle Data Guard and database backup and recovery. Thus, the users, administrators, and developers of a non-CDB have substantially the same experience after the database has been consolidated.
The following figure depicts the databases in Figure 17-2 after consolidation onto one computer. The DBA team is reduced from five to three, with one CDB administrator managing the CDB while two PDB administrators split management of the PDBs.
Figure 17-3 Single CDB
Description of «Figure 17-3 Single CDB»
Using the multitenant architecture for database consolidation has the following benefits:
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 on a single server share one database instance.
Easier and more rapid movement of data and code
By design, you can plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. The implementation technique for plugging and unplugging is similar to the transportable tablespace technique.
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 database, PDBs mimic the behavior of non-CDBs. For example, a PDB administrator can flush the shared pool or buffer cache in the context of a PDB without affecting other PDBs in the CDB.
Secure separation of administrative duties
A user account is common, which means that it can connect to any container on which it has privileges, or local, which means that it is restricted to a specific PDB. A CDB administrator can use a common user account to manage the CDB. A PDB administrator uses a local 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 database than for multiple databases. It is easier to size one SGA than 100 SGAs.
Support for Oracle Database Resource Manager
In any shared resource environment, administrators must manage system resources to provide a predictable environment for users and address unexpected or transient resource contention. To address these issues, and to provide resource usage monitoring, you can use Oracle Database Resource Manager.
Fewer database patches and upgrades
It is easier to apply a patch to one database than to 100 databases, and to upgrade one database than to upgrade 100 databases.
Benefits of the Multitenant Architecture for Manageability
The multitenant architecture has benefits beyond database consolidation. These benefits derive from storing the data and data dictionary metadata specific to a PDB in the PDB itself rather than storing all dictionary metadata in one place. By storing its own dictionary metadata, a PDB becomes easier to manage as a distinct unit, even when only one PDB resides in a CDB.
Benefits of data dictionary separation include the following:
Easier migration of data and code
For example, instead of upgrading a CDB from one database release to another, you can unplug a PDB from the existing CDB, and then plug it into a newly created CDB from a higher release.
Easier testing of applications
You can develop an application on a test PDB and, when it is ready for deployment, plug this PDB into the production CDB.
Path to Database Consolidation
For the duration of its existence, a database is either a CDB or a non-CDB. You cannot transform a non-CDB into a CDB, or a CDB into a non-CDB. You must define a database as a CDB at creation, and then create PDBs and application containers within this CDB.
The basic path to database consolidation is:
Creation of a CDB
Along with the root container ( CDB$ROOT ), Oracle Database automatically creates a seed PDB ( PDB$SEED ). The following graphic shows a newly created CDB:
Figure 17-4 CDB with Seed PDB
Description of «Figure 17-4 CDB with Seed PDB»
Example 17-1 Determining Whether a Database Is a CDB
The following simple query determines whether the database to which an administrative user is currently connected is a non-CDB, or a container in a CDB:
Oracle Database Administrator’s Guide to learn how to create a CDB using DBCA or the CREATE DATABASE statement
Oracle Database SQL Language Reference for more information about specifying the clauses and parameter values for the CREATE DATABASE statement
Creation of a PDB
The CREATE PLUGGABLE DATABASE SQL statement creates a PDB. This PDB automatically includes a full data dictionary including metadata and internal links to system-supplied objects in the root. You can only create a PDB in a CDB and not within another PDB.
The following figure depicts the options for creating a PDB.
Figure 17-5 Options for Creating a PDB
Description of «Figure 17-5 Options for Creating a PDB»
Figure 17-6 CDB with Six PDBs
Description of «Figure 17-6 CDB with Six PDBs»
The following sections describe the different techniques for creating PDBs.
Creation of a PDB from Seed
The following figure illustrates creation from the seed.
Figure 17-7 Creation of a PDB from the Seed PDB
Description of «Figure 17-7 Creation of a PDB from the Seed PDB»
The following SQL statement creates a PDB named hrpdb from the seed using Oracle Managed Files:
Creation of a PDB by Cloning a PDB or a Non-CDB
You can use the CREATE PLUGGABLE DATABASE statement to clone a source PDB or non-CDB and plug the clone into the CDB.
The source can be a PDB in a local or remote CDB, or starting in Oracle Database 12 c Release 1 (12.1.0.2), it can also be a remote non-CDB. This technique copies the files associated with the source PDB or non-CDB to a new location and associates the copied files with the new PDB.
The following graphic illustrates cloning a PDB from an existing PDB in the same CDB.
Figure 17-8 Cloning a PDB from a PDB in the Same CDB
Description of «Figure 17-8 Cloning a PDB from a PDB in the Same CDB»
If the underlying file system of a PDB supports storage snapshots, then you may specify the SNAPSHOT COPY clause to clone a PDB using storage snapshots. In this case, Oracle Database does not make a complete copy of source data files, but creates a storage-level snapshot of the underlying file system, and uses it to create PDB clones. Snapshot copies make cloning almost instantaneous.
The following SQL statement clones a PDB named salespdb from the plugged-in PDB named hrpdb :
Oracle Database Administrator’s Guide to learn how to create a PDB by cloning an existing PDB or non-CDB
Creation of a PDB by Plugging In an Unplugged PDB
In its unplugged state, a PDB is a self-contained set of data files and an XML metadata file. This technique uses the XML metadata file that describes the PDB and the files associated with the PDB to associate it with the CDB.
The following figure illustrates plugging in an unplugged PDB.
Figure 17-9 Plugging In an Unplugged PDB
Description of «Figure 17-9 Plugging In an Unplugged PDB»
The following SQL statement plugs in a PDB named financepdb 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 renamed:
Oracle Database Administrator’s Guide to learn how to perform this technique
Creation of a PDB from a Non-CDB
You can move a non-CDB into a PDB.
You can accomplish this task in the following ways:
Executing DBMS_PDB.DESCRIBE on a non-CDB in Oracle Database 12 c
See Oracle Database Administrator’s Guide to learn how to perform this technique.
Using Oracle Data Pump with or without transportable tablespaces
See Oracle Database Administrator’s Guide to learn how to perform this technique.
Using Oracle GoldenGate replication
You replicate the data from the non-CDB to a PDB. When the PDB becomes current with the non-CDB, you switch over to the PDB.
See Oracle Database Administrator’s Guide to learn how to perform this technique.
The following figure illustrates running the DBMS_PDB.DESCRIBE function on a non-CDB, and then creating a PDB using the non-CDB files.
Figure 17-10 Creating a PDB from a Non-CDB
Description of «Figure 17-10 Creating a PDB from a Non-CDB»
Oracle Database Administrator’s Guide for an overview of how to create PDBs
Что такое pdb oracle
You can create and administer multitenant container databases (CDBs), pluggable databases (PDBs), and application containers.
Changes in Oracle Database Release 21c for Oracle Multitenant Administrator’s Guide
The following features are new in this release.
A multitenant container database is the only supported architecture in Oracle Database 21c. While the documentation is being revised, legacy terminology may persist. In most cases, «database» and «non-CDB» refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, «non-CDB» refers to a non-CDB from a previous release.
Changed scope for Oracle Multitenant Administrator’s Guide
Starting in Oracle Database 21c, this publication 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 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. Oracle Database Security Guide explains how to secure multitenant databases.
Adopting a non-CDB as a PDB using Replay Upgrade
When adopting a non-CDB from a previous release as a PDB in an Oracle Database 21c CDB, the upgrade occurs automatically when the PDB is opened normally. The Replay Upgrade feature automatically captures necessary CREATE OR REPLACE statements, replays the statements only for changed objects, and converts the data dictionary. The replay mechanism is the same one used in application synchronization.
Replay Upgrade on PDB Open
Opening a PDB upgrades it automatically when a version mismatch occurs between the PDB and the CDB root. The Replay Upgrade on PDB Open optimization, which is the default, avoids manual error correction by re-executing statements stored in capture tables. The mechanism is the same used in application synchronization. Oracle Database 21c uses Replay Upgrade on PDB Open in the following scenarios:
A CDB from a previous release was upgraded to Oracle Database 21c, but a PDB in the CDB was not upgraded. If you open this PDB without the OPEN UPGRADE option, then the CDB automatically performs a Replay Upgrade of the PDB.
Namespace integration with Oracle Database
DbNest provides operating system resource isolation and management, file system isolation, and secure computing for PDBs. When DbNest is enabled, provisioning of a database instance occurs inside a nest, which is a type of isolated, hierarchical container. The database instance system resources are isolated from other instances. Files and directories are only accessible to the CDB or PDB for which they were configured.
Transparent Application Continuity in the Oracle Cloud
During planned maintenance, the database may decide that a session is unlikely to drain in the drain window. In this case, the database invokes Application Continuity and fails over the session automatically.
CPU_MIN_COUNT initialization parameter enhancements
CPU_MIN_COUNT expresses the minimum number of CPU threads required by the PDB or CDB. CPU resource manager uses the PDB-level CPU_MIN_COUNT value to set the PDB shares in the resource plan. For each PDB, CPU Resource Manager ensures fair access to the CPU, guarantees the minimum CPU, and enforces the maximum CPU.
JOB_QUEUE_PROCESSES initialization parameter enhancements
The default value for JOB_QUEUE_PROCESSES across all containers has changed its default from 4000 to an automatic value that depends on the number of sessions and CPU threads.
MAX_IDLE_BLOCKER_TIME initialization parameter enhancements
MAX_IDLE_BLOCKER_TIME sets the number of minutes that a session holding needed resources can be idle before it is a candidate for termination.
Expanded syntax for application synchronization
When applications depend on one another, synchronizing them in a single statement is necessary for functional correctness. Assume that you upgrade apexapp from 1.0 to 2.0, upgrade ordsapp from 1.0 to 2.0, and then upgrade apexapp to 3.0. The statement ALTER PLUGGABLE DATABASE APPLICATION apexapp, ordsapp SYNC replays the upgrades in sequence, upgrading apexapp to 2.0, ordsapp to 2.0, and then apexapp to 3.0. Synchronizing apexapp and then ordsapp in separate statements does not preserve the upgrade order.
New parameters for the Database Configuration Assistant (DBCA) silent mode commands
Multitenant Architecture
The multitenant architecture enables an Oracle database to be a CDB.
Every Oracle 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).
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 Database Instance and CDB»
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.
Figure 1-2 PDBs in a CDB
Description of «Figure 1-2 PDBs in a CDB»
Physically, MYCDB is an Oracle 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.
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 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 SaaS Use Case»
Benefits of the Multitenant Architecture
Creating separate PDBs and application containers within a single CDB provides benefits for manageability and performance.
Benefits of Consolidating Data into a Single CDB
Consolidating data into a single CDB has the following benefits:
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
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.
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
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.
Oracle Database Administrator’s Guide to learn more about the Resource Manager
Oracle Database Concepts to learn more about data dictionary separation
Overview of Multitenant Administration
Become familiar with basic concepts related to configuring and managing a multitenant environment.
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.
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.
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.
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.
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
Application Common Phenomena
Within an application container, names for local and application common phenomena must not conflict.
Application common users and roles
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 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.
Oracle Database Security Guide to learn more about common users and roles
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 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 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.
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.
Figure 1-6 Users and Schemas in a CDB
Description of «Figure 1-6 Users and Schemas in a CDB»
Common users have the following characteristics:
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.
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.
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.
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.
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.
Figure 1-7 Users and Schemas in a CDB
Description of «Figure 1-7 Users and Schemas in a CDB»
Oracle Database Security Guide to learn about common user accounts