Local/Shared Undo mode in oracle 12.2 multitenant database

There are two undo modes in oracle 12.2 Multitenant database

1. Local undo mode
2. Shared undo mode

Local undo mode:

In this mode, each container ( i.e PDB ) in multitenant will have their own active undo tablespace.

Share Undo mode:

In this mode, There will be only one undo tablespace for the instance.

NOTE – In previous releases, undo was in shared mode.
NOTE – From oracle 12.2, Oracle recommends to use local undo mode only.

How to check the current undo mode:

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ---------------
LOCAL_UNDO_ENABLED TRUE

Convert local undo mode to shared undo mode:

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ---------------
LOCAL_UNDO_ENABLED TRUE

SQL> sho con_name

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

shutdown immediate;
startup upgrade;
SQL> ALTER DATABASE LOCAL UNDO off;

Database altered.

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ---------------
LOCAL_UNDO_ENABLED FALSE

shutdown immediate;
startup

alter pluggable database PDB1 OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

After converting to shared undo mode, the individual undo tablespaces won’t be dropped automatically.
We have to drop them manually, by connecting to the respective database.

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
---------- ---------------- ----------------------
1 UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
2 UNDOTBS1 /u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/orcl/pdb2/undotbs01.dbf

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> ALTER SESSION SET CONTAINER=PDB2;

Session altered

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

Convert shared undo mode to local undo mode:

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ---------------
LOCAL_UNDO_ENABLED FALSE

shutdown immediate;
startup upgrade;

alter database local undo on;

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ---------------
LOCAL_UNDO_ENABLED TRUE

shutdown immediate;
startup

alter pluggable database PDB1 OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

Once local undo is enabled, individual undo tablespaces will be created automatically for each PDB.