Oracle RAC & Multitenant: Fun with UNDO Tablespaces

2025-08-26 Off By Markus Flechtner

Some days ago, there was a discussion in the My Oracle Support Community on the naming on the UNDO-Tablespaces of PDBs on a RAC system. And yes, this can be a little confusing. Let’s take a closer look at it:

My test cluster is an Oracle Linux based cluster with 2 nodes, ernie and bert, running Oracle Database 19c, 19.18.

node	ernie	bert
instance ID	1	2
instance name	TESTCDB1	TESTCDB2

I’ve created a RAC database without a PDB with dbca. Let’s take the look on the initial Undo configuration:

SQL> select instance_number,instance_name from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
	      1 TESTCDB1

SQL> alter session set "_exclude_seed_cdb_view"=false;
Session altered.


SQL> column con$name format a30
SQL> select con$name,con_id,tablespace_name from cdb_tablespaces where contents='UNDO' order by con_id;

CON$NAME			   CON_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
CDB$ROOT				1 UNDOTBS2
CDB$ROOT				1 UNDOTBS1
PDB$SEED				2 UNDOTBS1

SQL> column name format a30
SQL> column value format a30
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id, inst_id,name,value from gv$parameter where name='undo_tablespace' order by inst_id;

    CON_ID    INST_ID NAME			     VALUE
---------- ---------- ------------------------------ ------------------------
	 1	    1 undo_tablespace		     UNDOTBS1
	 1	    2 undo_tablespace		     UNDOTBS2

So we have two Undo-Tablespaces for CDB$ROOT, named UNDOTBS1 and UNDOTBS2, and the names and instance IDs match. For PDB$SEED, there’s only UNDO tablespace.

Let’s create a PDB named PDB1 (when connected to instance 1, i.e. TESTCDB1):

SQL> create pluggable database pdb1 admin user pdbadmin identified by manager;
Pluggable database created.

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> select con$name,con_id,tablespace_name from cdb_tablespaces where contents='UNDO' order by con_id;

CON$NAME			   CON_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
CDB$ROOT				1 UNDOTBS2
CDB$ROOT				1 UNDOTBS1
PDB$SEED				2 UNDOTBS1
PDB1					3 UNDOTBS1

As PDB1 is a copy of the PDB$SEED, there’s one Undo tablespace called UNDOTBS1.

Let’s open the PDB PDB1 on instance #2:

SQL> select instance_number,instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
	      2 TESTCDB2

SQL> alter session set "_exclude_seed_cdb_view"=false;
Session altered.


SQL> column name format a30
SQL> select inst_id,con_id,name,open_mode from gv$pdbs order by con_id,inst_id;

   INST_ID     CON_ID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 1	    2 PDB$SEED			     READ ONLY
	 2	    2 PDB$SEED			     READ ONLY
	 1	    3 PDB1			     READ WRITE
	 2	    3 PDB1			     MOUNTED
	 
	 
SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> select con$name,con_id,tablespace_name from cdb_tablespaces where contents='UNDO' order by con_id;

CON$NAME			   CON_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
CDB$ROOT				1 UNDOTBS1
CDB$ROOT				1 UNDOTBS2
PDB$SEED				2 UNDOTBS1
PDB1					3 UNDO_2
PDB1					3 UNDOTBS1

As we can see from the alert.log below, a new UNDO tablespace UNDO_2 is created automatically when opening the PDB on instance 2 for the first time:

2025-08-25T00:27:19.439961+02:00
alter pluggable database pdb1 open
2025-08-25T00:27:19.453227+02:00
PDB1(3):Pluggable database PDB1 opening in read write
PDB1(3):Increasing priority of 1 RS
PDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):queued attach DA request 0x8c97e748 for pdb 3, ospid 73800
2025-08-25T00:27:19.689041+02:00
Domain Action Reconfiguration started (domid 3, new da inc 4, cluster inc 4)
Instance 2 is attaching to domain 3
 Global Resource Directory partially frozen for domain action
 Non-local Process blocks cleaned out
 Set master node info
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
Domain Action Reconfiguration complete (total time 0.1 secs)
Decreasing priority of 1 RS
2025-08-25T00:27:19.958955+02:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):Undo initialization recovery: err:0 start: 5452079 end: 5452080 diff: 1 ms (0.0 seconds)
PDB1(3):Undo initialization online undo segments: err:0 start: 5452080 end: 5452198 diff: 118 ms (0.1 seconds)
PDB1(3):Undo initialization finished serial:0 start:5452079 end:5452205 diff:126 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2025-08-25T00:27:20.852768+02:00
PDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2025-08-25T00:27:21.281628+02:00
PDB1(3):Opening pdb with no Resource Manager plan active
PDB1(3):joxcsys_required_dirobj_exists: directory object exists with required path /app/oracle/product/19.18-ee/javavm/admin/, pid 73800 cid 3
PDB1(3):CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA' SIZE 277872640 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2025-08-25T00:27:23.806131+02:00
PDB1(3):[73800] Successfully onlined Undo Tablespace 5.
PDB1(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA' SIZE 277872640 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable database PDB1 opened read write
Completed: alter pluggable database pdb1 open

As we can see from the view v$parameter: UNDOTBS1 is assigned to instance #1 and UNDO_2 is assigned to instance #2:

SQL> select con_id, inst_id,name,value from gv$parameter where name='undo_tablespace' order by inst_id;

    CON_ID    INST_ID NAME			     VALUE
---------- ---------- ------------------------------ ------------------------
	 3	    1 undo_tablespace		     UNDOTBS1
	 3	    2 undo_tablespace		     UNDO_2

OK, let’s do it the other way round: let’s create a PDB named PDB2 when connected to instance #2:

SQL> create pluggable database pdb2 admin user pdbadmin identified by manager;
Pluggable database created.

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> select con$name,con_id,tablespace_name from cdb_tablespaces where contents='UNDO' order by con_id;

CON$NAME			   CON_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
CDB$ROOT				1 UNDOTBS1
CDB$ROOT				1 UNDOTBS2
PDB$SEED				2 UNDOTBS1
PDB1					3 UNDOTBS1
PDB1					3 UNDO_2
PDB2					4 UNDOTBS1

After opening the PDB2 on instance 1 we can see that for PDB2, the Undo tablepace UNDOTBS1 is assigned to instance 2 and UNDO_2 is assigned to instance 1. OK, on the one hand that’s confusing, but on the other hand names don’t matter.

How can we avoid this problem:

Let’s create another PDB3, when connected to instance #1:

SQL> create pluggable database pdb3 admin user pdbadmin identified by manager;
Pluggable database created.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.

It’s no surprise – and I’ll skip the SQL output because it’s the same as in PDB1: Undo tablespace UNDOTBS is the Undo tablespace for instance #1.

Let’s connect to the PDB and create another UNDO tablespace:

SQL> alter session set container=PDB3;
Session altered.

SQL> create undo tablespace UNDOTBS2 datafile '+DATA';
Tablespace created.

In the final step, we’ll open this PDB on instance #2:

QL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TESTCDB2



SQL> alter pluggable database pdb3 open;
Pluggable database altered.

SQL> alter session set container=PDB3;
Session altered.

SQL> select con_id, inst_id,name,value from gv$parameter where name='undo_tablespace' order by inst_id;

    CON_ID    INST_ID NAME			     VALUE
---------- ---------- ------------------------------ ------------------------
	 5	    1 undo_tablespace		     UNDOTBS1
	 5	    2 undo_tablespace		     UNDOTBS2

As we can see, in this case Oracle does not create a new Undo tablespace (UNDO_2) but uses the existing Undo tablespace UNDOTBS2.

Long story short:

If you want to keep the names of the Undo tablespaces in your CDB consistent and aligned to the instance IDs: