Hello,
i need help with undo. My system is a 2 node RAC, with ASM:
OS: Oracle Linux 8
DB: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (Version 19.10.0.0.0)
Multitenant install with 2 PDB
SYS@cdb1> select name, con_id from v$pdbs;
NAME CON_ID
---------- ------
PDB$SEED 2
DEV 3
TESZT 5
My TESZT pdb works correctly, and uses the default undo settings.
SYS@teszt1> show parameter undo_tab;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SYS@teszt2> show parameter undo_tab;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO_2
My DEV pdb however is strange, and I can not figure out the problem. I wanted to change the undo tablespace. On the 1st node it was successful
SYS@dev1> show parameter undo_tab;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO_2
SYS@dev1> alter system set undo_tablespace='UNDOTBS1_2' scope=both sid='DEV1';
System altered.
SYS@dev1> show parameter undo_tab;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1_2
But on the 2nd node, it's not working:
SYS@dev2> show parameter undo_tab;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SYS@dev2> alter system set undo_tablespace='UNDO2_2' scope=both sid='DEV2';
System altered.
SYS@dev2> show parameter undo_tab;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
Why I can't change the undo tablespace? I have bounced multiple times the PDB and even the CDB, and no changes. PDB on node2 dosen't change undo tablespace.
BUT!
In the CDB, a datafile query shows that my DEV pdb is using 3 undo tablespaces!
My CDB, and the TESZT pdb are working normally usig only 2 undo tablespaces.
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 +DATA/ORGAN/DATAFILE/undotbs1.xxx.yyyyyyyyyy
1 UNDOTBS2 +DATA/ORGAN/DATAFILE/undotbs2.xxx.yyyyyyyyyy
5 UNDOTBS1 +DATA/ORGAN/.../DATAFILE/undotbs1.xxx.yyyyyyyyyy
5 UNDO_2 +DATA/ORGAN/.../DATAFILE/undo_2.xxx.yyyyyyyyyy
3 UNDOTBS1 +DATA/ORGAN/.../DATAFILE/undotbs1.xxx.yyyyyyyyyy
3 UNDOTBS1_2 +DATA/ORGAN/.../DATAFILE/undotbs1_2.xxx.yyyyyyyyyy
3 UNDO2_2 +DATA/ORGAN/.../DATAFILE/undo2_2.xxx.yyyyyyyyyy
Why is that?
How can I remove the unwanted UNDOTBS1 tablespace from the DEV PDB on the 2nd node?
Thanks and regards.
Peter