Local Undo of PDB for Each RAC Instance

  • by

Usually, we have separate undo tablespace for each instance in a RAC database. For example, UNDOTBS1 is for the first instance and UNDOTBS2 is for the second instance.

But for a newly created PDB in a RAC container, it's not the case, we have only one undo tablespace UNDOTBS1 at PDB creation time.

Missing UNDOTBS2

Let's see what happened.

[oracle@primary01 oracle]$ sqlplus / as sysdba
...
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ERPAPP1                        READ WRITE NO

We have an opened PDB in the RAC container.

Make sure that we have enabled local undo for the PDB.

SQL> alter session set container=ERPAPP1;

Session altered.

SQL> column property_value format a20;
SQL> select property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_VALUE
--------------------
TRUE

Check what undo tablespace we have?

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

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

As you can see, there's an odd undo tablespace called UNDO_2 in the PDB for instance 1. Now some questions come in my mind.

  • Where did it come from? I know I didn't create it.
  • Secondly, where is UNDOTBS2?
  • Is it an unusual scenario for Oracle to create a PDB on a RAC container?

In the second node, we found something in the alert log.

[oracle@primary02 oracle]$ vi $ORACLE_BASE/diag/rdbms/erpcdb1/ORCLCDB/trace/alert_$ORACLE_SID.log
...
ERPAPP1(3):CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA/ERPCDB1/ERPAPP1/system01_i2_undo.dbf' SIZE 104857600 AUTOEXTEND
ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
ERPAPP1(3):[66978226] Successfully onlined Undo Tablespace 5.
ERPAPP1(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA/ERPCDB1/ERPAPP1/system01_i2_undo.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable database ERPAPP1 opened read write

It looks like the RAC container created an undo tablespace UNDO_2 for instance 1 at the first time open of the PDB to workaround the strange issue.

Of course I can use the tablespace to open the PDB on both nodes without problem. But in this case, it's really weird to see that instance 1 uses UNDO_2 and instance 2 uses UNDOTBS1.

I'd rather re-arrange both instances well.

Solution

Create a new undo tablespace called UNDOTBS2 for instance 2.

SQL> alter session set container=ERPAPP1;

Session altered.

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA/ERPCDB1/ERPAPP1/undotbs2_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED, '+DATA/ERPCDB1/ERPAPP1/undotbs2_02.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Tablespace created.

Set UNDO_TABLESPACE for each instance with SPFILE scope.

SQL> alter system set undo_tablespace=UNDOTBS1 container=current sid='ERPCDB11' scope=spfile;

System altered.

SQL> alter system set undo_tablespace=UNDOTBS2 container=current sid='ERPCDB12' scope=spfile;

System altered.

If you're in the PDB, then you don't have to set CONTAINER=CURRENT, because CURRENT is the default value of CONTAINER.

Restart the RAC database.

[oracle@primary01 dbs]$ srvctl stop database -d erpcdb1
[oracle@primary01 dbs]$ srvctl start database -d erpcdb1

Check the result after restarting the RAC database.

[oracle@primary01 oracle]$ sqlplus / as sysdba
...
SQL> column pdb_name format a10;
SQL> column sid format a10;
SQL> column name format a15;
SQL> column value$ format a15;
SQL> select b.pdb_name, a.sid, a.name, a.value$ from pdb_spfile$ a, cdb_pdbs b where a.pdb_uid = b.con_uid;

PDB_NAME   SID        NAME            VALUE$
---------- ---------- --------------- ---------------
ERPAPP1    ERPCDB11   undo_tablespace 'UNDOTBS1'
ERPAPP1    ERPCDB12   undo_tablespace 'UNDOTBS2'

We have changed the default undo tablespace for both nodes from the container's point of view.

Let's switch to the PDB.

SQL> alter session set container=ERPAPP1;

Session altered.

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

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

We correct the problem.

Leave a Reply

Your email address will not be published. Required fields are marked *