Oracle Multitenant-CH3
Oracle Multitenant
1
Oracle Multitenant-CH3
Title Page Number
Oracle Multitenant architecture 4
Multitenant Features 5
Create and Configure a Multitenant Database 6
Connecting to a Multitenant Database 9
Startup and Shutdown 12
ALTER PLUGGABLE DATABASE 13
Configure Instance Parameters 14
Data dictionary Views 14
Manage Tablespaces in a PDB 15
2
Oracle Multitenant-CH3
Key words:
CDB, PDB, OUI, DBCA, EXPORT, ORACLE_SID, SQLPLUS,
STARTUP NOMOUNT, MOUNT, RESTRICT, UPGRADE,
FORCE, READ ONLY, SHUTDOWN, ABORT
Brief:
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).
Objective:
In this chapter we will introduce Oracle Multitenant which
represented the architecture for the next-generation database
cloud, it delivers isolation, agility and economies of scale, a
multitenant container database can hold many pluggable
databases, includes:
▪ Oracle Multitenant architecture
▪ Multitenant Features
▪ Create and Configure a Multitenant Database
▪ Connecting to a Multitenant Database
▪ Startup and Shutdown
▪ ALTER PLUGGABLE DATABASE
▪ Configure Instance Parameters
▪ data dictionary Views
▪ Manage Tablespaces in a PDB
3
Oracle Multitenant-CH3
1. Oracle Multitenant architecture:
1.1. 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 (control files, datafiles, undo, temp files,
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.
1.2. 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 control files, redo logs and
undo etc. Instead it is just made up of datafiles and temp files to
handle its own objects, this includes its own data dictionary,
containing information about only those objects that are specific
to the PDB see (Figure 1_3).
Figure 1_3
Oracle Multitenant architecture
4
Oracle Multitenant-CH3
2. Multitenant Features
2.1. High Consolidation Density:
The many pluggable databases in a single multitenant container
database share its memory and background processes. This
enables consolidation of many more pluggable databases
compared to the old architecture, offering similar benefits to
schema-based consolidation but with none of the major
application changes required by that approach.
2.2. Rapid Provisioning and Cloning Using SQL
Creating pluggable databases, moving pluggable databases
between containers, and cloning pluggable databases are done
with new SQL commands and take just seconds, when the
underlying file system supports thin provisioning. Many terabytes
can be cloned almost instantaneously simply by using the
keywords “snapshot copy” in the SQL command.
2.3. New Paradigms for Rapid Patching and Upgrades
The investment of time and effort to patch one multitenant
container database results in patching all of its many pluggable
databases. To patch a single pluggable database, you simply
unplug/plug to a multitenant container database at a different
Oracle Database software version.
To upgrade all hosted pluggable databases in a container
database, simply upgrade the container database and all hosted
pluggable databases are upgraded 'in-place', to upgrade a
single PDB, you simply unplug/plug the pluggable database in to
a container database at a higher version and upgrade the
pluggable database
2.4. Manage Many Databases as One
By consolidating existing databases as pluggable databases,
administrators can manage many databases as one. For
example, tasks such as backup and disaster recovery are
performed at the multitenant container database level.
5
Oracle Multitenant-CH3
2.5. Pluggable Database Resource Management
Resource Manager has specific functionality to control the
competition for resources between pluggable databases within a
multitenant container database.
3. Create and Configure a Multitenant Database
3.1. Create and Configure a (CDB)
• Oracle Universal Installer (OUI)
• Database Configuration Assistant (DBCA)
• Manual Creation
• Configure a Container Database (CDB)
3.2. Database Configuration Assistant (DBCA)
The Database Configuration Assistant (DBCA) see (Figure 2_3)
gives similar options to the Oracle Universal Installer (OUI). The
"Creation Mode" page allows you to enter the default installation
configuration details directly.
6
Oracle Multitenant-CH3
With the exception of selecting
Figurethe
2_3"Create as Container database"
option, the rest of the configuration
DBCA during the installation or when
using the DBCA is very similar to working with a single instance
3.3. Creating Pluggable Databases (PDBs)
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 see (Figure 3_3).
Figure 3_3
DBCA
• 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.
• Unplug a PDB from a CDB and plug it back into another
CDB.
•
7
Oracle Multitenant-CH3
3.4. Create and Configure a Pluggable Database (PDB)
▪ Create a Pluggable Database (PDB) using the DBCA
▪ Unplug a Pluggable Database (PDB) using the DBCA
▪ Plugin a Pluggable Database (PDB) using the DBCA
▪ Delete a Pluggable Database (PDB) using the DBCA
▪ Configure a Pluggable Database (PDB) using the DBCA
On the "Manage Pluggable Databases" screen, select the "Create
a Pluggable Database" option and click the "Next" button. On the
resulting screen, select the container database to house the new
pluggable database and click the "Next" button (Figure 4_3).
Figure 4_3
DBCA
Select the "Create a new Pluggable Database" option and click the
"Next" button. If you were plugging in a previously unplugged
database, you would select the PDB Archive or PDB File Set
options to match the format of the files containing the unplugged
PDB (Figure 5_3).
8
Oracle Multitenant-CH3
Figure 5_3
DBCA
Enter the pluggable database name, database location and admin
credentials, and then click the "Next" button (Figure 6_3).
Figure 6_3
DBCA
9
Oracle Multitenant-CH3
4. Connecting to Database
4.1. Connecting to (CDB)
Connecting to the root of a container database is the same as that
of any previous database instance. On the database server you
can use OS Authentication.
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 15:29:49
2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.1.0.3.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
4.2. Connecting to (PDB)
Direct connections to pluggable databases must be made using a
service. Each pluggable database automatically registers a service
with the listener: We can build the local net service using netca
(Figure 7_3)
Figure 7_3
netca
SQL> CONN system/password@test1
10
Oracle Multitenant-CH3
4.3. Displaying the Current Container
$ sqlplus user/pass@service_name
The SHOW CON_NAME and SHOW CON_ID commands in
SQL*Plus display the current container name and ID respectively.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> SHOW CON_ID
CON_ID
------------------------------
1
4.3. Switching Between Containers
When logged in to the CDB as an appropriately privileged user,
the ALTER SESSION command can be used to switch between
containers within the container database.
SQL> ALTER SESSION SET CONTAINER=test1;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
test1
SQL> ALTER SESSION SET CONTAINER=cdb$root;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
11
Oracle Multitenant-CH3
4.4. Create Local Users
When creating a local user, the following requirements must all be
met.
• You must be connected to a user with the CREATE
USER privilege.
• The username for the local user must not be prefixed with
"C##" or "c##".
• The username must be unique within the PDB.
• You can either specify the CONTAINER=CURRENT clause,
or omit it, as this is the default setting when the current
container is a PDB.
The following example shows how to create local users with and
without the CONTAINER clause from the root container.
CONN / AS SYSDBA
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = test1;
-- Create the local user using the CONTAINER clause.
CREATE USER test_user1 IDENTIFIED BY test_user1
CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user1
CONTAINER=CURRENT;
-- Connect to a privileged user in the PDB.
CONN system/password@test1
CREATE USER test_user4 IDENTIFIED BY password1;
GRANT CREATE SESSION TO test_user4;
When connected to a multitenant database the management of
users and privileges is a little different to traditional Oracle
environments. In multitenant environments there are two types of
user.
• Common User: The user is present in all containers (root and
all PDBs).
• Local User: The user is only present in a specific PDB. The
same username can be present in multiple PDBs, but they
are unrelated.
12
Oracle Multitenant-CH3
5. Startup and Shutdown
5.1. Startup and Shutdown (CDB)
Startup and shutdown of the container database is the same as it
has always been for regular instances
STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE |
FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]
5.2. Startup and Shutdown (PDB)
STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];
6. ALTER PLUGGABLE DATABASE
The ALTER PLUGGABLE DATABASE command can be used
from the CDB or the PDB.
The following commands are available to open and close the
current PDB when connected to the PDB as a privileged user.
ALTER PLUGGABLE DATABASE OPEN READ WRITE
[RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY
[RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE
[RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
Some examples are shown below.
ALTER PLUGGABLE DATABASE OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
13
Oracle Multitenant-CH3
6.1. Delete a Pluggable Database (PDB) Manually
When dropping a pluggable database, you must decide whether to
keep or drop the associated datafiles. The PDBs must be closed
before being dropped.
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;
ALTER PLUGGABLE DATABASE pdb4 CLOSE;
DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;
SELECT name, open_mode
FROM v$pdbs
ORDER BY name;
7. Configure Instance Parameters
7.1. In a CDB
Configuring instance parameters for a CDB is not much different
than it was for non-CDB databases
7.2. In a PDB
SELECT name, value
FROM v$system_parameter
WHERE ispdb_modifiable = 'TRUE'
ORDER BY name;
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM SET parameter_name=value;
ALTER SYSTEM SET parameter_name=value
CONTAINER=CURRENT;
8. Data dictionary 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).
14
Oracle Multitenant-CH3
CDB_: All objects in all containers * (root and all PDBs).
DBA_: All objects in the current container (root or PDB).
ALL_: Objects accessible by the current user in the current
container (root or PDB), including those owned by the current user.
USER_: Objects owned by the current user in the current
container (root or PDB).
9. Manage Tablespaces in a PDB
The same tablespace management commands are available from
a pluggable database (PDB), provided you are pointing to the
correct container. You can connect using a common user then
switch to the correct container.
SQL> CONN pdb_admin@test1
Enter password:
Connected.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
Test1
Once pointed to the correct container, tablespaces can be
managed using the same commands you have always used. Make
sure you put the datafiles in a suitable location for the PDB.
CREATE TABLESPACE test1
DATAFILE ' /test01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M;
Tablespace created.
SQL>
ALTER TABLESPACE test1 ADD
DATAFILE ' /test02.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M;
Tablespace altered.
15
Oracle Multitenant-CH3
DROP TABLESPACE test2 INCLUDING CONTENTS AND
DATAFILES;
Tablespace dropped.
SQL>
16
