How to Switch or Recreate Undo Tablespace at PDB Level

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.

Local and Shared Undo Tablespace Modes

Oracle introduced Local Undo since 12cR2. Before 12cR2, there is no local undo concept, the Undo tablespace used to be shared or global across all the PDBs in a container.

How to check the current Undo mode

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        TRUE       true if local undo is enabled

How to convert CDB from Local to Shared Undo Mode

When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

SQL> startup upgrade

SQL> sho con_name 

CON_NAME
-----------------
CDB$ROOT

SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.

 
SQL> shutdown immediate

SQL> startup

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        FALSE       true if local undo is enabled

-- Check current undo tablespaces for CDB and all PDBS.
--
SQL> 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';

 
--
-- Drop the Undo tablespace from the PDBs
--
SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.

How to convert CDB from Shared to Local Undo Mode

When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

SQL> startup upgrade

SQL>  alter database local undo on;

SQL> startup force;

-- Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace
--
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

SQL> CREATE UNDO TABLESPACE seed_undo datafile '+DATA1'......

SQL> alter system set  UNDO_TABLESPACE='SEED_UNDO' scope=both;

SQL> show parameter undo_tablespace
undo_tablespace                      string      SEED_UNDO


SQL>  ALTER SESSION SET CONTAINER=CDB$ROOT; 

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

-- Open the PDB is read write mode

SQL> ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE;

SQL> 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
-------------------------------------------------------------------
         3 UNDO_1

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf  << Undo tablespace is created automatically.

          1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

-- From the PDB
--

SQL> ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> 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
---------------------------------------------------------------

         3 UNDO_1 << Undo tablespace is created automatically.

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

Manage Temporary Tablespace in Container Database (CDB) and Pluggable Database (PDB)

Both CDB and PDB has its own dedicated TEMP tablespace.

In a 12cR1, both CDB and PDB have own dedicated TEMP tablespace by default

SQL> show con_id con_name

CON_ID
---------
1

CON_NAME
---------
CDB$ROOT

SQL> select CON_ID,NAME from v$tablespace where name='TEMP';

CON_ID      NAME
---------- -------------
 1         TEMP
 2         TEMP
 3         TEMP

Default Temporary Tablespace in PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP OLTP_TEMP;

Tablespace altered.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE OLTP_TEMP;

Pluggable database altered.

Check the default Temporary Tablespace for PDB:

SQL> show con_id con_name

CON_ID
---------
3

CON_NAME
---------
PDB1

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME           PROPERTY_VALUE  DESCRIPTION
----------------------- --------------- --------------------------
DEFAULT_TEMP_TABLESPACE OLTP_TEMP       Name of default temporary 
                                        tablespace

Check the default Temporary Tablespace for CDB as below, we can see CDB is still using its default temporary tablespace TEMP.

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME             PROPERTY_VALUE  DESCRIPTION
------------------------- --------------- -------------------------
DEFAULT_TEMP_TABLESPACE   TEMP            Name of default temporary
                                          tablespace

How to Fix Incomplete RDBMS DST Upgrading in 12c

It is a good practice to raise a service request for issues of DST upgrading in production environment.

There is a database upgraded from 11.2.0.4 to 12.1.0.2 by using “Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)”. For some reason, the DST upgrading was not complete successfully after the upgrading checking:

Current DST state is still “Upgrade”.

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

PROPERTY_NAME			    VALUE
----------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION		    18
DST_SECONDARY_TT_VERSION	    14
DST_UPGRADE_STATE		    UPGRADE

Try to end the upgrading, still got issues.

SQL>shutdown immediate
SQL>startup

SQL>alter session set "_with_subquery"=materialize;
SQL>alter session set "_simple_view_merging"=TRUE;


SQL>VAR fail number
SQL>BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
Number of failures: 2
BEGIN
*
ERROR at line 1:
ORA-56929: Ending an upgrade window failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1289
ORA-06512: at line 

SQL>SELECT * FROM sys.dst$error_table;

no rows selected

SQL>SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS 
      FROM ALL_TSTZ_TABLES 
     where UPGRADE_IN_PROGRESS='YES';

OWNER            TABLE_NAME                    UPG
---------------- ----------------------------  ----  
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_L YES
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES

Start database normally and run DBMS_DST.UPGRADE_DATABASE.

SQL>set serveroutput on
SQL>VAR numfail number
SQL>BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.



SQL>SELECT * FROM sys.dst$error_table;

no rows selected

End the upgrading.

SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

Check the successful upgrading.

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
      FROM DATABASE_PROPERTIES
     WHERE PROPERTY_NAME LIKE 'DST_%'
  ORDER BY PROPERTY_NAME;  

PROPERTY_NAME             VALUE
------------------------- -------
DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE          NONE


SQL>SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 	     18 	 0

Reference:

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)