Local Undo in Oracle Database 12c R2(12.2)/19c and above

Why Local Undo is required

We have global or shared undo for the entire CDB till 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 and flashback of individual PDB.

Starting 12.2, Oracle introduces 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 configuration 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 all 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 their 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.

Check the configuration

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 switching 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 has 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 Points for Undo

1. Undo mode – whether it’s local or shared – is an all-or-nothing property of the entire CDB. There’s no halfway 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: Check out this post for remotely clone a PDB or non-CDB. Detailed steps are given with examples to execute them.
Oracle Database 19c new features: The most significant Oracle Database 19c new features are Auto indexing,sql Quarantine, ADDM support for Pluggable databases, privileged analysis etc.
approx_count_distinct: Oracle Database 12c: The approx_count_distinct is a 12c new feature to reduce the time required to get the number of distinct values (NDV) for a table column
New Optimizer Feature with 11g,12c and 19c
Oracle Database Interactive Quick Reference Guide | 11g ,12c, 19c & 23c
Cloud Computing Basics: iaas paas saas