Oracle 12c Multi-tenant CDB and PDB shared undo and Local Undo Toggle

Undo only supports the global shared undo mode in the 12C R1 release, all container share an undo tablespace, which is currently reserved for upgrade transitions, and the PDB Local Undo mode is introduced in 12C R2. Each container has its own undo tablespace, which is recommended for each container that has its own undo tablespace for each instance of RAC. The local undo option is available at DBCA and is checked by default.

Benefits of the local Undo mode:
1. Reduce the contention of the undo table space while facilitating the plug-in
2. The following new features are supported only with local undo: Refresh PDB, Flashback pdb, (Hot Clone, relocate pdb in open Read/write mode)
3.point-in-time Recovery PDB

One, local undo transfer to shared undo:

Check if the database is on local undo and if Property_value is true indicates that it is turned on.
Sql>
Col property_name for A25;
Col Property_value for A25;
Select Property_name,property_value from database_properties where property_name= ' local_undo_enabled ';

Property_name Property_value
------------------------- -------------------------
Local_undo_enabled TRUE
Sql>shutdown immediate;
Sql>startup upgrade;
Sql>show Con_namecon_name
------------------------------
Cdb$root
Sql>alter database local undo off;
Sql>shutdown immediate;
sql>startup;
Sql>show PDBs
Sql>alter session Set CONTAINER=PDB01;
E/o1_mf_undotbs1_djszmxkc_.dbf
Sql>select tablespace_name from Dba_tablespaces;
Tablespace_name
------------------------------
SYSTEM
Sysaux
UNDOTBS1
TEMP
USERS
Sql>
Col property_name for A30;
Col Property_value for A20;
Select Property_name,property_value from database_properties where property_name= ' local_undo_enabled ';
Property_name Property_value
------------------------- -------------------------
Local_undo_enabled FALSE
Sql>
Col file_name for A70;
Select A.con_id,a.table con_id tablespace_name 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 ';
--------- ------------------------------ ----------------------------------------
3 undotbs1/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598/
Datafile/o1_mf_undotbs1_djszmxkc_.dbfspace_name,b.file_name
Sql>drop tablespace UNDOTBS1 including contents and datafiles; (Remember to execute in the PDB)
Sql>select name from V$datafile where name is like '%undo% ';
NAME
-----------------------------------------------------------------------------
/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/DATAFILE/O1_MF_UNDOTBS1_DJSYYJLK_.DBF > is now the Undo datafile in CDB.

Ii. shared undo transfer to local undo:

sql> shutdown immediate;
Sql> startup upgrade;
Sql> Show Con_name > Confirm is CDB, if not CDB, alter session set Container=cdb$root;
Sql> ALTER DATABASE local undo on;
sql> shutdown immediate;
Sql> startup;
Sql>show PDBs
Sql>alter session Set CONTAINER=PDB01;
Sql>select name from V$datafile where name is like '%undo% ';
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/andycdb/4ecf8621e3da38eee0531019640aa598/datafil
E/o1_mf_undo_1_djz4jq1z_.dbf
Sql>
Col property_name for A30;
Col Property_value for A20;
Select Property_name,property_value from database_properties where property_name= ' local_undo_enabled ';

Property_name Property_value
------------------------------ --------------------
Local_undo_enabled TRUE
Description: After you open Loacl Undo, the Undo Tablespace is automatically created for the current PDB, and if you create a new PDB previously, your undo tablespace will be created automatically. The name and size of the created tablespace is determined by the configuration information in Pdb$seed.

Oracle 12c Multi-tenant CDB and PDB shared undo and Local Undo Toggle