Failed vCloud 1.5.2 to 5.1.2 upgrade

While upgrading our final vCloud 1.5 install to 5.1.2, we ran into an issue while upgrading the vCloud database.  The upgrade log in $VCLOUD_HOME/logs/ contained the following

2013-09-20 10:21:45,658 | DEBUG    | pool-1-thread-1           | RawSQLTask                     | Executing sql ‘
DECLARE num_rows integer;
BEGIN
SELECT count(*)
INTO num_rows
FROM all_constraints
WHERE constraint_name = ‘FK_NETWORK_POOL’;

IF num_rows <> 0 THEN
EXECUTE IMMEDIATE ‘ALTER TABLE org_prov_vdc DROP CONSTRAINT fk_network_pool’;
END IF;
END;
‘ |
2013-09-20 10:21:46,163 | WARN     | pool-1-thread-1           | SerialAggregateTask            | Steps to upgrade to 2.0.238: Task failed due to uncaught exception |
java.sql.SQLException: ORA-02443: Cannot drop constraint  – nonexistent constraint
ORA-06512: at line 10

VMware supported provided the following database edit that resolved the issue.  Remember to commit the changes if you’re using Oracle.Confirm the database.schema.version value by running this query.

Confirm the database.schema.version value by running this query:

SELECT value FROM config WHERE name=’database.schema.version’;

This query should return 2.0.237.transition.

Run this script:

DECLARE
 no_constraint EXCEPTION;
 PRAGMA EXCEPTION_INIT(no_constraint, -2443);
BEGIN
 EXECUTE IMMEDIATE ‘ALTER TABLE network_assigned_mac DROP CONSTRAINT fk_namac_to_resource DROP INDEX’;
 EXCEPTION WHEN no_constraint THEN
   NULL;
END;

DECLARE
 no_constraint EXCEPTION;
 PRAGMA EXCEPTION_INIT(no_constraint, -2443);
BEGIN
 EXECUTE IMMEDIATE ‘ALTER TABLE network_assigned_mac DROP CONSTRAINT fk_namac_to_ri DROP INDEX’;
 EXCEPTION WHEN no_constraint THEN
   NULL;
END;

UPDATE config SET value=’2.0.238′ WHERE name=’database.schema.version’;

Re-run the ./bin/upgrade utility to complete the upgrade process.