A Guide to Why and How to Consolidate Databases.
By Divit Gupta
You may have recently noticed the press about Oracle’s partnership with Deutsche Bank, to modernize Deutsche Bank’s mission-critical databases and help them enable new financial products and services. A critical part of this modernization process is the consolidation of Oracle Databases with Exadata.
The point of this article is to provide you with a good understanding of what’s involved in database consolidation, and how to leverage Oracle’s Exadata systems to leverage database consolidation, including the motivations behind it, pitfalls to avoid, and the chief ways I see Oracle customers do it successfully.
Database consolidation basics
Database consolidation is fundamentally about database server consolidation. And it’s possible because server CPUs have many more cores than they used to.
The easy consolidation path is to virtualize each of your database servers and move these virtual machines to the new server.
Storage devices are getting bigger. Disk drives are bulking up with 10, 12, 14, 16 and 18TB drives, and 20TB models are getting ready for prime time, and SSDs are getting larger. Multiple databases can be consolidated by moving to a bigger and faster storage device
Consolidate Oracle databases with Exadata
An Oracle Exadata Database Machine, whether on premises or in the Oracle Cloud, is an engineered system purpose-built to provide optimal performance, availability, and manageability for Oracle databases. Its scalable architecture and advanced software capabilities make it ideally suited as a standard database platform for the consolidation of Oracle databases. Oracle Exadata incorporates the full range of Oracle’s Maximum Availability Architecture (MAA) best practices, which are combined with high availability architecture, enabling customers to achieve the highest levels of reliability possible in a consolidated database environment.
Oracle offers three deployment models, all of which use the same underlying Exadata technology:
• Exadata on premises
• Exadata Cloud@Customer
• Exadata Cloud Services (including Oracle Autonomous Database)
Each of these deployment models brings the same benefits in terms of managing costs, simplifying operations, ensuring security, meeting availability goals, and providing the necessary level of database performance.
Migrating systems to Oracle Exadata Cloud@Customer enables customers to consolidate existing critical applications on a single platform while complying with their data residency requirements and significantly reducing operational costs.
Oracle’s service can run in customer’s current data centers as well as in future cloud co-location sites, minimizing latency while supporting the shift of applications to the cloud.
Deployment in the Oracle Cloud and via Cloud@Customer brings additional capabilities that further simplify operations through Oracle’s cloud tooling and cloud service offerings. The Oracle Cloud also enhances security further through use of mandatory data encryption, and cloud network security.
Security
Security is improved by minimizing the number of vulnerabilities an organization must protect against. Consolidation helps companies standardize on fewer technologies, which in turn provides commonality across systems and more focus on employing the best practices needed to secure those technologies. It also allows organizations to deploy security fixes more quickly, since there are fewer systems to manage.
Consolidate with virtual machines, containers & multitenant
Multiple databases per physical server/virtual machine
On systems that use virtual machines, Oracle recommends a limit of 2-4 virtual machines per physical server at most. Virtual machines can be used to isolate groups of databases according to environment (e.g. isolating DEV from TEST) or isolate databases to meet governance requirements (such as PCI data from non-PCI data). Virtual machines can also be used to isolate a subset of databases to reduce the impact of upgrades and other O/S level system maintenance tasks. Splitting the physical hardware into several virtual machines provides hardware consolidation, not database-specific consolidation.
Containers can be thought of as providing a “thinner” version of virtualization, giving some of the application separation which allows customers to perform maintenance tasks on one application without affecting the others, 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.
Oracle Multitenant
Oracle’s Multitenant architecture consists of a container database and multiple pluggable databases (PDBs). Most of the working parts of a database are already present in the root container database. Therefore, creating a new PDB is a quick and easy task.
When a completely new PDB is created, the PDB is created as a copy of a seed PDB, so it only takes as long to create as the files take to copy. When a user connects to a plugged database, the database environment looks identical to a traditional database. The Multitenant architecture makes consolidating databases into a single instance significantly easier than attempting to do basic schema consolidation.
Many of the pros and cons are closely aligned with schema consolidation, except that PDBs provide namespace separation, allowing each one to make use of public database links, public synonyms and matching database usernames, without any name clashes with the other PDBs.
Schema consolidation
The methods discussed below can be used separately or in combination to consolidate databases into a smaller set of physical machines or virtual machines.
Schema consolidation is an approach that was used prior to the advent of Oracle Multitenant to combine schemas from multiple databases into a single database, making more efficient use of system resources and enabling a much higher consolidation density. While schema consolidation makes more efficient of computing resources, this approach primarily requires application changes as there is change in the schema structure and also because it does not provide the necessary degree of isolation that Oracle Multitenant provides between applications and does not facilitate isolation for maintenance operations such as upgrades.
Oracle Multitenant has become the preferred solution for high density consolidation because it addresses the complexities found with schema consolidation. Schema consolidation is a useful approach in some circumstances, such as combining multiple databases belonging to a single application. Some database applications are built with very few dependencies outside the main schema owner. 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. There is a reduction in administration though when we pick Multitenant database over multiple separate databases, since there is only a single database to patch.
Consolidate your production databases by migrate to Oracle Cloud using Golden Gate
OCI Golden Gate is the first cloud service of its kind to provide an elastic, pay-per-use solution for general purpose database replication. The OCI Golden Gate service uniquely delivers highly automated data integration, change data capture, and real-time transaction replication.
Once the Oracle cloud database has been set up, Golden Gate can be used to replicate back to the on-premises environment, providing the ability to fail back to it should anything unforeseen occur with the new environment
Database consolidation tools
My first pre-consolidation step would be to check for inactive databases, underutilized database servers, and backup and recovery gaps in your IT infrastructure. You can use the information you return to efficiently detect candidates for consolidation, reducing licensing and operational costs, and close maintenance gaps for critical data protection and peace of mind. The Database Consolidation Workbench in Enterprise Manager (EM) can help with this.
The Database Consolidation Workbench is an end-to-end consolidation solution if you are using EM and need to consolidate Oracle databases. The flow includes three steps: Planning, Migrating, and Validating.
Planning gives you consolidation advice by identifying candidate databases for the designated consolidation platform using Automatic Workload Repository (AWR) data. AWR is a tool that collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.
Enterprise Manager (EM) helps implement the consolidation plan by migrating databases to new consolidation platform using EM’s provisioning features, while validating consolidation plans with Real Application Testing by running test workloads on consolidated databases.
The Database Consolidation Advisor in EM helps you:
• Estimate the resource utilization under various consolidation scenarios from ultra conservative to aggressive
• Identify the type of consolidation that is supported: Database-to-Database (Multitenant) consolidation, Database-to-Server (Engineered systems) consolidation, or Oracle Public Cloud consolidation
• Identify conflicts based on workload characteristics, Exadata suitability – Storage/Platform advice: impact of compression on I/O & storage, impact of I/O offloading & Flash Cache
The Database Consolidation Advisor recommends an optimal consolidation strategy based on historical workload information; however, actual database consolidation involves potentially many major changes and therefore can impact business SLAs and system stability.
A SQL tuning set (STS) is a database object that you can use as input to tuning tools. An STS includes a set of SQL statements and associated execution context, such as user schema, application module name and action, list of bind values, and the environment for SQL compilation of the cursor.
The Performance Analyzer (SPA) component of Real Application Testing is a great way to assess the success of the consolidation effort. It’s used to validate SQL execution plans for the given workload on the consolidated platform. It works by capturing interesting SQL workloads into an STS on each of the source databases to be consolidated onto the new platform and using the SPA to assess the query performance. The same STS can also be used on Exadata for simulation to assess the success of the consolidation effort.
Summary
Consolidating databases using Exadata’s underlying technology is a great way to modernize the database infrastructure, it nicely complements with the High Availability and, Maximum Availability Architecture.
Database consolidation tools such as OCI GoldenGate, STS, SPA, EM are very effective for consolidating databases of different flavors and version.
We hope you enjoy using Oracle’s Exadata and OCI GoldenGate technology.
Read more about Oracle’s GoldenGate tool that could help you consolidate databases in real time onto Oracle Cloud
docs.oracle.com/en/cloud/paas/goldengate-service/tutorials.html.
https://www.oracle.com/a/tech/docs/maa-database-migration-to-oci-with-a-goldengate-hub.pdf