OPERATIONAL DEFECT DATABASE
...
...
Upgrading vCenter Server 6.0 and later failsIn the vcdb_reg.err file, you see the error: Source vCenter Server schema validation found an issue
To resolve this issue follow the steps below: Go to %TEMP%...\vcsMigration\vcdb_req.err file.In vcdb_err.err search for following patterns: "ERROR ! Missing" or "ERROR ! Extra" Note: The first refers to missing and the later to extra objects. MSSQL and Oracle message are limited in size. Therefore in case of many inconsistencies the message doesn't contains all missing or extra objects. It is possible after fixing of listed errors on next run customer see another errors. Message limitations: MS SQL: 2000 symbols per error type (Missing tables or columns/Missing indexes .. and 4000 symbols for all message. Oracle: 2000 symbols per error type and 32000 symbols for all message PostgreSQL: unlimited The full set of possible missing/extra objects is: Missing tables or columns:Missing indexes:Missing constraints:Extra tables or columns:Extra indexes:Extra constraints:User created statistics:Sequences owner: Retrieve the password of the database password How to obtain vCenter Server database password see: Selective deletion of tasks, events, and historical performance data in vSphere 5.x and 6.x (2110031)Stop the vCenter Server service. For Windows machine: Log in as an administrator to the Windows machine on which vCenter Server is installed.Navigate to Start > Administrative Tools > Services.Right-click the VMware VirtualCenter Server service.Click Stop. Note: For more information, see Stopping, starting, or restarting VMware vCenter Server services (1003895) and Stopping, starting, or restarting VMware vCenter Server 6.0 services (2109881) . For vCenter Server Appliance: Access the appliance shell, and press Alt+F1.Log in to the appliance shell as root user.Stop the vCenter Server service by running the command: service-control --stop vmware-vpxd Note: For more information, see Stopping, starting or restarting vCenter Server Appliance services (2054085) and Stopping, starting or restarting VMware vCenter Server Appliance 6.0 services (2109887) . Back up the vCenter Server database. Note: The following procedure modifies the database. Ensure to take a backup of the database before proceeding: For MS SQL and Oracle databases, see your database vendor's documentation.For information about backing up the embedded PostgreSQL database, see Back up and restore vCenter Server Appliance/vCenter Server 6.0 vPostgres database (2091961) . How to run the script Microsoft SQL Server Express: Log in as an administrator to the machine on which vCenter Server is installed Open the command prompt and run the script: sqlcmd -E -S IP-address-or-FQDN-of-the-database-machine\instance_name -d database-name Microsoft SQL Server: Log in as an administrator to the machine on which vCenter Server is installedOpen the command prompt and run the script: sqlcmd -S IP-address-or-FQDN-of-the-database-machine\instance_name -U vCenter-Server-database-user -P password -d database-name OR, Use Microsoft SQL Server management StudioOracle: Log in to the Oracle machine.Log in to sqlplus with the vCenter Server database user name and password: sqlplus vCenter-Server-database-user/password PostgreSQL: Log in to the machine in which vCenter Server is running.Run the script by using psql and when prompted provide the vCenter Server user password. If you installed vCenter Server on Windows, at the command prompt navigate to the vCenter Server installation directory (%VMWARE_CIS_HOME%\vPostgres\bin), which by default is C:\Program Files\VMware\vCenter Server\vPostgres\bin and run psql: psql -U vCenter-Server-database-user -d database-name If you deployed the vCenter Server Appliance, run this command: /opt/vmware/vpostgres/current/bin/psql -U vCenter-Server-database-user -d database-name Fix reported inconsistencies Navigate to vCenter Server installation directory (%VMWARE_CIS_HOME%\vpxd\sql). In Windows the default path is C:\Program Files\vmware\infrastructure\VirtualCenter Server\sql Depend on customer database type locate the SQL fresh install scripts VCDB_mssql.sqlMSSQL VCDB_oracle.sqlOracle VCDB_PostgreSQLPostgreSQL Open the file in a text editor such as notepad and locate the missing objectOpen a connection to the database server (See How to Run Scripts)Next step depends from type of inconsistency Missing tables or columns.Check list of missing tables and columns in the message. List existing columns in VCDB for each table in the message from database. List existing columns:MS SQL: SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE FROM information_schema.columns WHERE table_name = 'VPX_ENTITY';Oracle: SELECT TABLE_NAME, COLUMN_NAME, NULLABLE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='VPX_ENTITY';PostgreSQL: SELECT TABLE_NAME, In case of missing table all columns are listed in the message and the above query doesn't return rows. Example:ERROR ! Missing tables or columns: VPX_ENTITY.ID; VPX_ENTITY.NAME; VPX_ENTITY.TYPE_ID;VPX_ENTITY.PARENT_ID; Resolution: Find sql code to create the table in VCDB_mssql/VCDB_oracle/VCDB_PostgreSQL.sql and execute it. Some tables are catalog tables and for such we have also INSERT statements in the same sql file. Thus you need to search for INSERT statements for this table.If such exist - execute it. If the table exists, but a column is missing, prepare sql command to add it according to definition in fresh install script. For each missing columns execute command to add it. Standard for MS SQL/Oracle/PostgreSQL:ALTER TABLE VPX_ENTITY ADD PARENT_ID INT; Note: If missing column is defined as NOT NULL without DEFAULT value - customer should contact customer support. Missing indexes. In the fresh install script locate the code to create the respective missing index. Before you try to create the index you first need to make sure you can do so. For that: If such index exists -rename it or drop it and run the script to create missing index. List indexes per table:MS SQL: PostreSQL: ALTER INDEX IF EXISTS IX_VPX_ENTITY_TYPE_ID RENAME TO VPXI_ENTITY_TYPE_ID;If missing index is unique index check the table for duplicate values.Example: SELECT ID, COUNT(*) FROM VPX_ENTITY GROUP BY ID HAVING COUNT(*) > 1;Note: If duplicated values exist - customer should contact customer support. Start the vCenter Server Service.Do the above step for each missing/extra objects.User created statistics: MS SQL enables users to create user defined statistics. Such statistics would prevent the upgrade from completing successfully. Therefore user statistics should be dropped before upgrade. To check all user defined statistics on vCenter database use: SELECT OBJECT_NAME(object_id) table_name, c.name statistic_name FROM sys.stats c WHERE user_created > 0 AND OBJECT_NAME(object_id) like 'VPX%' For each row returned by the query, drop the respective user statistic. Example: DROP STATISTICS .<statistic name>; Extra constraints: In case an extra constraint(s) was reported, the customer should drop it before rerunning the upgrade. In case extra primary key constraint customer may see the error if primary key is referenced by some foreign key to another table. For example For example:MS SQL: ALTER TABLE VPX_ENTITY DROP CONSTRAINT FK_VPX_ENT_REF_VPX_ENT_TYPE; Oracle: ALTER TABLE VPX_ENTITY DROP CONSTRAINT PK_VPX_ENTITY DROP INDEX; PostgreSQL: ALTER TABLE VPX_ENTITY DROP CONSTRAINT FK_VPX_ENT_REF_VPX_ENT_TYPE; Extra indexes: In case an extra index(es) was reported, the customer should drop it before rerunning the upgrade. For example:MS SQL: DROP INDEX VPX_ENTITY_F1 ON VPX_ENTITY; Oracle: DROP INDEX VPX_ENTITY_F1; PostgreSQL: DROP INDEX IF EXISTS VPX_ENTITY_F1 CASCADE; Extra tables or columns: All tables with prefix VPX_ which are not expected to exist in customer database will be listed in the message. If all columns in a table are listed, the table should be dropped or renamed with other prefix. Before drop table check for table references Script for check dependent objects:MS SQL:SELECT tab1.name AS table_name, obj.name AS constraint_name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE tab2.name ='VPX_ENTITY' Oracle: SELECT table_name, constraint_name FROM all_constraints WHERE constraint_type = 'R' AND r_constraint_name in (SELECT constraint_name FROM all_constraints WHERE constraint_type in ('P', 'U') AND table_name = 'VPX_ENTITY') ORDER BY table_name, constraint_name PostgreSQL: SELECT tc.table_name, tc.constraint_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND UPPER(ccu.table_name) = 'VPX_ENTITY';Example of remove table script for MS SQL/Oracle/PostgreSQL DROP TABLE VPX_ENTITY; Missing constraints. In the fresh install script locate the code to create the respective missing constraint. Before you try to create the foreign key you first need to make sure you can do so. For that: Check for another constraint on the same column.In case you locate a primary/foreign key is already defined on the same table-column relationship, you can rename it. Rename constraint: MS SQL: exec sp_rename @objname = 'dbo.PK_VPX_AL_STATE', @newname = 'PK_VPX_ALARM_STATE', @objtype = 'OBJECT' Note:When renaming a constraint, the schema to which the constraint belongs must be specified. Replace dbo schema with customer used schema name. Oracle: ALTER TABLE VPX_ALARM_STATE RENAME CONSTRAINT PK_VPX_AL_STATE to PK_VPX_ALARM_STATE; PostreSQL: ALTER TABLE VPX_ALARM_STATE RENAME CONSTRAINT PK_VPX_ALARM_STATE1 TO PK_VPX_ALARM_STATE; If not exists - create it You also need to make sure that parent-child tables relationship is consistent and you indeed can deploy the missing foreign key. You need to have corresponding parent-child records in both tables participating at both ends of the foreign key. Example of script for orphan data for all databases:SELECT * FROM VPX_ENTITY child_table WHERE NOT EXISTS (SELECT 1 FROM VPX_OBJECT_TYPE master_table WHERE master_table.ID = child_table.TYPE_ID);If orphan/unknown data exists - customer should contact the support. Create constraint after cleanup.Note: Do not use WITH NOCHECK/NOVALIDATE. In case you use such option the database upgrade may fail.List existing constraints per table:MS SQL: SELECT tab1.name AS table_name, obj.name AS constraint_name, col1.name AS column_name, tab2.name AS referenced_table, col2.name AS referenced_column FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE tab1.name ='VPX_ENTITY' Oracle: SELECT cc.table_name, cc.constraint_name,column_name, index_name FROM all_cons_columns cc JOIN user_constraints uc on cc.TABLE_NAME = uc.table_name AND cc.CONSTRAINT_NAME = uc.constraint_name WHERE uc.table_name = UPPER('VPX_ENTITY' ) AND CONSTRAINT_TYPE in ('P','R'); PostgreSQL: SELECT tc.table_name, tc.constraint_name, kcu.column_name, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND UPPER(tc.table_name) = 'VPX_ENTITY';The above query you'll get the list of all existing constraints and their respective columns. Using the create statement from the fresh install script, you need to cross reference the tables and columns for your missing constraints. Check columns included in index definition.Check that there is not another index on the same columns.If such index exists -rename it or drop it and run the script to create missing index. List indexes per table: MS SQL: SELECT OBJECT_NAME(ind.object_id) table_name, ind.name index_name, ic.index_column_id column_id, col.name column_name, ind.is_unique unique_idx FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id WHERE OBJECT_NAME(ind.object_id) = 'VPX_ENTITY' ORDER BY ind.name, ind.index_id, ic.index_column_id; PostgreSQL: SELECT t.relname as table_name, i.relname as index_name, a.attname as column_name, indisunique as unique_idx FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' AND UPPER(t.relname) = 'VPX_ACCESS' ORDER BY t.relname, i.relname; Oracle: SELECT a.table_name, a.index_name, a.column_name, b.uniqueness FROM all_ind_columns a JOIN all_indexes b ON a.index_name=b.index_name WHERE a.table_name = upper('VPX_ALARM_RUNTIME') ORDER BY a.table_name, a.index_name, a.column_position; Rename index: MS SQL: EXEC sp_rename @objname =N'VPX_ENTITY.IX_VPX_ENTITY_TYPE_ID',@newname = N'VPXI_ENTITY_TYPE_ID', @objtype = N'INDEX'; Oracle: ALTER INDEX IX_VPX_ENTITY_TYPE_ID rename to VPXI_ENTITY_TYPE_ID; PostreSQL: ALTER INDEX IF EXISTS IX_VPX_ENTITY_TYPE_ID RENAME TO VPXI_ENTITY_TYPE_ID; If missing index is unique index check the table for duplicate values. Example: SELECT ID, COUNT(*) FROM VPX_ENTITY GROUP BY ID HAVING COUNT(*) > 1;Note: If duplicated values exist - customer should contact customer support. Missing constraints. In the fresh install script locate the code to create the respective missing constraint. Before you try to create the foreign key you first need to make sure you can do so. For that: Check for another constraint on the same column.In case you locate a primary/foreign key is already defined on the same table-column relationship, you can rename it. Rename constraint: MS SQL: exec sp_rename @objname = 'dbo.PK_VPX_AL_STATE', @newname = 'PK_VPX_ALARM_STATE', @objtype = 'OBJECT' Note: When renaming a constraint, the schema to which the constraint belongs must be specified. Replace dbo schema with customer used schema name.Oracle: ALTER TABLE VPX_ALARM_STATE RENAME CONSTRAINT PK_VPX_AL_STATE to PK_VPX_ALARM_STATE; PostreSQL: ALTER TABLE VPX_ALARM_STATE RENAME CONSTRAINT PK_VPX_ALARM_STATE1 TO PK_VPX_ALARM_STATE; If not exists - create it You also need to make sure that parent-child tables relationship is consistent and you indeed can deploy the missing foreign key. You need to have corresponding parent-child records in both tables participating at both ends of the foreign key. Example of script for orphan data for all databases: SELECT * FROM VPX_ENTITY child_table WHERE NOT EXISTS (SELECT 1 FROM VPX_OBJECT_TYPE master_table WHERE master_table.ID = child_table.TYPE_ID); If orphan/unknown data exists - customer should contact the support. Create constraint after cleanup. Note: Do not use WITH NOCHECK/NOVALIDATE. In case you use such option the database upgrade may fail. List existing constraints per table: MS SQL: SELECT tab1.name AS table_name, obj.name AS constraint_name, col1.name AS column_name, tab2.name AS referenced_table, col2.name AS referenced_column FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE tab1.name ='VPX_ENTITY' Oracle: SELECT cc.table_name, cc.constraint_name,column_name, index_name FROM all_cons_columns cc JOIN user_constraints uc on cc.TABLE_NAME = uc.table_name AND cc.CONSTRAINT_NAME = uc.constraint_name WHERE uc.table_name = UPPER('VPX_ENTITY' ) AND CONSTRAINT_TYPE in ('P','R'); PostgreSQL: SELECT tc.table_name, tc.constraint_name, kcu.column_name, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND UPPER(tc.table_name) = 'VPX_ENTITY';The above query you'll get the list of all existing constraints and their respective columns. Using the create statement from the fresh install script, you need to cross reference the tables and columns for your missing constraints. Extra tables or columns: All tables with prefix VPX_ which are not expected to exist in customer database will be listed in the message. If all columns in a table are listed, the table should be dropped or renamed with other prefix. Before drop table check for table references Script for check dependent objects:MS SQL: SELECT tab1.name AS table_name, obj.name AS constraint_name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE tab2.name ='VPX_ENTITY' Oracle: SELECT table_name, constraint_name FROM all_constraints WHERE constraint_type = 'R' AND r_constraint_name in (SELECT constraint_name FROM all_constraints WHERE constraint_type in ('P', 'U') AND table_name = 'VPX_ENTITY') ORDER BY table_name, constraint_name PostgreSQL: SELECT tc.table_name, tc.constraint_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND UPPER(ccu.table_name) = 'VPX_ENTITY'; Example of remove table script for MS SQL/Oracle/PostgreSQL DROP TABLE VPX_ENTITY; Extra indexes: In case an extra index(es) was reported, the customer should drop it before rerunning the upgrade. Example: MS SQL: DROP INDEX VPX_ENTITY_F1 ON VPX_ENTITY; Oracle: DROP INDEX VPX_ENTITY_F1; PostgreSQL: DROP INDEX IF EXISTS VPX_ENTITY_F1 CASCADE; Extra constraints: In case an extra constraint(s) was reported, the customer should drop it before rerunning the upgrade. In case extra primary key constraint customer may see the error if primary key is referenced by some foreign key to another table. Example: MS SQL: ALTER TABLE VPX_ENTITY DROP CONSTRAINT FK_VPX_ENT_REF_VPX_ENT_TYPE; Oracle: ALTER TABLE VPX_ENTITY DROP CONSTRAINT PK_VPX_ENTITY DROP INDEX; PostgreSQL: ALTER TABLE VPX_ENTITY DROP CONSTRAINT FK_VPX_ENT_REF_VPX_ENT_TYPE; User created statistics: MS SQL enables users to create user defined statistics. Such statistics would prevent the upgrade from completing successfully. Therefore user statistics should be dropped before upgrade. To check all user defined statistics on vCenter database use:SELECT OBJECT_NAME(object_id) table_name, c.name statistic_name FROM sys.stats c WHERE user_created > 0 AND OBJECT_NAME(object_id) like 'VPX%' For each row returned by the query, drop the respective user statistic.Example: DROP STATISTICS <table name>.<statistic name>; Do the above step for each missing/extra objects.Start the vCenter Server Service. Sequences owner: See vCenter Server Appliance 6.x Upgrade or Migration Fails During Firstboot - VMware vCenter Server Firstboot Failed - "ERROR: must be owner of relation vpx_sn_vdevice_backing_rel_seq".
Click on a version to see all relevant bugs
VMware Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.
