Configure an Oracle database for vCenter Appliance 5.1

We recently purchased a few extra ESX servers and this was a good time to also upgrade our vCenter. We were still running Vmware ESX 4.1 and Vcenter 4.1. We did not upgrade to 5.0 because of the vRAM licensing model. Fortunately, this is again a thing of the past.

Our vCenter contains 15 host and a few hundred VMs and this was getting a bit too much for MSSQL Express. Rather than go for MSSQL Standard, we decided to use an Oracle database for vCenter. This made sense since we are an Oracle shop with a couple of dedicated Oracle DBA's.

We decided to use the Vmware vCenter appliance an connect that to an Oracle database. Since the appliance expects a database that is already populated, we needed to setup the database ourselves. We followed the instructions "Configure Oracle Databases" in the vSphere Installation and Setup guide.

Documentation caveats

The documentation for configuring the Oracle database does not seem very accurate:

• a user is created before the tablespace used by the user.
• the scripts are not executed, they do not contain a /. This means that after pressing enter, you have to type / and press enter again.
• half the scripts executed returned errors. the scripts were expecting tables, views and stored procedures that were not available. We found that all the objects that we needed were created by running a few extra .sql scripts in the dbschema directory.
• the Vmware documentation says that temptable0_proc_oracle.sql, temptable1_proc_oracle.sql and temptable2_proc_oracle.sql should be run. We had some trouble finding these files, but could not execute them either because the required tables were missing. We could not find any "create table" statement for the required tables in any of the .sql files. Because vmware kb "Verifying jobs and stored procedures installed in vCenter Server 5.1" does not mention the temptable stored procedures while the same kb for 5.0 does, we think these are no longer required.

Procedure

Create the Oracle database and start with creating a tablespace for the VCENTER schema:

create tablespace VPX datafile '' size 2000M autoextend on next 500M maxsize unlimited;

create user VCENTER profile "DEFAULT" identified by "" default tablespace "VPX" account unlock;

Run the script VCDB_oracle.sql:

@VCDB_oracle.SQL
This script creates the tables and indexes.

Now run some extra scripts not mentioned in the documentation to create some stored procedures and views that are required by the other scripts:

@VCDB_views_oracle.sql

@insert_stats_proc_oracle.sql

@l_stats_rollup1_proc_oracle.sql

@l_stats_rollup2_proc_oracle.sql

@l_purge_stat2_proc_oracle

@l_stats_rollup3_proc_oracle.sql

@l_purge_stat3_proc_oracle

Continue with the scripts mentioned in the documentation

@load_stats_proc_oracle.sql

@purge_stat2_proc_oracle.sql

@purge_stat3_proc_oracle.sql

@purge_usage_stats_proc_oracle.sql

@stats_rollup1_proc_oracle.sql

@stats_rollup2_proc_oracle.sql

@stats_rollup3_proc_oracle.sql

@cleanup_events_oracle.sql

@delete_stats_proc_oracle.sql

@load_usage_stats_proc_oracle.sql

@TopN_DB_oracle.sql

@calc_topn1_proc_oracle.sql

@calc_topn2_proc_oracle.sql

@calc_topn3_proc_oracle.sql

@calc_topn4_proc_oracle.sql

@clear_topn1_proc_oracle.sql

@clear_topn2_proc_oracle.sql

@clear_topn3_proc_oracle.sql

@clear_topn4_proc_oracle.sql

@rule_topn1_proc_oracle.sql

@rule_topn2_proc_oracle.sql

@rule_topn3_proc_oracle.sql

@rule_topn4_proc_oracle.sql

@process_license_snapshot_oracle.sql

The process_temptable scripts can be skipped.

@process_temptable0_proc_oracle.sql

@process_temptable1_proc_oracle.sql

@process_temptable2_proc_oracle.sql

Run the scripts to create a few jobs:

@job_schedule1_oracle.sql

@job_schedule2_oracle.sql

@job_schedule3_oracle.sql

@job_cleanup_events_oracle.sql

@job_topn_past_day_oracle.sql

@job_topn_past_week_oracle.sql

@job_topn_past_month_oracle.sql

@job_topn_past_year_oracle.sql

@job_property_bulletin_oracle.sql