Determine the Oracle Database Size and the Storage Size for the New Appliance

Before upgrading a vCenter Server Appliance or migrating a vCenter Server on Windows that uses an external Oracle database, you must determine the size of the existing database. Based on the size of the existing database, you can calculate the minimum storage size for the new appliance so that the embedded PostgreSQL database can successfully assume the data from the old database with enough free disk space after the upgrade.

You run scripts to determine the Oracle core table size, the events and tasks table size, and the statistics table size. The Oracle core table corresponds to the database (/storage/db) partition of the PostgreSQL database. The Oracle events and tasks and statistics tables correspond to the statistics, events, alarms, and tasks (/storage/seat) partition of the PostgreSQL database.

During the upgrade of the appliance, you must select a storage size for the new appliance that is at least twice the size of the Oracle tables size.

During the upgrade of the appliance, you can select the types of data to transfer to the new appliance. For minimum upgrade time and storage requirement for the new appliance, you can select to transfer only the configuration data.

Prerequisites

You must have the vCenter Server database login credentials.

Procedure

1. Log in to a SQL*Plus session with the vCenter Server database user.
2. Determine the core table size by running the following script.

   SELECT ROUND(SUM(s.bytes)/(1024*1024)) SIZE_MB
     FROM   user_segments s
     WHERE  (s.segment_name,s.segment_type)
                   IN (SELECT seg_name, seg_type FROM
                            (SELECT t.table_name seg_name, t.table_name tname,
                              'TABLE' seg_type
                              FROM   user_tables t
                             UNION
                             SELECT i.index_name, i.table_name,
                             'INDEX'
                              FROM   user_indexes i
                            ) ti
                       WHERE  (ti.tname LIKE 'VPX_%'
                               OR ti.tname LIKE 'CL_%'
                               OR ti.tname LIKE 'VDC_%')
                           AND ti.tname NOT LIKE 'VPX_SAMPLE_TIME%'
                           AND ti.tname NOT LIKE 'VPX_HIST_STAT%'
                           AND ti.tname NOT LIKE 'VPX_TOPN%'
                           AND ti.tname NOT LIKE 'VPX_SDRS_STATS_VM%'
                           AND ti.tname NOT LIKE 'VPX_SDRS_STATS_DATASTORE%'
                           AND ti.tname NOT LIKE 'VPX_TASK%'
                           AND ti.tname NOT LIKE 'VPX_EVENT%'
                           AND ti.tname NOT LIKE 'VPX_PROPERTY_BULLETIN%');

   The script returns the database storage size in MB.
3. Determine the events and tasks table size by running the following script.

   SELECT ROUND(SUM(s.bytes)/(1024*1024)) SIZE_MB
   FROM   user_segments s
   WHERE  (s.segment_name,s.segment_type)
                IN (SELECT seg_name, seg_type FROM
                         (SELECT t.table_name seg_name, t.table_name tname,
                           'TABLE' seg_type
                           FROM   user_tables t
                          UNION
                          SELECT i.index_name, i.table_name,
                          'INDEX'
                           FROM   user_indexes i
                         ) ti
                    WHERE
                       ti.tname LIKE 'VPX_TASK%'
                    OR ti.tname LIKE 'VPX_EVENT%');

   The script returns the events and tasks storage size in MB.
4. Determine the statistics table size by running the following script.

   SELECT ROUND(SUM(s.bytes)/(1024*1024)) SIZE_MB
   FROM   user_segments s
   WHERE  (s.segment_name,s.segment_type)
                IN (SELECT seg_name, seg_type FROM
                         (SELECT t.table_name seg_name, t.table_name tname,
                           'TABLE' seg_type
                           FROM   user_tables t
                          UNION
                          SELECT i.index_name, i.table_name,
                          'INDEX'
                           FROM   user_indexes i
                         ) ti
                    WHERE
                       ti.tname LIKE 'VPX_SAMPLE_TIME%'
                    OR ti.tname LIKE 'VPX_TOPN%'
                    OR ti.tname LIKE 'VPX_TASK%'
                    OR ti.tname LIKE 'VPX_EVENT%'
                    OR ti.tname LIKE 'VPX_HIST_STAT%');

   The script returns the statistics storage size in MB.
5. Calculate the minimum storage size for the new appliance that you are going to deploy during the upgrade.
   1. The size of the database (/storage/db) partition of the embedded PostgreSQL database must be at least twice the size of the Oracle core table returned in Step 2.
   2. The size of the statistics, events, alarms, and tasks (/storage/seat) partition of the embedded PostgreSQL database must be at least twice the sum of the sizes of the Oracle events and tasks and statistics tables returned in Step 3 and Step 4.
   For example, if the Oracle core table is 100 MB, the events and tasks table is 1,000 MB, and the statistics table is 2,000 MB, then the Postgres /storage/db partition must be at least 200 MB and the /storage/seat partition must be at least 6,000 MB.