by Porus Homi Havewala

We started to detail the concept and workings of Consolidation Planning for the Cloud, using Oracle Enterprise Manager Cloud Control 13c, in the previous parts of this article series. This is Part VII.

To recap, in the previous part of the series, we looked at consolidating to virtual machines when we created a new project for a P2V (Physical to Virtual) consolidation. One important point we noted was that only Oracle Virtual machines are considered in this case, if you are using other virtualization techniques such as VMware, then those virtual machines need to be treated as physical machines for the sake of consolidation.

We then started to look at the benchmark rate, the SPECint®_base_rate2006 benchmark being used by the Consolidation Planner for database or application hosts, or hosts with a mixed workload. The SPECjbb®2005 benchmark is used for middleware platforms. The technique to refresh these rates was to go to the Host Consolidation Planner homepage in Enterprise Manager, via Enterprise | Consolidation | Host Consolidation Planner, and then select Actions | View Data Collection. On this page, examine the section titled “Load Benchmark CSV File”.

The built-in benchmark rates can be updated with a downloaded Comma Separated Values (CSV) file. The latest data is available from the published page As per the instructions shown on the Enterprise Manager page, the new spec rates can be downloaded from in the form of CSV files, and then uploaded to the repository.

And where are these stored inside the Enterprise Manager repository? The full list of all the SPEC rates that are being used in Enterprise Manager can be found in the repository table EMCT_SPEC_RATE_LIB (owned by the SYSMAN user in the repository database).

The host consolidation plan results can then be reviewed, and the consolidation ratio, mapping, target server utilization, and excluded servers examined.

The Host Consolidation planner we have seen so far is essentially a server consolidation tool and is based on CPU, Memory and I/O Metrics at the O/S level. These metrics are collected by Oracle Enterprise Manager for Linux, Solaris, HP-UX, AIX and Windows and this means the planner would work on these platforms.

Note that if a Phantom Exadata server is used as the destination candidate in a P2P project and scenario, the Host Consolidation Planner (which was the only consolidation planner available in the previous Enterprise Manager Cloud Control 12c version)  does not by itself take into account the Oracle Exadata features such as Smart Scan, which could potentially have a positive impact on performance by reducing the CPU utilization of the databases and allowing more servers to be consolidated on the Exadata server.

However, in the latest Enterprise Manager Cloud Control 13c version, we have a new “Database Consolidation Workbench” that takes into consideration the effects of the Exadata features on consolidation. This is described in the next section.


Database Consolidation Workbench

Oracle Enterprise Manager Cloud Control 13c offers the new capability of a “Database Consolidation Workbench”. This is actually a feature of the Oracle Real Application Testing (RAT) Database Option, so it is mandatory to hold the RAT option license if you intend to use the Database Consolidation workbench.

The workbench is accessed via Enterprise | Consolidation | Database Consolidation Workbench in Enterprise Manager, and is a new comprehensive end-to-end solution for managing database consolidation. The analysis is based on historical workload data—database and host metrics, in combination with AWR data. The workbench provides automation in all consolidation phases, from planning to deployment and validation. Guesswork and human errors in database consolidation can be considerably eliminated if you use this workbench.

Database versions 10.2 and above are supported, as also consolidation to the Oracle Private/Public Cloud, or consolidation to Exadata. The workbench also supports high availability options for the implementation to minimize downtime, depending on what platform and version of database is present at the source and destination.

Note that for the Database Consolidation workbench, if you have the RAT option, you can run SQL Performance Analyzer (SPA)’s Exadata simulation, which will help assess the benefit for Exadata smart scans for phantom servers. SPA’s Exadata simulation runs on existing hardware and uses the init.ora parameter cell_simulation_enabled to assess the benefit. You can perform  this by going to each database’s home page in Enterprise Manager, and selecting Performance | SQL | SQL Tuning Sets from the menu to create a SQL Tuning set. Next, select  Performance | SQL | SQL Performance Analyzer Home, and “Exadata Simulation” on that page.

The Database Consolidation workbench has three main phases, Plan – Migrate – Validate. In the Plan phase, consolidation advice is given by identifying candidate databases for the designated consolidation platform, and Automatic Workload Repository (AWR) data gathered from the Oracle databases is used for this phase. Since this uses AWR, the Oracle Enterprise Manager Diagnostics Pack License is also required.

In the Migrate phase, the consolidation plan is actually implemented, by migrating the databases to the new consolidation platform using Enterprise Manager’s provisioning features. To perform the actual provisioning using Enterprise Manager, an additional Enterprise Manager pack needs to be licensed. This is the Database Lifecycle Management pack (DBLM) for Oracle Database. If you use Real Application Clusters (RAC) or Active Data Guard in the migration phase, those database options need to be licensed as well.

In the Validate phase, the consolidation plan is validated with SQL Performance Analyzer (SPA) (a component of the Real Application Testing Option) by running test workloads on the consolidated databases. The RAT option license is required.

Conflicts are identified based on workload characteristics, and you are also told if the workload is not suitable for Exadata consolidation. Storage/Platform advice is made available—such as the impact of using compression on I/O and storage, and the impact of the I/O offloading and Flash Cache features of Exadata. 

Note that for licensing database options such as RAT or Active Data Guard, and the Enterprise Manager Packs such as Diagnostics or DBLM, the Enterprise Edition (EE) of the database is required. Other editions of the Oracle Database such as standard edition (SE) cannot be used with these options or Enterprise Manager packs.

We’ll have a look at the new Database consolidation workbench in the continuation of this Consolidation Planning for the Cloud article series in Part VIII.

(This article is a modified excerpt from Chapter I of the new book by the author titled “Oracle Database Cloud Cookbook with Oracle Enterprise Manager 13c Cloud Control” published by Oracle Press in August 2016. For more information on the book, see