Administering a CDB

Active Session History (ASH)

ASH collects information about active database sessions. You can use this information to analyze and identify performance issues.

Most of the ASH data is stored in memory. A small percentage of the ASH data samples are stored in the CDB root.

ASH data related to a PDB is not included if the PDB is unplugged.

Oracle Database 2 Day + Performance Tuning Guide

Oracle Database Performance Tuning Guide

An alert is a notification of a possible problem.

Threshold settings that pertain to a PDB are stored in the PDB.

Alerts posted when thresholds are violated are enqueued into the alert queue in the CDB root.

Threshold settings that pertain to a PDB are included if the PDB is unplugged. Alerts related to a PDB are not included if the PDB is unplugged.

Automated Database Maintenance Tasks

Automated database maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. Automated tasks include automatic optimizer statistics collection, Automatic Segment Advisor tasks, and Automatic SQL Tuning Advisor tasks.

The ENABLE_AUTOMATIC_MAINTENANCE_PDB initialization parameter can enable or disable the running of automated maintenance tasks for all the PDBs in a CDB or for individual PDBs in a CDB.

The AUTOTASK_MAX_ACTIVE_PDBS initialization parameter limits the number of PDBs that can schedule automated maintenance tasks at the same time (during a maintenance window).

Data related to a PDB is stored in the PDB for automatic optimizer statistics collection and the Automatic Segment Advisor. This data is included if the PDB is unplugged.

Automatic SQL Tuning Advisor runs only in the CDB root. See the SQL Tuning Advisor row in this table for information about data collected by Automatic SQL Tuning Advisor.

Oracle Database Reference for information about the ENABLE_AUTOMATIC_MAINTENANCE_PDB initialization parameter

Oracle Database Reference for information about the AUTOTASK_MAX_ACTIVE_PDBS initialization parameter

Automatic Database Diagnostic Monitor (ADDM)

ADDM can diagnose the performance of a CDB or PDB and determine how identified problems can be resolved.

  • AWR data stored inside the PDB through an AWR snapshot taken inside the PDB

  • AWR data from a non-CDB, CDB root, or PDB that is imported into the AWR storage of a PDB

  • AWR data stored in the root container through an AWR snapshot taken in root

Before the start of the analysis, ADDM determines the source of the AWR data (PDB or CDB root) and applies the rules applicable to each data type.

Automatic ADDM for a PDB is enabled only when automatic snapshots are enabled for the PDB.

Oracle Database 2 Day DBA

Oracle Database Performance Tuning Guide

Automatic Optimizer Statistics Collection

Automatic optimizer statistics collection gathers optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.

When an automatic optimizer statistics collection task gathers data for a PDB, it stores this data in the PDB. This data is included if the PDB is unplugged.

«Using Oracle Resource Manager for PDBs«

Oracle Database SQL Tuning Guide

Automatic Segment Advisor

The Automatic Segment Advisor identifies segments that have space available for reclamation and makes recommendations on how to defragment those segments.

When Automatic Segment Advisor gathers data for a PDB, it stores this data in the PDB. This data is included if the PDB is unplugged.

«Using Oracle Resource Manager for PDBs«

Automatic Workload Repository (AWR)

The AWR collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is stored in the database. The gathered data can be displayed in both reports and views.

AWR reports can be generated in the CDB root or in any PDB. AWR reports generated in the CDB root pertain to the entire CDB, while AWR reports generated when a PDB is the current container only pertain to that PDB.

AWR data generated in the CDB root is stored in the CDB root. AWR data generated in a PDB is stored in the PDB.

When a PDB is unplugged, AWR data stored in the CDB root is not included.

When a PDB is unplugged, AWR data stored in the PDB is included.

Oracle Database Performance Tuning Guide

Database Replay is a feature of Oracle Real Application Testing. Database Replay captures the workload for a CDB or PDB and replays it exactly on a test database.

Capture files are always stored in operating system files, regardless of whether the capture and replay is at the CDB level or PDB level.

Oracle Database Testing Guide

Optimizer Statistics Advisor

Optimizer Statistics Advisor analyzes how statistics are being gathered and suggests changes that can be made to fine tune statistics collection.

Data related to a PDB is stored in the PDB for Optimizer Statistics Advisor. This data is included if the PDB is unplugged.

Oracle Database SQL Tuning Guide

SQL Management Base (SMB)

SMB stores statement logs, plan histories, SQL plan baselines, and SQL profiles in the data dictionary.

SMB data related to a PDB is stored in the PDB. The SMB data related to a PDB is included if the PDB is unplugged.

Oracle Database SQL Tuning Guide

SQL Performance Analyzer (SPA)

SPA can analyze the SQL performance impact of SQL tuning and other system changes. SPA is often used with Database Replay.

Oracle Database Testing Guide

SQL Tuning Sets (STS)

You can use an STS to tune a group of SQL statements or test their performance using SPA.

An STS can be stored in the CDB root or in any PDB. If it is stored in the CDB root, then you can load SQL statements from any PDB into it.

When a PDB is unplugged, an STS stored in the CDB root is not included, even if the STS contains SQL statements from the PDB.

When a PDB is unplugged, an STS stored in the PDB is included.

Oracle Database SQL Tuning Guide

SQL Tuning Advisor

SQL Tuning Advisor optimizes SQL statements that have been identified as high-load SQL statements.

Automatic SQL Tuning Advisor data is stored in the CDB root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged.

Oracle Database 2 Day + Performance Tuning Guide

Oracle Database SQL Tuning Guide

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.

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.

Содержание
  1. Containers
  2. Benefits of Multitenant Architecture
  3. Connect to an Oracle CDB
  4. Connect to an Oracle PDB
  5. Switch Between Containers (CDB and PDB)
  6. View Information about the CDB and PDB
  7. View Services
  8. View Current Container Name
  9. View Connection Information
  10. Creating a Container Database (CDB)
  11. Create a Pluggable Database (PDB)
  12. Oracle Views
  13. Conclusion
  14. Challenges
  15. Non-CDB Architecture
  16. Other Benefits of Multitenant Architecture
  17. Oracle Multitenant Container Database
  18. Configurations
  19. Multitenant configuration
  20. Single-tenant configuration
  21. Non-CDB
  22. Database Objects in a non-CDB
  23. User-Added Objects to a non-CDB
  24. SYSTEM Objects in the USER Container
  25. Provisioning a Pluggable Database
  26. Multitenant Container Database Architecture
  27. Containers
  28. CDB root
  29. Pluggable database (PDBs)
  30. Tools
  31. Data Dictionary and Dynamic Views
  32. Terminology
  33. Impacts
  34. Creating a CDB
  35. Creating a CDB: Using SQL*Plus
  36. New Clause: SEED FILE_NAME_CONVERT
  37. New Clause: ENABLE PLUGGABLE DATABASE
  38. Oracle Managed Files
  39. PDB_FILE_NAME_CONVERT Instance Parameter
  40. After CDB Creation: What’s New in CDB
  41. Data Dictionary Views: DBA_xxx
  42. Data Dictionary Views: CDB_xxx
  43. Data Dictionary Views: Examples
  44. Data Dictionary Views: V$xxx Views
  45. After CDB Creation: To do List
  46. Automatic Diagnostic Repository
  47. Automatic Diagnostic Repository: alert.log File
  48. Provisioning New Pluggable Databases
  49. Tools
  50. Create New PDB from PDB$SEED
  51. Summary
  52. Steps: With FILE_NAME_CONVERT
  53. Steps: Without FILE_NAME_CONVERT
  54. Using OS authetication
  55. Connect with service
  56. Switching to CDB from PDB
  57. How to Connect to PDBs with Same Name in Different CDBs

Containers

An Oracle CDB has many containers. A container is either a PDB or the root.

Here’s a diagram to represent it.

CDB

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.

With PDB

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:

XEPDB1

We can see the root container, the seed PDB, and the default PDB (called xepdb1) that comes with the database.

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?

Well, you can, but the benefits of using this architecture are:

  • 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.

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.

connection_xe_cdb

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.

connection_xe_pdb

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.

ALTER SESSION SET container=pdb1;

This will change your connection to the pdb1 database, which is a pluggable database.

Дополнительно:  Must not run with sudo while trying to create a runner using github-actions

If you’re on Oracle XE, your command may look like this:

ALTER SESSION SET container=xepdb1;

To change to the CDB, you specify CDB$ROOT.

ALTER SESSION SET container=CDB$ROOT;

You can then run the SQL that you want to on the container you’re connected to.

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.

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.

SELECT name, pdb
FROM v$services;

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.

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:

SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
FROM dual;

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:

When we run this on a PDB, we see this:

So, this can help us see the name of the container we’re running this on.

View Connection Information

We can expand on the use of the SYS_CONTEXT function to show the container ID and the database name.

SELECT
SYS_CONTEXT('USERENV','CON_NAME') AS con_name,
SYS_CONTEXT('USERENV','CON_ID') AS con_id,
SYS_CONTEXT('USERENV','DB_NAME') AS db_name
FROM dual;

When run on a PDB, here’s what you see:

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

Creating a Container Database (CDB)

To create a new CDB, use the Create Database command with the suffix Enable Pluggable Database.

CREATE DATABASE … 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.

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:

CREATE PLUGGABLE DATABASE my_new_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.

Oracle Views

Oracle database contains many dynamic views in the data dictionary that are used to see information about objects.

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:

You may not notice a difference in how you use these, but it’s good to know how they have changed.

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.

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.


Challenges

Starting with Oracle Database 12c, the multitenant architecture enables you to have many pluggable databases inside a single Oracle Database instance.

What is the benefit of using the multitenant architecture in Oracle Database 18c?

Currently, many Oracle customers have large numbers of “departmental” applications built on Oracle RDBMS.

  • These applications rarely use a significant percentage of the hardware on which they are deployed. A significant number of instances and the amount of storage allocation for all these small databases prevent these from being placed on the same physical and storage server.
  • Moreover, they are typically not sufficiently complex to require 100 percent of the attention of a full-time administrator.
  • To better exploit hardware and DBA resources, customers would prefer to have most of these departmental applications consolidated onto a single Oracle RDBMS deployment.

The multitenant architecture allows DBAs to consolidate large numbers of small departmental database applications into a single, larger RDBMS installation.

Non-CDB Architecture

When you need to upgrade your applications to a new version, you have to upgrade each database, which is time-consuming for the DBA.

Consolidating many non-CDB databases onto a single platform reduces instance overhead, avoids redundant copies of data dictionaries, and consequently storage allocation. It benefits from fast provisioning, time-saving upgrading, and better security through separation of duties and application isolation. The new multitenant database that consolidates databases together is a multitenant container database or CDB and a database consolidated within a CDB, a pluggable database, or PDB.

  • No application change and very fast provisioning: A new database can be provisioned quickly. A clone of a populated database can be created quickly. A populated database can be quickly unplugged from its CDB on one platform and quickly plugged into a CDB on a different platform. A non-CDB can quickly be plugged into a CDB.
  • Fast upgrade and patching of the Oracle Database version: The cost (time taken and human effort needed) to upgrade many PDBs is the cost of upgrading a single Oracle Database occurrence. You can also upgrade a single PDB by unplugging it and plugging it into a CDB at a different Oracle database version.
  • Secure separation of duties: The administrator of an application can perform the required tasks by connecting to the PDB that implements its back end. However, a user who connects to a PDB cannot see other PDBs. To manage PDBs as entities (for example, to create, drop, or unplug or plug one), a system administrator must connect to the CDB. A user connecting to a CDB must have special privileges.
  • Isolation of applications: This task may not be achieved manually unless you use Oracle Database Vault, for example. A good example of isolation is dictionary separation enabling an Oracle database to manage the multiple PDBs separately from each other and from the CDB itself.

Other Benefits of Multitenant Architecture

  • The multitenant architecture ensures the backward-compatibility principle. An example is the data dictionary views. The DBA_OBJECTS view shows the same results in a PDB as in a non-CDB for a particular application.
  • The multitenant architecture is designed to be fully interoperable with RAC. Each instance in an Oracle RAC opens the CDB as a whole. A session sees only the single PDB it connects to.
  • Enterprise Manager integrates CDBs and models the separation of duties of the CDB administrator and the PDB administrator.
    • A CDB can be defined as a target. An Enterprise Manager user can be given the credentials to act as a CDB administrator in such a target.
    • A PDB can be set up as a subtarget of a CDB target. An Enterprise Manager user can be given the credentials to act as a PDB administrator in such a target. An Enterprise Manager user that has been set up with the credentials to act as a PDB administrator for a particular PDB is able to connect to that one PDB and is unaware of the existence of peer PDBs in the same CDB. Moreover, when the intention is to carry out the duties of an application administrator, this Enterprise Manager user is unaware that the environment is a CDB and not a non-CDB.
  • Resource Manager is extended with new between-PDB capabilities to allow the management of resources between the PDBs within a CDB. The backward-compatibility principle implies that Resource Manager must function in exactly the same way within a PDB as it does in a non-CDB.
  • When you upgrade a whole CDB with n PDBs, you achieve the effect of upgrading n non-CDBs for the cost of upgrading one non-CDB.

Oracle Multitenant Container Database

Oracle Multitenant Container Database architecture

It is easy to plug non-CDBs into a CDB. A CDB avoids redundancy of:

  • Background processes
  • Memory allocation
  • Oracle metadata in several data dictionaries

A CDB that groups several applications ends up with one instance. This instance will have one set of background processes, one SGA allocation, and one data dictionary in the CDB root container, common for all PDBs. Each PDB will maintain its own application data dictionary.

When you must patch or upgrade an application, you can perform maintenance operation only once on the CDB. Consequently, all the applications associated with the CDB will be patched or updated at the same time.

Configurations

What are the possible instances of database configurations?

  • Each database instance can be associated with one and only one non-CDB or multitenant container database.
  • In an Oracle RAC environment, several instances can be associated to a non-CDB or multitenant container database.
  • An instance is associated with an entire CDB.

There are three possible configuration options:

Multitenant configuration

Typically more than one PDB per CDB, but can hold zero, one, or many PDBs at any one time, taking advantage of the full capabilities of the Multitenant architecture, which requires the licensed Oracle Multitenant option.

container database configurations oracle

Single-tenant configuration

The special case of the Multitenant architecture, which does not require the licensed option.

Non-CDB

The Oracle Database 11g architecture

Database Objects in a non-CDB

User-Added Objects to a non-CDB

  • object definitions
  • User definitions
  • PL/SQL code
  • Other user-created objects

SYSTEM Objects in the USER Container

Each container has a SYSTEM tablespace that holds a data dictionary.

  • There is a dictionary in the Oracle metadata–only container that has the metadata for the Oraclesupplied objects.
  • There is a dictionary in the user container holding the user metadata.

One of the goals of the multitenant architecture is that each container has a one-to-one relationship with an application.

The Oracle objects could have been duplicated in each PDB, but that takes a lot of space and would require every PDB to be upgraded each time an Oracle-supplied object changes, for example, with patches. The Oracle-supplied objects reside in a container called the CDB root container, which is named CDB$ROOT.

Pointers from a PDB to the Oracle-supplied objects allow the “system” objects to be accessed without duplicating them in the PDB. The PDB has the pieces it needs to be a complete environment for a database application. The application can run in the PDB just as it does in a non-CDB.

Provisioning a Pluggable Database

To create a new PDB, use the provided CDB seed PDB. This CDB seed container is named PDB$SEED and is a part of every CDB. When you create a new PDB, the CDB seed PDB is cloned and gives the new PDB the name you specify. This operation is very fast. It is measured in seconds. The time that is taken is mostly for copying the files.

There are different methods to provision pluggable databases:

  • Create a new PDB from the PDB$SEED pluggable database: This scenario is useful, for example,for a new application implementation.
  • Create a new PDB from a non-CDB: Plug the non-CDBs in a CDB as PDBs as part of migration strategy. It is also a good way to consolidate the non-CDBs into a CDB.
  • Clone a non-CDB: Clone the non-CDBs in a CDB as PDBs, as part of migration strategy. This is a good way to keep the non-CDB and therefore have the opportunity to compare the performance between the new PDB and the original non-CDB or at least wait until you consider that the PDB can work appropriately.
  • Clone a PDB from another PDB into the same or another CDB: An example of this method is application testing. Relocate a PDB into another CDB so as to dispatch resources.
  • Plug an unplugged PDB into another CDB: For example, instead of upgrading a multitenant container database from one release to another, you can unplug a pluggable database from one Oracle Database release and then plug it into a newly created multitenant container database from a higher release.
  • Proxy a PDB: A proxy PDB provides fully functional access to another PDB in a remote CDB. This feature enables you to build location-transparent applications that can aggregate data from multiple sources that are in the same data center or distributed across data centers.

Multitenant Container Database Architecture

Oracle Multitenant Container Database architecture

The image above shows a CDB with four containers: the CDB root, the CDB seed, and two PDBs. The two applications use a single instance and are maintained separately.

Дополнительно:  How to Root and Unroot Acer Iconia One 10 B3-A30

At the physical level, the CDB has a database instance and database files, just as a non-CDB does.

  • The redo log files are common for the whole CDB. The information it contains is annotated with the identity of the PDB where a change occurs. Oracle GoldenGate can understand the format of the redo log for a CDB. All PDBs in a CDB share the ARCHIVELOG mode of the CDB.
  • The control files are common for the whole CDB. The control files are updated to reflect any additional tablespace and datafiles of plugged PDBs.
  • An UNDO tablespace is by default local in each container. It is possible to have a single UNDO tablespace shared by all containers. In this case, there is one UNDO tablespace per instance in a RAC database.
  • The CDB root or a PDB can have only one default temporary tablespace or tablespace group. Each PDB can have temporary tablespaces for use by local or common users in the PDB.
  • Each container has its own data dictionary stored in its proper SYSTEM tablespace, containing its own metadata, and a SYSAUX tablespace.
  • The PDBs can create tablespaces within the PDB according to application needs.
  • Each datafile is associated with a specific container, named CON_ID.

Containers

There is only one CDB seed PDB in a CDB. The CDB seed PDB is a system-supplied template that is used to create new PDBs. A CDB can contain up to 4,096 PDBs, including the CDB seed, the services being limited to 10,000. The V$CONTAINERS view displays all PDBs, including the CDB root and the CDB seed.

CDB root

  • The first mandatory container created at CDB creation
  • Oracle system–supplied common objects and metadata
  • Oracle system–supplied common users and roles

Pluggable database (PDBs)

  • Tablespaces (permanent and temporary)
  • Schemas / Objects / Privileges
  • Created / cloned / unplugged / plugged / proxied
  • Particular PDB: CDB seed (PDB$SEED)used for fast provisioning of a new PDB

Tools

There are different tools to create and upgrade container databases. As shown in the table below, you can create a new CDB or new PDBs either using SQL*Plus or Database Configuration Assistant (DBCA) or during the installation of Oracle Database 18c. SQL Developer and EM Cloud Control allow you to create pluggable databases.

After you create a CDB, you can use views to explore the instance, database architecture, files, and pluggable databases of the CDB. Query views directly with SELECT statements using SQL*Plus or indirectly using GUI tools such as Enterprise Manager or SQL Developer.

You can upgrade an Oracle Database release 12c CDB to an Oracle Database 18c CDB with Enterprise Manager or Database Upgrade Assistant (DBUA).

Note: Oracle Enterprise Manager Database Express cannot be used to create a CDB, but it can be used to create PDBs and explore PDBs architecture or CDBs structures by using different port configurations

Data Dictionary and Dynamic Views

For backward compatibility, DBA views show the same results in a PDB as in a non-CDB: DBA_OBJECTS shows the objects that exist in the PDB from which you run the query. This implies, in turn, that although the PDB and the CDB root have separate data dictionaries, each data dictionary view in a PDB shows results fetched from both of these data dictionaries. The DBA_xxx views in the CDB root shows, even in a populated CDB, only the Oracle-supplied system—as is seen in a freshly created non-CDB.

The same backward-compatibility principle also implies to each of the familiar V$ views. For example:

SQL> select OBJECT_ID, ORACLE_USERNAME, LOCKED_MODE, CON_ID from V$LOCKED_OBJECT;

OBJECT_ID    ORACLE_USERNAME   LOCKED_MODE  CON_ID
----------   ----------------  -----------  -------
     83711   SYS               3            3          <--- PDB1
     83710   DOM               3            4          <--- PDB2

Terminology

  • In a non-CDB, the DBA is responsible for all administrative tasks at the database level.
  • In a CDB, there are different levels of administration:
    • The DBA responsible for administering the CDB instance, the CDB root, and all PDBs
    • The DBAs responsible for administering their respective PDB
  • Common users, roles, and profiles exist in all containers and have the same name throughout these containers. Local users, roles, and profiles have a unique name for the container (PDB) in which they reside.
  • Common privileges are privileges that are «commonly» granted for all containers in the CDB, rather than privileges that are granted locally within a PDB.
  • Common objects exist in Oracle-supplied schemas. Local objects are created in PDBs in local schemas.
  • CDB resource management works at the CDB level, and PDB resource management works at the PDB level.
  • Audit policies can be created in the CDB root and also in each PDB. There is the same concept for encryption master keys and for Database Vault realms and command rules.
  • XStream Out is only available at CDB level and XStream In only at PDB level. XStream consists of Oracle Database components and application programming interfaces (APIs) that enable client applications to receive data changes from an Oracle database and send data changes to an Oracle database. XStream Out provides Oracle Database components and APIs that enable you to share data changes made to an Oracle database with other systems. XStream In provides Oracle Database components and APIs that enable you to share data changes made to other systems with an Oracle database.

Impacts

— If the CDB has a unicode database character set of AL32UTF8, the CDB can contain PDBs with different database character sets, because all character sets can be converted to AL32UTF8. Character set of an existing PDB can be changed to any compatible character set using existing database character set migration steps. There is only one single spfile for the CDB. PDB parameters values are stored in a dictionary table.

— Use a database link to access an object in another PDB.

SQL> CREATE PLUGGABLE DATABASE pdb1 … STANDBYS=(stdby1,stdby2);

— In Oracle Database Vault, each PDB has its own Database Vault metadata. Database Vault constructs, such as realms, are isolated within a PDB. Oracle Database release 12.2 introduced protection on common objects with common realms and command rules.

— Each PDB has its own master key used to encrypt data in the PDB. The TDE master encryption key must be transported from the source database keystore to the target database keystore when a PDB is moved from one host to another. For column encryption, each PDB maintains its own ENC$, which is not a metadata-linked object.

— XStream is a programmatic interface to allow a client application access to the changes in the database, known as XStream Outbound Server. XStream Inbound Server allows a client application to feed changes into the database and takes advantage of the apply process available in the database. Oracle GoldenGate is the logical replication, and XStream is licensed via the Oracle GoldenGate (OGG) license. Capturing changes from the database must always be from a CDB root. The XStream outbound can be configured to capture changes from a PDB or the entire CDB. Applying changes via Oracle GoldenGate is done per PDB. An XStream inbound server is configured to apply changes into a specific PDB and performs all of its work within the context of the PDB. Support in XStream and Oracle GoldenGate applies with no specific restrictions.

— Logminer ad hoc query (V$LOGMNR_CONTENTS, DBMS_LOGMNR) supports customer common objects in PDBs just as they support local objects in PDBs.


Creating a CDB

The steps required to create a new CDB, using either DBCA or SQL*Plus, are the same.

  • The first step, as for any database, non-CDB or CDB, consists of configuring an instance with an init.ora parameter file and then starting the instance.
  • The second step is the creation of the CDB using the CREATE DATABASE command with a new clause ENABLE PLUGGABLE DATABASE specifying that the database is a multitenant container database and not a non-CDB. The operation creates the controlfiles during the mount phase, the redo log files, and CDB root datafiles during the open phase. The CDB root datafiles are used for the SYSTEM tablespace containing the Oracle-supplied metadata and data dictionary, the SYSAUX tablespace for AWR, and the UNDO tablespace. It also creates the CDB seed with its own datafiles used for the SYSAUX, SYSTEM and UNDO tablespaces. You may use the new clause SEED FILE_NAME_CONVERT to define the location of the datafiles of the CDB seed pluggable database. The clause creates the CDB seed. The CDB seed datafiles can be used as templates for future PDBs creation. If you omit this clause, Oracle Managed Files determines the names and locations of the CDB seed’s files.
  • The third step is the creation of the catalog with the execution of the $ORACLE_HOME/rdbms/admin/catcdb.sql script connected to the CDB root.

Creating a CDB: Using SQL*Plus

Below are the detailed steps to create a new CDB using SQL*Plus.

SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT

If you are using Oracle ASM storage to manage your disk storage, then you must start the Oracle ASM instance and configure your disk groups before performing the next steps.

2. Use the CREATE DATABASE command with the clause ENABLE PLUGGABLE DATABASE to create a CDB and not a non-CDB. The command creates the CDB root and the CDB seed. You can use the clause SEED FILE_NAME_CONVERT to specify the location of the CDB seed’s files. If you omit the clause, OMF determines the names and locations of the CDB seed’s files. The FILE_NAME_CONVERT specifies the source directory of the CDB root datafiles and the target CDB seed directory. Omit the clause SEED FILE_NAME_CONVERT if you use the new init.ora parameter PDB_FILE_NAME_CONVERT, mapping names of the CDB root datafiles to the CDB seed datafiles. The directories must exist. The character set defined is the single one for the CDB.

SQL> CREATE DATABASE cdb1 ENABLE PLUGGABLE DATABASE ...
SEED FILE_NAME_CONVERT = ('/oracle/dbs','/oracle/seed');

3. Run the catcdb.sql SQL script to build views on the data dictionary tables and install standard PL/SQL packages in the CDB root. You can also execute the catalog.sql and catproc.sql SQL scripts and all other SQL scripts related to the options installed.

New Clause: SEED FILE_NAME_CONVERT

What is new compared to the non-CDB CREATE DATABASE statement? You can find an example of a full CREATE DATABASE statement below:

SQL> CREATE DATABASE cdb1
     USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2
     LOGFILE GROUP 1 ('/u01/app/oradata/CDB1/redo1a.log',
                      '/u02/app/oradata/CDB1/redo1b.log') SIZE 100M,
             GROUP 2 ('/u01/app/oradata/CDB1/redo2a.log',
                     '/u02/app/oradata/CDB1/redo2b.log') SIZE 100M
     CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16
     EXTENT MANAGEMENT LOCAL DATAFILE
                      '/u01/app/oradata/CDB1/system01.dbf' SIZE 325M
     SYSAUX DATAFILE '/u01/app/oradata/CDB1/sysaux01.dbf' SIZE 325M
     DEFAULT TEMPORARY TABLESPACE tempts1
           TEMPFILE '/u01/app/oradata/CDB1/temp01.dbf' SIZE 20M
     UNDO TABLESPACE undotbs
           DATAFILE '/u01/app/oradata/CDB1/undotbs01.dbf' SIZE 200M
     ENABLE PLUGGABLE DATABASE
     SEED FILE_NAME_CONVERT =('/u01/app/oradata/CDB1','/u01/app/oradata/CDB1/seed');

The first important clause required if you want the database to be a multitenant container database is ENABLE PLUGGABLE DATABASE clause correlated with the ENABLE_PLUGGABLE_DATABASE initialization parameter set to TRUE, and one way to declare the directory for the CDB seed datafiles is to use the SEED FILE_NAME_CONVERT clause. The FILE_NAME_CONVERT specifies the source directory of the CDB root datafiles and the target CDB seed directory.

The /u01/app/oradata/CDB1 CDB root directory and the /u01/app/oradata/CDB1/seed CDB seed directory must exist.

New Clause: ENABLE PLUGGABLE DATABASE

Oracle Managed Files

SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> CREATE DATABASE cdb2
     USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2
     EXTENT MANAGEMENT LOCAL
     DEFAULT TEMPORARY TABLESPACE temp
     UNDO TABLESPACE undotbs
     DEFAULT TABLESPACE users
     ENABLE PLUGGABLE DATABASE;

PDB_FILE_NAME_CONVERT Instance Parameter

If you do not use the SEED FILE_NAME_CONVERT clause, use a new instance parameter – The PDB_FILE_NAME_CONVERT instance parameter maps names of existing files (the root datafiles in your case) to new file names (the seed datafiles in this case). In the example, both /u02/app/oradata/CDB1 and /u02/app/oradata/seed directories must exist. For example:

PDB_FILE_NAME_CONVERT = '/u02/app/oradata/CDB2','/u02/app/oradata/seed'

After CDB Creation: What’s New in CDB

After the CDB is created, there are new CDB components and objects such as:

  • Two containers: The CDB root and the CDB seed (maximum number of PDBs: 4096)
  • As many services as containers: The service name for the root container is the CDB name given at the CDB creation concatenated with the domain name. Each new PDB is assigned a service name: the service name is the PDB name given at PDB creation concatenated with the domain name. If you create or plug a PDBtest PDB, its service name would be PDBtest concatenated with the domain name. You can find all service names maintained in a CDB in the CDB_SERVICES view. To connect to the CDB, you connect to the root, using either local OS authentication or the root service name. For example, if you set the ORACLE_SID to the CDB instance name and use CONNECT / AS SYSDBA, you are connected to the root under the common SYS user granted system privileges to manage and maintain all PDBs. To connect to the desired PDB, use either easyconnect or the tnsnames.ora file. For example, CONNECT username/password@net_service_name.
  • Common users: SYS, SYSTEM, created in all containers, the root, and the seed
  • Common privileges granted to all users in all containers
  • Predefined common roles in all containers, the root, and the seed
  • Tablespaces: SYSTEM, SYSAUX associated with each container (maximum number of datafiles:65534)
Дополнительно:  BSOD: истории из жизни, советы, новости, юмор и картинки — Горячее | Пикабу

Data Dictionary Views: DBA_xxx

For backward-compatibility, DBA views show the same results in a PDB as in a non-CDB. For example, the DBA_OBJECTS view shows the objects that exist in the PDB from which you run the query.

  • In the root, DBA_xxx views only show objects contained in the root.
  • In a PDB, the DBA_xxx views only show objects contained in the PDB.
  • While connected to the root, you query DBA_USERS. You get the list of common users created from the root (in the root, only common users exist).
  • While connected to a PDB, you query DBA_USERS. You get the list of users, common and local, of the PDB.

DBA dictionary views providing information within PDB:

SQL> SELECT table_name FROM dict WHERE table_name like 'DBA%';
  • DBA_tablespaces: All tablespaces of the PDB
  • DBA_data_files: All datafiles of the PDB
  • DBA_tables: All tables in the PDB
  • DBA_users: All common and local users of the PDB

Data Dictionary Views: CDB_xxx

In addition to all the columns found in a given DBA_* view, the corresponding CDB_* view also contains the CON_ID column, which identifies a container whose data a given CDB_* row represents. In a non-CDB, the value of a CON_ID column is 0. In a CDB, the value can be either 1 used for rows containing data pertaining to the CDB root only or n where n is the applicable container ID.

  • Connected to the CDB root and querying CDB_USERS, you get the list of users, common and local, of each container.
  • Connected to a PDB and querying CDB_USERS or DBA_USERS, you get the same list of users, common and local, of the PDB.
  • Connected to the CDB root and querying the CDB_PDBS view, you get the list of all PDBs. Querying the CDB_TABLESPACES view, you get the list of all tablespaces of all PDBs.

CDB dictionary views provide information across PDBs:

 SQL> SELECT view_name FROM dba_views WHERE view_name like 'CDB%';
  • CDB_pdbs: All PDBs within the CDB
  • CDB_tablespaces: All tablespaces within the CDB
  • CDB_users: All users within the CDB (common and local)

Data Dictionary Views: Examples

Given below are some examples to make comparisons between DBA_xxx and CDB_xxx views.

SQL> CONNECT / AS SYSDBA
SQL> SELECT role, common, con_id FROM cdb_roles;
SQL> SELECT role, common FROM dba_roles;
SQL> CONNECT sys@PDB1 AS SYSDBA
SQL> SELECT role, common, con_id FROM cdb_roles;
SQL> SELECT role, common FROM dba_roles;
  • In the first example, connected to the CDB root and querying CDB_ROLES, you get the list of roles, common and local, of each container. Note that the new column CON_ID displays the container the role belongs to.
  • In the second example, querying DBA_ROLES, you get all common roles of the CDB root only (there cannot be any local roles in the CDB root).
  • In the third example, connected to the PDB1 and querying CDB_ROLES, you get the list of roles, common and local, of the container you are connected to. The CON_ID displays the same value in all rows.
  • In the fourth example, querying DBA_ROLES, you get the same list except that there is no CON_ID column. Because the CON_ID column in CDB_ROLES displays the same value in all rows, this value is not helpful.

The same backward-compatibility principle applies also to each of the familiar v$views. Access to V$views showing data from PDBs can be secured using privilege.

SQL> SELECT name,open_mode FROM v$pdbs;

NAME               OPEN_MODE
----------------   ----------
PDB$SEED           READ ONLY
PDB1               READ WRITE

Data Dictionary Views: V$xxx Views

Below are some examples of V$xxx views. The new column CON_ID in V$xxx views display how the single SGA is accessed by any PDB within the CDB.

SQL> SELECT distinct status, con_id FROM v$bh order by 2;

STATUS        CON_ID
----------    ------
 cr            1      <--- CDB root
 free          1
 xcur          1
 xcur          2      <--- CDB seed
 cr            3      <--- PDB1 PDB
SQL> select OBJECT_ID, ORACLE_USERNAME, LOCKED_MODE, CON_ID from V$LOCKED_OBJECT;

OBJECT_ID    ORACLE_USERNAME  LOCKED_MODE  CON_ID
----------   ---------------- -----------  -------
    83711     SYS               3            3       <--- PDB1 PDB
    83710     DOM               3            4       <--- PDB2 PDB

In the second example, the V$LOCKED_OBJECT view provides the list of locks currently held on objects in different PDBs. The locks are clearly identified in the CON_ID column as locked by a specific container. 3 stands for one PDB and 4 for another PDB.

After CDB Creation: To do List

  • Create the SPFILE from the PFILE.
  • Execute the $ORACLE_HOME/rdbms/admin/utlrp.sql SQL script.
  • Optionally plug non-CDBs if the initial plan was to consolidate several non-CDBs into a single one.
  • Test startup and shutdown procedures.
  • Create new event triggers to automate PDBs opening.
  • Create backup and recovery procedures.
  • Set a default permanent tablespace.
  • Create additional temporary tablespaces if specific amount of temporary space is required in the PDB.

Automatic Diagnostic Repository

All traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the Automatic Diagnostic Repository (ADR), a file-based repository for database diagnostic data. It has a unified directory structure across multiple instances and multiple products stored outside of any database. It is, therefore, available for problem diagnosis when the database is down. Nothing is changed with the arrival of container databases. Each instance of each product stores diagnostic data underneath its own ADR home directory. Each CDB, linked to a single instance, stores trace files in the same ADR home directory.

Automatic Diagnostic Repository: alert.log File

The alert_CBD1.log shows new DDL statements as shown below:

CREATE DATABASE cdb1
…
ENABLE PLUGGABLE DATABASE
SEED   FILE_NAME_CONVERT=('/u01/app/oradata/CDB1','/u01/app/oradata/seed');

CREATE PLUGGABLE DATABASE PDB$SEED AS CLONE USING …
CREATE PLUGGABLE DATABASE pdb1 … ;
ALTER  PLUGGABLE DATABASE pdb1 UNPLUG INTO … ;
ALTER  PLUGGABLE DATABASE ALL OPEN ;
ALTER  PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE ;

Some of the common DDL statements logged in alert log are listed below:

  • CREATE PLUGGABLE DATABASE
  • ALTER PLUGGABLE DATABASE
  • DROP PLUGGABLE DATABASE

Provisioning New Pluggable Databases

There are different methods to provision new PDBs in a CDB.

  • Create a new PDB from the CDB seed, the PDB$SEED, for example for a new application implementation. This type of PDB creation is nearly instantaneous.
  • Plug an unplugged PDB into another CDB or into the same CDB. For example, you have to upgrade a PDB to the latest Oracle version, but you do not want to apply it on all PDBs. Instead of upgrading a CDB from one release to another, you can unplug a PDB from one Oracle Database release and then plug it into a newly created CDB from a higher release. In case you unplugged a PDB inappropriately, you can still replug it into the same CDB.
  • Plug non-CDBs in a CDB as PDBs, as part of the migration strategy. It is also a good way to consolidate several non-CDBs into a CDB.
  • Clone a PDB from another PDB of the same CDB. For example, you want to test the application patch of your production. You first clone your production application in a cloned PDB and patch the cloned PDB to test.
  • Relocate a PDB into another CDB so as to dispatch resources.
  • Proxy a PDB. A proxy PDB provides fully functional access to another PDB in a remote CDB. This feature enables you to build location-transparent applications that can aggregate data from multiple sources that are in the same data center or distributed across data centers.

Tools

There are different tools to provision new PDBs in a CDB.

  • SQL*Plus
  • SQL Developer
  • Enterprise Manager Cloud Control
  • Enterprise Manager Database Express

To create a new PDB from the CDB seed or from an existing PDB or by plugging an unplugged PDB method, you can also use Database Configuration Assistant (DBCA).

Create New PDB from PDB$SEED

Create New PDB from PDB$SEED

The creation of a new PDB from the CDB seed is nearly instantaneous. The operation copies the datafiles from the READ ONLY seed PDB to the target directory defined in the CREATE PLUGGABLE DATABASE statement.

It creates tablespaces such as SYSTEM, to store a full catalog including metadata pointing to Oraclesupplied objects, SYSAUX for local auxiliary data, and UNDO for local undo segments.

New default service is also created for the PDB.

Summary

  • Copies the datafiles from PDB$SEED datafiles
  • Creates tablespaces SYSTEM, SYSAUX, UNDO
  • Creates a full catalog including metadata pointing to Oracle- supplied objects
  • Creates common users:
    • SYS
    • SYSTEM
  • Creates a local user (PDBA), granted local PDB_DBA role
  • Creates a new default service

Steps: With FILE_NAME_CONVERT

If you do not use OMF (Oracle Managed Files):

SQL> CREATE PLUGGABLE DATABASE pdb1
     ADMIN USER admin1 IDENTIFIED BY p1 ROLES=(CONNECT)
     FILE_NAME_CONVERT = ('PDB$SEEDdir', 'PDB1dir');
SQL> CONNECT / AS SYSDBA
SQL> SELECT * FROM cdb_pdbs;
SQL> SELECT * FROM cdb_tablespaces;
SQL> SELECT * FROM cdb_data_files;
SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN RESTRICTED;
SQL> CONNECT sys@pdb1 AS SYSDBA
SQL> CONNECT admin1@pdb1

3. The CDB_PDBS view shows the STATUS of the new PDB: it is NEW. The PDB has never been opened. It must be opened in READ WRITE or RESTRICTED mode for Oracle to perform processing that is needed to complete the integration of the PDB into the CDB and mark it NORMAL. An error will be thrown if an attempt is made to open the PDB read only.

Steps: Without FILE_NAME_CONVERT

If you use OMF or PDB_FILE_NAME_CONVERT, then first connect to the CDB root as SYS.

— With OMF, set, in init.ora, the DB_CREATE_FILE_DEST instance parameter to a target directory for the datafiles of the new PDB. For example:

DB_CREATE_FILE_DEST = '/u01/app/oradata/CDB1/pdb1'

— Without OMF, set the PDB_FILE_NAME_CONVERT new instance parameter to both the source directory of the CDB seed datafiles and the target directory for the new PDB datafiles.

PDB_FILE_NAME_CONVERT = '/u01/app/oradata/CDB1/seed','/u01/app/oradata/CDB1/pdb1'

The /u01/app/oradata/CDB1/pdb1 directory must exist.

— Or you can also use the clause in the CREATE PLUGGABLE DATABASE command:

CREATE_FILE_DEST = '/u01/app/oradata/CDB1/pdb1'

Then use the cdb_pdbs view to verify that the new PDB and its tablespaces exist:

SQL> SELECT * FROM cdb_pdbs;
SQL> SELECT * FROM cdb_tablespaces;
SQL> SELECT * FROM cdb_data_files;


When it comes to Multi-tenant architecture, one question that comes to our mind is how to connect to container database and pluggable database. Today we will see all possible ways to connect to the container database. Obviously, we connect database using service name for the non-local connection. When we create CDB, a service is created with the same name as the database concatenated with the domain name.

And each PDB is has a service associated with it having the same name we have given while creating a pluggable database concatenated with the domain name. We can check the service name for each pdb using cdb_services.

SQL> SELECT PDB,NAME FROM CDB_SERVICES;

PDB		     NAME
-------------------- --------------------
CDB$ROOT	     SYS$BACKGROUND
CDB$ROOT	     SYS$USERS
CDB$ROOT	     cdb122XDB
CDB$ROOT	     cdb122
PDB122		     pdb122

We can see here services. PDB122 database has pdb122 service associated. CDB$ROOT has cdb122 service associated as my cdb database name is cdb122.

Now various ways to connect to CDB.

Using OS authetication

We can connect with OS authentication.

$ export ORACLE_SID=cdb122
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 14 05:51:12 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Connect with service

We can also connect using the tns service name as shown below:

$ sqlplus system/oracle@cdb122
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 14 05:53:28 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Switching to CDB from PDB

When we are connected to other pdb we can switch to cdb using alter session set container command.

SQL> show con_name

CON_NAME
------------------------------
PDB122
SQL> alter session set container=CDB$ROOT;

Session altered.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

How to Connect to PDBs with Same Name in Different CDBs

You have duplicated 2 auxiliary database instances from a single target CDB container database. Both instances reside on the same server and are registered against the same listener. How can I connect to a specific PDB with the same name but configured in different CDBs?

You’ll find that a single PDB service is registered under 2 instances. This is how LSNRCTL services might look:

Services Summary...

The command completed successfully
 Service "PDB_1" has 2 instance(s).  <==Pluggable database created in each container database

Instance "cdb_1", status READY, has 1 handler(s) for this service...     <== Container database1
Handler(s):      "DEDICATED" established:63112 refused:0 state:ready        

Instance "cdb_2", status READY, has 1 handler(s) for this service...     <== Container database2
Handler(s):      "DEDICATED" established:349293 refused:0 state:ready  

If you want to connect to a PDB service in a specific CDB instance, you can specify the INSTANCE_NAME in the TNS connect string. Set INSTANCE_NAME to point to the specific container database name.

Note that in our example, the SERVICE_NAME matches the PDB name. The INSTANCE_NAME in each TNS connect string is set to each CDB instance name.

PDB_1=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = yourhosthere)(PORT = 1521))
        (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = PDB_1) (INSTANCE_NAME = cdb_1)
        )
    )
PDB_2=
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = yourhosthere)(PORT = 1521))
         (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = PDB_1) (INSTANCE_NAME = cdb_2)
       )
    )

Оцените статью
Master Hi-technology
Добавить комментарий