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

Document ID : KB000104114
Last Modified Date : 28/01/2019
Show Technical Document Details
Issue:
CA 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
Environment:
CA PPM 15.4.0 
Cause:
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. 
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')
 
File Attachments:
updateddbpatchscript.xml