Oracle Multitenant Best Practices John P. McHugh Senior Principal Product Management Oracle Multitenant NOTE: Published .pdf version does not have the demo builds from the original presentation and formatting may be off.
Copyright © 2014, 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 © 2014, Oracle and/or its affiliates. All rights reserved. |
CAPEX
RATIONALIZE STANDARDIZE
OPEX
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Oracle Multitenant New architecture for consolidating databases and simplifying operations AP GL
OE
Self-contained PDB for each application • • •
PDBs CDB
Portability (via pluggability) Rapid provisioning (via clones) Applications run unchanged
Common operations performed at CDB level • •
Root
Manage many as one (upgrade, backups, HA) Granular control when appropriate
Shared memory and background processes • More applications per server Complementary to VMs Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Public
Key Benefits Benefit
Capability Enabled
Minimize CapEx
• More applications per server
Minimize OpEx
• Manage many as one (reduced patching!) • Standardized procedures & service levels • Enable self-service provisioning
Maximize Agility Easy
• Snapshot cloning for development and testing • Portability through “pluggability” • Scalability with RAC • To Adopt: Applications run unchanged • To Use: Interface is SQL Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Consolidation Performance Test Highlights Oracle Multitenant Achieves Higher Performance with Less Resource Requirements Total Throughput 252 PDBs vs. non-CDBs
80% higher aggregate throughput
Number of Supported Database (same Throughput per Database)
tps
databases
150000
300
100000
200
50000
100
0
50% more databases consolidated
0 non-CDBs
PDBs
non-CDBs
Memory Footprint per Database (not including Buffer Cache)
PDBs
Number of Cores Required to support 252 Databases
Storage IOPS Required to support 252 Databases
MB
cores
IOPS
2000
200
400000
1500
150
300000
1000
100
200000
500
50
100000
0
0
0
non-CDBs
PDBs
8x reduction in memory footprint
non-CDBs
PDBs
64 fewer cores needed
non-CDBs
PDBs
3x reduction in storage IOPS
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
7
Agenda
1
Deployment
2
Management
4
High Availability
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
8
Agenda
1
Deployment
2
Management
4
High Availability
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
9
Deployment PLANNING
• Management Policies
• PDB Candidate Checklist – Define consolidation Criteria. Examples might be: • • • •
SLAs – Performance and/or availability Workload Line of Business Character set and/or Time zone
– Positions you to more easily define a DBaaS Service Catalog
• Select and size the server – Consider workload and consolidation density – CPU, Memory, IO, Network, Storage
– – – –
Where/when to Manage Many as one Define the SLAs and HA solution Define the cloning policies Define DB Resource Management Policies
• Evaluate PDB Candidate Uncertainties – Deploy as Single Tenant – Position the PDB for Multitenant • Clone and unplug/plug features available
• Analyze the Workloads – Identify compatibility and conflicts – Identify oversubscription opportunities Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Real Application Testing Multitenant Load
Deployment PLANNING – USE CONSOLIDATION DATABASE REPLAY
Test System
Production Systems CRM- DB1
HCM-DB2
Consolidated Replay Directory
Directories
DW-DB3
Capture 1 Windows DB 10.2
AIX DB 9.2.0.8
HP-UX DB 11g
Capture 2
Capture 3
… Replay on CDB
Directories Capture 1
Capture 2
May: Month-end June15: Daily Close Peak
Capture 3
CDB – DB12c
June 18: DW ETL
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Deployment
CONSOLIDATION WORKBENCH
PLANNING – USE CONSOLIDATION WORKBENCH
• Databases to Database
• Initial consolidation
– Consolidate multiple source databases into a destination database – Multitenant (Single Instance and RAC)
– Consolidate servers on Exadata or Generic Server
• Incremental consolidation
• Databases to Servers – Consolidate multiple source databases onto a fewer number of destination servers – No change in the number of databases – Exadata and other server platforms
– Consolidate additional databases onto existing systems to fill spare capacity
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
CONSOLIDATION WORKBENCH
Deployment PLANNING – DEFINE CONSOLIDATION SCENARIOS
• Ultra-Conservative Mode – Size for peak maximum
• Conservative Mode – Size for maximums within hourly buckets
• Medium Conservative Mode – Size for 80th percentile within hourly buckets
• Aggressive Mode – Size for average within hourly buckets
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Deployment
CONSOLIDATION WORKBENCH
PLANNING – IDENTIFY RESOURCE REQUIREMENTS AND CONFLICTS 120% 100% 80% 60% 40% 20% 0%
120% 100% 80% 60% 40% 20% 0%
Application A CPU Utilization
• Resource considerations
120% 100% 80% 60% 40% 20% 0%
Application B CPU Utilization
– CPU Combined CPU Utilization
– Memory – I/O – Storage
120% 100% 80% 60% 40% 20% 0%
120% 100% 80% 60% 40% 20% 0%
Application A CPU Utilization
120% 100% 80% 60% 40% 20% 0%
Application B CPU Utilization
• Conflicts – Configuration – Workload Combined CPU Utilization
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
– Namespaces
Deployment CDB Creation and Configuration
• Size the CDB as you would a large database – Configure Huge Pages if SGA > 30GB – Modify memlock limits (soft/hard) accordingly
• Use DBCA – GUI or Silent Mode – MOS NOTE for Silent Mode Syntax – If you must use your scripts call catcdb.sql
• Standardize – Options – Character set • AL32UTF8
– Custom Standardizations – Use OMF
• ‘slightly smaller than installed memory’
– Use ASMM – Set Processes to 100 x physical core – Set SGA_TARGET to 60% of physical memory • Let Oracle Manage the Memory Pools
– Automatic PGA memory management • 20% of SGA
– Limit UNDO to grow no more than 10-20% of SGA
– Use ASM (high/normal) – Set DB_FILES to anticipate growth – Redo • Minimum 4GB and size to switch max <= 10-20 mins • 3-4 redo groups • Archive Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Deployment
• ISPDB_MODIFIABLE
PDB Creation and configuration
– Evaluate and adjust any parameter that affects application performance
• Clone! – – – – –
• • • •
Full Snapshot Remote Subset Metadata
• To check the PDB parameter settings in your session execute:
• Configure clone quotas and storage limits • Don’t modify PDB$SEED – Create and customize your own SEED
• Use CREATE_FILE_DEST for PDB file dest agile management (12.1.0.2)
OPEN_CURSORS OPTIMIZER settings CURSOR_SHARING Evaluate as consolidation density increases
select con_id, name, value from v$parameter where name =
;
– PDB init parameters are stored in CDB$ROOT dictionary – Events can be set within PDBs and CDBs
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Deployment Use the Pluggable Database Self-Service Provisioning App • Self-Service – Lines of Business subscribe and manage their data
• Define and restrict clone images • Define Quotas and Storage Limits • Free download – http://oracle.com/goto/multitenant/downloads
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Deployment Use the Pluggable Database Self-Service Provisioning App
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Deployment UPGRADE / PATCHING • Conservative Upgrade Methodology: 1.
Upgrade to 12c non-CDB
2.
Startup the 12c non-CDB in read only mode
3.
Exec procedure dbms_pdb.describe() to generate manifest
4.
Backup the manifest (.xml) and data files together
5.
On the CDB, using the manifest, exec function dbms_pdb.check_plug_compatibility()
6.
In CDB$ROOT query pdb_plug_in_violations view for ERRORS and/or WARNINGs
7.
Using the manifest, execute Create pluggable database …. using ‘ ….
• Follow Upgrade Documentation! – run preupgrd.sql – Run describe and check violations – Open the pdb with the `…open upgrade…` flag
• Patch Applies – Manage Many-as-One
• Schema Consolidation Migrations – Use non-CDB to PDB subset cloning
- No copy or copy of storage 8.
Run noncdb_to_pdb.sql in the new PDB
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Agenda
1
Deployment
2
Management
4
High Availability
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
20
Management DATABASE RESOURCE MANAGEMENT • Evaluate your consolidation strategy based application resource requirements • Applications contend for – – – – –
CPU Memory Parallel servers Disk bandwidth Flash
• Use Resource Manager to – Provide resource guarantees – Limit resource usage
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Resource Management Managing CPU - PDBs
A CDB Resource Plan uses “shares” to specify how CPU is distributed between PDBs.
CDB Resource Plan Pluggable Database
Shares
Sales
Utilization Limit
“Sales” is guaranteed 50% of the CPU. If it doesn’t use it, someone else can.
Guaranteed CPU
Maximum CPU
5
5/(5+2+2+1) = 50%
100%
Marketing
2
20%
100%
Support
2
20%
90%
Other
1
10%
100%
90%
PDBs are managed just like Consumer Groups. Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Resource Management Managing CPU – PDBs
CDB Resource Plan Pluggable Database
Shares
Sales
5
Marketing
default (2)
Support (Default directive)
Utilization Limit
Guaranteed CPU
Maximum CPU
5/(5+2+2) = 56%
100%
default (75%)
22%
75%
default (2)
default (75%)
22%
75%
2
75%
Configure a “default directive”: the default shares and utilization limit for a PDB Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
How Do CDB and PDB Resource Plans Work Together? 100
CDB resource plan controls how CPU is utilized by PDBs
90 80 70 60
CPU Utilization
50 40
Support (1 share) Marketing (1 share) Sales (2 shares) - Batch (1 share) Sales (2 shares) - Critical (4 shares)
30 20 10 0
PDB resource plan controls how CPU is utilized by Consumer Groups in a PDB Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Resource Management Define Utilization Limits for PDBs 100
With a utilization limit of 75%, SUPPORT is throttled, even though CPU is available.
90
80 70
CPU Utilization
60
Support (1 share)
50
Marketing (1 share)
40
Sales (2 shares)
30 20 10
0
Utilization Limits provide clients consistent performance. They also restrict their resource usage, based on what the client paid Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Monitoring and Tuning a PDB PDB is getting all the CPU it needs. Its usage exceeds its guaranteed 50 share.
PDB is being throttled because its usage exceeds its guaranteed share.
45
Utilization Limit = 40%
40
PDB is being throttled because its usage exceeds its limit.
35 30
# Sessions Waiting for CPU
25
Guaranteed CPU = 20%
# Running Sessions
20 15 10
PDB is getting all the CPU it needs
5 0
See how the PDB is affected by its resource plan settings. Monitor using v$rsrcmgrmetric_history. Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Resource Management Monitor with ASH
• All sessions are sampled every second • ASH data saved for 8 days (by default) • View breakdown of CPU usage by PDB – Use ASH Analytics – Use query from MOS note 1338988.1
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Management Use EM EXPRESS
• CDB – Global Performance Monitoring • • • • •
Performance Resources SQL Monitor Session Summary and drill down
• PDB – Performance Monitoring • Wait/User IO/CPU
– – – –
Resource use – Storage Real Time SQL Monitor and SQL Tuning Advisor ASH ANALYTICS Initialization Parameters
• PDB Filtering and Drill Down – Clone Management – Storage and Resource Limits – Complete PDB Life-cycle Management
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Management Common Users • Define and Generalize your common user local user policies – Define the role separation between local and common users – Avoid GRANT session to common user globally • New PDBs plugged in inherit common user • Enforce local grant session to the common user
• Standardize on common user prefix and suffix – Avoids plug in violations if the common user is unknown to the CDB
• Use the common user to lock down accounts across PDBs
• Do not create objects in the common users schema
• Do not change privileges on Oracle supplied common users – Grant the privilege within the targeted PDBs
• Validate impact of common users prior to unplug/plug operations – ‘conflicts’ are not captured in PDB compatibility checks
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Management Security Practices • Implement Security features where appropriate
• Implement Security features with post clone trigger
– Consolidate PDBs of common security features – Use Oracle Security products • Fully integrated with Multitenant • DB Vault, Label Security, TDE, Masking, Redaction
• Follow Standard Oracle Security Best Practices to Minize vulnerability – Monitor database role and privilege usage • Privilege Analysis – DBMS_PRIVILEGE_CAPTURE
– Use the 12c Unified Auditing – Simplify the privilege model – Leverage the separation of duties • SYSBACKUP, SYSDG, SYSKM Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Management Operational • Review AutoTask scheduling
• Size SYSAUX appropriately – Monitor free space in CDB and in all PDBs – Ensure 10GB free before upgrade to 12.1.0.2 – Look for ORA-1652 temp extent allocation errors in the mmon slaves trace files
• Monitor the Audit Directory – SYS connections are audited whether audit is enabled or not and cannot be disabled – Monitor and purge ?/rdbms/audit regularly
– By default, all 3 auto tasks start at the same time in all PDBs. – Stagger the schedule to manage performance – Enable ‘gather stats’ and disable segment advisor and auto sql tuning advisor if they are not needed.
• Archive Logging – Pre-Consolidation: Evaluate and Size Aggregate Redo Rate – Post-Consolidation: Size Archive Log Dest and Monitor Purging Policies
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Management Connections • Use Services
• Client Authentication
– Default service in single instance – Managed service in RAC
– SQLNET.ALLOWED_LOGON_VERSION_CLIENT
• USE_SID_AS_SERVICE_listenername=ON – Only for legacy SID connect strings – Opportunity to identify laggards
– Avoid ‘ORA-28040: No matching authentication protocol’
• Check client library versions for 12c compatibility
• Set TWO_TASK for legacy management scripts
– Specifically for 3 party COTS apps – 3rd party monitoring tools
– Update these scripts ASAP
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Management Connections – Connection Pools
• Use One of Oracle’s Connection Pools – UCP, Active GridLink in WLS
• Release Connections as soon as possible in to the pool – Don’t leave connections checked out for too long.
• Don’t Oversize the Pool – Degrades performance – Size 10 to 20 times the number of cores – Use setMinPoolSize() and setMaxPoolSize() – Use connection labeling in UCP to map a tenant with a PDB
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
34
High Availability RAC • Use DB QoS for sub second performance requirement
• Use Policy Managed Services – Default, Admin, Policy – FAN, Application Continuity, Database QoS – PDB and service relocation
– Avoid Memory Pressure • `note enabled by def. 12c
• Use Policy Managed Databases – You can establish and manage PDB node affinity • Minimizes GCS and GES chatter • Partitions SGA across cluster • Efficiently segregates workloads in to server pools
• Use Flex ASM
• Set PDB SAVE STATE (12.1.0.2) – Service pulls up the PDB by default – Open mode must be consistent across the cluster
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
High Availability DATA GUARD - Follow MAA Best Practices for Data Guard • Role Transition is at the CDB level – Ensure all PDBs common uptime and protection requirements
• Understand Standby Behavior (12.1.0.2) – Physical Standby automatically creates files on standby when PDB created from PDB$SEED – Cloning a local PDB, physical standby must be Active Data Guard to automatically copy files
• Configure a Local Standby – Low latency synchronous apply – Decreases maintenance window with faster switchover – Great for cloning purposes
• Remote Cloning and Standby – Must pre-copy files to expected location – If shared storage links can be used
• PDB Logging Options (12.1.0.2) – STANDBYS = (ALL | NONE) • No redo apply on standby • See MOS Note 1916648.1
– NOLOGGING • Direct path writes only • Conventional DML continues to log • FORCE and HINT overrides
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
High Availability • Backup the PDB after plug-in operations as soon as possible
BACKUP • Follow MAA and RMAN Best Practices – “High Availability Best Practices for Database Consolidation, The Foundation for Databaseas-a Service”
• Evaluate Targeted Consolidation Databases for RPO and RTO requirements – Business impact for data loss and recovery time tolerance
• Group Candidates for Consolidation based on HA RPO/RTO requirements
• Manage Many-as-One – CDB level backups captures all PDBs
– Starting point for recovery
• Proactively practice PDB PITR
• Leverage Clone Archives for Point-in-Time Recovery – Clones are not a backup solution but are an effective means to retrieve data at a specific point in time – Take a full clone of a snapshot clone for this purpose
• Make an archive copy of the PDB manifest (.xml) and datafiles after an uplug operation and before dropping the PDB. Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Summary • Standardization is a Best Practice • Oracle Multitenant Imposes Standardization • Identify Standardization opportunities • Integrate these standards as best practices in your Multitenant Deployments, Policies and Procedures
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Multitenant Internal Deployments
17 Applications consolidated on a single Container Database. 9 migrated and 8 new applications
50% Less downtime during patching
31.5%
0
3.3
Unplanned Outages since production. DBA team delighted !!!
Faster backups with
times data growth
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
39
Oracle Multitenant Customer Success Stories Multitenant Customer Sessions at Oracle OpenWorld 2014 Venue
Title
Customer
Session
SAS
CON6328 Mon 2:45pm SAS Solutions OnDemand: A Multitenant Cloud Offering
Cigna
CON6379 Mon 5:15pm Oracle Multitenant Customer Success Story
GE
CON7304 Wed 10am
Pulte Group
CON3203 Wed 3:30pm Finding the Bleeding Edge Without Hemorrhaging
Oracle Multitenant: Seven Sources of Savings
Sherwin-Williams CON6387 Wed 4:45pm Oracle Multitenant Deployment on Oracle Exadata Taleo
CON7307 Thu 9:30am
Successful Migration of Taleo Business Edition Cloud Service
e-DBA
CON2785 Thu noon
Rolling Out a PaaS with Oracle Multitenant
HDFC Bank
CON3211 Thu 10:45am Deliver Oracle Database Cloud: Multitenant on SuperCluster Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Session ID: CON8381 Session Title: Resource Manager: Best Practices Venue / Room: Moscone South - 102 Date and Time: 10/1/14, 10:15 - 11:00
Session ID: CON12068 Session Title: Oracle Multitenant: Ask the Experts Venue / Room: Moscone South - 308 Date and Time: 10/2/14, 10:45 - 11:30
Session ID: CON7648 Session Title: How an Oracle Database 12c Upgrade Works in a Multitenant Environment Venue / Room: Moscone South - 306 Date and Time: 9/30/14, 12:00 - 12:45
Session ID: CON6379 Session Title: Oracle Multitenant Customer Success Story Venue / Room: Moscone South - 308 Date and Time: 9/29/14, 5:15 - 6:00 Session ID: CON7305 Session Title: Oracle Multitenant: The Beating Heart of Database as a Service Venue / Room: Moscone South - 102 Date and Time: 9/30/14, 10:45 - 11:30
Session ID: CON7649 Session Title: How and Why to Migrate from Schema Consolidation to Pluggable Databases Venue / Room: Moscone South - 306 Date and Time: 10/1/14, 11:30 - 12:15 Session ID: CON6990 Session Title: How to Best Consolidate and Live-Migrate Databases with Oracle Multitenant Venue / Room: Intercontinental B Date and Time: 10/2/14, 10:45 AM - 11:30 AM Session ID: CON6387 Session Title: Oracle Multitenant Deployment on Exadata Thursday, Oct 1, 10:45 AM - 4:45
Session ID: CON7304 Session Title: Oracle Multitenant: Seven Sources of Savings Venue / Room: Moscone South - 130 Date and Time: 10/01/14, 10:15 - 11:00 Session ID: CON3203 Session Title: How to Find the Bleeding Edge without Hemorrhaging Venue / Room: Moscone South - 130 Date and Time: 10/01/14, 3:30 - 4:15
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
