Database Lifecycle Management with Enterprise Manager 12c

2. Database Lifecycle Management with Enterprise Manager 12c ShaileshDwivedi Director Product Management, Oracle

3. Description Session: CON1454 Title: Database Lifecycle Management with Enterprise Manager 12c Description: Enterprise Manager Database Lifecycle Management features automate day-to-day tasks for DBAs across hundreds or thousands of databases. In this session you will learn about customers who run Enterprise Manager 12c in production and use features like automated discovery, provisioning, patching, change management and compliance.

4. Safe Harbor 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.

5. Lifecycle Management Overview • Verizon Wireless Story • Qualcomm Story • Q&A ProgramAgenda This is just for preparation purposes

6. Top 10 Database Management Challenges IOUG Survey (2011) Track configurations for compliance 45% 26% Keep patches current Promote changes from development or test to production 42% 21% Diagnose performance Handle growing security threats Provision test or development systems 35% 21% Identify resource-intensive SQL statements in real-time Manage rapid growth of data and systems 33% 17% Manage datacenter growth with level resources Perform repetitive tasks and processes 33% 13%

7. Top 10 Database Management Challenges Lifecycle Management Challenges Trackconfigurations for compliance Keep patches current Promote changes from development or test to production Diagnose performance Handle growing security threats Provisiontest or development systems Identify resource-intensive SQL statements in real-time Manage rapid growth of data and systems Manage datacenter growth with level resources Perform repetitive tasks and processes

8. Database Lifecycle Management Discovery & Initial Provisioning Ongoing Change Management Continuous Configuration & Compliance Management 1 2 3 PROVISION PATCH TRACK COMPLIANCE PROMOTE, DEPLOY MANAGE GROWTH SECURE & AUDIT REPEAT, AUTOMATE REPEAT, AUTOMATE REPEAT, AUTOMATE

9. Database Lifecycle Management Discovery & Provisioning Patching & Change Management Configuration & Compliance Management 1 2 3 End-to-end management of patches, upgrades, and schema changes Discover assets and provision software on them Track inventory, configuration drifts and compliance

10. Discovery Lifecycle Management 1 Know What you have • Process without Enterprise Manager • Use stand alone network discovery tool • Discover manually using host names from name servers • Challenges and Problems • Cumbersome process: • Weed out non-critical targets • Separate process to upload discovered targets to monitoring tools • Risk of missing out: • New Databases go unmanaged posing potential compliance risk

11. Automated Discovery Know What you have Enterprise Manager 12c Solutions Challenges and Problems Agent based IP scan for known software and ports 1 Cumbersome process Agent-less and Agent-based automated discovery Risk of missing out

12. Agent Based IP Scan for known Software and Ports Auto scan between IP ranges from Agents Scans for known software signature, users can extend it further.

13. Agent Less and Agent based Automated Discovery Categorization of targets as Host and Non-Host Ignores monitored targets

14. Provisioning Databases Lifecycle Management 1 Provision test, development or production systems • Process without Enterprise Manager • Manual or scripts based Installation • Silent Mode installation with response/template files • Challenges and Problems • Long and Error prone: • Long process for complex configurations such as RAC • Most installations are not pre-patched • Lack of Standardization: • Variations in deployments due to individual DBA preferences • Scripts need frequent modification to support new versions “Standardization is also very important—not just of technology and processes, but also standardization of methodology” said Surren Partabh, CTO, core technologies, BT Operate

15. Provisioning Databases Provision test, development or production systems Enterprise Manager 12c Solutions Automated Mass Deployment Challenges and Problems 1 Standardization using Provisioning Profiles Long and Error Prone process Lack of Standardization Role and Access Separation

16. Automated Mass Deployment of Databases Deployment Procedures to automate of Database software deployments

17. Standardization using Provisioning Profiles Capture Gold Image and Configuration properties from existing installation pre-patched, approved installations to deploy ready to use standardized software

18. Role and Access separation between Designers and Operators Operator View As Designer specify and lock input values Minimize entry required for Operators Reduces errors and configuration variations Designer View Designer View

19. Patching Lifecycle Management 2 Maintain Patch Levels • Process without Enterprise Manager • Manual installations either directly or using scripts • Multiple people involved across multiple hours to cover the environment • Challenges or Problems • Predictability: • Unable to identify problems and patch conflicts before the patching process. • Downtime Management: • Hard to manage downtime windows across different teams for doing maintenance • Scalability & Tracking: • Applying multiple patches to large number of databases • Its hard to keep track of the inventory of databases patched and unpatched

20. Patching 2 Maintain Patch Levels Enterprise Manager 12c Solutions Challenges and Problems Automated Mass Deployment Predictability Minimize Downtime, identify issues with pre requisite check Downtime Management Patch Templates and Compliance Standards Scalability

21. Identify Oracle Recommended Patches Proactive patch recommendations for Oracle recommended patches (including CPU,PSU..). Supports: Online Mode ( Direct connectivity to My Oracle Support) Offline Mode (No connectivity to My Oracle Support), where user can upload the catalog to generate the recommendations. Rich Information about patches like bugs fixed, associated KM articles, number of downloads, trends.

22. Mass Deploy to Avoid Time Constraints Simplified, Wizard based approach Users can apply multiple patches to multiple targets

23. Preflight checks using Analyze Mode Automated Patch conflict resolution process Comprehensive pre-flight checks

24. Reduce Downtime, Manage Maintenance Schedules Better • Patch “Out of Place” to: • Reduce Downtime • Manage maintenance windows flexibly • Switchback to old configuration in case of issues

25. Patch Rollout Automation and Tracking Create Patch Templates and Compliance Standard with Patches from Patch Plans Administer and Monitor Patch Rollouts using Patch Templates and Compliance Standards

26. Automated Major Version Upgrades • Plan for Upgrades using Upgrade Planner • Download the Software and Patch required to Software Library • Automate Upgrades in Mass scale

27. Change Management Lifecycle Management 2 Applying Database Schema changes from Dev to Prod • Process without Enterprise Manager: • Use SQL Scripts • Manual operation to validate and propagate changes • Challenges and Problems: • Lack of Preview: • Unable to do previews and edit changes before apply without reworking completely on it • Scalability: • Unable to rollout changes across multiple databases

28. Change Management 2 Applying Database Schema changes from Dev to Prod Enterprise Manager 12c Solutions Challenges and Problems Data Comparison and Baselining Manual Validate and Propagate Planned Changes Lack of Preview Scalability

29. Schema and Data Comparison • Baselines : • Capture database and schema definitions • Baseline Versioning • History of changes • Comparison • Baseline with database • Database with database • Schema with schema • Data Comparison • Automated Propagation • Propagate desired changes – Change Plans

30. Schema and Data Comparison • Baselines : • Capture database and schema definitions • Baseline Versioning • History of changes • Comparison • Baseline with database • Database with database • Schema with schema • Data Comparison • Automated Propagation • Propagate desired changes – Change Plans

31. Propagate Planned Changes • Validate planned changes to identify conflicts or previously applied changes. • Preview and edit validated changes before applying. • Generate SQL script of final set of validated changes. • Apply validated planned changes

32. Configuration Management Lifecycle Management 3 Ensure Consistent Configurations • Process without Enterprise Manager: • Maintain the details in a spread sheet • Manually compare configurations by uploading into a database • Challenges and Problems: • Time Consuming: • Time consuming and error prone configuration comparisons • Very Reactive: • Process is reactive and cannot automatically capture configuration drift over time • Scalability: • Comparison is usually not a blanket one, it has to be done in the context of an application

33. Configuration Management 3 Ensure Consistent Configurations Enterprise Manager 12c Solutions Challenges and Problems Identify and Track Assets Time Consuming Compare Assets and Configurations Totally Reactive Track and Remediate Drifts Scalability

34. Identify and Track Assets Dashboard of Inventory and Usage Details of Assets Use Trend information for planning

35. Configuration Comparison Templates • Oracle Pre-Configured Templates • Customize Templates to be Use Case specific ( Gold, Baseline ) • Configure property differences for which to be notified • Ability to Ignore Certain Differences

36. Track and Remediate Drifts from Standards Identify drifts across entire environment stack. Remediate using corrective actions.

37. Compliance Management Lifecycle Management 3 Ensure all the Databases are compliant • Process without Enterprise Manager: • Lengthy manual audits of configuration, repeated each audit period • Challenges and Problems: • High Cost: • Enormous resource consumption, high cost of audits • High Risk Factor: • Risk to company in case of legislative standards

38. Compliance Management 3 Ensure all the Databases are compliant Enterprise Manager 12c Solutions Challenges and Problems Out of the Box Compliance Library High Cost Monitor and Manage Compliance High Risk Adhere to Compliance and Generate Reports

39. Dashboards to Monitor and Manage Compliance

40. Adhere to Compliance and generate reports

41. Engineered Systems Management Exadata Management ? = Baseline Gold Image Current Apply recommended patches in zero downtime and Out of Place Identify drifts across entire system

42. Database Patching with Enterprise Manager 12c

43. Agenda • VZW DBA Challenges • Enterprise Manger 12c Environment • Patching Use case • Features and Plans • Benefits

44. VZW DBA’s Challenges • Patching / Upgrades • Regulations require us to patch Quarterly • Large number of databases with minimal maintenance time • Engaging System Administrator’s into the process • Manageability • Inventory tracking • Deploying changes to non-prod databases • Compliance Management & Management Reports

45. VZW Business Requirement • Mandatory to apply PSU to over 520 Databases 2 Time a year • 400 non-production and over 120 Production Databases. • DBAs patch for four months a year ( Two 90 day Patch windows) • During the period DBA‘s work a lot of extra hours, especially night and weekend hours. • Engaging System Administrator’s into the process. Additional teams are involved with the patching process. • Team Leads, Project Manages, Application Testers, and System Administrators. • All need to work extra hours during the patching windows.

46. Why Oracle Enterprise Manager 12c? Patch Management (Patching / Upgrades) Apply Quarterly GI PSU patches to RAC environments Patch both Grid Infrastructure and RAC DBs Patching Single Instance Databases Change Management(Manageability) Track changes across non-production and production environments by Application Team. Coverage: RAC DBs and Single Instance DBs across multiple platforms ~30 RAC Clusters (excluding Single Instances) within just our team

47. Enterprise Manager 12c Landscape • Test Environment EM12c - Installed and Running.  Version: 12.1.0.1 (Includes Bundle Patch 1) • Platform : Linux x86-64 • No. of OMSs: 1 • Repository DB: 11.2.0.3 Single Instance Database • Production Environment :  In Progress  Version: 12.1.0.1       • Platform: Solaris 10 on SPARC -T4 (8 CPU each, 32g memory) • No. of OMSs: 2Repository: 2 Node RAC Database

48. Pilot Use Case: Applying GI PSU on RAC • Scenario: • Round 1: Applied Jan PSU 2012 on RACs. • Round 2: Applied Jul PSU 2012 on RACs. • 2 Node RAC with 15 - 11.2.0.2 RAC Databases on Solaris • Approach: • Identified the patch and the impacted targets from Patch recommendations • Created Patch Plans with the Patches and Targets • Ran pre-requisite checks via Analyze Mode ahead of time • Negotiated with Applications Team for non-Peak utility period • Proceeded to Deploy the patch in Rolling mode • Important Pre-conditions: • All Instances of RAC to be up. • All RAC DBs identified as targets in EM

49. Pilot Use Case: Results • Successfully patched 15 databases across 2 Node RAC • Includes patching both GI and RAC DB OHs with SQL application • Patching Time – Manual (6 to 8 hours) Vs EM (1:48:58) • Attended time with EM can just be minutes (1st time effort 15mins) • Via EM the SQL was applied in parallel • Additional Things: • Require to setup EM with the latest patches ( …) • Created Named credentials, for ROOT we had temporary passwords from SA’s (Area to work on further) • As a pre-condition all Instances needs to up: Helped in figuring out issues ahead of time.

50. Step 1/5: Identify Recommended Patches Comprehensive Patch Recommendations: Identify PSU recommendations directly