Till 12cR1 the Undo tablespace used to be shared or global across all the PDBs in a container. From 12cR2 onwards, the local Undo is introduced. When local undo is enabled, each container has its own undo tablespace for every instance in which it is open. When local undo is disabled, there is one undo tablespace for the entire CDB.
There are capabilities to switch between local and shared undo and back again. And the transition is seamless.
Local Undo Mode
Local undo mode means that each container has its own undo tablespace for every instance in which it is open. In this mode, Oracle Database automatically creates an undo tablespace for every container in the CDB (The initial size of undo datafile may vary). For an Oracle RAC CDB, there is one active undo tablespace for each instance for each PDB in local undo mode.
Local undo enables many of the major new capabilities of Multitenant in 12.2, including:
- Hot Clone
- Refresh PDB
- PDB Relocate
- Flashback PDB
Shared Undo Mode
Shared undo mode means that there is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. Oracle continues to support shared undo in 12.2, but that is for upgrade transitional purposes only.
How to check the current Undo mode
To check the current undo mode use the below query:
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
How to Convert CDB From LOCAL to SHARED UNDO MODE
When a CDB is in local undo mode, you can change it to use shared undo mode by issuing an ALTER DATABASE LOCAL UNDO OFF statement and restarting the database.
1. Startup the database with “upgrade” option.
SQL> startup upgrade ORACLE instance started. Total System Global Area 734003200 bytes Fixed Size 8261248 bytes Variable Size 562037120 bytes Database Buffers 50331648 bytes Redo Buffers 8515584 bytes In-Memory Area 104857600 bytes Database mounted. Database opened.
2. Verify the current undo mode as “local”.
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE -------------------- --------------- LOCAL_UNDO_ENABLED TRUE
3. Change the Local Undo mode to shared undo mode by issuing an “ALTER DATABASE LOCAL UNDO OFF” statement.
SQL> sho con_name CON_NAME ------------------------------ CDB$ROOT
SQL> ALTER DATABASE LOCAL UNDO off; Database altered.
4. Verify the current Undo mode again.
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE -------------------- --------------- LOCAL_UNDO_ENABLED FALSE
5. Shutdown the database and startup again in normal mode.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup ORACLE instance started. Total System Global Area 734003200 bytes Fixed Size 8261248 bytes Variable Size 562037120 bytes Database Buffers 50331648 bytes Redo Buffers 8515584 bytes In-Memory Area 104857600 bytes Database mounted. Database opened.
6. When database is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.
SQL> 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
Drop the Undo tablespace from the PDBs:
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE; Pluggable database altered.
SQL> ALTER SESSION SET CONTAINER=PDB1; Session altered. SQL> drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped.
How to Convert CDB from SHARED to LOCAL UNDO MODE
When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.
When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.
1. Startup the database in “upgrade” mode.
SQL> startup upgrade ORACLE instance started. Total System Global Area 734003200 bytes Fixed Size 8261248 bytes Variable Size 562037120 bytes Database Buffers 50331648 bytes Redo Buffers 8515584 bytes In-Memory Area 104857600 bytes Database mounted. Database opened.
2. Next, convert the CDB from shared to local undo mode using the below statement.
SQL> alter database local undo on; Database altered.
3. Startup the database with “force” option.
SQL> startup force; ORACLE instance started. Total System Global Area 734003200 bytes Fixed Size 8261248 bytes Variable Size 562037120 bytes Database Buffers 50331648 bytes Redo Buffers 8515584 bytes In-Memory Area 104857600 bytes Database mounted. Database opened.
4. Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace, created for all the PDBs.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE; Pluggable database altered.
SQL> ALTER SESSION SET CONTAINER=PDB$SEED; Session altered.
SQL> CREATE UNDO TABLESPACE test_undo datafile '/u01/app/oracle/oradata/orcl/pdb1/testundo.dbf' SIZE 10M AUTOEXTEND ON; Tablespace created.
SQL> alter system set UNDO_TABLESPACE='TEST_UNDO' scope=both; System altered.
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string TEST_UNDO
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT; Session altered.
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE; Pluggable database altered.
5. Open the PDB is read write mode.
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE; Pluggable database altered.
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
6. Verify that the Undo tablespace is created automatically.
SQL> 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 ---------- ---------------- ---------------------- 3 UNDO_1 /u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf
From the PDB
SQL> ALTER SESSION SET CONTAINER=PDB1; Session altered.
SQL> 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 ---------- ---------------- ---------------------- 3 UNDO_1 /u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf
As you can see from the output above, Undo tablespace is created automatically.
Alert Log File entries (for PDB1)
2018-03-05T14:44:52.406849+05:30 ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE 2018-03-05T14:44:54.137062+05:30 PDB1(3):Endian type of dictionary set to little PDB1(3):Autotune of undo retention is turned on. PDB1(3):Undo initialization finished serial:0 start:2394185293 end:2394185366 diff:73 ms (0.1 seconds) PDB1(3):Database Characterset for PDB1 is AL32UTF8 PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0) 2018-03-05T14:44:55.457499+05:30 PDB1(3):Opening pdb with no Resource Manager plan active PDB1(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE 2018-03-05T14:44:58.688075+05:30 PDB1(3):[18452] Successfully onlined Undo Tablespace 5. PDB1(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE Pluggable database PDB1 opened read write Completed: ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE