General CDB / PDB Concept in 12c Multitenant Architecture
What is a pluggable database (PDB) in Multitenant Architecture?
Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 (12.1). You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are fully backwards compatible with an ordinary pre-12.1 database.
Why would I consider using the Multitenant option?
You should consider following Database Consolidation Goals to achieve:
Reduce Total Cost of Operation
Administration costs
Operations costs
Data center costs
Storage costs
Contingency costs
Improve Functionalities
Resource utilization
Manageability
Integration
Service management
Must not need to change applications
Must not harm performance
Must provide resource management and isolation between applications
Must simplify patching and upgrade of Oracle Database
What other benefits will I get from Multitenant option?
The benefits of mulitenant Pluggable Databases are:
Fast provisioning of a new database or of a copy of an existing database.
Fast redeployment, by unplug and plug, of an existing database to a new platform.
Quickly patch or upgrade the Oracle Database version for many databases and for the cost of doing it once.
Patch or upgrade by unplugging a PDB and plugging it into a different container database (CDB) in a later version.
A machine can run more database instances in the form of PDBs than as individual, monolithic databases.
Separate the duties of the application administrator from the duties of the administrator of the Oracle-supplied system.
How easy to migrate an existing pre 12.1 database to 12c Multitenant database ?
Migrate to 12c Pluggable database is very simple and easy. You can evaluate and adopt which one best suit for you.
Plan A.
Upgrade an existing pre 12.1 database to 12.1
Plug-in the database post upgrade into a CDB
Plan B.
Provision empty PDBs per database that need to consolidated
Use datapump or golden gate replication to migrate a Database into a PDB
Which Oracle Database features are currently not supported in a Multitenant architecture?
The following Oracle Database features are currently not supported in a CDB:
Continuous Query Notification
Flashback Data Archive
Heat Maps
Automatic Data Optimization
If you must use one or more of these features, then create a non-CDB.
Q) Does each non-CDB need its own CDB if I do not have a license for Multitenant?
Yes
Q) Can multiple CDBs run on the same server?
Yes
Q) Can multiple CDBs run out of the same ORACLE_HOME installation?
Yes
Q. What are the major changes in architecture for 12c?
From 12c Onwards, the instance is shared with multiple databases.
This multiple databases are self contained and pluggable from one database to another database. This is very useful methodology where database consolidation.
In short a single SGA and background process will be shared to multiple databases, the databases can be created on fly and drop or attach and detach from one server to another server.
Q. What is a pluggable database (PDB) in Multitenant Architecture?
Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 (12.1).
You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are always part of a Container Database (CDB) but a PDB looks like a normal standalone database to the outside world.
Q. Does In-memory require a separate license cost?
Yes, In-memory does require a separate license cost.
Q. Why would I consider using the Multitenant option?
You should consider following Database Consolidation Goals to achieve:
Reduce Total Cost of Operation
Administration costs
Operations costs
Data center costs
Storage costs
Contingency costs
Improve Functionalities
Resource utilization
Manageability
Integration
Service management
Must not need to change applications
Must not harm performance
Must provide resource management and isolation between applications
Must simplify patching and upgrade of Oracle Database
Q. What are the common concepts of multitenant database?
Multitenant database consists of:
CDB is a container database which is similar like standalone database. Called CDB$ROOT
PDB$SEED is a template database to create a databases within the CDB databases
PDB<n> are individual or application databases
Data dictionary between this databases are shared via internal links called object link and data link
Users between CDB and PDB are different, there will be common users (starts with C##) and local users
When the CDB starts up, the PDB will be in mount state, you must open them exclusively
Q. Will CDB’s become the future?
Yes, Oracle has already depreciated standalone database development. No further improvements will be released in future for standalone databases.
Q. How many PDBs can you create?
12cR1 –> 252 PDBs
12cR2 –> 4096 PDBs
Q. Can multiple CDBs run on the same server?
Yes!
Q. Can multiple CDBs run out of the same ORACLE_HOME installation?
Yes, you can invoke DBCA and create new CDBs out of same Oracle_Home
Q. What are the methods to create Multitenant Database?
DBCA method
DBCA silent method
Manual method using CREATE DATABASE statement
Q. What is the limit of container databases (CDBs) on a server?
As many as supported by server CPU, RAM and Hard disk space.
Q. How do I know if my database is Multitenant or not?
You can use below query to identify a CDB database:
SELECT NAME, OPEN_MODE, CDB FROM V$DATABASE;
SHOW CON_ID;
SHOW CON_NAME;
Q. How to distinguish you are in CDB or PDB?
Once you logged in you can check show con_name or con_id will show you which db you are in.
Q. What are the different ways you can create a PDB?
Copying from PDB$SEED
Copying from another PDB
Copying from a remote PDB
Converting a Non-CDB into PDB
Unplugging and plugging in a PDB
Q. Can I have one PDB at release 1, and a second PDB at release 2?
No, one instance, one version for all PDBs.
Q. What Pluggable databases do we have in this container database ?
You can check this by querying v$containers:
SELECT NAME, OPEN_MODE FROM V$CONTAINERS;
SHOW pdbs;
Q. How do you switch from one container to another container inside SQL*PLUS?
ALTER SESSION SET CONTAINER=pdb1;
Q. How about the datafiles system, sysaux , undo, redo etc , does they create when you create PDB?
Datafiles are individual to each database for cdb and each pdb
Undofiles and redofiles are only one across container
From 12cR2 onwards we can create local undo for each PDB
Tempfiles can be created in each database or share one across all databases
SGA is shared across all databases
Background process are shared across all databases , no additional back ground process defined
Q. Is the alert log the same for all pdbs in a cdb, or are they different?
Yes, one CDB, one alert log.
Q. How can I connect to a PDB directly from SQL* PLUS?
You can use Oracle easy connect method to connect a PDB directly.
CONNECT username/password@host[:port][/service_name][:server][/instance_name]
OR
sqlplus user/password@//localhost/pdb2
Q. How do I switch to main container Database?
ALTER SESSION SET CONTAINER = CDB$ROOT;
Q. As you said, if SGA and background process are shared, is there any performance impact?
Ideally this architecture is used for database consolidation projects which and where small databases are
shared in a single database host and not that high critical applications running.
This leverages the reduction in licensing cost and also resource utilization effectively.
Q. How do I start up a Pluggable database?
From CDB$ROOT container:
ALTER PLUGGABLE DATABASE PDB1 OPEN;
Q. How about creating a user?
Normally you will use create user username identified by password, however this is not work anymore.
When you want to create a common user across all databases for example, you must use C## as prefix
SQL> create user c##username identified by password; ---> will create common user across all databases
SQL> create user c##username identified by password container=current; ---> will create common user only at current container
SQL> create user username identified by password container=all; ---->does not work since the username does not contain c## prefix
Q. How about AWR data, does it common across all databases or individual to database?
That is why you have individual sysaux tablespace for each database, whenever the AWR statistics collected the statistics will be pushed respective databases not the common sysaux.
Since this will give you the ability to have self contained database where if you plug this database to another instance, the statistics will not lost.
Q. Which parameters are modifiable at PDB level?
select NAME, ISPDB_MODIFIABLE from V$PARAMETER;
Q. What is the difference between Container ID Zero and One?
CON_ID “0” means data does not pertain to any particular Container but to the CDB as a whole.
For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “0”.
A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0.
Following table describes various values of CON_ID Column in Container Data Objects.
0 = The data pertains to the entire CDB
1 = The data pertains to the root
2 = The data pertains to the seed
3 – 254 = The data pertains to a PDB, Each PDB has its own container ID.
Q. Are there any background processes ex, PMON, SMON etc associated with PDBs?
No. There is one set of background processes shared by the root and all PDBs.
Q. Are there separate control file required for each PDB?
No. There is a single redo log and a single control file for an entire CDB.
Q. Are there separate Redo log file required for each PDB?
No. There is a single redo log and a single control file for an entire CDB.
Q. Can I monitor SGA usage on a PDB by PDB basis?
SQL> alter session set container=CDB$ROOT;
SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id';
SQL> select CON_ID, POOL, sum(bytes) from v$sgastat group by CON_ID, POOL order by CON_ID, POOL;
Q. Do I need separate SYSTEM and SYSAUX tablespaces for each of my PDB?
There is a separate SYSTEM and SYSAUX tablespace for the root and for each PDB.
Q. Where is user data stored in CDB?
In a CDB, most user data is in the PDBs. The root contains no user data or minimal user data.
Q. How can I create a pluggable database ?
sql> create pluggable database x admin user a identified by p;
Q. How to drop a PDB irrevocably?
sql> drop pluggable database x including datafiles;
Basic Multitenant CDB / PDB Operations:---
How do I know if my database is Multitenant or not?
Establish a SQL Session and run this Query:
SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;
NAME Multitenant Option? OPEN_MODE CON_ID
----- ---------------------------- ----------------------
CDB2 Multitenant Option enabled MOUNTED 0
What Pluggable databases do we have in this container database ?
To view pluggable database in the container databases execute the below query.
SQL> Show pdb's;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ --------------- ---------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 PDB3 READ WRITE YES
How do I connect to a Pluggable Database, say PDB6?
You can switch to PDB6 from other PDB or Root Container using following command.
SQL> alter session set container = pdb6;
Connecting to a PDB directly Using the SQL*Plus CONNECT Command:
You can use the following techniques to connect to a PDB with the SQL*Plus CONNECT command:
A) Database connection using easy connect:
SQL> CONNECT username/password@host[:port][/service_name][:server][/instance_name]
Examples of SQLPLUS from OS prompt:
$ sqlplus user/password@//node1:1521/pdb2
$ sqlplus user/password@//localhost:1521/pdb2
$ sqlplus user/password@//localhost/pdb2
SQL> show con_name
CON_NAME
------------------------------
PDB2
B) Database connection using a net service name:
Example TNSNAMES.ora:
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hpal-node1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
Example of SQLPLUS from Os prompt:
$ sqlplus user/password@pdb2
How do I switch to main container Database?
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
How do I determine which PDB or CDB I am currently connected to?
SQL> show con_name
CON_NAME
------------------------------
PDB6
SQL> select sys_context ( 'Userenv', 'Con_Name') "Container DB" from dual;
Container DB
--------------------------------------------------------------------------------
PDB6
How do I start up a Pluggable database ?
When connected to current PDB:
SQL> alter pluggable database open;
When connected to root:
SQL> alter pluggable database pdb6 open;
How do I shutdown / close a Pluggable database?
When connected to current PDB:
SQL> alter pluggable database close;
When connected to root:
SQL> alter pluggable database pdb6 close;
How do I shut down / Startup the Container Database?
Use startup/Shutdown command similar to startup/shutdown of Non-CDB.
When the container database is shutdown, no PDB is accessible.
In a CDB, the root and all of the PDBs share a single instance, or, when using Oracle RAC, multiple concurrent database instances
.
You start up and shut down an entire CDB, not individual PDBs.
However, when the CDB is open, you can change the open mode of an individual PDB by using the ALTER PLUGGABLE DATABASE statement.
Which parameters are modifiable at PDB level?
SQL> select NAME, ISPDB_MODIFIABLE from V$PARAMETER;
What common users do I have in my cdb?
SQL> select distinct USERNAME from CDB_USERS where common = 'YES';
How do I create a common user ?
SQL> create user c##db_dba1 identified by manager1 container=all;
How do I create a local user ?
SQL> create user pdb6_dba1 identified by manager1 container=current;
Multitenant Architecture
What is the difference between Container ID Zero and One?
CON_ID “0” means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “0”. A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0.
Following table describes various values of CON_ID Column in Container Data Objects.
0 = The data pertains to the entire CDB
1 = The data pertains to the root
2 = The data pertains to the seed
3 – 254 = The data pertains to a PDB, Each PDB has its own container ID.
Are there any background processes ex, PMON, SMON etc associated with PDBs?
No. There is one set of background processes shared by the root and all PDBs.
Are there separate control file required for each PDB?
No. There is a single redo log and a single control file for an entire CDB.
Are there separate Redo log file required for each PDB?
No. There is a single redo log and a single control file for an entire CDB.
Can I monitor SGA usage on a PDB by PDB basis?
There are single SGA shared by all pluggable databases. However, you can determine SGA consumptions by all containers i.e, root and PDB.
SQL> alter session set container=CDB$ROOT;
SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id';
SQL> select CON_ID, POOL, sum(bytes) from v$sgastat group by CON_ID, POOL order by CON_ID, POOL;
Can I monitor PGA usage on a PDB by PDB basis?
Use the below queries to monitor the SGA usage on per PDB basis.
SQL> select CON_ID, sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM), sum(PGA_MAX_MEM)
from v$process
group by CON_ID order by CON_ID;
SQL> alter session set container =CDB$ROOT;
SQL> select NAME , value from v$sysstat where NAME like 'workarea%';
SQL> alter session set container = [targetPDB];
select NAME , value from v$sysstat where NAME like 'workarea%';
Do I need separate UNDO tablespaces for for each of my PDB?
There is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace.
Please refer the below post for various modes of Undo available from 12.2 onwards:
Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes
Do I need separate SYSTEM tablespaces for each of my PDB?
There is a separate SYSTEM tablespace for the root and for each PDB.
Do I need separate SYSAUX tablespaces for for each of my PDB?
There is a separate SYSAUX tablespace for the root and for each PDB.
Do I need Temporary tablespaces for for each of my PDB?
There is one default temporary tablespace for the entire CDB.
However, you can create additional temporary tablespaces in individual PDBs.
One active temporary tablespace is needed for a single-instance CDB, or one active temporary tablespace is needed for each instance of an Oracle RAC CDB.
Can I specify a separate default tablespace for the root and for each PDB?
Yes. You can specify a separate default tablespace for the root and for each PDB.
Are all physical datafiles separate for root and PDB?
There are separate datafiles for the root, the seed, and each PDB.
Where is user data stored in CDB?
In a CDB, most user data is in the PDBs.
The root contains no user data or minimal user data.
Does Pluggable database support separate database characterset?
A CDB uses a single character set.
All of the PDBs in the CDB use this character set. Oracle recommends the following:
For all new deployments and if all PDBs are created empty, Oracle strongly recommends AL32UTF8 for the CDB database character set and AL16UTF16 for the CDB national character set.
If you can migrate your existing databases to AL32UTF8 before consolidation, Oracle recommends that you do so and consolidate into one or more AL32UTF8 CDBs, depending on your needs.
You can use Oracle Database Migration Assistant for Unicode to migrate a non-CDB to AL32UTF8. You cannot migrate the CDB using Oracle Database Migration Assistant for Unicode, after creation.
If you cannot migrate your existing databases prior to consolidation, then you have to partition them into sets with plug-in compatible database character sets and plug each set into a separate CDB with the appropriate superset character set.
How do I configure Net Files in a Pluggable database environment?
There is a single listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.
Advance CDB / PDB Operations
How can I install and setup Pluggable Database?
Use runInstaller to install the Oracle Database software.
Use dbca to create databases.
You can create many pluggable databases in a single operation.
DBCA enables you to specify the number of PDBs in the CDB when it is created.After a CDB is created, you can use DBCA to plug PDBs into it and unplug PDBs from it.
What Operations act on PDBs as entities?
These operations act on PDBs as entities:
create PDB (brand-new, as a clone of an existing PDB, by plugging in an unplugged PDB)
unplug PDB
drop PDB
set the Open_Mode for a PDB
How can I create a pluggable database ?
Use the below queries to create a pluggable database.
sql> create pluggable database x admin user a identified by p;
sql> create pluggable database y admin user a identified by p file_name_convert = ('pdbseed', 'y');
How to drop a PDB irrevocably?
Use the below queries to drop a PDB database irrevocably.
sql> drop pluggable database x including datafiles;
How easy it is to manage the provisioning of PDBs using PL/SQL?
Following an Example of PL/SQL Code to show this.
Using Oracle-Managed Files
declare
t0 integer not null := -1;
procedure Show_Time(What in varchar2) is
t varchar2(10);
begin
t := Lpad((DBMS_Utility.Get_Time() - t0), 5);
DBMS_Output.Put_Line('create PDB:'||t||' centiseconds');
end Show_Time;
begin
t0 := DBMS_Utility.Get_Time();
execute immediate '
create pluggable database x
admin user a identified by p
';
Show_Time('create PDB:');
t0 := DBMS_Utility.Get_Time();
execute immediate '
drop pluggable database x
including datafiles
';
Show_Time('drop PDB: ');
end;
How to clone a PDB from an existing PDB?
The clonee must be open in read only mode. Example using Oracle-Managed Files:
sql> create pluggable database x2 from x;
How to unplug a PDB ?
Use the below query to unplug a plugged database.
sql> alter pluggable database x unplug into '/some_directory/x_description.xml' ;
The into keyword must be followed by the full path for a description of the PDB, in XML, generated by the operation.
Scalability & RAC
How to add or modify a user-managed service ?
sql> srvctl add service –pdb [pdb_name]
Starting a user-managed service using srvtcl will open the PDB automatically in all the instances in which the service is started. Specifying the empty string (“”) as the [pdb_name] will cause the pluggable database attribute of a service to be set to null.
The service can then be used only to connect to the root.
How can I view which service is attached to my Pluggable database?
SQL> column NAME format a30
SQL> select PDB, INST_ID, NAME from gv$services order by 1;
PDB INST_ID NAME
-------------------------------- ---------- --------------------------------
CDB$ROOT 1 cdb1XDB
CDB$ROOT 1 SYS$BACKGROUND
CDB$ROOT 1 SYS$USERS
CDB$ROOT 1 cdb1
PDB1 1 pdb1
PDB2 1 pdb2
Diagnosibility
Where can I find the Alert log for my pluggable Database?
A Single copy of Alert log is generated which contains warnings and alert information for all PDBs.
XML version of alert can be found in “Diag Alert” and text formatted Aler log can be found in “Diag Trace” of the container database.
You can find details by selecting from v$diag_info dynamic view.
Where can I find trace files related to my pluggable Database?
All traces generated from all PDBs are currently found in “Diag Trace” of the container database.
You can find details by selecting from v$diag_info dynamic view.
Miscellaneous
If a user-defined, common user creates schema objects in a PDB, and if later that PDB is unplugged and plugged into a different CDB in which that common user does not exist, then what happens to the schema objects?
By which user will they be owned? Will other users within the PDB, which had been granted privileges on those schema objects, still retain those privileges?
If you plug a PDB that contains a common user into a CDB, then the following actions take place:
– The common user accounts in this PDB lose commonly granted privileges that they may have had, including the SET CONTAINER privilege.
– If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB, then the new common user is merged with the target CDB common user.
– The password of the target CDB common user takes precedence. Otherwise, a common user in a newly plugged in PDB becomes a locked account. In this case, you can do one of the following:
Leave the user account locked and use the objects of its schema.
Use Oracle Data Pump to copy these objects to another schema, and then drop the locked user account.
Close the PDB, connect to the root, and then create a common user with the same name as the locked account. When you re-open the PDB, Oracle Database resolves the differences in the roles and privileges that were commonly granted to the locked user. Afterward, you can unlock this user account in the PDB. Privileges and roles that were locally granted to the user will remain unchanged.
Is the multitenant option available in Standard Edition?
Yes, but you may only create one PDB, per CDB.
Can a transaction span across PDBS?
No, though “alter session set container” is allowed after starting a transaction in a PDB, only select is allowed in the second PDB. The transaction is preserved and you can do commit or rollback after switch back to original PDB.
What data can be seen in CDB_ and V$ views from each container?
CDB_* views are container data objects.
When a user connected to the root queries a CDB_* view, the query results will depend on the CONTAINER_DATA attribute for users for the view.
The CONTAINER_DATA clause of the SQL ALTER USER statement is used to set and modify users’ CONTAINER_DATA attribute.
In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables, tablespaces, users, privileges, parameters, and so on contained in the root and in pluggable databases (PDBs).
The CDB_* views are owned by SYS, regardless of who owns the underlying DBA_* view. By default, a user connected to the root will only see data pertaining to the root.
May the database timezone be set on a per-PDB basis?
Yes.
May NLS currency settings (NLS_CURRENCY) be set on a per-PDB basis?
Yes.
How to monitor the undo usage of each container/database in CDB/PDB?
Use the below queries to monitor the undo usage of each container or database in a CDB or PDB architecture.
sql> select NAME,MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT)
from V$CONTAINERS c , V$UNDOSTAT u
where c.CON_ID=u.CON_ID
group by NAME;
sql> select NAME,SNAP_ID,UNDOTSN,UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXQUERYSQLID
from V$CONTAINERS c , DBA_HIST_UNDOSTAT u
where c.CON_ID=u.CON_ID
and u.CON_DBID=c.DBID
order by NAME;
What is the difference between schema-based consolidation and the multitenant architecture?
1. Name collision might prevent schema-based consolidation.
2. Schema-based consolidation brings weak security.
3. Per-application, back-end, point-in-time recovery is prohibitively difficult.
4. Resource management between application back-ends is difficult.
5. Patching the Oracle version for a single application back-end is not possible.
6. Cloning a single application back-end is difficult.
