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

Document ID : KB000005929
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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)

Environment:
CA Release Automation 6.XCA Release Automation 6.3Oracle Database (11g / 12c Version)
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

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)

File Attachments:
TEC1935174.zip