Difference between 12cR1 and 12cR2 Multitenant database
There are lot of new features introduced by Oracle in 12c Release 2 version when compared to 12c Release 1. Here are some of the differences that you must know
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 operation, connections to the PDB will have been switched from its original location to the new location. 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.
PDB Relocate
Enhancements to Unplug/Plug
Flashback PDB
4k PDBs per CDB on Oracle Cloud and Oracle Exadata
Memory Management Enhancement at PDB level
Per-PDB Character Sets
Automatic Workload Repository (AWR) Data at PDB Level
System Access
File Access
Lockdown Profiles
Data Guard Enhancements
Software as a Service (SaaS)
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 the source; Refreshable PDB Let say for Example We have a 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 operation, connections to the PDB will have been switched from its original location to the new location. 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 the 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.
Related Posts
Heading 2
Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.