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.
