Upgrade to v6.5.0.10007 fails with FlyWayException

Products

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

Issue/Introduction

The upgrade from 6.3 to 6.5 fails with below error

ERROR (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:268) - Error calling Flyway Migrate org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:Migration V6.4.0.0.201612191532037__alter_audit_table_datatypes_-_ORACLE.sql failed

Environment

Release Automation: 6.5

Cause

During analysis we can see below error in the nolio_center_upgrade.log

2017-12-28 12:23:51,868 [localhost-startStop-1] ERROR (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:268) - Error calling Flyway Migrate org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Migration V6.4.0.0.201612191532037__alter_audit_table_datatypes_-_ORACLE.sql failed

-----------------------------------------------------------------------------------SQL State  : 61000

Error Code : 54

Message    : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-06512: at line 10

Location   : /opt/nolio/NolioAutomationCenter/webapps/datamanagement/resources/db/migration/oracle/V6.4.0.0.201612191532037__alter_audit_table_datatypes_-_ORACLE.sql (/opt/nolio/NolioAutomationCenter/webapps/datamanagement/resources/db/migration/oracle/V6.4.0.0.201612191532037__alter_audit_table_datatypes_-_ORACLE.sql)

The above error which reflect a NOWAIT or timeout occurred due to lock on one or more table (which ideally should not be the case), but in some environment the lock retains and blocks flyway from successful migration.

Resolution

 

Below are the steps to follow to resolve above upgrade issue.

1. Stop the NAC a.k.a. DMS
2. Wait for 5-10 minutes making sure that NAC is completely stopped and all connection to DB are released and no open transaction.
3. Execute the script (/opt/nolio/NolioAutomationCenter/webapps/datamanagement/resources/db/migration/oracle/V6.4.0.0.201612191532037__alter_audit_table_datatypes_-_ORACLE.sql) directly against the database as RA Schema owner (make sure script completes successfully)
4. On successful completion of script execution in step 3, we need to tell “flyway” that the script does not need to be executed on the next startup and to unmarked it as failed, we need to run below query

   update "schema_version" set "success" = 1 where "version" = '6.4.0.0.201612191532037';

   commit;

5. Start the NAC a.k.a. DMS and then the upgrade should continue

 

 

Additional Information

To minimize the likelihood of the script failing during the production upgrade:

• After stopping both NACS, wait a short amount of time – 5-10 minutes, so that any in-flight transactions have a chance to rollback.
• Connect to the database via SQL*plus as a user with select permission on the following views -- v$transaction,  v$session, and execute below SQL statements to determine whether or not there are any open transactions associated with the RA database user.

-- substitute accordingly for the actual name of the RA_DB_USER

-- (1) How many running any open transactions for NOLIO user?

           SELECT count(*)FROM v$transaction t, v$session s WHERE t.ses_addr = s.saddr AND s.username = 'RA_DB_USER';

-- (2) Get the sql for running transactions for NOLIO user

           SELECT s.sql_id, sql.sql_text FROM v$transaction t inner join v$session s on t.ses_addr = s.saddr left join v$sql sql on  sql.sql_id = s.sql_id where s.username = 'RA_DB_USER';

• If there are open transactions, then there is a likelihood that the specific SQL script will fail during the upgrade process.  Either determine the nature of the open transactions and terminate them, or proceed with the upgrade and follow the procedure below in the event the script does in fact fail.