WCC/WebUI Upgrade fails with "ORA-02298: cannot validate (DBNAME.AO_SCHAGENT_PLUGIN_FK) - parent keys not found"

Products

• Autosys Workload Automation

Issue/Introduction

WCC DB errors while upgrading to R12.1.01
Oracle based WebUI/WCC upgrade fails with below error.

2024-06-19 22:40:54,102 [main] ERROR com.ca.wcc.db.DBChangesForUpgrade(251) -
There was a problem with database while running the upgrade:
ORA-02298: cannot validate (AO_SCHAGENT_PLUGIN_FK) - parent keys not found
java.sql.SQLIntegrityConstraintViolationException: ORA-02298: cannot validate (DBNAME.AO_SCHAGENT_PLUGIN_FK) - parent keys not found

Environment

Autosys 12.1.01
WCC 12.1.01

Cause

New version introduced foreign key in table AO_SCHAGENT_PLUGIN

Resolution

• Stop WCC if it is running
• Execute the attached SQL queries (sqlqueries.txt) against the WCC database, as the WCC schema owner (default is wccadmin)
  • Once the queries are complete, try the below query: 

    select count(*) from AO_SCHAGENT_PLUGIN WHERE SCHEDULERAGENTID NOT IN (SELECT ID FROM AO_SCHAGENT);

    -- If it returns zero rows they proceed to upgrade.

• Retry the upgrade again (by running the installer again) this time it should finish the database upgrade successfully