Oracle multitenant что это
If you’ve worked with Oracle databases recently, you may have heard of the terms “pluggable database” and “container database”.
In this guide, you’ll learn:
- what these terms mean
- the benefits of this new architecture
- how to connect to a container or pluggable database
- how to switch between these databases
- how to create a container or pluggable database
- how to see information about a container or pluggable database
Let’s get into it.
Pluggable Database (PDB) Snapshot Carousel
From Oracle 18c onward it is possible to create automatically managed snapshots of a PDB, also know as a snapshot carousel.
You can read about PDB Snapshot Carousel here.
18c Onward
Oracle 18c XE allows up to three user-defined PDBs for free. All other editions have the same rules as 12.2 regarding the Multitant Option.
-
introduced for the first time. - Upgrade from 11.2 to 18c, including conversion to PDB.
- Multitenant : Logical Partitioning - Container Maps (docs).
- Multitenant : Relocation of Sessions During Planned Maintenance (docs).
- Multitenant : Parallel Statement Queuing at the PDB Level (docs).
- Multitenant : Split Mirror Clone PDBs (docs).
- Multitenant : Create a Keystore for Each Pluggable Database (docs).
12.2.0.1 Onward
From 12.2 onward we are allowed to have a Proxy PDB, Application Root Container and a single user-defined PDB (regular or Application PDB) inside a single CDB without having to pay for the Multitenant Option. Notice we are still limited to a single user-defined PDB. The Multitenant Option still entitles you to 2-252 PDBs on regular kit, or 2-4096 PDBs on Oracle engineered systems or Oracle Database Clouds Services.
Relocate a Pluggable Database (PDB)
From Oracle 12.2 onward it is possible to relocate a PDB, moving it from one CDB to another. This is significantly simpler than doing a conventional unplug/plugin.
You can read about the relocating PDBs here.
Containers
An Oracle CDB has many containers. A container is either a PDB or the root.
Here’s a diagram to represent it.
This diagram shows that the database contains the CDB. Inside the CDB are two containers:
- Root, named CDB$ROOT. This contains Oracle metadata and common users.
- Seed PDB, named PDB$SEED. This is a template that can be used to create new PDBs. You can’t add or modify objects in this PDB.
There are no other PDBs created by default in an Oracle database, if you’re using the full version of Oracle.
PDBs can be created by you (assuming you have the right privileges).
Here’s a diagram showing the same database with a new PDB created, called PDB1.
We’ll explain how to create a PDB later in this guide.
If you’re running Oracle XE (also known as Oracle Express), you have a PDB created already, called XEPDB1.
This is what it looks like as a diagram:
We can see the root container, the seed PDB, and the default PDB (called xepdb1) that comes with the database.
Unpluging and Plugging in Pluggable Databases (PDBs)
One of the most powerful features of the multitenant option is the ability to unplug a PDB from a CDB and plug it back into another CDB.
Not only does this allow databases to be moved easily, but it also provides an alternative way to patch and upgrade to future versions. An example of using unplug/plugin to perform a patch can be found here. A general discussion of the unplug/plugin mechanism is described here.
Conversion of a non-CDB database to a pluggable database involves getting a description the non-CDB database and using this to plug it into a CDB as a new PDB. This method is described here.
21c Onward
12.1.0.1 Onward
In 12.1 you can use a single PDB per CDB, also known as Lone-PDB, for free. If you want more than one PDB (2-252) you have to buy the Multitenant Option on top of Enterprise Edition.
Connect to an Oracle CDB
You can connect to an Oracle container database (CDB) in the same way as connecting to any other database.
If you’re a developer, you may not do this very often, as many of your connections and work will be done on a PDB.
In SQL Developer this is done by specifying:
- Username: a username that exists on the database, such as SYSTEM
- Password: the password for that user
- Service Name: this will depend on your database.
- If you’re using the Docker image: ORCLCDB.localdomain
- If you’re using Oracle Express: XE (yes, the service name for the container database in Oracle Express is XE)
Here’s the connection screen in SQL Developer if you want to connect to Oracle Express.
1.2 Benefits of the Multitenant Architecture
The multitenant architecture solves several problems posed by the traditional non-CDB architecture.
This section contains the following topics:
Large enterprises may use hundreds or thousands of databases. Often these databases run on different platforms on multiple physical servers.
Database consolidation is the process of consolidating data from multiple databases into one database on one computer. The Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications .
The multitenant architecture has benefits beyond database consolidation. These benefits derive from storing the data and metadata specific to a PDB in the PDB itself rather than storing all dictionary metadata in one place.1.2.1 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 can 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 1-4 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 1-4 Database Environment Before Database Consolidation
Description of "Figure 1-4 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.
1.2.2 Benefits of the Multitenant Architecture for Database Consolidation
Database consolidation is the process of consolidating data from multiple databases into one database on one computer. The Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications .
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 definition (for example, tables and PL/SQL packages) 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 definition is identical to the behavior of client code that connected to the non-CDB containing this application definition.
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 graphic depicts the databases in Figure 1-4 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 1-5 Single CDB
Description of "Figure 1-5 Single CDB"Starting in Oracle Database 12c Release 2 (12.2), you can create an application container that contains application PDBs. This approach enables you to create and manage an application within this container. Most benefits that apply to consolidation into a CDB also apply to consolidation within an application container.
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 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 database, PDBs mimic the behavior of non-CDBs. For example, if user error loses critical data, then a 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 account to manage a CDB or application container. 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.
An administrator uses a local account to manage an individual PDB.
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.
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.
1.2.3 Benefits of the Multitenant Architecture for Manageability
The multitenant architecture has benefits beyond database consolidation. These benefits derive from storing the data and 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. 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 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 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. This feature is analogous to the Flashback Database feature for a non-CDB.
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 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
In a multitenant environment, one concern is contention for system resources among the PDBs running on the same server. Another concern is limiting resource usage for more consistent, predictable performance. To address such resource contention, usage, and monitoring issues, use Oracle Database Resource Manager.
Benefits of Multitenant Architecture
So, Oracle uses this new CDB and PDB architecture. What are the benefits of this? Can’t you just create different databases or VMs?
- Better use of resources: PDBs and CDBs use resources on the server more effectively compared to VMs (which duplicate the operating system) and separate databases (which don’t share processes)
- Easier movement of data and code: if you need to move a pluggable database from one container database to another, this is quite easy
- Easier management and monitoring: for administrators, applying patches, upgrades, monitoring the database, performing backups, and other tasks are much easier.
The non-CDB architecture (the way the databases work before 12c) is available in recent versions, but it was deprecated in Oracle 12c and desupported in Oracle 20c.
Creating Pluggable Databases (PDBs)
Since the bulk of the working parts are already present in the root container, creating a new PDB is a comparatively quick and simple task. When creating a completely new PDP, the PDB is created as a copy of a seed PDB, so it only takes as long as the files take to copy.
Instead of creating a new PDB from the seed, you can clone an existing PDB.
It is also possible to create clones in a remote CDB.
A more detailed description of creating and cloning PDBs can be found here.
Multitenant Articles
The following articles provide more detailed explanations of some of the concepts described in this article.
Инсталляция
Ставим Oracle linux 6.4 c апдейтами, зависимости для oracle и поддержку ASM:
конфигурируем ASM и создаем ASM disk:
Инсталлируем Oracle Database 12c Release 1 Grid Infrastructure (12.1.0.1.0) for Linux x86-64 в режиме singl node. В discovery path -> /dev/oracleasm/disks.
Командами asmcmd или с помощью asmca создаем disk group (DATA) volume (DATAVOL) и ASM cluster filesystem c точкой монтирования (/data). Из под root монтируем ACFS и убеждаемся что все хорошо:Подключаемся к инстансу ASM и меняем режим совместимости:
В результате получаем базу-контейнер и с seed database (модельная база для создания pluggable database).
Проверяем что все получилось:Non-CDB Architecture Deprecated
With the release of Oracle Database (12.1.0.2), the non-CDB architecture has been deprecated. Some 12c features do not currently work with the multitenant architecture (see here), so depending on the features you require, you may still need the old pre-12c style instances.
Remember, using a single PDB does not require the Multitenant option, so lone-PDB setups can be used at no extra cost, allowing you to get familiar with the multitenant architecture.
From 12.2 onward we are allowed to have a Proxy PDB, Application Root Container and a single user-defined PDB (regular or Application PDB) inside a single CDB without having to pay for the Multitenant Option. Notice we are still limited to a single user-defined PDB.
Oracle Views
Oracle database contains many dynamic views in the data dictionary that are used to see information about objects.
These views had prefixes, such as dba_, all_, and user_. Each of these prefixes indicates views that show different levels of information.
After Oracle 12c and the CDB/PDB functionality, the information shown in each of these types of views was different. There is also a new series of views added which have the prefix of cdb_.
Here’s how they changed:
Prefix of View Before CDBs After CDBs cdb_ Did not exist All objects in all containers (root and all PDBs) dba_ All objects in the database All objects in the current container (root or PDB) all_ Objects accessible by the current user Objects accessible by the current user in the current container (root or PDB) user_ Objects owned by the current user Objects owned by the current user in the current container (root or PDB) You may not notice a difference in how you use these, but it’s good to know how they have changed.
Refreshable Pluggable Database (PDB) Switchover
From Oracle 18c onward it is possible to switchover a refreshable PDB.
You can read about refreshable PDB switchover here.
View Current Container Name
Showing the name of the container you are connected to is very handy, as it can help you decide what commands to run next or whether you need to switch containers.
This is done with the SYS_CONTEXT function. You can use the SHOW CON_NAME command, but this only works on SQL*Plus.
To find the container name, use the parameter of CON_NAME:
We’ve specified the Oracle DUAL table because we don’t need data from any table here.
When we run this on the CDB, we see this:
SYS_CONTEXT(‘USERENV’,’CON_NAME’) CDB$ROOT When we run this on a PDB, we see this:
SYS_CONTEXT(‘USERENV’,’CON_NAME’) ORCLPDB1 So, this can help us see the name of the container we’re running this on.
Switch Between Containers (CDB and PDB)
If you’ve connected to one of the containers, you can easily change your session to be connected to another container.
This means you can:
- change from a PDB to the CDB
- change from the CDB to a PDB
- change from one PDB to another PDB
This is helpful if you connect to the wrong container or want to work on a different container.
You can change containers by using the Alter Session command.
This will change your connection to the pdb1 database, which is a pluggable database.
If you’re on Oracle XE, your command may look like this:
To change to the CDB, you specify CDB$ROOT.
You can then run the SQL that you want to on the container you’re connected to.
12.1.0.2 Onward
Refreshable Pluggable Database (PDB)
From Oracle 12.2 onward it is possible to refresh a cloned PDB from the source PDB, provided it has only ever been opened in read-only mode.
You can read about refreshing PDBs here.
Non-CDB Architecture Desupported
At Oracle OpenWorld 2019 it was announced that the non-CDB architecture will be desupported from Oracle 20c onward. Oracle 21c is the first on-prem release that has the non-CDB architecture desupported.
Become familiar with the Oracle Multitenant option.
This chapter contains the following topics:
The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).
The multitenant architecture solves several problems posed by the traditional non-CDB architecture.
For the duration of its existence, a database is either a CDB or a non-CDB.
This topic lists the most important topics for understanding and using CDBs, and includes cross-references to the appropriate documentation.Ради чего все это затевалось
Мы имеем тестовую базу большого объема и хотим протестировать особо крупное изменение, затрагивающее структуру таблиц и требующее для проверки всех данных.
Клонируем тестовую базу в режиме snapshot:И смотрим что произошло на файловой системе:
Файлы данных всех табличных пространств кроме TEMP это ссылки на ACFS снапшот, и место на диске они не занимают. Узнать сколько реально занял снапшот можно так:
Чего и добивались — 286МB против более 3GB
Естественно, если активно начать изменять блоки клона со снапшотами, занимаемое им место вырастет.
После того как провели тестирование, удаляем ненужный клон:
Убеждаемся что место освободилось:
Connect to an Oracle PDB
If you want to connect to an Oracle pluggable database (PDB), you can do that in a similar way to a CDB.
There are a couple of things to remember:
- Select Service Name instead of SID. SID is only used if you want an alternative way to connect to a container database.
- The PDB must exist in order to connect to it. If you’re using Oracle XE, or the docker image on Docker Hub, one has been created.
- The connection to the PDB is done by specifying the service, not the PDB itself. They can often have the same names but may have different names.
In SQL Developer this is done by specifying:
- Username: a username that exists, such as one you have created or SYSTEM
- Password: the password for that user
- Service Name: the name of the service that runs the PDB.
- If you’re using the Docker image, ORCLPDB1.localdomain
- If you’re using Oracle Express: XEPDB1.
Here’s the connection screen for connecting to a PDB in Oracle Express.
Proxy Pluggable Database (PDB)
From Oracle 12.2 onward it is possible to create proxy PDB, which is a skeleton PDB that sends SQL across to a remote PDB to be processed. This allows you to have a local endpoint for a remote database.
You can read about proxy PDBs here.
Creating a Container Database (CDB)
To create a new CDB, use the Create Database command with the suffix Enable Pluggable Database.
This will create a new CDB, with a root container of CDB$ROOT and a new seed PDB of PDB$SEED.
If you omit the ENABLE PLUGGABLE DATABASE, then this new database is a non-CDB, and can never be changed to contain PDBs.
Conclusion
The Oracle multitenant architecture may seem confusing if it’s new to you, but the concept of a container DB and a pluggable DB can be understood easier after you work with it for a while.
If you’re a developer and work with an Oracle database, you may not notice any difference except your connection strings are different.
There are many advantages of working with pluggable databases for administrators.
I hope this article was helpful for you to understand CDBs and PDBs.
If you have any questions or comments, leave them in the comments section below.
Самым крупным нововведением недавно вышедшего Oracle 12c безусловно является Multitenant Architecture. Сам Oracle преподносит эту возможность в основном как средство консолидации и снижения расходов.
Суть технологии состоит в возможности запустить несколько независимых баз (pluggable database, PDB) в рамках одного инстанса (container database, CDB). Каждая база имеет свой набор схем и табличных пространств, но при этом у них общая SGA и один набор серверных процессов. Есть возможность клонировать pluggable database, как в рамках одного контейнера, так и между контейнерами. Вот эту возможность и будем использовать для создания копий тестовых баз и экономии ресурсов.
Задача — имеем большую систему, с большой базой. Нужно проводить тестирование изменений, причем изменений разрушительных — удаление/модификация таблиц. Как это делается сейчас — создаем новую базу, заливаем в нее минимально возможный набор схем и данных и проводим тестирование. Процесс сам по себе не быстрый, трудоемкий и всегда есть возможность ошибиться. Да и «минимальный набор данных» может быть не таким уж и маленьким.В документации на команду CREATE PLUGGABLE DATABASE, в разделе клонирования есть упоминание об опции SNAPSHOT COPY. Судя по описанию, при создании клона с опцией SNAPSHOT COPY, файлы данных клонируемой базы копироваться не будут. Для них будут
созданы copy on write снапшоты и место на диске будут занимать только измененные блоки клонированной базы. Создание клонов со снапшотами возможно либо на ACFS, либо на специализированных NAS .Эксперимент проводился в среде Oracle Virtualbox 4.2.14. Я не буду подробно описывать инсталляцию, она хорошо освещена в документации, буду останавливаться только на важных моментах.
Oracle Managed Files (OMF) and Multitenant
Oracle recommend the use of Oracle Managed Files (OMF) when using the multitenant architecture, as it simplifies a number of pieces of functionality. It seems the use of OMF is mandatory for some functionality, like the Application Containers functionality in Oracle 12.2.
Работа с клонами
Создаем каталог на ACFS /data/oradata, и меняем владельца на oracle. Меняем параметр db_create_file_dest на /data/oradata. Cоздаем клон который будет изображать тестовую базу:
На ACFS должен появиться каталог с буквенно-цифровым именем (случайным), содержащий наш PDB:
PDB после создания или рестарта контейнера нужно открывать:
Имитируем создание тестовой базы — создадим tablespace test размером 2G:
Убеждаемся что файл данных есть и размер его 2 гигабайта:
Application Containers
Oracle 12.2 introduces the concept of application containers, which act like a mini-root container. They can be used to centralise shared configuration and applications, which are used by their dependent application PDBs.
You can read about application containers here.
What is a Container Database and Pluggable Database?
In Oracle 12c, a new architecture or design of the databases was introduced, called “container databases” or “multitenant architecture”.
The Oracle database can function as a “multitenant container database”, otherwise known as a CDB. This CDB can include zero or more “pluggable databases”, or PDBs.
A PDB is a collection of schemas and objects that act like a “regular” database to applications and IDEs.
If you’ve been working with Oracle for a while and this CDB and PDB structure is new to you, then the simple answer is that a PDB is like a “regular database” that you work with.
But, it’s much more than that.
Views
The introduction of the multitenant option brings with it an extra layer of data dictionary views, allowing reporting across the root container and the pluggable databases (PDBs). Ignoring editions for the moment, prior releases had the following hierarchy.
With Oracle 12c, an extra layer is added to the hierarchy.
The views are described in the Reference Manual.
* The output of the CDB_ views is dependent on the container they are accessed from. When accessed from the root container, they do indeed present all the information from all the containers. When accessed from a PDB, they effectively act like the DBA_ views from within the container. This can be a little confusing at first.
Результат
В результате нам удалось сэкономить время и ресурсы. И не только дисковые, напомню, что все базы PDB используют один комплект процессов и одну SGA.
PS. Статья не претендует на полное описание Oracle Multitenant Architecture, рассмотрен лишь частный случай применительно к конкретной задаче.
Oracle 12c Release 1 (12.1) introduced the Multitenant option. This article provides a basic overview of the multitenant option, with links to more detailed articles on the functionality.
View Connection Information
We can expand on the use of the SYS_CONTEXT function to show the container ID and the database name.
This will show the following information when run on a CDB:
CON_NAME CON_ID DB_NAME CDB$ROOT 1 ORCLCDB When run on a PDB, here’s what you see:
CON_NAME CON_ID DB_NAME ORCLPDB1 3 ORCLPDB1 The CON_ID values are predetermined.
- 0 is for the whole multitenant database
- 1 is for the root container
- 2 is for PDB$SEED
- Values from 3 onwards are used for PDBs
Create a Pluggable Database (PDB)
To create a pluggable database, you need to be connected to the CDB with the container set to the root (which is the default). You must also have the Create Pluggable Database privilege.
To create a PDB:
This will create a new pluggable database called my_new_pdb. This will contain the full data dictionary and internal links to objects in the root container.
There are several other ways to create a PDB:
- Create a PDB from the PDB$SEED database
- Create a PDB from an existing PDB
- Create a PDB from a remote PDB
- Unplug a PDB from a CDB and plug it into a different CDB
Once the PDB is created, you can connect to it and begin working on it.
Overview
The multitenant option represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB).
- Container Database (CDB) : On the surface this seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (controlfiles, datafiles, undo, tempfiles, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.
- Pluggable Database (PDB) : Since the CDB contains most of the working parts for the database, the PDB only needs to contain information specific to itself. It does not need to worry about controlfiles, redo logs and undo etc. Instead it is just made up of datafiles and tempfiles to handle it's own objects. This includes it's own data dictionary, containing information about only those objects that are specific to the PDB. From Oracle 12.2 onward a PDB can, and should, have a local undo tablespace.
This split of the data dictionary between common objects, in the root container, and PDB-specific objects, in the PDB's data dictionary, is very important, because this separation is what gives the multitenant option its flexibility. From the perspective of the PDB, the data dictionary is the union of the root and PDB data dictionaries, so internally the PDB feels very much like a normal Oracle database. For example, the DBA_% and ALL_% views within the PDB appears the same as any non-CDB database.
View Services
You can see all of the services on the database, which are the names that are specified when you want to create a new connection. This is useful to get an idea of the PDBs on the database and to find the details if you want to create a new connection.
NAME PDB orclpdb1.localdomain ORCLPDB1 In this example, we can see the name of the service (which is what is used to connect to the database on the connection screen), and the name of the PDB that is used.
Miscellaneous
Oracle Multitenant. Part 1
Опция Multitenant - новая прекрасная опция 12c, которая стала возможна благодаря существенной переработке архитектуры Oracle. Вкратце (я не претендую тут на полноту описания) теперь вы можете запустить несколько экземпляров баз данных (Pluggable DB) в рамках одной физической базы данных (container или CDB). Экономия немедленно достигается за счет того, что:
- ну нужно больше размножать служебные процессы Oracle для каждого экземпляра
- единая SGAДополнительные плюсы - теперь вам необходимо выполнять резервное копирование одной СУБД, а не нескольких.
Прекрасная ссылка по теме - Tom Kyte. Tom часто употребляет термин консолидация, что так и есть на самом деле: собирая на один большой сервер базы данных с несколько маленьких сервизов, вы на самом деле начинаете больше утилизировать процессоры, а значит на одном большом сервере их вам понадобиться меньше (вопрос насколько, обсудим чуть дальше). На самом деле, Multitenant, как и любая EE опция -это средство экономии денег заказчика, при правильном применении. В примере выше - если на одном большом сервере нам нужно меньше процессоров - следовательно нам нужно меньше лицензий. Понятно, что в таком ключе (про лицензии) Oracle не очень хотел бы рассказывать, но подразумевается что заказчики это понимают и поэтому готовы платить на опцию Multitenant. Кстати, стоит она 37% стоимости Enterprise Editions, так что вот и простой вопрос насколько эффективна должна быть ваша консолидация, чтобы быть экономически оправданной.
Теперь рассмотрим несколько вопросов, возникающих в момент миграции обычных баз данных в Multitenant.
Используете ли вы прочие другие опции Enterprise Edition? Дело в том, что лицезировании Multitenant базы данных единое для всех включенных в нее баз данных. Скажем если, вы объединили две базы, одна из которых использовала partitioning на 4 ядрах, a другая нет (на 8 ядрах), теперь придется лицензировать partitioning на всех процессорах более крупного сервера (10 ядерного 4+8=12 - 2 экономии за счет консилидации). Ссылка по теме.
Character Set. Объединить в одну Multitenant базу данных удастся только БД с одинаковым character set.
Консолидация Баз данных Standard Edition - не поддерживается из-за лицензионных ограничений. Учитывая, насколько стали мощные 4-х сокетные сервера - разумное решение со стороны Oracle.
Multitenant и RAC
Я с большим интересом прочитал мнение Alex Gorbachev, что сравнивать обе опции это как сравнивать яблоки и апельсины. Ну действительно, RAC позволяет системам расти вширь (scale out), в то время как Multitenant вверх (scale in). Стоит ли их использовать вместе, как на том настаивает Oracle?
Для примера слева (источник) когда у вас есть 5 баз данных и 3 узла можно было вполне обойтись только RAC - никто не мешает вам зарегистрировать несколько баз данных в RAC. Другое дело, когда у вас есть двух-узловой кластер и вы собираете на нем десятки баз данных - тут Multitenant, вне всякого сомнения пригодится.
Обратная сторона медали - применение патчей на Multitenant database. Сложили все яйца в одну корзину? Теперь хотите применить патч? Он применится ко всем базам данных и, теоретически, может повлиять на несколько из них. Конечно это не очень продуктивная стратегия, применять патч ко всем БД в один момент времени. Выход есть, вам нужно создать еще одну container (CDB) базу данных, применить патч на ней, и по одной подключать к ней PDB из старой CDB. Вы тоже заметили, что в какой то момент сервера становится 2, что страшно обрадует Oracle? Я пока не знаю ответа, надеюсь на комментарии. Update 1. конечно можно создать новую CDB на том же сервере, и играть в домино с ресурсами, такими как память, например.
В части 2 мы рассмотрим собственно как посчитать выигрыш от консолидации и что ожидать от производительности в CDB.
View Information about the CDB and PDB
Once you’ve connected, you may want to know what container you’re connected to and a bit more information about the environment.
There are a few things you can see.
19c Onward
From 19c onward you are allowed to have 3 user-defined PDBs in a CDB without having to license the multitenant option, as described in the documentation here.
1.1 About the Multitenant Architecture
The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).
A CDB includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB . All Oracle databases before Oracle Database 12 c were non-CDBs.
This section contains the following topics:
A container is logical collection of data or metadata within the multitenant architecture.
You can use the same administration tools for both CDBs and non-CDBs.1.1.1 About Containers in a CDB
A container is logical collection of data or metadata within the multitenant architecture.
The following figure represents possible containers in a CDB.
Figure 1-1 Containers in a CDB
Description of "Figure 1-1 Containers in a CDB"Every CDB has the following containers:
Exactly one CDB root container (also called simply the root )
The CDB root is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong (see "Overview of Containers in a CDB" ). The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages (see "Data Dictionary Architecture in a CDB" ). A common user is a database user known in every container (see "Common Users in a CDB" ). The root container is named CDB$ROOT .
Exactly one system container
The system container includes the root CDB and all PDBs in the CDB. Thus, the system container is the logical container for the CDB itself.
Zero or more application containers
An application container consists of exactly one application root , and the PDBs plugged in to this root. Whereas the system container contains the CDB root and all the PDBs within the CDB, an application container includes only the PDBs plugged into the application root. An application root belongs to the CDB root and no other container.
Zero or more user-created PDBs
A PDB contains the data and code required for a specific set of features (see "PDBs" ). 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.
A PDB belongs to exactly zero or one application container. If a PDB belongs to an application container, then it is an application PDB . For example, the cust1_pdb and cust2_pdb application PDBs might belong to the saas_sales_ac application container, in which case they belong to no other application containers. An application seed is an optional application PDB that acts as a user-created PDB template, enabling you to create new application PDBs rapidly.
The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED . You cannot add or modify objects in PDB$SEED .
Example 1-1 CDB with No Application Containers
This example shows a simple CDB with five containers: the system container (the entire CDB), the CDB root, the PDB seed ( PDB$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 1-2 CDB with No Application Containers
Description of "Figure 1-2 CDB with No Application Containers"Example 1-2 CDB with an Application Container
In this variation, the CDB contains an application container named saas_sales_ac . Within the application container, the application PDB cust1_pdb supports an application for one customer, and the application PDB cust2_pdb supports an application for a different customer. The CDB also contains a PDB named hrpdb , which supports an HR application, but does not belong to an application container.
Figure 1-3 CDB with an Application Container
Description of "Figure 1-3 CDB with an Application Container"In this example, multiple DBAs manage the CDB environment:
A CDB administrator manages the CDB itself.
An application container administrator manages the saas_sales_ac container, including application installation and upgrades.
An application PDB administrator manages the two PDBs in the saas_sales_ac container: cust1_pdb and cust2_pdb .
A PDB administrator manages hrpdb .
1.1.2 About User Interfaces for the Multitenant Architecture
You can use the same administration tools for both CDBs and non-CDBs.
Table 1-1 Tools in a Multitenant Environment
SQL*Plus and SQL Developer for command-line access
SQL*Plus is an interactive and batch query tool that is installed with Oracle Database.
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 12c targets, including PDBs, CDBs, and non-CDBs.
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
Oracle Database Performance Tuning Guide to learn more about using EM Express for managing CDBs and PDBs
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 Database Concepts for more information about tools for database administrators
Container Database (CDB) Fleet Management
From Oracle 18c onward it is possible to monitor multiple container databases centrally as a fleet.
You can read about CDB Fleet Management here.
Читайте также: