Switch UNDO tablespace in Standalone Oracle Multitenant Pluggable Database
The aim of this post is to provide an example of switching the undo tablespace in a standalone Oracle multitenant pluggable database.
We may experience the situation where current UNDO tablespace in the PDB is oversized and underutilized, and cannot shrink it directly to reclaim unused space back to storage. To address this issue, a new undo tablespace must be created, followed by switching to newly created undo tablespace and dropping the old undo tablespace.
Note: We can only switch UNDO tablespace in a PDB if the CDB is in local undo mode.
Environment=
===========
CDB : PROD
PDB: PRODPDB1
Old Undo tablespace of PDB: UNDOTBS1
New Undo tablespace of PDB: NEW_UNDO_TBS
DB Version: 19.27
1. The undo must be in local_mode.
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> col PROPERTY_NAME for a20
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
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 READ WRITE NO
2. Create the New UNDO Tablespace in PDB.
SQL> alter session set container = prodpdb1;
Session altered.
SQL> show con_name
CON_NAME
——————————
PRODPDB1
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace NEW_UNDO_TBS datafile ‘/u01/app/oracle/oradata/PROD/prodpdb1/new_undo_tbs01.dbf’ size 500m;
Tablespace created.
3. Switch the undo tablespace to newly created in PDB.
Before switching UNDO tablespaces in a PDB, you should always check that no active rollback/undo transactions are running. If you switch while transactions are still using the old UNDO, you’ll hit errors or risk corruption.
SQL> select s.sid, s.serial#, t.addr, t.ubafil, t.ubablk, t.used_ublk, t.used_urec from v$transaction t, v$session s where t.ses_addr = s.saddr;
no rows selected
SQL> alter system set undo_tablespace = NEW_UNDO_TBS 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 NEW_UNDO_TBS
4. Drop the old undo tablespace from PDB.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
New UNDO tablespace is in place for undo transactions.
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
