CA PPM Upgrade fails on POSTUPGRADE_VIEW_SETTINGS_ALIAS_TO_CODE.xml with ORA-30926

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM On Premise

Issue/Introduction

Clarity PPM Upgrade fails with error:

[exec]      [exec] Process - post-upgrade: POSTUPGRADE_VIEW_SETTINGS_ALIAS_TO_CODE.xml

     [exec]      [exec] com.ca.clarity.jdbc.oraclebase.ddc: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in the source tables

     [exec]      [exec] ORA-06512: at line 27

     [exec]      [exec] SQL Text: at com.ca.clarity.jdbc.oraclebase.dde3.l(Unknown Source)

     [exec]      [exec] BEGIN

     [exec]      [exec]     --Update the attribute codes in the picklist def table to be lowercase

     [exec]      [exec]     UPDATE RDM_ROADMAP_PICKLIST_DEFS SET ATTRIBUTE_CODE = NLS_LOWER(ATTRIBUTE_CODE);

     [exec]      [exec]     --Global picklists

     [exec]      [exec]     MERGE INTO ODF_UI_VIEW_SETTINGS view_settings

A similar error also referencing the Process - post-upgrade: POSTUPGRADE_UPDATE_EV_TOTALS_JOB.xml along with the above POSTUPGRADE_VIEW_SETTINGS_ALIAS_TO_CODE.xml may also be seen.

Cause

This can be caused by duplicate data mapping with Roadmaps and their Picket List's. We can validate them using the queries in Additional Information section of the document. 

Environment

CA PPM 15.4.0 

Resolution

0. Rollback the upgrade to prior environment / last backup point
1. Download the attached file and rename it to "POSTUPGRADE_VIEW_SETTINGS_ALIAS_TO_CODE.xml"
2. Upload the file to the PPM app server and store it in an easily accessible path on disk.
3. Open a command prompt, navigate to $CLARITY_HOME/bin and run the following command:

    dbpatch -install -file=<pathtofile>/POSTUPGRADE_VIEW_SETTINGS_ALIAS_TO_CODE.xml -apply

   If the file is successfully applied, then you'll see the following output from the command (without any error messages):

    ==========================================
    DBTools Log - Tue Jul 03 19:15:50 CDT 2018
    ==========================================

    Total time: 0H:0M:1S

     Note :- The script should run for atleast 1 second. If it shows 0 seconds then the update is not successful. 
     
4. Now re-run the install again.

Additional Information

You can validate the duplicates using below queries.

Global Picklist: 
select distinct ODF_UI_VIEW_SETTINGS.id, NLS_LOWER(RDM_ROADMAP_PICKLIST_DEFS.ATTRIBUTE_CODE) att_code FROM RDM_ROADMAP_PICKLIST_DEFS INNER JOIN ODF_UI_VIEW_SETTINGS ON TO_CHAR(ODF_UI_VIEW_SETTINGS.VALUE) = RDM_ROADMAP_PICKLIST_DEFS.API_ALIAS INNER JOIN ODF_UI_VIEWS ON RDM_ROADMAP_PICKLIST_DEFS.ROADMAP_ID = 0 AND ODF_UI_VIEWS.ASSOCIATED_OBJECT_TYPE = 'rdm_roadmap' INNER JOIN ODF_UI_VIEW_SETTINGS ON ODF_UI_VIEW_SETTINGS.VIEW_ID = ODF_UI_VIEWS.ID AND ODF_UI_VIEW_SETTINGS.LAYOUT IN ('timeline', 'board') AND ODF_UI_VIEW_SETTINGS.NAME IN ('swimlanes', 'timelineColorBy', 'columns', 'colors');

Local Picklist:
select distinct ODF_UI_VIEW_SETTINGS.id, NLS_LOWER(RDM_ROADMAP_PICKLIST_DEFS.ATTRIBUTE_CODE) att_code FROM RDM_ROADMAP_PICKLIST_DEFS INNER JOIN ODF_UI_VIEW_SETTINGS ON TO_CHAR(ODF_UI_VIEW_SETTINGS.VALUE) = RDM_ROADMAP_PICKLIST_DEFS.API_ALIAS INNER JOIN ODF_UI_VIEWS ON ODF_UI_VIEWS.ASSOCIATED_INSTANCE_ID = RDM_ROADMAP_PICKLIST_DEFS.ROADMAP_ID AND ODF_UI_VIEWS.ASSOCIATED_OBJECT_TYPE = 'rdm_roadmap' INNER JOIN ODF_UI_VIEW_SETTINGS ON ODF_UI_VIEW_SETTINGS.VIEW_ID = ODF_UI_VIEWS.ID AND ODF_UI_VIEW_SETTINGS.LAYOUT IN ('timeline', 'board') AND ODF_UI_VIEW_SETTINGS.NAME IN ('swimlanes', 'timelineColorBy', 'columns', 'colors')