Release Automation upgrade from 6.X to 6.3 with Oralce DB on Windows may fail and NAC service may not start
Article ID: 5929
Updated On:
Products
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
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)
Attachments
Feedback
