Hi,
Oracle12 introduced a new database architecture for the Oracle database: multi-tenant or pluggable databases. The basic concept is a container database (CDB) that can contain one or more pluggable databases (PDBs). The main feature of this new architecture is to significantly reduce the number of database background processes when more than one Oracle database instance is on a single computing environment. This pluggable feature has a few more fringe benefits mostly intended for the DBA staff.
Andy Mendleshon (Senior VP at Oracle Corp) talked at OOW a couple of years ago about what the ‘C’ stands for in Oracle12. He went into detail on how the C stands for:
- Container – one database environment that contains other application databases.
- Consolidate – allows for the sharing of resources (such as computer disk and background processes), sharing similar backup/recovery schemes, and simplifying the DBA task of patch management and database upgrades.
- Cloud – the CDBs are well suited for the Oracle Cloud Computing model. PDBs can easily be moved from one CDB to another, including CDBs on-premises to CDBs in the cloud (and vice-versa).
The container database contains a data dictionary used to manage the PDB environment. The accounts SYS and SYSTEM are at the container level. The CDB has the database environment that will be common to all the PDBs plugged into it. So, if you have a RAC instance, any PDB plugged into this CDB with the RAC features installed will be using the RAC environment! Likewise, databases and applications that have similar uptime/backup/recovery requirements can all use the same CDB as well. Once a PDB is plugged into a CDB, it inherits all of that CDB’s features. It is this simple to also move database applications using the PDB environment to a CDB that is hosted in the cloud.
Consolidating related database environments can significantly reduce hardware requirements. I’ve been in shops where they have 10 to 15 Oracle instances running on a Unix platform, each having its own set of background processes and memory footprints. Using the CDB architecture, similar applications can share memory and background processes. Consolidation can also be considered for applications that have similar backup/recovery features, Data Guard, RAC, and database release levels.
Consolidation also GREATLY aids the DBA. Using the above example with 10 to 15 Oracle instances that need to be upgraded, if these were all in the same CDB environment, just one upgrade is necessary. The PDBs will inherit the upgrade. The DBAs monitor and manage at the CDB level, hence managing dozens of databases at the same time.
Click here to read more about consolidating your Oracle databases using this technology.
Pluggable Database Architecture Source:
http://www.oracle.com/us/corporate/analystreports/corporate/managing-dbs-as-one-pdbs-2061401.pdf
The above pictorial shows a CDB environment. There is a root database that the PDBs are plugged into. This instance is called CDB$ROOT. The syntax to add and drop PDBs from one CDB to another is simple. This alone could make for fast and easy test and QA environments as PDBs are easy and fast to clone…copy. Rapid cloning can also occur across CDBs. Also, a base PDB exists named SEED$PDB. This PDB gives the DBA the ability to quickly clone and start a new database instance for a particular or new application need. In the standalone database mode, the DBA would have to start the database installer, going through a bunch of steps to install an instance of Oracle. Using the seed PDB, this whole task is as simple as one command line.
Oracle12 release 1 can support up to 252 pluggable databases. In Oracle12 release 2, this number has been increased to over 1,000 pluggable databases!
I created this image for my user group presentation,Oracle12 New Features. to show a possible multi-tenant environment and to illustrate how to setup your SQL*Net to connect to each instance, including the CDB instance.
The CDB above has been given the Oracle default name: ORCL. Notice this instance has its own SYSTEM and SYSAUX tablespaces (for its database catalog information and common catalog information for the PDBs), and application tablespaces.
***Tip*** If you have some flash disk available, my recommendation is to use it for the TEMP Tablespace at the CDB level. The PDBs can then use this same TEMP Tablespace (sharing resources) on this super-fast disk environment, giving a good performance gain for the entire database environment!
Notice that both SYS and SYSTEM accounts are at the CDB level. I recommend making a DBA account for each PDB to manage the application better.
Notice that each PDB also has its own SYSTEM and SYSAUX tablespaces. This is for the Oracle catalog as it pertains to that particular PDB. In here will be the application tables, indexes, and users. This instance is pretty much managed as a single standalone instance of Oracle is managed today, from an application point of view.
Each PDB can have its own application tablespaces or they can share the tablespaces assigned at the CDB level. I already made reference to possibly sharing temporary tablespaces that could be on very fast flash drives. Some thought needs to be given to the application within the PDB. Might it be relocated to a different CDB? If so, then you might consider giving the PDB its own set of tablespaces.
My advice is to come up with some common attributes for all applications being migrated to this environment, such as their own DBA account, sharing or not sharing the TEMP tablespaces, and possibly sharing/not sharing application tablespaces as well. If all your CDBs have the same temporary tablespace structure, then moving PDBs around wouldn’t be a concern in relation to the temporary tablespaces.
There is syntax to allow navigation across the CDB/PDBs: alter session set container = <service name>;perhaps in the future, there will be a button on our GUI tools to do this task.
Service name becomes the syntax needed to both navigate around the PDB/CDB and to connect your users to the appropriate PDB or CDB.
# TNSNAMES.ORA Network Configuration File: # Generated by Oracle configuration tools.
DANXP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11r2)
)
)
ORA12CDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ACCTPAY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPDB1)
)
)
PAYROLL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPDB2)
)
)
Example TNSNAMES.ora file
In the above TNSNAMES.ora file, notice the ACCTPAY application is assigned to the PDB ORCLPDB1 and the application PAYROLL is assigned to ORCLPDB2. The TNSNAMES.ora entries ACCTPAY and PAYROLL become the connect string to the end user’s applications. Likewise, the ORA12CDB can be used as a connect string by DBAs wishing to connect to the container database over these applications.
Oracle12.1.0.2 Multi-tenant New Features
- Cross database queries using CON_ID …connection ID by PDB
- Coding this way will hard-code your applications to this CDB though
- Standby DB enhancements
- In-Memory structures supported
- Can subset PDBs by tablespace
- Supports Flashback features
- Maintains PDB state between CDB restarts
Oracle12.2 Multi-tenant New Features
- Support for thousands of PDBs (documentation didn’t give a specific number)
- Can upgrade at the PDB level
- Better memory management at the PDB level
- Flashback at the PDB level
Click here to see Oracle Documentation on Oracle12.2 New Features.
I hope this article helps you see the value in the new multi-tenant database environment.
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com