Undo Tablespace
2 min readThere are two types of Undo Modes in 12.2 1) Shared Undo - The Undo tablespace is common, it is shared among the entire CDB. The Management of undo tablespace is similar to Non-CDB database. 2) Local Undo - The Local undo is local particular PDB or a container
The benefits having local undo enables below operations on PDB. 1) Relocate PDB 2) Plugging PDB 3) Flashback PDB 4) PITR of PDB without auxiliary Instance
How to check the Undo mode for the database
SQL> set lines 300
SQL> col property_name for a30
SQL> col description for a40
SQL> col PROPERTY_VALUE for a15
SQL> SELECT * FROM database_properties where property_name like '%UNDO%';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ --------------- ----------------------------------------
LOCAL_UNDO_ENABLED TRUE true if local undo is enabled
Changing UNDO mode to Local Undo
SQL> SELECT * FROM database_properties where property_name like '%UNDO%';
no rows selected
SQL> shutdown immediate
SQL> startup upgrade
SQL> alter database local undo on;
SQL> shutdown immediate
SQL> startup
SQL> SELECT * FROM database_properties where property_name like '%UNDO%';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ --------------- ----------------------------------------
LOCAL_UNDO_ENABLED TRUE true if local undo is enabled
Turn off Local Undo
SQL> shutdown immediate
SQL> startup upgrade
SQL> alter database local undo off;
Drop undo tablespace in PDB
SQL> shutdown immediate
SQL> startup
EDIT PDB$SEED to change Undo properties
The Database must be in Local Undo Mode.
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;
SQL> CREATE UNDO TABLESPACE LOCALUNDO DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
NOTE: The PDB$SEED in shared undo mode can be open to read write with “_oracle_srcipt”=TRUE underscore parameter.
