Release Automation upgrade from 6.X to 6.3 with Oralce DB on Windows may fail and NAC service may not start

Products

• CA Release Automation - Release Operations Center (Nolio)
• CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

Upgrade from RA 6.x to a RA 6.3.0 release that utilizes Oracle database on windows MIGHT fail and NAC may not start properly.

Also, the below error is recorded in the “center_upgrade.log” file

[localhost-startStop-1] ERROR (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:267) - Error calling Flyway Migrate org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:

Migration V6.3.0.0.201609281209001__declare_fix_container_id_in_executable_-_ORACLE.sql failed

----------------------------------------------------------------------------------------------

SQL State  : 23000

Error Code : 1

Message    : ORA-00001: unique constraint (CARADB.SYS_C0014418) violated

ORA-06512: at line 45

 

Location   : C:/Program Files/CA/ReleaseAutomationServer/webapps/datamanagement/resources/db/migration/oracle/V6.3.0.0.201609281209001__declare_fix_container_id_in_executable_-_ORACLE.sql (C:\Program Files\CA\ReleaseAutomationServer\webapps\datamanagement\resources\db\migration\oracle\V6.3.0.0.201609281209001__declare_fix_container_id_in_executable_-_ORACLE.sql)

Cause

Upgrading from earlier releases which contains “malformed” data (with 6.3 there is a script that modifies data and correct “malformed” data. The bug is identified with this script).

This “malformed” data was on design objects which were referencing “non-existing” elements. This may have caused failures on import/export, loading design data on screen, etc.

This will occur ONLY if the exact following combination of events

-        The RA database is an Oracle instance running on Windows machine

-        The RA schema contains “malformed” data items

 -     Upgrade the RA instance from 6.1 or 6.2 to Release Automation 6.3

Environment

CA Release Automation 6.XCA Release Automation 6.3Oracle Database (11g / 12c Version)

Resolution

-       1. Stop NAC

-       2. Using the RA schema owner, execute the attached SQL command on the Oracle database,

3. Start NAC.

===========RA.6.3.Fix.Upgrade.Oracle.Windows.sql===========

-- February 21 2017
-- This script is intended to fix an upgrade issue to RA 6.3
-- The problem was with the "V6.3.0.0.201609281209001__declare_fix_container_id_in_executable_-_ORACLE.sql" flayways script


declare
  l_constraint_name varchar2(30);
begin
  select constraint_name into l_constraint_name from user_constraints
    where upper(table_name) = 'FIXCONTAINERID_LOG' and constraint_type = 'P'; 
   execute immediate 'alter table FIXCONTAINERID_LOG drop constraint ' ||  l_constraint_name; 
end;
/

delete from "schema_version" where "version" = '6.3.0.0.201609281209001';

commit;

 

Note: The script is also available for download from here RA.6.3.Fix.Upgrade.Oracle.Windows.sql.

 

Additional Information

 

To check if problem might occur, the below scripts can be executed against the RA database

If the count result is greater than ‘0’, “malformed” data exist

                SELECT COUNT(*) FROM <RA SCHEMA OWNER>.EXECUTABLE_INSTANCES WHERE DELEGATE_ID NOT IN (SELECT ID FROM <RA SCHEMA OWNER>.EXECUTABLE);

                SELECT COUNT(*) from <RA SCHEMA OWNER>.EXECUTABLE WHERE NAME= 'null';

 

(there is no easy way to “fix” this in earlier 6.x releases and avoid the upgrade failure pending the specific scenario/configuration. That said, the above steps will workaround the problem)