Migration from Non-CDB to Multitenant : CDB is using local undo, but no undo tablespace found in the PDB

During a past migration test from on-premises to ExaCC, I faced a pdb violation, stating, CDB is using local undo, but no undo tablespace found in the PDB, after having run noncdb_to_pdb.sql script.

Explanation

The problem comes from the fact that the non-cdb source is a single instance Database (cluster_database=false), which is converted to a PDB hosted in a RAC CDB, therefore having 2 instances for the PDB, but there is only one UNDO tablespace. Please see below how to quickly resolve this problem.

SQL> select status, message from pdb_plug_in_violations;

STATUS    MESSAGE
--------- ------------------------------------------------------------------------------------------------------------------------
PENDING   CDB is using local undo, but no undo tablespace found in the PDB.

1 rows selected.

Solution

I first check, and confirmed that the CDB$ROOT was set with local undo enabled. This would mean that each container (PDB) will use their own UNDO.

SQL> show con_name

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

SQL> select property_name, property_value
  2  from   database_properties
  3  where  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

This CDB is a RAC database, with 2 nodes, so each PDB should have 2 UNDO tablespace.

SQL> select a.con_id, b.name, tablespace_name
  2  from   cdb_tablespaces a, v$pdbs b
  3  where  a.con_id=b.con_id and contents = 'UNDO'
  4  order by con_id;

    CON_ID NAME                      TABLESPACE_NAME
---------- ------------------------- ------------------------------
         3 PDB1           UNDOTBS2
         3 PDB1           UNDOTBS1
         4 PDB2           UNDO_2
         4 PDB2           UNDOTBS1
         5 PDB3           UNDOTBS1

This is what we can see for PDB1 and PDB2. But PDB3 is only having one UNDO tablespace.

This is what we can confirm, connecting to the PDB3 and listing tablespaces.

SQL> alter session set container=PDB3;

Session altered.

SQL> @/u02/app/oracle/local/dmk_sql/sql/qdbstbssize.sql

PL/SQL procedure successfully completed.


                             Nb      Extent Segment    Alloc.      Space        Max. Percent Block
Name                      files Type Mgmnt  Mgmnt    Size (GB)  Free (GB)  Size (GB)  used % size  Log Encrypt Compress
------------------------- ----- ---- ------ ------- ---------- ---------- ---------- ------- ----- --- ------- --------
APP                           1 DATA LM-SYS MANUAL         .02        .02        .02    9.38 8 KB  YES YES     NO
APP1                          2 DATA LM-SYS AUTO         17.00      16.88      17.00     .68 8 KB  YES YES     NO
APP1_INDEX                    2 DATA LM-SYS AUTO         17.00      16.93      17.00     .41 8 KB  YES YES     NO
...
SYSAUX                        1 DATA LM-SYS AUTO          2.00        .89       2.00   55.71 8 KB  YES YES     NO
SYSTEM                        1 DATA LM-SYS MANUAL        3.00       2.16       3.00   27.94 8 KB  YES YES     NO
TEMP                          1 TEMP LM-UNI MANUAL         .49       2.44        .49 -399.00 8 KB  NO  YES     NO
UNDOTBS1                      1 UNDO LM-SYS MANUAL        1.00        .52       1.00   47.83 8 KB  YES YES     NO
USERS                         1 DATA LM-SYS AUTO           .01        .01        .01   34.38 8 KB  YES YES     NO
...
                          -----                     ---------- ---------- ----------
TOTAL                        19                         104.75      92.96     104.75

16 rows selected.

This is also confirmed checking undo_tablespace parameter for both instances.

SQL> select inst_id, name, value from gv$parameter where upper(name)='UNDO_TABLESPACE';

   INST_ID NAME                      VALUE
---------- ------------------------- ------------------------------------------------------------
         1 undo_tablespace           UNDOTBS1
         2 undo_tablespace

Same for UNDO datafile.

SQL> select file_name from dba_data_files where tablespace_name like '%UNDO%';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATAC1/CDB_NAME/7314A6F9C85A0827E0538E08440AC21A/DATAFILE/undotbs1.536.1159283017

SQL>

Still connected to PDB3, let’s create a new UNDO tablespace.

SQL> create undo tablespace UNDOTBS2 datafile '+DATAC1' size 1G;

Tablespace created.

We have 2 UNDO datafiles now.

SQL>  select file_name from dba_data_files where tablespace_name like '%UNDO%';

FILE_NAME
----------------------------------------------------------------------------------------------------
+DATAC1/CDB_NAME/7314A6F9C85A0827E0538E08440AC21A/DATAFILE/undotbs1.536.1159283017
+DATAC1/CDB_NAME/7314A6F9C85A0827E0538E08440AC21A/DATAFILE/undotbs2.550.1159291013

SQL>

We will assign the new UNDO tablespace to Instance 2.

SQL> alter system set undo_tablespace=UNDOTBS2 container=current sid='CDB_NAME2' scope=both;

System altered.

We can check UNDO_TABLESPACE parameter which is not reflecting the changes for the moment.

SQL> select inst_id, name, value from gv$parameter where upper(name)='UNDO_TABLESPACE';

   INST_ID NAME                      VALUE
---------- ------------------------- ------------------------------------------------------------
         2 undo_tablespace
         1 undo_tablespace           UNDOTBS1

I restarted the PDB3.

SQL> alter pluggable database PDB3 close instances=all;

Pluggable database altered.

SQL> alter pluggable database PDB3 open instances=all;

Pluggable database altered.

And could check that now both instances have got an UNDO tablespace assigned.

SQL> select inst_id, name, value from gv$parameter where upper(name)='UNDO_TABLESPACE';

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

And I finally checked that the pdb violation is resolved.

SQL> select status, message from pdb_plug_in_violations;

STATUS    MESSAGE
--------- ------------------------------------------------------------------------------------------------------------------------
RESOLVED  CDB is using local undo, but no undo tablespace found in the PDB.

1 rows selected.