1 northerntrust.com © 2015 northern trust corporation kevin hacker, manager database engineering,...
Post on 17-Jan-2016
219 Views
Preview:
TRANSCRIPT
1 northerntrust.com© 2015 Northern Trust Corporation
Kevin Hacker, Manager Database Engineering, The Northern Trust Company
Oracle OpenWorld 2015
Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
N O R T H E R N T R U S T
2 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
About Northern Trust
Chicago-based Global leader in delivering innovative investment management, asset and fund administration, fiduciary and banking solutions to corporations, institutions and affluent individuals.
Established in 1889
Over 20 international locations:
USA, Europe, Asia, Canada, Middle East, Africa, Australia & New Zealand
As of June 30, 2015, Northern Trust Corporation had:
$120 billion in banking assets $6.2 trillion in assets under custody $946 billion in assets under management
16,000+ employees
3 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Industry Awards
Recent Distinctions
• Best Banks in AmericaForbes, 2015
• World’s Most Admired Companies Fortune Magazine, 2015 – Ninth Consecutive Year
• Best Private Bank in U.S.Financial Times Group, October 2014 – Sixth Consecutive Year
• A World's Most Ethical CompanyEthisphere Institute, 2015
• Best Places to WorkPension & Investments, 2014- Second Consecutive Year
Technology
• Best Infrastructure Initiative-American Financial Technology AwardWaters Magazine, 2014
• Mobile Marketing Excellence in Wealth ManagementDigital Marketing of Financial Services, 2014
• Top 500 Innovators in IT Information Week, 1997 - 2013
4 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
About Myself
Kevin HackerManager, Database EngineeringEmail: kh49@ntrs.com
25 Years experience as DBA (DB2, SQL Server, Sybase, Oracle)
13 Years experience working in Oracle technologies
Worked at Northern Trust since 2001
Northern Trust Manager Database Engineering since 2014
Master’s degree in Mathematics from Purdue University
Bachelor of Arts in Mathematics and Computer Science from University of Indianapolis
5 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
About Northern Trust Database Organization
Database Organization
Engineering and Projects
Engineering Projects
DB2(and IMS)
Production Health
Operations and Support
5 DBAs 1 Consultant
3 DBAs8 Consultants
7 DBAs 3 DBAs 3 DBAs24 Offshore Consultants
Engineering Operations
Platforms: Oracle Oracle RAC
Exadata Sybase
SQL Server MySQL Hadoop
6 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Northern Trust Current Database Environment
SQL Server: 1,001
Oracle: 1,161
Sybase: 150
Oracle Release
Operating System
Total
9i Solaris 8 7
Solaris 10 58
10g Solaris 8 8
Solaris 10 238
11g Solaris 10 30
Linux REL 5 726
11g RAC Exadata OEL 6 24
12c Containers
Exadata OEL 6 39
DBA Employees (Oracle): 10DBA Consultants:(Oracle): 23
7 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Northern Trust Timeline with Oracle
2002• Oracle selected as standard distributed database platform• One 3-node prod Veritas Cluster housing Multiple Oracle instances (Schema Sharing)• Oracle 9i
2003 – 2008• Added a few Solaris hosts: 15 to 25 Oracle instances each (Lots of Schema Sharing)• Oracle 9i to 10g• No zoning or caging• Performance issues begin to cause outages (one bad query affects all applications)
2008 – 2009• Mass Isolation effort begins• Move each existing schema to its own Oracle database on an isolated Solaris Zone• Resources capped at zone level• Each zone: own OS, one set of Oracle binaries, one database, and one application
2010 – 2015• Oracle 11g on VMware• VM guest: One set of Oracle binaries, one database, and one application• Sprawl of 1000s of Database VMs
2015 - *** Consolidation and Migration to Oracle 12c Multitenant ***
PERFORMANCE
MANAGEABI L I TY
8 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Northern Trust Challenges
Lack of Standardization on Database Platforms• Solaris 8 and 10• Red Hat Enterprise Linux on VMware• Custom built RAC on Red Hat Enterprise Linux with HP blades• Exadata on Oracle Linux
Lack of Standardization on Database Software• Oracle 9i, 10g, and 11g• Different licensing options for RAC, Partitioning, Advanced Compression, Diagnostics and Tuning
Lack of Standardization on Patching• OS Team to patch Solaris and Linux OS on physical, VM guests, and home-grown RAC nodes
• DBA Teams to patch databases (multiple flavors)
• VMware Team to patch ESX Clusters
• Oracle ACS to patch Exadata Clusters (Storage Cells, OS, and Databases)
Lack of Standardization on DR Solution• Hitachi Storage Replication for most VM guests and physical servers• Oracle Data Guard for Oracle RAC, Exadata, and some critical VM guests
9 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Northern Trust Challenges
Lack of Standardization Consequences
• High Effort of Management• Virtualization led to copy of OS and database binaries for every database• One database per application (no schema sharing)• One VM guest per database (no OS sharing)• Thousands of database environments to patch• Multiple DR procedures to follow
• Increasing Pressure to Deliver Databases Faster
• Long Turnaround Time to Provision Database: 2 to 3 weeks for VM - Longer for RAC
• Multiple teams required to provision VM database • Storage Team to provision storage• VMware Team to provision VM Guest • Platform Team to provision OS• DBA Standard Build Team to provision database software and database
• Oracle RAC databases (Highly critical apps) very complicated to build• No automated build processes• 200+ page document to manually build RAC database and setup Data Guard• Manual process is error prone
10 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Northern Trust Challenges
The Solution: A Standardized Foundation with Consolidation
• Standardize the Platform• Exadata on Oracle Linux
• Standardize the Software• Oracle 12c with the Multitenant option• Standard software option: RAC, Partitioning, Advanced Compression, Active Data Guard,
Diagnostics and Tuning
• Standardize the Patching • Engage Oracle Platinum Support
• Standardize the DR Solution• Oracle Data Guard Replication
• Standardize the Offerings• Develop automated build scripts to lay groundwork for DBaaS• Develop service catalog with silver, gold and platinum offerings
11 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Northern Trust 12c Infrastructure Project
Thus was born …
Oracle 12C
The Oracle 12c Multitenant ProjectDate of Conception: Spring, 2014Delivery Date (First Live Application): September 17, 2015Date of Maturity: TBD
12 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12C Infrastructure Project
Build Infrastructure for Oracle 12C
Create and Design Oracle 12C Standards, Infrastructure, and Project Procedures
Consolidate Databases using Oracle 12C Multitenant Strategy (CDBs and PDBs)
Standardize on one Platform (Exadata – ULA)
Reduce Deployment Time for Databases
Provide Different Tiering Options (Silver, Gold, and Platinum)
Develop Patching Strategy
Position Oracle 12C for “Database as a Service” Offering
Goals
13 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12C Infrastructure Project
• Storage Savings – Shared Binaries and OS
• Less Environments to Patch – Platinum Agreement
• More Efficient Use of Resources – Fewer Background Processes
• High Availability – All UAT and Prod will be RAC Databases
• Granular Failover for DR (Data Guard) – Container Level
• Fewer Databases to Maintain – Backups at Container Level
• Quick Deployment – One Team Controls Everything
• Consistent Builds – Automated Deployment Procedures
• High Performing Hardware – Exadata
• Multiple Tiers Allow for Different Service Levels
Benefits
14 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12C Infrastructure Project
• New Release of Oracle (12.1.0.2)• Starting with Less Critical Apps (Silver)
• 12.2 Not Scheduled for Release until 2016
• Few Companies using Multitenant• Reference Calls (Pulte Homes and Sherwin Williams)• Working Closely with Oracle (SCP Agreement)
• Potential Performance Impact (Sharing Containers and Hosts)• Instance Caging• Resource Manager (CDB and PDB Levels)• IO Resource Manager• Quick Ability to Unplug/Plug/Clone to another Container
• More Impact for Planned/Unplanned Outage• Data Guard for Switchover• Rolling Patches for Gold/Platinum Environment
RisksMitigation
Mitigation
Mitigation
Mitigation
15 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Silver
Gold
Platinum
Oracle 12C Tiers
Silver Gold Platinum
Oracle 12C Tiering Strategy
Non-Critical Databases Few Densely Packed Containers (1 CDB to many PDBs) Non-Rolling Patching of Exadata Cluster Oversubscription
Business Critical Applications (Key Applications) Some Multitenant Databases (Shared Containers for same
Business Unit) Rolling Patching of Exadata Cluster Little to No Oversubscription
Mission Critical, Revenue Generating, or Customer-Facing Applications
No Multitenant Databases (CDB’s can have only 1 PDB) Rolling Patching of Exadata No Oversubscription
Tier Definitions and Characteristics
16 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12C Tiering Strategy
Sample Application Dimensions and Exadata Tier Assignments
Exadata Tiers
Application Dimensions Silver Gold Platinum Maintenance Window
Sat 5:00pm to Sun 4:00am (Current Tier 2) x x Sun 12:00am to Sun 4:00am (Current Tier 1)
x x Capacity
Size of db < 2 Terabytes >= 2 Terabytes >= 2 Terabytes Memory Requirements < x GB > y GB > y GB CPU Requirements < 16 (?) > 16 (?) > 16 (?)
Criticality of Application (SLA) Mission Critical (Money Movement) - Top 10 x Key Apps - Top 50
x x Non-Critical x
Predictable Performance Critical x x Not Critical x
Shareability Can share with no issues x Can share with related apps (sub-TLAs, common ownership, etc.)
x x Can not share x
Use Active Data Guard for Read-Ony Reporting x x
Disclaimer: This is a sample guideline and not a “hard and fast” set of rules
17 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Development (D)
pdbxyz01p
pdbjkl01p
pdbxyz01a
pdbjkl01apdbabc01d
pdbdef01d
Oracle 12C Naming Standards
Needed to Develop Naming Standards for 12c Databases (Containers and Pluggables)
• Distinguish between existing pre-12c databases• Distinguish between CDB and PDB• Embed Application Identifier in PDB Name• Embed Environment Indicator in Name• Force same set of PDBs to reside in same Container Series throughout Migration Lifecycle
UAT (A) Production (P)
pdbabc01d
pdbdef01d
pdbghi01d
CDB001D
pdbabc01a
pdbdef01a
pdbghi01a
CDB001A
pdbabc01p
pdbdef01p
pdbghi01p
CDB001P
18 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Discovery Testing Phase - Kicking the Tires• No Knowledge or Experience with Multitenancy
• Acquired Exadata Loaner Machine from Oracle
• Developed Formal Test Plans and Parallel Testing Streams
• 4+ Months of Testing and Recording Results
• Met Weekly to Discuss Findings
• Created SRs for Bugs
Discovery Testing of 12C
Testing Streams
Backup and Recovery Testing
Multitenant Testing
RAC Testing
Data Guard Testing
Resource Manager Testing
Security Testing
Monitoring Testing
Cloud Mgmt Pack
Testing
19 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Bugs (and Features) Discovered• Cloud Mgmt Pack did not meet our Requirements
• No support for Container Placement of PDBs and no full control over PDB Name
• Inconsistent error logging and cleanup
• Oracle incorporated our suggestions into next release
• Decided to develop custom automated scripts for provisioning CDBs, PDBs, and Data Guard
• “Max Parallel Server Limit” not honored in CDB Resource Plan• Oracle will fix in release 12.2
• Our Workaround:• Change “parallel_degree_policy” from "manual" to "auto“ in spfile• Set “max degree of parallelism” = 4 and the “Bypass Queue” = “Y” for each Consumer Group in PDB Resource Plan
• Cannot Enable Auto Open of PDB's on Standby using “Save State” (Bug 19728794)
• Our Workaround: Ignore “Save State” and Use Startup Trigger to Open all PDBs on Primary and Standby Containers
• RMAN Resynch Issue when Backing up on Standby after adding PDB or data file to Primary
• Oracle fixed in one-off and quarterly patch
• PDB Cloning/Plugging Complicated in Data Guard Environment
• Requires turning off “log apply” during operation
•
Discovery Testing of 12C
20 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
$x BN
Instance Caging: Controls CPU Usage of Each Container Database (CPU_COUNT)
Resource Manager: Two Levels of Plans
1. CDB Resource Plan: Controls Resources for each PDB
2. PDB Resource Plan: Controls Resources within each PDB
IO Resource Manager: Exadata Only Feature• We use “IORMPLAN Objective” value of “Auto” (Best Practice “Starting Value”)
Resource Manager
Pluggable Database
Shares Max Utilization
limit
Max Parallel server limit
PDB1 2 10% 10%PDB2 2 10% 10%PDB3 2 10% 10%DEFAULT 2 10% 10%
Considerations
• Cap each PDB at 10% as starting point
• Load test to ensure max is sufficient
• Increase max value if necessary
• Ensure consistent performance as PDBs are added
Consumer Group Shares Utilization limit
TNT_Priority_1_Group 12 10%
TNT_Priority_2_Group 4 10%
TNT_Priority_3_Group 2 10%
• Map Services to Consumer Groups as needed
• Give “Admin” Top Priority
21 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Configuration Management Database Changes• Northern Trust has Internally Developed CMDB (Oracle database backend)
• Inventory of Hosts, Clusters, Databases, Applications (Oracle, Sybase, SQL Server, MySQL)• Application Information (Maintenance Windows, Services, Contacts, Configuration Parameters, etc.)• Collection Scripts update nightly (Release, Parameters, Space information, etc.)• Metric Reporting for Management• Used to Drive Administrative Processing against all Databases
• Data Model Changes Required to store Oracle 12C Metadata• Extensive Schema Changes to Support CDB and PDBs • Reduce Impact to Existing Processes and User Interface• Conversion Effort to fit Existing Data into New Structures
• Script and User Interface Changes• Hundreds of Script Changes Needed for Updated Data Model• New Collection Scripts Required for 12C databases• Many User Interface Changes for 12C Objects
Design and Development
22 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12C Automated Build Scripts
Developed Automated Scripts for Deploying CDBs and PDBs
Replaces 200+ Pages of Documentation for Manual RAC Database Build and Data Guard Setup
Consistency of Builds (Eliminates Human Error from Manual Process)
Integration with internal CMDB database – Validates and Inserts Rows when CDB and PDB are Created
Automated Scripts
“Northernize” Exadata Cluster -- Distribute Maintenance Scripts, etc. -- One Time per Exadata Host
Create Container Database Add DBA Users
Configure RMAN for Backup
Setup Auditing
Setup CDB Resource Plan and Instance Caging
Register CDB with OEM and Assign Department and Life Cycle Property
Create PDB Create TLA Objects
Setup Auditing
Register with OEM and Set Life Cycle Property
Build Physical Standby CDB for Production
Faster Deployment -- Reduction in Time from 3 to 4 Weeks to a Few Hours
23 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Data Center BData Center A
Oracle 12C Exadata Environment
Development UAT
Prod DR
Silver Cluster
Gold/Platinum Cluster
Data Guard Replication
Data Guard Replication
Exadata Purchases• Purchased and Installed 7 ½ Rack Exadata Clusters (Late 2014 – Early 2015)
• Separate Physical Clusters by Tier
• 1 Exadata Cluster for Shared Development for Silver, Gold, and Platinum• 3 Exadata Clusters for Silver Tier (UAT, Prod, and DR)• 3 Exadata Clusters for Gold/Platinum Tier (UAT, Prod, and DR)
Shared Tier ClusterSilver/Gold/Platinum
Silver Cluster
Gold/Platinum ClusterSilver Cluster Gold/Platinum Cluster
24 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12c Exadata Environment
Q: Biggest Pain Point Prior to 12c Project …. ?
A: Pressure to Deliver Environments … FAST
Where is my Database ?
Two Most Noticeable Benefits of Oracle 12c Multitenancy on Exadata:
1. Speed of Deployment -- Deploy a PDB in less than 30 minutes• Container RAC Databases already pre-built• No waiting on Storage or OS• Backup jobs already scheduled and deployed (CDB level)• Data Guard already established• DBA has full control (storage and OS
2. Extreme Performance• Exadata is Fast!
I Needed it Yesterday !
25 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12C Exadata Next Steps
Next Steps …..
Refine and Formalize Container Placement Process for New PDBs Determine Container Determine Instance Determine Capacity
Transition to OEM Cloud Management Pack for PDB Deployment
Upgrade OMS and Plugins to Required Release Level Investigate Self Service Features Integrate custom-built automated deployment scripts into OEM Use OEM GUI to Build PDBs (End of Year 2015) Investigate Incorporating CDB and Data Guard Scripts via OEM (2016)
BUILD PDB
26 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Oracle 12c Consolidation Cost Benefits
Feature Benefit
Simplified, dense consolidation Reduced capital expenditures
Rapid Provisioning Reduced operational expenses
Reduced time to market (30 minutes to deploy PDB)
One Team in Control (DBA)
Resource Manager Tenants get fair share of computing resources
RAC Compatibility Greater Scalability , Availability & Resource UtilizationQuick Deployment with Automated Scripts
Data Guard Replication (instead of SAN Replication)
Reduced DR switchover time from hours to minutesOne Team in Control (DBA)
Exadata Hardware Reduced Capital Expenditures(ULA)
Extreme Performance
27 Northern Trust’s Strategy for Adopting Multitenant as a Foundation for DBaaS
Q & A
Questions?
top related