Local and Shared Undo Tablespace Modes

Oracle introduced Local Undo since 12cR2. Before 12cR2, there is no local undo concept, the Undo tablespace used to be shared or global across all the PDBs in a container.

How to check the current Undo mode

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        TRUE       true if local undo is enabled

How to convert CDB from Local to Shared Undo Mode

When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

SQL> startup upgrade

SQL> sho con_name 

CON_NAME
-----------------
CDB$ROOT

SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.

 
SQL> shutdown immediate

SQL> startup

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        FALSE       true if local undo is enabled

-- Check current undo tablespaces for CDB and all PDBS.
--
SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

 
--
-- Drop the Undo tablespace from the PDBs
--
SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.

How to convert CDB from Shared to Local Undo Mode

When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

SQL> startup upgrade

SQL>  alter database local undo on;

SQL> startup force;

-- Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace
--
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

SQL> CREATE UNDO TABLESPACE seed_undo datafile '+DATA1'......

SQL> alter system set  UNDO_TABLESPACE='SEED_UNDO' scope=both;

SQL> show parameter undo_tablespace
undo_tablespace                      string      SEED_UNDO


SQL>  ALTER SESSION SET CONTAINER=CDB$ROOT; 

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

-- Open the PDB is read write mode

SQL> ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE;

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
-------------------------------------------------------------------
         3 UNDO_1

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf  << Undo tablespace is created automatically.

          1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

-- From the PDB
--

SQL> ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
---------------------------------------------------------------

         3 UNDO_1 << Undo tablespace is created automatically.

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf