"[Ora]ORA-20000: ERROR ! Missing constraints" while upgrading VC from 6.0 to 6.5

Products

• VMware

Issue/Introduction

To prevent vCenter Server upgrade process failure when upgrading from version 6.0 to 6.5.

Symptoms:

• Upgrading vCenter Server from 6.0 to 6.5 and using a database that was initially created by VMware Virtual Infrastructure 3 or earlier
• The database had been upgraded from multiple versions of vCenter Server
• In the %TEMP%\...\vcsUpgrade\vcdb_req.err file, you see entries similar to:

Error while executing ./Upgrade-v2015-to-v2016/oracle/validate_oracle.sql:239, reason: Cannot execute statement(rc=-1).
DECLARE
l_text_full VARCHAR2(32767) := NULL;
l_text VARCHAR2(2000) := NULL;
l_verid int;
BEGIN
 
1 [HY000](20000) [Oracle][ODBC][Ora]ORA-20000: ERROR ! Missing constraints: VPX_EXT_TYPE_IDS.FK_VETI_REF_EXT,VPX_EXT_SERVER_EMAIL.FK_VPX_EMAIL_REF_SERVER; Extra tables or columns: VPX_HIST_STAT_TMP.COUNTER_ID,VPX_HIST_STAT_TMP.STAT_VAL,VPX_HIST_STAT_TMP.TIME_ID; Extra indexes: VPX_HIST_STAT_TMP.IX_VPX_HIST_STAT_TMP_TID; Extra constraints: VPX_EXT_TYPE_IDS.FK_VPX_EXT_TYPE_IDS_REF_EXT;
ORA-06512: at line 199
 
ERROR: Cannot execute statement(rc=-1).
DECLARE
l_text_full VARCHAR2(32767) := NULL;
l_text VARCHAR2(2000) := NULL;
l_verid int;
BEGIN
 
1 [HY000](20000) [Oracle][ODBC][Ora]ORA-20000: ERROR ! Missing constraints: VPX_EXT_TYPE_IDS.FK_VETI_REF_EXT,VPX_EXT_SERVER_EMAIL.FK_VPX_EMAIL_REF_SERVER; Extra tables or columns: VPX_HIST_STAT_TMP.COUNTER_ID,VPX_HIST_STAT_TMP.STAT_VAL,VPX_HIST_STAT_TMP.TIME_ID; Extra indexes: VPX_HIST_STAT_TMP.IX_VPX_HIST_STAT_TMP_TID; Extra constraints: VPX_EXT_TYPE_IDS.FK_VPX_EXT_TYPE_IDS_REF_EXT;
ORA-06512: at line 199

Cause

This issue occurs when tables or constraints that are no longer used still exist within the vCenter Server database. The vCenter Server 6.0 database upgrade scripts do not remove these tables and prevent the upgrade from completing.

Resolution

Ensure to take a good backup of the existing vCenter Server Oracle database before re running the upgrade process.

1. Log in to sqlplus with the vCenter Server database Schema owner and password.

Example: vpxadmin user

2. Do a cleanup of the vCenter Server Oracle database by executing the sql statements as follows:

alter table VPX_EXT_TYPE_IDS add constraint FK_VETI_REF_EXT foreign key (EXT_ID)  references VPX_EXT (EXT_ID) on delete cascade;
alter table VPX_EXT_SERVER_EMAIL add constraint FK_VPX_EMAIL_REF_SERVER foreign key (EXT_SERVER_ID) references VPX_EXT_SERVER (EXT_SERVER_ID) on delete cascade;
drop index VPX_HIST_STAT_TMP.IX_VPX_HIST_STAT_TMP_TID;
drop table VPX_HIST_STAT_TMP;
alter table VPX_EXT_TYPE_IDS drop constraint FK_VPX_EXT_TYPE_IDS_REF_EXT;

Note: Verify that the dropped objects are in the recycle bin with the below query:

SELECT object_name as recycle_name, original_name, type FROM recyclebin;

3. Re-run the vCenter Server 6.5 upgrade process.

Additional Information

Impact/Risks:
The vCenter Server 6.5 database upgrade process errors out and fail to complete successfully.
Ensure to take a good backup of the existing vCenter Server Oracle database before re-running the upgrade process.