There're 2 cases that makes problems about local UNDO tablespace in a RAC database.
Newly Created PDB
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 ORCLPDB READ WRITE NO
We have an opened PDB in the RAC container.
Make sure that we have enabled local undo for the PDB by querying DATABASE_PROPERTIES.
SQL> alter session set container=ORCLPDB;
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/orclcdb/ORCLCDB/trace/alert_$ORACLE_SID.log
...
ORCLPDB(3):CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA/ORCLCDB/ORCLPDB/system01_i2_undo.dbf' SIZE 104857600 AUTOEXTEND
ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
ORCLPDB(3):[66978226] Successfully onlined Undo Tablespace 5.
ORCLPDB(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA/ORCLCDB/ORCLPDB/system01_i2_undo.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable database ORCLPDB 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=ORCLPDB;
Session altered.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA/ORCLCDB/ORCLPDB/undotbs2_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED, '+DATA/ORCLCDB/ORCLPDB/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='ORCLCDB1' scope=spfile;
System altered.
SQL> alter system set undo_tablespace=UNDOTBS2 container=current sid='ORCLCDB2' 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 orclcdb
[oracle@primary01 dbs]$ srvctl start database -d orclcdb
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$
---------- ---------- --------------- ---------------
ORCLPDB ORCLCDB1 undo_tablespace 'UNDOTBS1'
ORCLPDB ORCLCDB2 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=ORCLPDB;
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.
Open Activated PDB
Since we had overcome the local undo problem in the above by assigning the correct undo tablespace for each instance, we thought we will never see the problem again on the standby side. Unfortunately, it appeared again.
After we activated the standby RAC database, we open the PDB from mount.
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> column value format a20;
SQL> select inst_id, con_id, value from gv$parameter where name = 'undo_tablespace' order by 1;
INST_ID CON_ID VALUE
---------- ---------- --------------------
1 3 UNDO_2
2 3 UNDOTBS1
Solution
To solve the mismatched local undo tablespace, we do the trick again.
Close PDB on all Instances
SQL> alter pluggable database ORCLPDB close immediate instances=all;
Pluggable database altered.
Set Correct Local Undo for Each Instance
Since we have already had UNDOTBS2 in the database, so we don't need to create it, we just assign it to the second instance.
SQL> alter system set undo_tablespace=UNDOTBS1 container=current sid='ORCLCDB1' scope=spfile;
System altered.
SQL> alter system set undo_tablespace=UNDOTBS2 container=current sid='ORCLCDB2' scope=spfile;
System altered.
Open PDB on all Instances
SQL> alter pluggable database ORCLPDB open instances=all;
Pluggable database altered.
Check Local Undo
SQL> select inst_id, con_id, value from gv$parameter where name = 'undo_tablespace' order by 1;
INST_ID CON_ID VALUE
---------- ---------- --------------------
1 3 UNDOTBS1
2 3 UNDOTBS2
It's back.
Is it a bug or something? Please leave your comment if you know it.