Having read numerous comments by people who blindly extol a one-size-fits-all approach to database consolidation, I felt compelled to produce a more reasoned overview. Before deciding on a database consolidation method, you really need to have a clear picture in your mind of what your goals are and what compromises you are willing to make in pursuit of those goals. There are several possible ways of achieving database consolidation, but all of them have specific pros and cons. The trick is to find one that has all the pros you need, with as few cons as possible. Invariably, no one consolidation method will be perfect for all your needs, so you will have to use multiple or compromise. To keep things simple, this article will attempt to ignore high availability, except for the odd passing mention. - Basic Resource Consolidation - Virtualization - Containers - Multi-Instance - Schema Consolidation - Multitenant Option - Cloud - Conclusion Related articles. - A Cure for Virtual Insanity: A Vendor-Neutral Introduction to Virtualization Without the Hype - Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB) - Oracle Databases in the Cloud At its simplest level, any type of consolidation will only work if there are enough resources available to complete the total workload. This is true of all resources, including CPU, memory, disk and network. When you are looking to consolidate databases you must make sure that all the workload profiles are compatible. The following graph shows two databases, both with high resource requirements at different times of the day. Since the high workloads don't overlap, these workloads are suitable to be consolidated. In the next case, the workloads run throughout the day, but the combination of the workloads does not exceed the capacity of the server at any point. These workloads are also suitable for consolidation.

In the following case, the combination of the workloads takes the resource usage above the maximum capacity of the server, which means these workloads are not suitable for consolidation.

Whenever you are considering any form of consolidation, you have to always keep in mind the total workload that will result from it. Splitting the physical hardware into several virtual machines provides hardware consolidation, not database-specific consolidation. For example, we may start off with 10 databases, each on its own physical server and end up with 10 databases, each on its own virtual machine, but running on a single physical server. This maintains the separation between the systems, with each able to run at a different OS and database version if required. There is of course resource associated with running the operating system in each of the 10 VMs, so this could be seen as wasted resources. What's more, there is no reduction in the amount of patching, upgrading and monitoring necessary as each VM feels like, and is handled like, a separate machine. The relative performance of the different databases is controlled by the hypervisor and the definitions of the virtual machines.

Pros: - Complete OS separation. - Allows differing OS versions in each machine. - Allows differing DB versions in each machine. - Allows separation of duties if different teams need to control each VM. - The virtual infrastructure can be used to provide basic high availability (HA) functionality. Cons: - Overhead associated with running the hypervisor on the hardware. - Overhead associated with running an entire OS in each VM. - Each OS has to be patched and monitored separately. - Overhead of multiple DBs running on a single physical server. - Each DB has to be patched and monitored separately. - Someone needs to take on the responsibility of learning about and maintaining the virtualization infrastructure. - Licensing and support of the virtualization infrastructure. Containers can be thought of as providing a "thinner" version of virtualization, giving some of the application separation, but with less overhead compared to virtualization. For example, on a single server we might have 10 containers, each with its own Oracle installation and database. All these containers will actually be based on a single shared operating system.

Pros: - Reduced overhead as only one OS is running on the physical hardware. - Reduced OS patching as the OS is shared between all the containers. Remember, there are libraries and binaries in the container that need updates, typically by creating a new container from an updated image. - Some separation, making each container "feel" like a separate installations. - With a separate Oracle installation in each container, each database could run at a different database version if required. - The container functionality can be used to provide basic high availability (HA) functionality. Cons: - Containers do not provide complete OS separation. Depending on configuration, you may get bleed-over effects, where one container can adversely affect the performance of another. - The lack of complete OS separation means there may be security implications where containers are concerned. - Operating system patches affect all containers. - Someone needs to take on the responsibility of learning about and maintaining the container infrastructure. - Overhead of multiple DBs running on a single physical server. - Each DB has to be patched and monitored separately. - Licensing and support of the container feature. Not all container solutions are supported to run Oracle products. In the multi-instance approach, we might have a single server running 10 separate databases, each with its own instance. Since there can be multiple Oracle installations on a single machine, these could be running at different database versions, but they would all be running on the same version of the operating system. This does not have the added overhead of the virtualization layer, but does ultimately have reduced flexibility as any change to the operating system affects all databases. The relative performance of the databases can be controlled based on their memory allocations and instance caging . Each database is still independent, so the total amount of database patching required is unchanged.

Pros: - Reduced overhead as only one OS is running. - Reduced OS patching as the OS is shared between all the instances. - Can share a single Oracle installation, or have a separate Oracle installation per database, allowing each database to run at a different database version if required. - No additional cost or knowledge needed to support a containers or virtualization. - Depending on the setup, patching and monitoring may be simplified. At a minimum, we may only need a single Oracle installation and a single Cloud Control agent on the server. Cons: - No OS separation. - The lack of complete OS separation means there may be security implications if you make silly mistakes. - Operating system patches affect all instances. - Overhead of multiple DBs running on a single physical server. - If you are using multiple Oracle installations, they will all have to be patched separately. If you are using a shared installation, all databases must be kept at the same version. - Does not provide any High Availability (HA) functionality directly, but Data Guard and Real Application Clusters (RAC) can provide this at an extra cost. Some database applications are built with very few dependencies outside the main schema owner. For example, the application server may connect to the database using a single database user and handle security internally or externally using LDAP. Provided the application does not make use of public database resources, like public database links or public synonyms, then it is possible to consider schema consolidation. If we consider 10 such schemas, each currently housed in their own database, these could be consolidated into a single database as separate schemas with no loss of functionality or reduction in security. Oracle Resource Manager can provide some control over CPU and I/O between these schemas, but there is no control over memory usage. Since the 10 databases have now been consolidated to 10 schemas in one database, there is even less flexibility, since all must be running on a single version of the database. There is a reduction in administration though, since there is now only a single database to patch.

Pros: - Reduced overhead as only one OS is running. - Reduced overhead as only one DB instance is running. - Only a single database installation is present. - No additional cost and knowledge needed to support a containers or virtualization. - Depending on the setup, patching and monitoring may be simplified. At a minimum, we may only need a single Oracle installation and a single Cloud Control agent on the server. Cons: - No OS separation, making it harder to prioritise performance of specific schemas. Resource Manager can not control memory usage. - The lack of complete OS separation means there may be security implications if you make silly mistakes. - Operating system patches affect all applications. - Database patches affect all applications. - Instance level changes affect all applications. - Does not provide any High Availability (HA) functionality directly, but Data Guard and Real Application Clusters (RAC) can provide this at an extra cost. - Database recovery and flashback have to be planned carefully as all top-level operations affect all schemas. This can be mitigated using tablespace point in time recovery (PITR). The multitenant architecture introduced in Oracle database 12c makes consolidating databases into a single instance significantly easier than attempting to do basic schema consolidation. Having said that, many of the pros and cons are closely aligned with schema consolidation, except that pluggable databases provide namespace separation, allowing each pluggable database to make use of public database links, public synonyms and matching database user names, without any name clashes with the other pluggable databases. Oracle Resource manager can prioritise resources between PDBs.

Pros: - Reduced overhead as only one OS is running. - Reduced overhead as only one DB instance is running. - Only a single database installation is present. - No additional cost or knowledge needed to support a containers or virtualization. - Depending on the setup, patching and monitoring may be simplified. At a minimum, we may only need a single Oracle installation and a single Cloud Control agent on the server. - Namespace separation, allowing you to have public database links, synonyms and even the same usernames in each PDB. Cons: There are several ways to run Oracle databases in the cloud, as discussed here . Depending on the cloud service you choose, some of the consolidation methods discussed here may be useful in reducing the total cost of your cloud environment. If you are running Oracle databases on a self managed virtual machine in the cloud, the following consolidation methods are still available to you. - Multi-Instance - Schema Consolidation - Multitenant Option If you are using a Database as a Service (DBaaS) offering, you will probably be limited to one of the following. - Schema Consolidation - Multitenant Option As you can see, there are lots of options, each with their own pros and cons. As I mentioned previously, you will undoubtedly use a variety of techniques. Using the company I currently work for as an example, at the time of writing, we use all of them with the exception of the paid multitenant option. - We have a heavy investment in VMware for our core infrastructure. All WebLogic, Tomcat and Apache servers run on Oracle Linux under VMware, as do the majority of our Oracle databases. All the Windows servers are VMware-based. So we are making heavy use of VMware for hardware consolidation, which includes database servers. - At the time of writing, we have one project that makes use of containers, although that will probably move to VMware in the future. - Some of our virtual machines host multiple Oracle instances, so we are combining VMware hardware consolidation and multi-instance database consolidation. - Some of our databases are used to house multiple applications using schema consolidation. In come cases, we have a combination of VMware hardware consolidation, multi-instance database consolidation and schema consolidation. In short, if someone tries to tell you there is one best solution to consolidation, they are either trying to sell you something, or they don't really know what they are talking about! For more information see: - A Cure for Virtual Insanity: A Vendor-Neutral Introduction to Virtualization Without the Hype - Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB) - Oracle Databases in the Cloud Hope this helps. Regards Tim...