• Arun Kumar

Difference between 12cR1 and 12cR2 Multitenant database

Local Undo


In 12cR1, there is a global or shared undo for the entire CDB. With shared undo, before performing operations such as clone or unplug, it is necessary to check for any uncommitted transactions in the source PDB. This is to avoid problems with transactional consistency in the PDB after the clone or plug-in operation. (If any uncommitted transactions exist, the following error is issued: ORA-65126 – Pluggable database was not closed cleanly and there are active transactions that need to be recovered. In such cases, it is necessary to open the PDB read-write, and wait until the SMON process can clean them up.)


In 12cR2 we introduce per-PDB or local undo. Local undo avoids these difficulties and thereby significantly improves important operations. Additionally, it enables many of the major new capabilities of Multitenant in 12cR2, including:

  • Hot Clone

  • Refreshable PDB

  • PDB Relocate

  • Flashback PDB

Note: shared undo mode is still supported in 12cR2

Cloning of PDB’s


In 12cR1 cloning of PDB was “cold cloning” because at the time of cloning there should not be any transaction running till cloning is in progress, so from 12cR2 oracle introduce “hot cloning” of PDB, it means while you clone your PDB, it could be available for read-write operations. No need to take the application down which running on PDB which is involved in cloning activity. condition apply —>Archive logging must also be enabled to perform hot clones.


No change in the existing statement of cloning PDB: create pluggable database target from source;


Refreshable PDB


Let say for Example We have Development Environment for which we use a copy of Production PDB. Here in Production PDB all data keep changing and database size keep increasing all the time, so if we want to refresh Development Data, it becomes a lengthy process to drop PDB and create new PDB from production PDB all the time so Developer can get the Latest data.


Refreshable PDB gives here a big advantage to all DBA working with Multitenant Architecture. We can create on taking a full clone of source PDB (Production PDB) consider it as “Golden Master Copy”. Now From That Golden Master PDB all, we can take clone in future which can be created in seconds or minutes even for very large databases. Here the condition is Refreshable PDB should be open in read-only mode and that PDB must be closed during the refresh operation. Now we need to refresh that Refreshable PDB from the source all the time to keep it aligns with the Production version.


Refresh Options:

  • Manual (we can refresh it manually when we want)

  • ALTER SESSION SET CONTAINER=refreshable_pdb;

  • ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

  • ALTER PLUGGABLE DATABASE REFRESH;

  • ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Automatic Refresh

  • create pluggable database refreshable_pdb from source_PDB@DBLink refresh mode every 60;(interval in minutes)

  • Can be altered in future: ALTER PLUGGABLE DATABASE refreshable_pdb REFRESH MODE EVERY 120 MINUTES;

Note: once the PDB is made non-refreshable, it can’t be made refreshable again.

PDB Relocate


Moving PDBs between CDBs using plug/unplug method required downtime during plug/unplug operations. For large databases, it takes considerable time. PDB Relocate is a new method of moving a PDB between CDBs. This is introduced in 12cR2. The goal is to eliminate downtime completely. Technically, PDB Relocate is built on top of Refreshable PDB, which is built on top of a hot clone. PDB Relocate could be achieved in two steps, as follows:


Relocate PDB to the new server :

  • It involves cloning the PDB from its original location to its desired new location. In other words, After Completion of this step there will be two transactionally consistent copies of this PDB, one in the source server and one in the target server. For the duration of the operation, all transactions continue on the database in its original location (source server). Users of an application or applications connected to the database will be unaware that relocation is going on. Technically, this is the same as the creation of a Refreshable PDB, as described above. All existing application connections, and new connections made during this step, continue to be to the PDB in its original location.

Open PDB in its new location :

  • This step completes the relocation. That is transparent to the application user, although at the end of the the operation, connections to the PDB will have been switched from its original location to the new location. An an important component of this step is to ensure that all transactions committed in the PDB in its source location are preserved in the target location.

  1. PDB Relocate

  2. Enhancements to Unplug/Plug

  3. Flashback PDB

  4. 4k PDBs per CDB on Oracle Cloud and Oracle Exadata

  5. Memory Management Enhancement at PDB level

  6. Per-PDB Character Sets

  7. Automatic Workload Repository (AWR) Data at PDB Level

  8. System Access

  9. File Access

  10. Lockdown Profiles

  11. Data Guard Enhancements

  12. Software as a Service (SaaS)

Difference between 12cR1 and 12cR2 Multitenant databaseLocal Undo In 12cR1, there is a global or shared undo for the entire CDB. With shared undo, before performing operations such as clone or unplug, it is necessary to check for any uncommitted transactions in the source PDB. This is to avoid problems with transactional consistency in the PDB after the clone or plug-in operation. (If any uncommitted transactions exist, the following error is issued: ORA-65126 – Pluggable database was not closed cleanly and there are active transactions that need to be recovered. In such cases, it is necessary to open the PDB read-write, and wait until the SMON process can clean them up.) In 12cR2 we introduce per-PDB or local undo. Local undo avoids these difficulties and thereby significantly improves important operations. Additionally, it enables many of the major new capabilities of Multitenant in 12cR2, including:

  • Hot Clone

  • Refreshable PDB

  • PDB Relocate

  • Flashback PDB

Note: shared undo mode is still supported in 12cR2 Cloning of PDB’s In 12cR1 cloning of PDB was “cold cloning” because at the time of cloning there should not be any transaction running till cloning is in progress, so from 12cR2 oracle introduce “hot cloning” of PDB, it means while you clone your PDB, it could be available for read-write operations. No need to take the application down which running on PDB which is involved in cloning activity. condition apply —>Archive logging must also be enabled to perform hot clones. No change in the existing statement of cloning PDB: create pluggable database target from source; Refreshable PDB Let say for Example We have Development Environment for which we use a copy of Production PDB. Here in Production PDB all data keep changing and database size keep increasing all the time, so if we want to refresh Development Data, it becomes a lengthy process to drop PDB and create new PDB from production PDB all the time so Developer can get the Latest data. Refreshable PDB gives here a big advantage to all DBA working with Multitenant Architecture. We can create on taking a full clone of source PDB (Production PDB) consider it as “Golden Master Copy”. Now From That Golden Master PDB all, we can take clone in future which can be created in seconds or minutes even for very large databases. Here the condition is Refreshable PDB should be open in read-only mode and that PDB must be closed during the refresh operation. Now we need to refresh that Refreshable PDB from the source all the time to keep it aligns with the Production version. Refresh Options:

  • Manual (we can refresh it manually when we want)

  • ALTER SESSION SET CONTAINER=refreshable_pdb;

  • ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

  • ALTER PLUGGABLE DATABASE REFRESH;

  • ALTER PLUGGABLE DATABASE OPEN READ ONLY;


  • Automatic Refresh

  • create pluggable database refreshable_pdb from source_PDB@DBLink refresh mode every 60;(interval in minutes)

  • Can be altered in future: ALTER PLUGGABLE DATABASE refreshable_pdb REFRESH MODE EVERY 120 MINUTES;


Note: once the PDB is made non-refreshable, it can’t be made refreshable again. PDB Relocate Moving PDBs between CDBs using plug/unplug method required downtime during plug/unplug operations. For large databases, it takes considerable time. PDB Relocate is a new method of moving a PDB between CDBs. This is introduced in 12cR2. The goal is to eliminate downtime completely. Technically, PDB Relocate is built on top of Refreshable PDB, which is built on top of a hot clone. PDB Relocate could be achieved in two steps, as follows:

  • Relocate PDB to the new server :

  • It involves cloning the PDB from its original location to its desired new location. In other words, After Completion of this step there will be two transactionally consistent copies of this PDB, one in the source server and one in the target server. For the duration of the operation, all transactions continue on the database in its original location (source server). Users of an application or applications connected to the database will be unaware that relocation is going on. Technically, this is the same as the creation of a Refreshable PDB, as described above. All existing application connections, and new connections made during this step, continue to be to the PDB in its original location.


  • Open PDB in its new location :

  • This step completes the relocation. That is transparent to the application user, although at the end of the the operation, connections to the PDB will have been switched from its original location to the new location. An an important component of this step is to ensure that all transactions committed in the PDB in its source location are preserved in the target location.

Enhancements to Unplug/Plug


In 12.1, export and import of keys were achieved separately from the PDB unplug/plug operation. In 12.2 the migration of encryption keys is simplified through integration with the unplug/plug operation itself. Technically the encryption keys are transported via the XML manifest file, where they are encrypted by a shared secret. This is achieved with some additional clauses in the unplug and plug statements as follows:


Unplug: Executed as SysDBA in Root container of source CDB: alter pluggable database PDB unplug into PDB_manifest.xml encrypt using <shared secret>;


Plug: Executed as SysDBA in Root container of destination CDB: create pluggable database PDB using PDB_manifest.xml decrypt using <shared secret>;


Flashback PDB


In Oracle 12cR1 there is no PDB-level flashback database. So We need to flashback entire CDB with it’s all associated PDBs which means loss of data for all PDBs during Flashback Database operation. Flashback PDB is now fully supported with Oracle 12R2 for that oracle introduced local undo feature in 12cR2.


4k PDBs per CDB on Oracle Cloud and Oracle Exadata


Oracle 12cR1 we can have 252 PDBs under one CDB and In Oracle 12cR2 we can have 4096 PDBs under one CDB (on Oracle Cloud and Oracle Exadata only). In other platforms, the limit remains 252 PDBs per CDB.


Memory Management Enhancement at PDB level


Now with Oracle 12cR2, it is possible to set the following parameters at PDB level (which were previously modifiable only at CDB level in 12cR1):

  • SGA_TARGET

  • SGA_MIN_SIZE (new in 12cR2)

  • DB_CACHE_SIZE

  • DB_SHARED_POOL_SIZE

  • PGA_AGGREGATE_LIMIT

  • PGA_AGGREGATE_TARGET

Per-PDB Character Sets


From 12cR2 Oracle now it is possible to set character set for each PDB under one CDB. It is not mandatory to have same character set for CDB and all associated PDB which was mandatory in Oracle 12cR1.


Automatic Workload Repository (AWR) Data at PDB Level


In Oracle 12cR1 AWR report were stored at CDB root container which means if you unplug PDB from one CDB to another then you will loss AWR data for that PDB. From Oracle 12cR2 AWR report available at PDB level.


System Access


While using HOST commands from SQL Plus to interact with Operating System from within Oracle Database all operations are performed under Oracle OS user credentials. In 12cR2 Oracle introduce a new PDB level parameter – PDB OS Credential. Where appropriate, this can be used to identify an OS user (presumably one far less privileged than the Oracle user) which will be used when the OS is accessed.


File Access


In 12cR2 Oracle introduce two new clauses to the create pluggable database statement – Path_Prefix and Create_File_Dest.


These control the placement of and access to the PDB’s files by specifying mount points within the file system so that each PDB’s datafiles and directory objects are isolated in a sub-branch of the file system.


Lockdown Profiles


From 12cR2 Oracle introduced a lockdown profile mechanism to restrict certain operations or functionalities in a PDB. This new Multitenant feature is managed by a CDB administrator and can be used to restrict user access in a particular PDB. A lockdown profile can prevent PDB users from:


Executing certain SQL statements, such as ALTER SYSTEM and ALTER SESSION,

  • Running procedures that access the network (e.g. UTL_SMTP, UTL_HTTP)

  • Accessing a common user’s objects

  • Interacting with the OS (In addition to the capabilities covered by PDB_OS_CREDENTIAL)

  • Making unrestricted cross-PDB connections in a CDB

  • Taking AWR snapshots

  • Using JAVA partially or as a whole

  • Using certain database options such as Advanced Queueing and Partitioning.

A single lockdown profile can have several rules defined in it. In other words, you don’t have to create a lockdown profile for every restriction you want to implement it.A PDB can have only one lockdown profile active at a time. The restrictions enforced by a lockdown profile are PDB-wide, they affect every single user including the SYS and SYSTEM.

Example Commands:

SQL> create lockdown profile pdb_profile;
Lockdown Profile created.

SQL> alter lockdown profile pdb_profile disable statement=('alter system') clause=('set') option all;

SQL> alter lockdown profile pdb_profile disable statement =(ALTER SYSTEM)clause = (SET)option = ALL EXCEPT(‘plsql_code_type’,’plsql_debug’,’plsql_warnings’);

SQL> alter lockdown profile pdb_profile disable feature=('NETWORK_ACCESS');

DBA_LOCKDOWN_PROFILES to see the details of our lockdown profile

SQL> select profile_name, rule_type, rule, clause, clause_option, status, users from DBA_LOCKDOWN_PROFILES;

Data Guard Enhancements


In 12cR1, ENABLED_PDBS_ON_STANDBY initialization parameter only supported two values: all PDBs or none

  • The ENABLED_PDBS_ON_STANDBY parameter is only applicable to the physical standby database

  • can accept a list of PDB names or a glob pattern such as “MYPDB?” or “MYPDB*a”, “MYPDB2”

  • Glob pattern rules are similar to regular expression rules in common UNIX shells

  • asterisk (*) and question mark (?) wildcard characters are supported.

  • The question mark (?) represents a single unknown character;

  • the asterisk (*) represents matches to any number of unknown characters.

  • ENABLED_PDBS_ON_STANDBY=“*” means that all PDBs will be created on physical standby in Oracle 12cR1 and Oracle 12cR2

  • ENABLED_PDBS_ON_STANDBY=“MYPDB1*” means that MYPDB1A, MYPDB1B, and MYPDB1C will be created on the physical standby in Oracle 12cR2

  • ENABLED_PDBS_ON_STANDBY=“MYPDB*A” means that MYPDB1A, MYPDB2A, and MYPDB3A will be created on physical standby in Oracle 12cR2

Software as a Service (SaaS)


Oracle Multitenant for Software as a Service Multitenancy implemented by the Database, not the Application in Oracle 12cR2.


In 12cR2, to address these important SaaS requirements, oracle introduce a new concept Application Container. The user now can Create a pluggable database as Application Container from 12cR2.




Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis