Local UNDO in Multitenancy

With the introduction of Oracle Database 12c, the multitenant architecture allows multiple pluggable databases (PDBs) to coexist within a single container database (CDB). In Oracle Database 19c, a significant enhancement to this architecture is the support for Local UNDO, which allows each PDB to manage its own UNDO tablespace independently. This feature enhances performance, isolation, and manageability in a multitenant environment.

Advantages of Local UNDO:

• Improved Performance: By isolating UNDO management within each PDB, Local UNDO reduces contention and potential bottlenecks that can occur when multiple PDBs share a common UNDO tablespace.
• Enhanced Isolation: Local UNDO ensures that operations in one PDB do not impact the UNDO performance of other PDBs, leading to better stability and predictability.
• Simplified Management: DBAs can manage UNDO tablespaces at the PDB level, tailoring UNDO settings to the specific workload and requirements of each PDB.

Enabling Local UNDO

To enable Local UNDO in a multitenant environment, you need to configure the CDB and each PDB appropriately. Below are the steps to enable Local UNDO:

1. Enable Local UNDO at the CDB Level: First, set the UNDO_MODE parameter to LOCAL in the CDB.

-- Connect to the root container as a privileged user
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Enable Local UNDO
ALTER SYSTEM SET UNDO_MODE = LOCAL SCOPE = SPFILE;

-- Restart the CDB to apply the changes
SHUTDOWN IMMEDIATE;
STARTUP;

2. Create UNDO Tablespace in Each PDB: Next, create an UNDO tablespace in each PDB that will use Local UNDO.

-- Connect to each PDB and create an UNDO tablespace
ALTER SESSION SET CONTAINER = pdb1;

-- Create an UNDO tablespace in PDB1
CREATE UNDO TABLESPACE undotbs1 DATAFILE 'undotbs1.dbf' SIZE 500M;

-- Repeat for other PDBs
ALTER SESSION SET CONTAINER = pdb2;
CREATE UNDO TABLESPACE undotbs2 DATAFILE 'undotbs2.dbf' SIZE 500M;

3. Configure PDBs to Use Local UNDO: Ensure each PDB is configured to use its Local UNDO tablespace.

-- Set the UNDO tablespace for each PDB
ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs1;

ALTER SESSION SET CONTAINER = pdb2;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

4. Verify Local UNDO Configuration: Finally, verify that each PDB is using its designated UNDO tablespace.

-- Check the UNDO tablespace settings in each PDB
ALTER SESSION SET CONTAINER = pdb1;
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

ALTER SESSION SET CONTAINER = pdb2;
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

By following these steps, you enable Local UNDO in a multitenant environment, ensuring each PDB can independently manage its own UNDO data. This configuration not only enhances the performance and isolation of individual PDBs but also simplifies the overall database management process, making it easier to tailor UNDO settings to the specific needs of different applications and workloads within the same CDB.