Local Undo in Oracle Database 12c R2(12.2)



Last updated on December 17th, 2017 at 05:20 pm

Problem statement

We have global or shared undo for the entire CDB in 12c R1. With shared undo, before performing operations such as (cold) clone or unplug, it is necessary for the database 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.)

The other difficulties were cloning of read write pluggable database , Flashback of individual PDB.

Solution

Oracle 12.2 introduce per-PDB or local undo.Local Undo is a new kind of undo configuration for Multitenant Architecture and it is a new feature introduced in 12.2.0.1.0. Local undo avoids these difficulties and thereby significantly improves the predictability of these important operations. Additionally, it enables many of the major new capabilities of Multitenant in 12.2, including:
a) Hot Clone
b) Refreshable PDB
c) PDB Relocate
d) Flashback PDB

 

How to configure Local Undo

Shutdown the database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start the database up in upgrade mode:

SQL> startup upgrade;
ORACLE instance started.

Database mounted.
Database opened.

Enable Local Undo:

SQL> alter database local undo on;

Database altered.

Shutdown the database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start the database up normally:

SQL> startup;
ORACLE instance started.
Database mounted.
Database opened.

Confirm the new undo confiruation is “Local Undo”:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘LOCAL_UNDO_ENABLED’

PROPERTY_NAME PROPERTY_VALUE


LOCAL_UNDO_ENABLED TRUE

When you try to open the all the the Pluggable Databases after this change, a new local undo tablespace will be created for each PDB

SQL> alter pluggable database all open;

Pluggable database altered.

All the Pluggable Databases will have its own Undo Tablespace, by default the undo tablespace is called “UNDO_1”.

SQL> select pdb.name PDB_NAME, tbs.name TABLESPACE_NAME from v$tablespace tbs, v$pdbs pdb where tbs.con_id=pdb.con_id and tbs.name like ‘UNDO%’ order by 1;

PDB_NAME TABLESPACE_NAME


PDB1 UNDO_1
PDB$SEED UNDO_1

 

How to disable it:

shutdown the database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start the database up in upgrade mode:

SQL> startup upgrade;
ORACLE instance started.

Database mounted.
Database opened.

Disable Local Undo:

SQL> alter database local undo off;

Database altered.

Shutdown the database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start the database up normally:

SQL> startup;
ORACLE instance started.

Database mounted.
Database opened.

 

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘LOCAL_UNDO_ENABLED’;

PROPERTY_NAME PROPERTY_VALUE


LOCAL_UNDO_ENABLED FALSE

 

How to delete Undo Tablespaces after switch from Local Undo to Shared Undo:

There is an important thing here that you should know when you switch from Local Undo to Shared Undo. Since you used Local Undo you know that every Pluggable Database had its own Undo Tablespace, however when you enable “Shared Undo” all those undo tablepaces are not removed, which means that you will have them there and you have to take a decision either leave them there or remove them.

SQL> select pdb.name PDB_NAME, tbs.name TABLESPACE_NAME from v$tablespace tbs, v$pdbs pdb where tbs.con_id=pdb.con_id and tbs.name like ‘UNDO%’ order by 1;

PDB_NAME TABLESPACE_NAME


PDB1 UNDO_1
PDB$SEED UNDO_1

 

Shared undo is still supported in 12.2, but that is primarily for upgrade transitional purposes only. Arguably there are minor technical and management overheads to having per-PDB undo but these are vastly outweighed by the benefits of having local undo (enabling all the new capabilities described in this White Paper and elsewhere). Simple rules are very often best. In the absence of a really strong case for shared undo in a specific situation, a very good simple rule to follow is “transition to local undo ASAP in all cases”. By default, Database Configuration Assistant (DBCA) creates new CDBs with local undo enabled.

Some Important Point

  1. Undo mode – whether it’s local or shared – is an all-or-nothing property of the entire CDB. There’s no half-way situation. Either all the PDBs have local undo or there’s shared undo for the entire CDB.
  2. There are capabilities to switch between local and shared undo and back again. This transition requires the CDB to be restarted.
  3. When moving a PDB from a CDB with shared undo to one with local undo, the required local undo tablespace(s) are created automatically. When moving a PDB from a CDB with local undo into one with shared undo, the local undo tablespace will briefly be used for rollback of uncommitted transactions the first time the PDB is opened read-write.Thereafter it is not required and can be dropped. The same applies when a CDB is transitioned from shared undo to local undo or vice versa.
  4. A local undo tablespace is required for each node in an Oracle Real Application Clusters (RAC) cluster in which the PDB is open. If a PDB is moved from 2-node RAC to 4-node RAC (and opened in all nodes) the additional required undo tablespaces are automatically created. If the PDB is moved back again, two will be redundant and can be dropped.
  5. It is not necessary to set CDB-level database parameter compatible to 12.2 to enable local undo.
  6. Undo mode (shared or local) is not considered a plug-compatibility issue.

Related links for 12c Oracle database

How to remotely clone a PDB or non-CDB in Oracle Database 12.1

New Optimizer Feature with 11g and 12c

Oracle Database Interactive Quick Reference Guide | 11g & 12c

approx_count_distinct : Oracle Database 12c

Cloud Computing Basics: iaas paas saas

Top Oracle 12c New Features for developers


Leave a Reply