This article will be discussing a new feature of Oracle 12c Database, the pluggable database, which builds on the container database concepts, found here. We will be discussing why it is needed, what it solves, its benefits and some examples of it.
What is it?
A pluggable database (PDB) is a portable database that that contains a user-created set of schema, data and objects. It appears as a separate database to applications regardless of which container database (CDB) in which it is contained.
Why it is there?
Let’s say you are in a team of DBAs working in a large enterprise which runs hundreds of databases supporting different applications. These databases are running on different hardware platforms according to the need of applications. Normally enterprises have under-provisioned hardware which can support excess workload compared to the actual workload of database. Just imagine a hundred such databases on separate database servers resulting in hardware and human resource waste.
Previously, there were two options to reduce database sprawl. One, just have multiple databases on each server; however, multiple databases on each server will not share background processes, system and process memory and oracle metadata. This consolidates the servers, but still wastes hardware resources. Two, consolidate the schemas between applications onto single database instances; however, this can require massive development effort and testing time to ensure that the applications and database all run as expected.
How PDB solves this problem
Oracle introduced multitenant architecture in Oracle 12c to solve this problem. It allows you to consolidate different databases into a single container database (CDB) without changing code or applications. Applications are isolated from this change in architecture and behave as in a non-multitenant environment. However, standard database operations like data guard and backups can be done on the CDB, across all the PDBs contained inside.
Benefits
- Reduction of Cost By consolidating many databases onto fewer hardware resources, capital and operational costs decrease.
- Easier and rapid movement of data and code You can easily plug a PDB to CDB, unplug a PDB from a CDB and plug it to another CDB.
- Easier management and monitoring of database You can easily manage one database instead of managing and monitoring hundreds of databases on different servers.
- Separation of code and data Multiple PDB databases are consolidated into single CDB; however, each PDB still mimics the behavior of a separate database. If data is lost in one PDB, then administrator can easily recover/restore lost data using Oracle recovery techniques.
- Secure separation of administration duties CDB administrators can create admin users in the CDB to manage it and even in PDBs to manage only those PDBs which are assigned to a particular admin user.
- Ease of performance tuning It is easier to get performance matrices of single database at the CDB level, than trying to aggregate multiple databases and sizing memory and performance parameters of each.
- Fewer database patches and upgrades Patches and upgrades can be done at the CDB level, which patches or upgrades all of the PDBs contained inside.
Examples
- Creating a PDB from Seed
-- Ensure that you are connected to a CDB
SQL> SELECT name, cdb, open_mode FROM v$database;
NAME CDB OPEN_MODE
--------- --- --------------------
ORCL YES READ WRITE
-- For simplicity, following scripts are coded for a database instance with
-- OMF, oracle managed files. In case your database is not using OMF, then
-- you need to add the FILE_NAME_CONVERT parameter to the CREATE PLUGGABLE
-- DATABASE statement
SQL> CREATE PLUGGABLE DATABASE mypdb ADMIN USER sys1
2 IDENTIFIED BY change_on_install;
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE mypdb OPEN;
Pluggable database altered.
SQL> SELECT name, con_id, open_mode FROM v$pdbs;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED 2 READ ONLY
PDBORCL 3 READ WRITE
MYPDB 4 READ WRITE- Creating a PDB by cloning other PD
SQL> ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE mypdb OPEN READ ONLY;
Pluggable database altered.
SQL> CREATE PLUGGABLE DATABASE mynewpdb FROM mypdb;
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE mynewpdb OPEN;
Pluggable database altered.
SQL> SELECT name, con_id, open_mode FROM v$pdbs;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED 2 READ ONLY
PDBORCL 3 READ WRITE
MYPDB 4 READ ONLY
MYNEWPDB 5 READ WRITE- Creating a PDB by plugging using unplugged PD
SQL> ALTER PLUGGABLE DATABASE mynewpdb CLOSE IMMEDIATE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE mynewpdb UNPLUG into '/tmp/mynewpdb.xml';
Pluggable database altered.
SQL> DROP PLUGGABLE DATABASE mynewpdb;
Pluggable database dropped.
SQL> CREATE PLUGGABLE DATABASE mynewpdb2 USING '/tmp/mynewpdb.xml';
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE mynewpdb2 OPEN;
Pluggable database altered.
SQL> SELECT name, con_id, open_mode FROM v$pdbs;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED 2 READ ONLY
PDBORCL 3 READ WRITE
MYPDB 4 READ ONLY
MYNEWPDB2 5 READ WRITE- Creating a PDB from a non-CDB (adopting)
You need to place this non-CDB in a transnationally consistent state in 12c database, and then run the DBMS_PDB.DESCRIBE function to generate XML metadata about this database. While connected to the root in the CDB, you execute the CREATE PLUGGABLE DATABASE statement to create a PDB from the existing non-CDB, just as in previous method.
-- execute when connected to non-CDB in 12c
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1202556928 bytes
Fixed Size 2287720 bytes
Variable Size 436209560 bytes
Database Buffers 754974720 bytes
Redo Buffers 9084928 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> BEGIN
2 DBMS_PDB.DESCRIBE( pdb_descr_file => '/tmp/noncdb.xml');
3 END;
4 /
PL/SQL procedure successfully completed.Now connect to CDB and execute following code
SQL> CREATE PLUGGABLE DATABASE mynoncdb USING '/tmp/noncdb.xml';
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE mynoncdb OPEN;
Warning: PDB altered with errors.
SQL> SELECT name, con_id, open_mode FROM v$pdbs;
NAME CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED 2 READ ONLY
PDBORCL 3 READ WRITE
MYPDB 4 READ ONLY
MYNEWPDB2 5 READ WRITE
MYNONCDB 6 READ WRITE