WHY DO WE REQUIRE LOCAL UNDO IN ORACLE MULTITENANT?
During the introduction of multitenant in 12.1.0.2 , there used to be global or shared undo tablespace for all pdb which had lot of challenges like ,
- cold clone of pdb in open read only mode
- Unable to refresh snapshot of pdb
- Flashback
- Relocation of pdb
From 12.2, local undo was introduced, which lead to lot of new features
In 12c, there was no option to check local undo.
In 19c, there is an automatic option for local undo which is enabled. It is up to the DBA, to switch on or off local undo. But disabling it, may have lot of features useless.
Local undo is used and should be recommended to use to get the following benefits
- Hot cloning
- PDB relocation
- PDB proxy
By default, the undo mode is true in 19c
kIsH<X>col PROPERTY_NAME for a20
kIsH<X>col PROPERTY_VALUE for a20
kIsH<X>SELECT DISTINCT property_name, property_value FROM cdb_properties WHERE property_name like '%LOCAL_UNDO%';
PROPERTY_NAME PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED TRUE