Forum Stats

  • 3,760,456 Users
  • 2,251,709 Discussions
  • 7,871,131 Comments

Discussions

Strange undo behavior

Péter Király
Péter Király Member Posts: 7 Green Ribbon
edited Jul 12, 2021 8:40PM in Multitenant

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
[email protected]> 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.

[email protected]> show parameter undo_tab;

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                     string     UNDOTBS1


[email protected]> 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

[email protected]> show parameter undo_tab;

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                     string     UNDO_2

[email protected]> alter system set undo_tablespace='UNDOTBS1_2' scope=both sid='DEV1';

System altered.

[email protected]> show parameter undo_tab;

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                     string     UNDOTBS1_2

But on the 2nd node, it's not working:

[email protected]> show parameter undo_tab;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

[email protected]> alter system set undo_tablespace='UNDO2_2' scope=both sid='DEV2';

System altered.

[email protected]> 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

Tagged:

Comments