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’;