Oracle Database 12c: Introduction to a Multitenant Environment with Tom Kyte

Oracle Database 12c: Introduction to a Multitenant Environment with Tom Kyte

Hi this is Tom Kyte and I’m here With Randy
Urbano and We’re going to be talking about pluggable
databases. A big challenge almost everyone is facing
is the challenge of database consolidation. You might start with thirty databases and
thirty separate machines and Decide “I’m going to consolidate.” So you take those thirty databases, and you
host them on one machine. You’ve consolidated servers, so you’ve gone
from thirty to one, But you haven’t consolidated databases yet. You still have thirty separate databases. Now consolidating databases can be a real
challenge. So for example if I tried to take two databases
and merge them into one, But they shared a common public synonym, They both wanted the same public synonym name, I wouldn’t be able to consolidate them together.
Right? Only one public synonym can exist. Or, if they both had an application schema
with the same name, I would not be able to consolidate them. Or, as common, the owners of these two databases,
the business owners, Do not want their data commingled with the
other database’s data. They don’t the other guy’s DBA to be able
to see their data, And their DBA should not be able to see the
other database’s data. So consolidating databases, for technical
and political reasons, has been historically really hard. However, if we just took thirty databases
and put them on a single machine, We haven’t really achieved consolidation yet. We still have thirty databases to upgrade,
thirty databases to backup, Thirty databases to monitor, thirty databases
to start up, and so on. We haven’t made the DBA’s life any easier. Enter the consolidated database with pluggable
databases. In this architecture, we can start one Oracle
instance, One set of background processes, one SGA, And into this one, what we call a container
database, We can plug in up to 252 other databases. This has a great effect on resource management. I don’t have the Oracle instance replicated
over and over again. For thirty databases, I’d need at least 600
processes. For thirty consolidated databases in a pluggable
databases environment, I would just need the twenty or so background
processes for the single instance. Furthermore, by having those thirty databases
plugged into a single container, I’ve made things like backing up easy. I back up my container database. Any database I plug into that is automatically
part of my backup plan. If I’ve got a standby database configuration, I configure the container database to be a
standby database. Any database I plug into that is automatically
a standby already database. Same with Real Application Clusters. Same with patching and upgrade. If I upgrade the container database, I’ve upgraded all thirty pluggable databases. Or, conversely, I could create a new container
database that’s at the next patch level, And then just simply unplug and plug in each
of those pluggable databases As they were ready to be upgraded. I wouldn’t have to do thirty upgrades anymore. I’m going to turn it over to Randy now s So that he can demonstrate the architecture
of the pluggable database, And show you a couple of the relevant features. Thanks Tom. This is Randy Urbano, a technical
writer here at Oracle. A multitenant container database, or CDB,
consists of containers. The root container is a collection of schemas,
schema objects, And non-schema objects to which all PDBs belong. Every CDB has one and only one root, which
stores the system metadata required to manage PDBs. The root does not store user data. A PDB is a user-created set of schemas, schema
objects, and related structures That appears logically to an application as
a separate database. The seed is a template for creating new PDBs. In a non-CDB, when users create their own
schemas and tables, The data dictionary contains some rows that
describe Oracle-supplied entities And other rows that describe user-created
entities. In this illustration, the metadata for the
HR user and the SALES user Is mixed with the Oracle metadata in the data
dictionary. All databases before Oracle Database 12c were
Non-CDBs. In a CDB, the data dictionary metadata is
split between the root and the PDBs. In this illustration, the root’s data dictionary
only contains Oracle system metadata While each PDB’s data dictionary contains
user metadata. Storing metadata in this way makes it easy
to move a PDB from one CDB to another. It also eliminates duplication of Oracle system
metadata And makes it easier to upgrade to a new release
of Oracle Database. This illustration shows the environment we’ll
be working with for this demonstration. MCDB is a single-instance CDB with the following
PDBs: ERP, DW, and SALES. CDBRAC is an Oracle RAC CDB with two instances:
CDBRAC and CDBRAC2. This CDB has the following PDBs: SERVICES,
HR, and MARKETING. STNDBY is a standby CDB for CDBRAC. Notice that it has the same PDBs. During this demonstration we will move a PDB
from MCDB to CDBRAC. We will clone a PDB in the CDBRAC CDB. And we will create a brand new PDB in the
CDBRAC CDB. Here is the environment in SQL Developer. It includes MCDB, The two instances of CDBRAC, which are CDBRAC
and CDBRAC2, STNDBY, the standby database for CDBRAC, And one of the PDBs – ERP. Let’s use SQL Developer to run a query that
shows the PDBs in each CDB. MCDB has the ERP, DW, and SALES PDBs. CDBRAC has the SERVICES, HR, and MARKETING
PDBs. Let’s go back to MCDB. Now assume that this company wants the SALES
PDB to be in a highly available CDB. Specifically, they want it to be in a CDB
with Oracle RAC and a standby database. So the SALES PDB will be moved to CDBRAC. This illustration shows the SALES PDB being
moved from MCDB to CDBRAC. To do this, use ALTER PLUGGABLE DATABASE statements
to close and then unplug the PDB. Next, we copy the files of the SALES PDB from
the MCDB system to the CDBRAC system. Notice the sales.xml file produced when we
unplugged the PDB. This file describes the SALES PDB and will
be used to plug it into the CDBRAC system. Now, let’s plug the SALES PDB into the CDBRAC
CDB. Notice that the SOURCE_FILE_NAME_CONVERT clause
is required Because the source files were moved from their
original location To the new location on the CDBRAC system. After we plug in the CDB, let’s open it with
an ALTER PLUGGABLE DATABASE statement. At this point, the SALES PDB is moved to CDBRAC. Remember that CDBRAC is an Oracle RAC database
with two instances that access the database. On the CDBRAC instance, the SALES PDB is open. On the CDBRAC2 instance, the SALES PDB remains
mounted but not open. Now, let’s open the SALES PDB on the other
instance of the CDBRAC database. To do so, we use an ALTER PLUGGABLE DATABASE
statement. Now that the SALES PDB is plugged into the
CDBRAC CDB, Let’s assume that we want to clone this PDB
for testing purposes. This illustration shows the SALES PDB being
cloned to a PDB called SALES_TEST. When a PDB is cloned, its data, user accounts,
and passwords are the same as the original PDB’s. These might need to be changed before the
PDB is passed on to developers or testers. First we use an ALTER PLUGGABLE DATABASE statement
to put the PDB in read-only mode. Next we use a CREATE PLUGGABLE DATABASE statement
with a FROM clause to clone the PDB. After that, we open the SALES_TEST PDB. Now let’s create a brand new PDB called CRM
on the CDBRAC CDB. This PDB will be created from the seed. Remember that the seed is a template for creating
new PDBs. We use a CREATE PLUGGABLE DATABASE statement
with a STORAGE clause To specify storage requirements for the PDB. A DEFAULT_TABLESPACE clause creates a tablespace
with a data file for the PDB. And a FILE_NAME_CONVERT clause converts the
files from the seed’s location to the new location. After that, we open the PDB. Let’s look at the changes we’ve made to our
environment. The single-instance MCDB CDB now has two PDBs:
ERP and DW. The CDBRAC CDB has the original PDBs plus
the PDBs we’ve added: SALES, SALES_TEST, and CRM. Now let’s look at the standby database STNDBY. Notice that it has the same PDBs as CDBRAC. Additional steps are necessary to ensure that
the standby database Reflects the changes to the primary database
CDBRAC, And these additional steps were not shown
in this demonstration. Let’s look at these changes in SQL Developer
by running a query that shows the PDBS in each CDB. MCDB has two of its original PDBs but it no
longer has the SALES PDB. CDBRAC has its original PDBs, and now it has
the SALES, SALES_TEST, and CRM PDBs. Thanks for watching this demonstration of
the Oracle multitenant environment using Oracle Database 12c. We hope you have found this demonstration
helpful. For more information on the Oracle multitenant
environment, Please see these documents in the Oracle Database
12c online library.

Danny Hutson

23 thoughts on “Oracle Database 12c: Introduction to a Multitenant Environment with Tom Kyte

  1. The concept of having let say 30 databases in one container is great indeed from the  maintenance purposes. But, how can you upgrade all these databases at once if we have mixed DB versions and releases ? let say 9i, 10g, 11gR1, 11gR2 and 12c etc.. You still have to upgrade one database at the time, right ? Please advise.  
    Thank you. 

  2. You're cloning a live PDB to a test PDB on a live CDB environment? Wouldn't it make more sense to clone the live PDB by migrating it to a test CDB and renaming the PDB?

  3. too many acronyms here, Oracle, you need to find a simpler way to communicate, pdb, cdb, mcdb, erp, too much too fast in this presentation. The audio during the presentation has too much distortion. Time to bring back some real words into your technology and use someone with a softer voice who can talk slower.

  4. The demo is very confusing, it is mixing rac and single instances. I think it is more helpful to finish the single and then demo the rac, instead of going back and forth.

  5. As usual Oracle excels at overcomplicating the simplest of things. I mean really "put dw PDB in MCDB CDB then erp PDB into CDBRAC CDB" then repeat the sentence 20 more times with interchanging the terms inside it and expect everyone to understand? seriously ???

  6. If you feel that your business is a bit disorganized with some unnecessary complicated procedures and delays in actions, this small 28 pages book could give you a very useful idea how to change it.

    An idea is very simple and it uses the system thinking approach. It is based on the “World Triangle” philosophy, which suggests that entire world consists of three basic entities such as objects, events and data. So is your business as well. All you have to do is to recognize that and fill your business database with these entities and their relationships. Once recognized, you have to give them number and name. This short book shows you how to do that. That is a first and essential step. If you do it in a wrong way you will have to carry on that mistake and in that way generated ambiguity through the entire life of your business. So a little time spent on reading this book might save you a lot of money, improve your business operations, eliminate unnecessary procedures, eliminate a lot of waste currently you have in your business and put a smile to your face. So act now and you will not regret.

  7. all good – up until the point where you want to perform certain DDL commands inside a PDB. Need a video explaining limitations of CDB/PDB architecture, feature comparison and caveats.

  8. This is an introduction to PDB's, but you need to be a seasoned DBA to get anything out of it. If you're new to Oracle Database Administration this video isn't for you.

  9. Literally

Leave a Reply

Your email address will not be published. Required fields are marked *