How to Enable Local UNDO Mode in CDB-PDB Multitenant in Oracle

January 16, 2022

How to Enable Local UNDO Mode in CDB-PDB Multitenant in Oracle

In this article, We will demonstrate How to Enable Local UNDO Mode in CDB-PDB Multitenant architecture in Oracle. Local UNDO mode is a new feature of the Oracle database version12cR2 (12.2.0.1). Prior to this version, CDB UNDO tablespace was shared across all PDBs, and still, it can be created as a shared UNDO tablespace. While creating a database using DBCA we have an option to create a Container database where we can select the option for Use Local Undo tablespace PDBs, refer to the below image, or manual create database command.

When a CDB is in local undo mode, each container (PDB) has its own undo tablespace for every instance in which it is open. Oracle Database automatically creates an undo tablespace in PDB in the multitenant architecture that does not have one. Local UNDO tablespace can be useful in the below-mentioned activities:

1. Hot cloning of pluggable databases (PDBs).
2. Relocate pluggable databases (PDBs).
3. Refresh pluggable databases (PDBs).
4. Flashback pluggable databases (PDBs).

Whenever a CDB is changed from shared UNDO mode to local UNDO mode, Oracle database performs automatically undo tablespace creation in all existing PDBs. So let’s move on change shared UNDO mode to local UNDO mode.

1. Shared or Local UNDO mode: Determine whether enable shared UNDO mode or local UNDO mode in CDB.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> SET LINES 333
SQL> COL PROPERTY_NAME FOR A21
SQL> COL PROPERTY_VALUE FOR A15
SQL> COL DESCRIPTION FOR A35
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';

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

2. Enable Local UNDO Mode: To change mode from shared to local mode, need to start CDB in upgrade mode.

2.1: Start the CDB in upgrade mode.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area  788526624 bytes
Fixed Size                  9139744 bytes
Variable Size             545259520 bytes
Database Buffers          230686720 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         3 DBSGURU_PDB                    MOUNTED
		 4 DBS_PDB1                       MOUNTED

2.2: Execute alter database command to enable local UNDO mode and restart CDB normal.

SQL> ALTER DATABASE LOCAL UNDO ON;

Database altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  788526624 bytes
Fixed Size                  9139744 bytes
Variable Size             545259520 bytes
Database Buffers          230686720 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.

3. Validate Local UNDO Mode: Now Validate the local undo mode enable in CDB & UNDO tablespace in each PDBs.

3.1. Validate the local UNDO mode in CDB.

SQL> SET LINES 333
SQL> COL PROPERTY_NAME FOR A21
SQL> COL PROPERTY_VALUE FOR A15
SQL> COL DESCRIPTION FOR A35
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';

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

3.2: Open all PDBs and validate UNDO tablespace.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DBSGURU_PDB                    READ WRITE NO
         4 DBS_PDB1                       READ WRITE NO
SQL> COL NAME for A11
SQL> SELECT C.CON_ID, P.NAME , C.TABLESPACE_NAME, C.STATUS FROM CDB_TABLESPACES C,V$PDBS P WHERE  C.TABLESPACE_NAME LIKE 'UNDO%' AND  C.CON_ID=P.CON_ID ORDER BY C.CON_ID;

    CON_ID NAME              TABLESPACE_NAME                STATUS
---------- ----------------- ------------------------------ ---------
         3 DBSGURU_PDB       UNDOTBS1                       ONLINE
         4 DBS_PDB1          UNDO_1                         ONLINE

NOTE: Refer CDB alert log for UNDO tablespace creation while opening pluggable databases (PDBs).

2022-01-08T17:11:34.758707+05:30
DBSGURU_PDB(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2022-01-08T17:11:35.429430+05:30
DBSGURU_PDB(3):Opening pdb with no Resource Manager plan active
2022-01-08T17:11:35.678189+05:30
db_recovery_file_dest_size of 5120 MB is 1.32% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2022-01-08T17:11:35.732736+05:30
DBSGURU_PDB(3):joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.0.0/db_1/javavm/admin/, pid 182310 cid 3
2022-01-08T17:11:37.706406+05:30
DBSGURU_PDB(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/oradata/DBSGURU/dbsguru_pdb/system01_i1_undo.dbf' SIZE 135266304 AUTOEXTEND ON NEXT 1572864 MAXSIZE 10307919872 ONLINE
2022-01-08T17:11:38.936486+05:30
DBSGURU_PDB(3):[182310] Successfully onlined Undo Tablespace 6.
DBSGURU_PDB(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/oradata/DBSGURU/dbsguru_pdb/system01_i1_undo.dbf' SIZE 135266304 AUTOEXTEND ON NEXT 1572864 MAXSIZE 10307919872 ONLINE
Pluggable database DBSGURU_PDB opened read write
-------------------------Trimmed Data-------------------------
Starting background process CJQ0
2022-01-08T17:11:40.057264+05:30
CJQ0 started with pid=57, OS id=182561
Completed: ALTER DATABASE OPEN

Click here for Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

CDB, DATABASE, MULTITENANT, ORACLE, ORACLE 19C, PDB

How to Enable Local UNDO Mode in CDB-PDB Multitenant in Oracle

Related Articles