Shulou(Shulou.com)06/01 Report--

Event: vcenter5.5 upgrade to 6. 0 failed

1. More than a dozen Pl (stored procedures) failed during upgrade

The 2.30g temp tablespace is quickly used up, and the tablespace is temporarily expanded to 99g, and the space is almost full.

3. Check the SQL statement executed during the upgrade and find that the following statement takes too long to execute

INSERT INTO VPX_HIST_STAT1_224 SELECT INSERTED.COUNTER_ID,HS.TIME_ID,SUM (STAT_VAL) FROM VPX_HIST_STAT1_224 HS INNER JOIN VPX_STAT_COUNTER SC ON HS.COUNTER_ID = SC.COUNTER_ID INNER JOIN VPX_DEVICE D ON SC.DEVICE_ID=D.DEVICE_ID INNER JOIN VPX_OBJECT_HIERARCHY H ON SC.ENTITY_ID = H.STAT_ENTITY_ID INNER JOIN (SELECT SC.ENTITY_ID, SC.COUNTER_ID CASE WHEN SD49.NAME='swapused' AND SD49.GROUP_NAME='mem' AND SD49.ROLLUP_TYPE=0 THEN (SELECT ID FROM VPX_STAT_DEF WHERE NAME='swapped' AND GROUP_NAME='mem' AND ROLLUP_TYPE=0) ELSE SC.STAT_ID END MODSTAT_ID, NC.AGGR_TO NC.AGGR_TYPE FROM VPX_STAT_ID T INNER JOIN VPX_STAT_COUNTER SC ON SC.ENTITY_ID=T.ID INNER JOIN VPX_NEW_COUNTERS NC ON NC.COUNTER_ID = SC.STAT_ID INNER JOIN VPX_STAT_DEF SD49 ON SD49.ID = SC.STAT_ID) INSERTED ON H.PARENT_STAT_ENTITY_ID=INSERTED.ENTITY_ID AND INSERTED.MODSTAT_ID=SC.STAT_ID AND (INSERTED.AGGR_TYPE=H.ENTITY_TYPE OR H.ENTITYTYPER') AND (H.ENTITY _ TYPE=INSERTED.AGGR_TO OR (INSERTED.AGGR_TO='C' AND H.ENTITY_TYPE IN ('V') 'H')) WHERE D.DEVICE_NAME IS NULL GROUP BY HS.TIME_ID,SC.STAT_ID,INSERTED.COUNTER_ID

Phenomenon: this statement is executed all the time, and the space in temp is full by this statement.

4. After the upgrade interface did not respond for more than an hour, the upgrade failure was reported.

Analysis:

The upgrade process went smoothly on other vc of the same version, but an error was reported on this production VC. I checked the following library and found that on this VC, the 74G VC B in the VCTBS tablespace is larger than any other database on the VC.

Looking through the official KB, it is found that a KB mentions that if the database is too large and exceeds 50 gigabytes, and the number of rows of the table exceeds 800 million, it is easy to upgrade slowly and fail, and it is necessary to re-reduce the database size and upgrade.

If the vCenter Server database is very large (50 GB or more) and the number of rows in the vpx_hist_stat# table is high (more than 800 million), the database summary script may be difficult to handle. To make the summarized scripts run faster, you need to reduce the size of the vCenter Server database.

Analysis:

To reduce the size of the vCenter Server database, you can delete all historical data. However, for the sake of security, you need to retain some historical performance data instead of deleting it all.

Resolve:

1. Make sure that the vCenter Server Database has been backed up properly.

2. Disable the VMware VirtualCenter Server service and any other services that use the database

3. Truncate VPX_HIST_STAT1 table and corresponding VPX_SAMPLE_TIME1 table. After the truncation is complete, verify that the summary job has now completed successfully.

To truncate a table, execute the following SQL statement:

Truncate table VPX_HIST_STAT1

Note: in vCenter Server 5.1 and 5.5, the table name is VPX_HIST_STAT1_n.

Truncate table VPX_SAMPLE_TIME1

Note: to reduce the size of the vCenter Server database, you can also delete data from the VPX_EVENT, VPX_EVENT_ARG, and VPX_TASK tables. To delete data from these tables, see Purging old data from the database used by vCenter Server (1025914) or Purging old data from the database used by VirtualCenter 2.x (1000125).

If the summary job does not complete successfully, you may need to truncate the VPX_HIST_STAT2 table and the corresponding VPX_SAMPLE_TIME2 table. Continue to truncate the VPX_HIST_STAT [1-4] table and the corresponding VPX_SAMPLE_TIME [1-4] table until the summary job completes successfully.

Note: the following other tables in vCenter Server 4.1 and 5.0 can also be truncated to further reduce the size of the vCenter Server database. The VPX_TEMPTABLEx table is the staging / caching area of the performance data until the performance data is processed and moved to VPX_HIST_STAT1.

Truncate table VPX_TEMPTABLE0

Truncate table VPX_TEMPTABLE1

Truncate table VPX_TEMPTABLE2

Practical solution:

Follow these steps

1. Https://kb.vmware.com/kb/2110031 download ORACLE script

two。 Upload to the ORACLE server

3. Run the script

SQL > @ / 2110031_Oracle_task_event_stat.sql

Table created.

... ..

Enter value for 1: 1

Enter value for 2: 1

Enter value for 3: 1

-

Database cleanup may take long time depends on size of VPX_TASK

VPX_EVENT, VPX_SAMPLE_TIME1, VPX_SAMPLE_TIME2, VPX_SAMPLE_TIME3

VPX_SAMPLE_TIME4 and all VPX_HIST_STATx_y tables.

-

Staring cleanup database at 03-07-2017 09:48:41

Deleting tasks older than 03-06-2017 01:48:41

Deleting events older than 03-06-2017 01:48:54

Deleting statistics older than 03-06-2017 01:50:01

Cleanup database completed at 03-07-2017 09:50:44

Table dropped.

...

4. Normal upgrade.

5. Check temporary tablespace usage

TABLESPACE_NAME TOTAL_BYTES FREE_BYTES USE_BYTES USE

SYSAUX 1670.000 208.250 1461.750 87.53%

UNDOTBS1 6075.000 4031.063 2043.938 33.65%

VUM 1024.000 1013.688 10.313 1.01

USERS 5.000 3.688 1.313 26.25%

SYSTEM 820.000 9.125 810.875 98.89%

VCTBS 73727.984 62865.250 10862.734 14.73%

TEMP 28672.000 23132.000 5540.000 19.32%

And then wait for the upgrade to be completed.

About Summary script

To run the summary script, perform the following steps:

Use SQL Management Studio to connect to vCenter Server's SQL database.

Navigate to the SQL Server Agent > job.

Select each summary job, then right-click and select start Job in step.

Start the VMware VirtualCenter Server service and any other services that were stopped in step 2.

Truncate all performance data in vCenter Server 5.1,5.5,6.0

For information on truncating data in vCenter Server and vCenter Server Appliance 5.1,5.5 and 6.0, see Selective deletion of tasks, events, and historical performance data in vSphere 5.x and 6.x (2110031)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.