From 12.2.0.1 onwards, local undo tablespace is introduced in PDB level. In many cases, the automatically created undo tablespace in PDB is not complying with naming standard. For example:
SQL> select inst_id,name, con_id, value
from gv$system_parameter
where name='undo_tablespace' and con_id=3
order by 1;
INST_ID NAME CON_ID VALUE
---------- -------------------- ---------- --------------------
1 undo_tablespace 3 UNDOTBS1
2 undo_tablespace 3 UNDOTBS2
3 undo_tablespace 3 UNDO_3
SQL> SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
RESOLUTION
Rename undo tablespace
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
UNDO_3
SQL> alter tablespace undo_3 rename to UNDOTBS3;
Tablespace altered.
SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
UNDOTBS3
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
undo_tablespace
string UNDO_3
SQL> ALTER SYSTEM set UNDO_TABLESPACE='UNDOTBS3' scope=both;
System altered.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
undo_tablespace string UNDOTBS3
Close PDB1 and start PDB1 to check further whether UNDOTBS3 assigned to instance 3.
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
undo_tablespace string UNDOTBS3
-- checked in spfile for PDB1??
SQL> create pfile='/tmp/x.x' from spfile;
File created.
SQL> ! cat /tmp/x.x
*.undo_tablespace='UNDOTBS3'
Check parameter UNDO_TABLESPACE in CDB and PDB, all looks good.
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;
INST_ID NAME CON_ID VALUE
---------- -------------------- ---------- --------------------
1 undo_tablespace 0 UNDOTBS1
1 undo_tablespace 3 UNDOTBS1
2 undo_tablespace 0 UNDOTBS2
2 undo_tablespace 3 UNDOTBS2
3 undo_tablespace 0 UNDOTBS3
3 undo_tablespace 3 UNDOTBS3
6 rows selected.
Finally reset undo_tablespace parameter in PDB , and bounce PDB on instance 3:
SQL> alter session set container=pdb1;
Session altered.
SQL> alter system reset undo_tablespace;
System altered.
SQL> create pfile='/tmp/x.x' from spfile;
File created.
SQL> ! cat /tmp/x.x
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;
INST_ID NAME CON_ID VALUE
---------- -------------------- ---------- --------------------
1 undo_tablespace 3 UNDOTBS1
2 undo_tablespace 3 UNDOTBS2
3 undo_tablespace 3 UNDOTBS3
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;
INST_ID NAME CON_ID VALUE
---------- -------------------- ---------- --------------------
1 undo_tablespace 0 UNDOTBS1
1 undo_tablespace 3 UNDOTBS1
2 undo_tablespace 0 UNDOTBS2
2 undo_tablespace 3 UNDOTBS2
3 undo_tablespace 0 UNDOTBS3
3 undo_tablespace 3 UNDOTBS3
6 rows selected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter pluggable database PDB1 close;
Pluggable database altered.
SQL> select name, open_mode from gv$pdbs;
NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB$SEED READ ONLY
PDB1 MOUNTED
6 rows selected.
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS3
SQL>
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;
INST_ID NAME CON_ID VALUE
---------- -------------------- ---------- --------------------
1 undo_tablespace 0 UNDOTBS1
1 undo_tablespace 3 UNDOTBS1
2 undo_tablespace 0 UNDOTBS2
2 undo_tablespace 3 UNDOTBS2
3 undo_tablespace 0 UNDOTBS3
3 undo_tablespace 3 UNDOTBS3
6 rows selected.
Recreate a New Undo Tablespace
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter session set container=PDB1;
SQL> alter pluggable database PDB1 open restricted;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE YES
SQL> CREATE UNDO TABLESPACE UNDOTBS3 datafile '+DATA2' SIZE 100M AUTOEXTEND ON ;
Tablespace created.
--
-- can NOT drop UNDO_3 now
--
SQL> DROP tablespace UNDO_3 including contents and datafiles;
DROP tablespace UNDO_3 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO_3' is currently in use
--
-- Here the following NO need scope=spfile
--
SQL> ALTER SYSTEM set UNDO_TABLESPACE='UNDOTBS3';
System altered.
SQL> DROP tablespace UNDO_3 including contents and datafiles;
Tablespace dropped.
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;
INST_ID NAME CON_ID VALUE
---------- -------------------- ---------- --------------------
1 undo_tablespace 0 UNDOTBS1
1 undo_tablespace 3 UNDOTBS1
2 undo_tablespace 0 UNDOTBS2
2 undo_tablespace 3 UNDOTBS2
3 undo_tablespace 0 UNDOTBS3
3 undo_tablespace 3 UNDOTBS3
6 rows selected.
SQL> alter pluggable database PDB1 close;
Pluggable database altered.
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
Make DBA Life Easy 