Site icon EasyOraDBA

Oracle Database 12c : New Features

Original Article on http://www.orafaq.com/node/2756 by Micheal Rajendran
Introduction
Oracle has leap forwarded the middleware technologies especially the database technology into the cloud. So far Oracle has been the traditional RDBMS database suitable for the private enterprise data centers within corporate walls. In Oracle Open World 2012 held in San Francisco, Larry Ellison announced that oracle database also be cloud enabled by introducing pluggable databases for multi-tenancy and easy database movement between systems, platforms or releases. When the database is a cloud ready, it should be hardware agnostic, platform agnostic and release agnostic so that it gives all the characteristics to be in Platform as a Service (PaaS) for middleware. It is a brand new capability insider a single container database. So the DBAs and developer community should be familiar with “Container Databases” or CDB and “Pluggable Database” or PDB. I will refer PDB and CDB to refer pluggable database and container database respectively.
Multi-Tenancy
Before we delve into deeper details about CDB and PDB, let us get some basic details about multi-tenancy. Most of the organizations use the multi-tenancy with application level logic i.e. multiple customer or different entities data within the same database. They can be setup with many different schemas or even within a schema. But managing the security has lot of caveats including auditing as Larry Ellison & Andrew Mendelsohn mentioned on their respective keynote addresses. It has been an administrative nightmare when multiple databases are running in one machine. The backups need to be run separately. Each database has memory footprint and each database has background processes. This increases the capacity of the server on what it can handle in terms of the loads. By consolidating into one container database and at the same time keeping all of them as separate databases are a great thing from consolidation, performance, capacity and operational perspective. This is going to help in a very big way for consolidation on many enterprises and at the same time it will reduce the server footprint significantly giving the maximum Return on Investments (ROI) on the middleware database technologies. I really think this new feature has lot of advantages from small customers to large scale enterprise customers no matter how we look at it. It is going to make the enterprises to become smarter in terms of utilizing the compute capacity what they have.
CDB vs. PDB
CDB is an acronym for “Container Database” and PDB is an acronym for “Pluggable Database”. I think it will be easier to explain with a metaphor for DBAs. Think of a freight train with many cars up to 250. Each container could be having different contents, with delivery target for different customers and completely packed/sealed independently with customer options but the entire freight is carried by a single engine or carrier at the front. It will be stupid enough to run 250 freight trucks but rather it is efficient to consolidate them into a single freight train. When running independently we will spend on gas, drivers’ expenses and much more complicated to manage them. The freight train is basically the CDB and each car is the PDB.
PDB is fully backward compatible to pre-12.1 database releases. There is nothing different from a developer or application connectivity perspective. Everything stays the same but the PDB will belong to a single CDB. When application connects to the PDB, it will specify the destination PDB via a database service. All home-grown or third party applications typically will have connectivity defined out of the application so it is easier to just change the service name outside of the application code. So all database connectivity should use “database service” rather than using the legacy approach of ORACLE_SID based connectivity. ORACLE_SID ties the application connectivity to a specific database instance and does not give the scalability or high availability. You can have many pluggable databases in 12.1.
Distinguished Product Manager Bryn Llewellyn,
who is shown in the picture, and Architect Kumar
Rajamani
, who is also the visionary for the pluggable databases, mentioned
that the maximum limit to the total number of Pluggable databases within a
container database is close to 250. So you can have up to 250 pluggable
databases or PDBs within one container database or CDB. It is clear that there
is one-to-many relationship between CDB and PDBS.
Architecture & Features
Split Data Dictionary
You might be already asking the question on how the data dictionary is managed for the CDB as well as for all the PDBs. That is where the architecture of CDB dictionary (or catalog) and PDBs dictionary come into picture. Oracle calls it as a “root” namespace where the CDB belongs. This will have an “Oracle System” dictionary which will be provided by Oracle in new releases and/or in patches. This will be exclusively controlled by Oracle. Each PDB will have its own dictionary or metadata which are owned by the customers. Each PDB dictionary defines its own namespace. Each PDB will also have the “read only” copy of Oracle dictionary as well. We can call it as a “Split Data Dictionary” architecture which enables the PDB to keep its own dictionary and makes it easy to be portable between multiple CDBs. Each PDB is a self contained system, a clear declarative definition of an application, which knows nothing about the next PDB within the CDB. Each PDB is a sealed container in a 250 freight container train or CDB as I explained before.
Compatible: RAC, Resource Manager etc
Each RAC instance opens the CDB as a whole so that versions would be same for CDB as well as for all of the PDBs. PDBs are also fully compatible with RAC. Pluggable database is if fully compatible with all the database options and features including resource manager. Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.
New Administrator: CDB Administrator
A new administrator has been introduced in 12.1 release databases. The new admin role is “CDB Administrator”. CDB administrator will login into the new concept of “CDB root” namespace. There are several commands which can be executed from the CDB root and all commands, which were working in pre-12.1 databases, are all compatible in a PDB.  You can use one of the several commands to find out which container currently you are logged in.
SQL> SELECT pdb FROM dba_services;
SQL> SELECT sys_context(‘userenv’,’con_name’) “MY_CONTAINER” FROM dual;
SQL> SHOW con_name
SQL> SELECT NAME, CON_ID FROM v$active_services ORDER BY 1;
Unplug and Plug PDB
What is more? You can plug out (unplug) a database from one container database and then plug in into another container database. This is pretty cool feature. This could be useful in many situations.

There are much more new features which are introduced to make the database completely agnostic to the hardware system, platform or releases. For an example “Application Continuity” which even connects to the database even if the database was restarted and the application would connect without failing by replaying the last failed transaction including DML. This will be explained in a separate article so please look for that article.
Faster Provisioning, Patching & Upgrades
You can clone a PDB within the same CDB or into another CDB. PDBs can also be provisioned very fast as each CDB comes with a “PDB Seed” from where the new PDB can be fast provisioned. So the provisioning becomes very fast.
Redeployment becomes much easier as we can unplug the database from one platform or a CDB version and then plug it into a CDB which is in another platform or version. This will make the upgrade, patching and redeployment efforts much faster! When you upgrade the CDB, all the PDBs will get upgraded. If you would like to control when the PDBs should be upgraded, you can create another CDB version and then unplug from the old release and then plug in to the new database release. All PDB contents are separate from each other PDB so the “separation of duties” works very well as well.
Database Administration & Granularity Control
Database administration becomes easier as CDB can be managed as a whole such as backup using RMAN, setting up disaster recovery using Data Guard etc. However you can also backup selected PDB if you choose to do so. You can also restore and recover a PDB to point in time giving separate recoverability at the PDB level by connecting to the root (CDB) or at the PDB level. When you add a PDB, it will take care of the Data Guard configuration for that PDB.
You want to do in a single operation such as backup, data guard setup, recovery, patching, upgrade etc or you want to do granular management such as recovering a PDB only, migrating / patching a PDB on its own schedule etc.  When you want to patch and you want to control when each PDB is getting patched, then you create a CDB for the new version or patch and then unplug/plug the PDB whenever the PDB is ready to be migrated. It is that easy with complete control.
Unique Service Names for PDBs
A service is created and started inside a PDB that identifies it as the initial container even though the metadata is stored separately for that PDB. But the service is unique across the CDB.  Please note that the sessions are created by authorizing as a user that cannot change the current container.  You should ensure the services within a container are unique. In fact you should have the unique services registered on a listener. I recommend using a unique service across the entire cluster. Creating services, dropping or maintaining a service within a PDB is nothing different than what we have been doing in pre-12.1 releases.  As I mentioned that each PDB comes with a default service which cannot be dropped. The only way to connect to a PDB (whose initial container is a PDB) is by using a service.
Pluggable Databases Examples
Create PDB
Now let us look at some examples to create the PDB. In this example let us assume we have a container database for “Investment Banking” called CDB01P. We will create a PDB database within that container database.

$ sqlplus sys/pwd@DBSRV/c01p as sysdba
SQL*Plus: Release 12.1.0.0.2 Production on Wed Oct 3 19:04:49 2012
Copyright (c) 1982, 2012, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “DB DETAILS” FORMAT A100
SQL> SELECT
2       ‘DB_NAME: ‘  ||sys_context(‘userenv’, ‘db_name’)||
3    ‘ / CDB?: ‘     ||(select cdb from v$database)||
4    ‘ / AUTH_ID: ‘  ||sys_context(‘userenv’, ‘authenticated_identity’)||
5    ‘ / USER: ‘     ||sys_context(‘userenv’, ‘current_user’)||
6    ‘ / CONTAINER: ‘||nvl(sys_Context(‘userenv’, ‘con_Name’), ‘NON-CDB’)
7     “DB DETAILS”
8  FROM DUAL
9  /
DB DETAILS
——————————————————————————-
DB_NAME: c01p / CDB?: YES / AUTH_ID: SYS / USER: SYS / CONTAINER: CDB$ROOT
REM Check how many PDBs are in the current container
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
REM Create the pluggable database
SQL> SET TIMING ON
SQL> create pluggable database c01p01p
2  admin user app_Admin identified by pwd
3  file_name_convert = (‘/pdbseed/’, ‘/c01p01p/’)
4  /
Pluggable database created.
Elapsed: 00:00:57.11
SQL> SET TIMING OFF
SQL>
SQL> alter pluggable database c01p01p open
2  /
Pluggable database altered.
REM Check how many PDBs are in the current container now
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P01P                           READ WRITE NO         NORMAL
REM You can also open all PDBs in command
SQL> alter pluggable database all open;
Pluggable database altered.
It was intentionally shown the pluggable database creation time to find out how fast the database can be deployed.  It took less than a minute to create a new PDB. Typically DBA takes time to create the database. Even if the infrastructure is ready, still the DBA can be spending 2 hours to half a day to provision a new database. Now that time can be wisely spent to understand the application or help tuning the SQLs to better improve the performance of the application. The database deployment can be automated and you can give an option to self provision the database really quick.  Every container has a PDB seed database which has SYSTEM and SYSAUX tablespaces from where the DB build is copied to provision a new PDB. The example shown is based on a file system based database but you can use it with ASM as well as PDB is fully compatible with RAC and ASM.
SQL> select con_id, tablespace_name, File_Name
2  from cdb_data_files
3  where file_name like ‘%/c01p/pdbseed/%’
4  or file_name like ‘%/c01p/c01p01p/%’
5  order by 1, 2
6  /
CON_ID TABLESPACE_NAME FILE_NAME
———- ————— ——————————————————–
2 SYSAUX         /u01/app/oracle/oradata/cib01p/pdbseed/sysaux01.dbf
2 SYSTEM         /u01/app/oracle/oradata/cib01p/pdbseed/system01.dbf
3 SYSAUX         /u01/app/oracle/oradata/cib01p/c01p01p/sysaux01.dbf
3 SYSTEM         /u01/app/oracle/oradata/cib01p/c01p01p/system01.dbf
Drop PDB
You can drop the PDB using a simple command. As DBAs are familiar with database commands, this command should NOT be a surprise. The syntaxes are fully in-line with existing syntaxes.
SQL> alter pluggable database c01p01p close;
Pluggable database altered.
SQL> drop pluggable database c01p01p including datafiles;
Pluggable database dropped.
Convert Non-CDB to PDB
We can convert a non-CDB database to a PDB into a container. There are few steps involved as listed below.

SQL> alter pluggable database noncdbp open;
Pluggable database altered.
SQL> alter pluggable database noncdbp close;
Pluggable database altered.
SQL> alter pluggable database noncdbp open restricted;
Pluggable database altered.
SQL> alter session set container = noncdbp;
Session altered.
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
STATUS   COUNT(*)
———- ———-
5       9968
6      39793
COUNT(*)
———-
6416
COUNT(*)
———-
6271
COUNT(*)
———-
3821
COUNT(*)
———-
3785
COUNT(*)
———-
12
COUNT(*)
———-
6
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN  2012-10-03 19:27:56
Clone a PDB
Oracle offers two types of cloning. The first option is shown in the example commands below and this can be used for fast provisioning with the same CDB or into another CDB. Oracle also offers another option to clone database on file systems which can work on “Copy On Write” technologies such as ZFS file systems or NetApp NAS file systems. The cloning will be done in few seconds and it will be much more useful in non-production environments.
REM Verify you are in the correct container database root namespace(CDB)
SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “DB DETAILS” FORMAT A100
SQL> SELECT
2       ‘DB_NAME: ‘  ||sys_context(‘userenv’, ‘db_name’)||
3    ‘ / CDB?: ‘     ||(select cdb from v$database)||
4    ‘ / AUTH_ID: ‘  ||sys_context(‘userenv’, ‘authenticated_identity’)||
5    ‘ / USER: ‘     ||sys_context(‘userenv’, ‘current_user’)||
6    ‘ / CONTAINER: ‘||nvl(sys_Context(‘userenv’, ‘con_Name’), ‘NON-CDB’)
7     “DB DETAILS”
8  FROM DUAL
9  /
DB DETAILS
——————————————————————————-
DB_NAME: c01p / CDB?: YES / AUTH_ID: SYS / USER: SYS / CONTAINER: CDB$ROOT
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P01P                           READ WRITE NO         NORMAL
SQL> alter pluggable database C01P01P close
2  /
Pluggable database altered.
SQL> alter pluggable database C01P01P open read only
2  /
Pluggable database altered.
SQL> SET TIMING ON
SQL> create pluggable database C01P02P
2  from C01P01P
3  file_name_convert = (‘/c01p01p’, ‘/c01p02p’)
4  /
Pluggable database created.
Elapsed: 00:00:31.90
SQL> SET TIMING OFF
SQL> alter pluggable database C01P01P close
2  /
Pluggable database altered.
SQL> alter pluggable database C01P01P open
2  /
Pluggable database altered.
SQL> alter pluggable database C01P02P open
2  /
Pluggable database altered.
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P01P                           READ WRITE NO         NORMAL
CO1P02P                           READ WRITE NO         NORMAL
SQL> select con_id, tablespace_name, File_Name
2  from cdb_data_files
3  order by 1, 2
4  /
CON_ID TABLESPACE_NAME FILE_NAME
———- ————— ——————————————————–
1 SYSAUX         /u01/app/oracle/oradata/cib01p/sysaux01.dbf
1 SYSTEM         /u01/app/oracle/oradata/cib01p/system01.dbf
1 USERS          /u01/app/oracle/oradata/cib01p/users01.dbf
1 UNDOTBS        /u01/app/oracle/oradata/cib01p/undotbs01.dbf
2 SYSAUX         /u01/app/oracle/oradata/cib01p/pdbseed/sysaux01.dbf
2 SYSTEM         /u01/app/oracle/oradata/cib01p/pdbseed/system01.dbf
3 SYSAUX         /u01/app/oracle/oradata/cib01p/c01p01p/sysaux01.dbf
3 SYSTEM         /u01/app/oracle/oradata/cib01p/c01p01p/system01.dbf
4 SYSAUX         /u01/app/oracle/oradata/cib01p/c01p02p/sysaux01.dbf
4 SYSTEM         /u01/app/oracle/oradata/cib01p/c01p02p/system01.dbf
Unplug and Plug PDB
Oracle offers to unplug a PDB from one CDB and then plug into a different CDB. This will be another additional feature of PDB to give the highest availability and scalability the database systems may need in a cloud infrastructure environment.
REM Verify you are in the correct container database root namespace(CDB)
SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “DB DETAILS” FORMAT A100
SQL> SELECT
2       ‘DB_NAME: ‘  ||sys_context(‘userenv’, ‘db_name’)||
3    ‘ / CDB?: ‘     ||(select cdb from v$database)||
4    ‘ / AUTH_ID: ‘  ||sys_context(‘userenv’, ‘authenticated_identity’)||
5    ‘ / USER: ‘     ||sys_context(‘userenv’, ‘current_user’)||
6    ‘ / CONTAINER: ‘||nvl(sys_Context(‘userenv’, ‘con_Name’), ‘NON-CDB’)
7     “DB DETAILS”
8  FROM DUAL
9  /
DB DETAILS
——————————————————————————-
DB_NAME: c01p / CDB?: YES / AUTH_ID: SYS / USER: SYS / CONTAINER: CDB$ROOT
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P01P                           READ WRITE NO         NORMAL
C01P02P                           READ WRITE NO         NORMAL
SQL> alter pluggable database C01P01P close
2  /
Pluggable database altered.
SQL> alter pluggable database C01P01P
2  unplug into
3  ‘/u01/app/oracle/oradata/c01p/c01p01p/c01p01pxml’
4  /
Pluggable database altered.
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P01P                           MOUNTED    n/a        UNPLUGGED
C01P02P                           READ WRITE NO         NORMAL
SQL> drop pluggable database C01P01P keep datafiles
2  /
Pluggable database dropped.
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P02P                           READ WRITE NO         NORMAL
REM Verify you are in the correct container database root namespace(CDB)
SQL> set echo on
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 10000
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “DB DETAILS” FORMAT A100
SQL> SELECT
2       ‘DB_NAME: ‘  ||sys_context(‘userenv’, ‘db_name’)||
3    ‘ / CDB?: ‘     ||(select cdb from v$database)||
4    ‘ / AUTH_ID: ‘  ||sys_context(‘userenv’, ‘authenticated_identity’)||
5    ‘ / USER: ‘     ||sys_context(‘userenv’, ‘current_user’)||
6    ‘ / CONTAINER: ‘||nvl(sys_Context(‘userenv’, ‘con_Name’), ‘NON-CDB’)
7     “DB DETAILS”
8  FROM DUAL
9  /
DB DETAILS
——————————————————————————-
DB_NAME: c02p / CDB?: YES / AUTH_ID: SYS / USER: SYS / CONTAINER: CDB$ROOT
SQL> begin
2    if not
3      dbms_pdb.check_plug_compatibility(
4        pdb_descr_file =>
5          ‘/u01/app/oracle/oradata/c01p/c01p01p/c01p01p.xml’)
6    then
7      raise_application_error(-20000, ‘PDB is not compatible to plug in’);
8    end if;
9  end;
10  /
PL/SQL procedure successfully completed.
SQL>
SQL> create pluggable database C01P01P
2  using ‘/u01/app/oracle/oradata/c01p/c01p01p/c01p01p.xml’
3  move
4  file_name_convert = (‘/c01p/’, ‘/c02p/’)
5  /
Pluggable database created.
SQL> alter pluggable database C01P01P open
2  /
Pluggable database altered.
SQL> SET SERVEROUTPUT ON
SQL> COLUMN “RESTRICTED” FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, ‘n/a’) “RESTRICTED”, d.status
2  from v$PDBs v inner join dba_pdbs d
3  using (GUID)
4  order by v.create_scn
5  /
NAME                           OPEN_MODE  RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED                       READ ONLY  NO         NORMAL
C01P01P                           READ WRITE NO         NORMAL
SQL> COLUMN “PDB NAME” FORMAT A20
SQL> select PDB_NAME, GUID
2  from DBA_PDBS
3  /
PDB_NAME             GUID
——————– ——————————–
PDB$SEED             C40F9B49FC9C19E0E0430BAAE80AFF01
C01P01P              CB2D6FABCEE71792E0430100007F79C2
Conclusion
Oracle Database 12c offers the pluggable databases which are extremely useful to run a cloud database environment whether that database runs in Oracle’s Public Cloud, Oracle Internal Cloud behind corporate firewalls or even customer owned discrete infrastructure with 12.1 clusters. Oracle Database 12c is completely platform agnostic which makes it as unique first RDBMS with ACID compliance which makes no difference between the version runs in the cloud as well as in private data centers.

Exit mobile version