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.