Oracle Enterprise Manager Database Consolidation Workbench

Oracle Enterprise Manager Database Consolidation Workbench —Secret to Successful Consolidation Prabhaker Gongloor (GP) Senior Director of Product Management, Oracle Mikael Fries Director Product Management, Oracle Copyright 2015, Oracle and/or its affiliates. All rights reserved.

Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Copyright 2015, Oracle and/or its affiliates. All rights reserved. 2

Program Agenda 1 Motivation & Consolidation Considerations 2 Database Consolidation Workbench 3 Database Consolidation Advisor 4 Consolidation: Making it Happen! 5 Consolidation: Validation 6 Demo and Q/A Copyright 2015, Oracle and/or its affiliates. All rights reserved. 3

Program Agenda Demogrounds: SLD-023: End-to-End Database Performance Management 1 Motivation & Consolidation Considerations 2 Database Consolidation Workbench 3 Database Consolidation Advisor 4 Consolidation: Making it Happen! 5 Consolidation: Validation 6 Demo and Q/A Copyright 2015, Oracle and/or its affiliates. All rights reserved. 4

Journey to the Cloud: Stages of Technology Adoption Maximize Server Utilization, Streamline Manageability and Drive Down Capital Expenses Maximize Performance with Advanced Automation Reduce Operating Expenses through Self-service Administration Consolidation Quality of Service 2015,and/or Oracle and/or its affiliates. All rights reserved. CopyrightCopyright 2015, Oracle its affiliates. All rights reserved. Cloud Services

Database Consolidation: Considerations – What database consolidation strategy to use? – Can workloads co-exist together for the chosen strategy, what conflicts exist? – Can the system handle peak workloads while maintaining performance? – How to perform migration with reduced downtime? – How to test the chosen consolidation strategy to minimize risk? Copyright 2015, Oracle and/or its affiliates. All rights reserved. 7

Consolidation Alternatives Virtual Machines Share servers Dedicated DBs Dedicated Schema(s) Share servers and OS Share servers, OS and database Pluggable DBs Share servers, OS and database Increasing Consolidation Copyright 2015, Oracle and/or its affiliates. All rights reserved. 8

Consolidation Architectural Choices Virtual Machines Dedicated DB Dedicated Schemas Pluggable DB Consolidation Density Low - Moderate High Highest Highest Management Very complex (VM Sprawl) Easy Excellent Good Low Good Easy Easy Difficult Easy All Home grown; requires app validation All but have to be certified for Database 12c Isolation Implementation & Onboarding Application Suitability Some (workload dependent) Easy to Involved (based on required resource isolation) Copyright 2015, Oracle and/or its affiliates. All rights reserved. Easy 9

Consolidation Tools Available Today Enterprise Manager Consolidation Planner XLS spreadsheet (seriously) Host Consolidation Planner - plan & consolidate multiple hosts to fewer machines – Physical Virtual, Physical Physical server support – Exadata, OVM, Oracle Compute Cloud shapes (OCPUs, OC3-7, OC1M-OC5M) Consolidation Scenario Report to pick the best strategy – Consolidation ratio & destination server utilization – Source to Target server mapping – Confidence of meeting requirements based on historical samples – Manually mapped consolidation constraint violations and exceptions Copyright 2015, Oracle and/or its affiliates. All rights reserved. 10

Program Agenda 1 Motivation & Consolidation Considerations 2 Database Consolidation Workbench 3 Database Consolidation Advisor 4 Consolidation: Making it Happen! 5 Consolidation: Validation 6 Demo and Q/A Copyright 2015, Oracle and/or its affiliates. All rights reserved. 11

Database Consolidation Workbench End-to-end Consolidation Solution Comprehensive end-to-end solution for managing database consolidation Provides a risk-free and accurate approach to consolidation by eliminating guess work, human errors – Analysis based on historical workload (DB, Host metrics) – Automation in all phases of consolidation from planning to deployment Provides a flexible approach for various customer scenarios – 10.2 DB versions and higher support – Support for consolidation to Oracle Private/Public Cloud or Exadata – HA options to minimize downtime depending on source/destination DB platform and versions Copyright 2015, Oracle and/or its affiliates. All rights reserved. 12

Copyright 2015, Oracle and/or its affiliates. All rights reserved. 13

Database Consolidation Workbench End-to-end Consolidation Solution Plan – Gives consolidation advice by identifying candidate databases for the designated consolidation platform using AWR data Migrate – Implements consolidation plan by migrating databases to new consolidation platform using EM’s provisioning features Validate – Validates consolidation plan with Real Application Testing (SPA) by running test workloads on consolidated databases Copyright 2015, Oracle and/or its affiliates. All rights reserved. 14

Program Agenda 1 Motivation & Consolidation Considerations 2 Database Consolidation Workbench 3 Database Consolidation Advisor 4 Consolidation: Making it Happen! 5 Consolidation: Validation 6 Demo and Q/A Copyright 2015, Oracle and/or its affiliates. All rights reserved. 15

Database Consolidation Advisor Key Features Estimates resource utilization under various consolidation scenarios – Ultra Conservative (peak maximum) Aggressive (average) Supported consolidation modes – Database-to-Database (Multitenant) consolidation – Database-to-Server (Server, e.g. Exadata) consolidation – Oracle Public Cloud consolidation Consolidation optimization advice – Identifies conflicts based on workload characteristics, Exadata suitability – Storage/Platform advice: impact of compression on I/O & storage, impact of I/O offloading & Flash Cache Copyright 2015, Oracle and/or its affiliates. All rights reserved. 16

Database Consolidation Advisor Consolidation Advice: Performance Bottleneck Analysis ADDM like rules engine to analyze AWR data from source databases Data driven rules that read inputs from AWR / EM Metrics / AWR Warehouse Two pass rules analysis generates findings which identify bottlenecks – First pass on source databases – Second pass when consolidating source databases on a single destination Rules Analysis can impact mapping of source databases to destination Currently there are 16 rules on top of 27 different metrics Rules can be easily extended & can be made self updatable Copyright 2015, Oracle and/or its affiliates. All rights reserved. 17

Database Consolidation Advisor: Findings Copyright 2015, Oracle and/or its affiliates. All rights reserved. 18

Database Consolidation Advisor Consolidation Advice: Performance Bottleneck Analysis Findings and Recommendations (some examples) – DATABASE CPU BOUND Detected potentially CPU-bound source database with average active session count of 21.8 that exceeds its core count. Consider tuning this database to eliminate any CPU bottleneck prior to consolidation planning. Its current workload measurements may not be true indicators of its resource requirements. – OLTP IOPS RECOMMEND EXADATA The I/O Requests/Second value of 300 and on-line transaction processing workload indicate that this database will benefit from consolidation to Oracle Exadata Database Machines to take advantage of Smart Flash Cache. Findings can be justified by showing performance charts & trends using AWR viewer Copyright 2015, Oracle and/or its affiliates. All rights reserved. 19

Copyright 2015, Oracle and/or its affiliates. All rights reserved. 20

Consolidation Workbench: Source Server Heatmap Copyright 2015, Oracle and/or its affiliates. All rights reserved. 21

Consolidation Workbench: Source Server Resource Usage Copyright 2015, Oracle and/or its affiliates. All rights reserved. 22

Database Consolidation Advisor Storage and I/O Consolidation Benefits Estimation Compression Advisor job – Can be deployed from EM & runs using DB Scheduler during system maintenance windows – Generates compression estimates at segment and Tablespace level – Scans top-N segments at a time incrementally – Estimates are based on sampling blocks in random and compressing them Compression Results are stored in DBSNMP and collected by a Metric Results are shown in Consolidation Advisor and Compression Home Copyright 2015, Oracle and/or its affiliates. All rights reserved. 23

Database Consolidation Advisor Exadata: Storage and I/O Consolidation Benefits Smart Scans – Estimate smart scans using SPA Trials Exadata Simulation mode – Uses an optimizer parameter to run all plans as if system was Exadata – Shows predicted impact of Exadata on IO Interconnect bytes Flash Cache – Calculate flash cache benefit from total small reads and writes – Post EM 13.1: Flash Cache recommendation for OLTP workloads, identify objects to pin in memory based on AWR I/O intensity report Compression – Calculate storage reduction from enabling compression, & post EM 13.1 IO savings Copyright 2015, Oracle and/or its affiliates. All rights reserved. 24

Database Consolidation Advisor: Storage Copyright 2015, Oracle and/or its affiliates. All rights reserved. 25

Consolidation Workbench: SPA Simulation Copyright 2015, Oracle and/or its affiliates. All rights reserved. 26

Consolidation Workbench: Destination Server Mapping Copyright 2015, Oracle and/or its affiliates. All rights reserved. 27

Program Agenda 1 Motivation & Consolidation Considerations 2 Database Consolidation Workbench 3 Database Consolidation Advisor 4 Consolidation: Making it Happen! 5 Consolidation: Validation 6 Demo and Q/A Copyright 2015, Oracle and/or its affiliates. All rights reserved. 28

Migrate Make it so: Database Migration to Consolidated Environment Multiple migration methods based on source and target database attributes (endian-ness, versions) – Offline: RMAN clone, Data Pump – Online: Data Guard standby , migrate as PDB with minimum downtime Transportable TTS is used to minimize downtime in "Migrate as PDB". Any source database non CDB version 11203, 11204 and 12.x can be moved to 12.x as PDB Supports 10g(10.2) to 12c (12.1) databases SI & RAC, Multitenant and non-CDB modes Added to EM’s provisioning DP library Requires O H to be pre-provisioned Copyright 2015, Oracle and/or its affiliates. All rights reserved. 29

DB Consolidation Migration (Make it so) Mode Source DB D2S 10.2 and up D2S 10.2 and up D2D D2D/ D2S 11.2.0.3 10.2 and up Solution Data Guard RMAN Full TTS Datapump Destination Minimal Downtime DB Type Platform/version Yes RAC/SI Same No RAC/SI Same Move DB Steps Uptime 1) Create Standby 2) Convert to RAC Downtime 3) Switchover standby 1) Create clone 2) Convert to RAC Yes RAC/SI Cross Platform and Endian DB 12.1 1) RMAN Level 0 Clone 2) Level 1 incremental No RAC/SI/ PDB Cross Platform and Endian & Same or higher DB Version 1) Create DB/PDB if required Copyright 2015, Oracle and/or its affiliates. All rights reserved. 3) Final Level 1 incremental & Metadata Export / Import with TTS 2) Export schema from source 3) Import 30

Make it so: Migration to Consolidated Platform XML-based emcli script generated to implement the recommended consolidation plan Script can be edited to override – Parameters – Credentials – Other fields Submitted as EM deployment procedures and can be tracked for completion Validation of SQL execution plans for the specified workload (STS) submitted as part of deployment script Copyright 2015, Oracle and/or its affiliates. All rights reserved. 31