Collaborate 17 - Database consolidation using the oracle multitenant architecture

Database Consolidation using Oracle Multitenant Architecture Pini Dibask, Product Manager for Database Solutions April 4th, 2017

Confidential2 • Pini Dibask, Product Manager, Database Solutions (Quest) • Based in Israel • Oracle DBA since 2006 • Oracle Certified Professional DBA (OCP) • My Blog: OracleDBPro.BlogSpot.com Pini.Dibask@Quest.com http://Linkedin.com/in/pinidibask @pini_dibask About Me

Confidential3 About • Quest is now an independent company again! • Simplifies IT management • #1 independent software company for Database Tools • Driven by innovation “Spend less time on what you need to do, and more time on what you want to do!” • Committed to providing great products and superior support

Confidential4 Agenda • Introduction to Database Consolidation • Oracle Multitenant Concepts • Ensuring QoS in Multitenant Environments • RAC and Multitenant • Performance Monitoring for Multitenant Environments

Introduction to Database Consolidation

Confidential6 Database Consolidation - Prior to Oracle 12c Server Consolidation  Multiple databases reside on a single server

Confidential7 Database Consolidation - Prior to Oracle 12c (Cont’d) Database Consolidation  Single database with multiple schemas

Confidential8 Database Consolidation with Schema Separation - Challenges  Name Collisions  Same schema name or same public synonym name  Security  DBA can access data of both applications  Upgrades  You cannot patch/upgrade only one schema  Point-In-Time Recovery  Impossible to perform schema level point-in-time recovery

Confidential10 Database to Instance Relationship  Pluggable Database  Self-contained Oracle database  Root Container  Oracle-supplied metadata

Confidential11 Oracle 12c – Multitenant Architecture  One SGA  One set of background processes  One root container  Multiple pluggable databases  Up to 252 PDBs (12cR1)  Up to 4096 PDBs (12cR2)

Confidential12 Oracle 12c – Multitenant Architecture (Cont’d)  Pluggable Databases share the following files:  Undo Tablespace  Redo Logs  Control Files  (S)Pfile Local undo introduced in 12cR2

Confidential13 Multitenant Architecture - Containers s CON_ID Description 0 Entire CDB/Non-CDB 1 Root container 2 Seed container 3-4098 User PDBs Created by default - Used as a template PDB for cloning

Confidential14 Manage Many as One Data Guard  Data Guard operates at CDB-Level  Maintenance at CDB-Level = Reduced DBA efforts

Confidential15 Multitenant Advantages - Manage Many as One RMAN - Granular Backup & Restore Options  Backup entire CDB at once or at PDB level  Recover entire container at once or at PDB level s s

Confidential16 Multitenant Advantages - Unplug/Plug Moving PDB from one container to another is straightforward s

Confidential17 Multitenant Advantages - Fast Cloning  Clone PDB from another PDB within the same CDB  Requires source PDB to be OPEN READ ONLY (12cR1) s Hot Clone is available in 12cR2

Confidential18 Multitenant Advantages - Fast Cloning  Clone PDB from another PDB in remote CDB  Requires source PDB to be OPEN READ ONLY (12cR1) Hot Clone is available in 12cR2 s

Confidential19 Multitenant Advantages - Easy Replication (12c Release 2)  Refreshable PDB – Allows manually/automatically refreshing contents of a remotely cloned PDB s

Confidential20 Multitenant Advantages - Manage Many as One Upgrades  Upgrade or apply a patch at CDB-Level https://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_everything_at_once1  Unplug/plug PDB into another container database https://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_one_at_a

Confidential21 CDB Level vs. PDB-Level CDB-Level • Oracle Software • SGA & Background Processes • RMAN Scheduled Backups • Data Guard • Some Parameters (IsPDB_Modifiable= 'FALSE') • Control Files, Redo • (S)Pfile, Password File PDB-Level • FLUSH SHARED_POOL • FLUSH BUFFER_CACHE • Point In-Time Recovery • RMAN Ad hoc Backups • Some Parameters (IsPDB_Modifiable= 'TRUE') • Undo Tablespace (12cR2) • Character Set (12cR2) • Flashback Database (12cR2)

Confidential22 Oracle 12c – Deployment Options DB CDB$ROOT CDB$ROOT PDB1 PDB1 PDB2 PDB252 Non-CDB • Same as before 12c Single Tenant • No additional license • Available in SE/SE1/SE2 • One active PDB Multitenant • Requires Enterprise Edition • Extra Cost Option • Supports up to 4096 active PDBs …

Confidential23 Oracle 12c – Deployment Options (Cont’d) Why use Single Tenant instead of Non-CDB?  Unplug/Plug  Fast Cloning  but most importantly … (source: Oracle 12c Release 2 Documentation)

Confidential24 Oracle 12c – DBCA Non-CDB Single Tenant / Multitenant

Ensuring High Level of QoS with Multitenant Environments

Confidential26 QoS Challenges – Multitenant Environments  PDB-Level QoS challenge  Allocation of resources among competing sessions  Example: One session consumes too many resources  CDB-Level QoS challenge  Allocation of resources among competing PDBs  Example: One PDB consumes too many resources

Confidential27 Oracle Resource Manager - The Basics (Pre 12c) Resource Manager Elements  Resource Plan  Resource Plan Directive  Consumer Group Resource Plan “WEEKEND” Directive 1 70% of CPU Directive 2 20% of CPU Directive 3 10% of CPU Consumer Group “WAREHOUSE” Consumer Group “OLTP” Consumer Group “OTHERS_GROUPS”

Confidential28 The Solution - Oracle Resource Manager  PDB-Level Resource Plan  Specifies how resources are allocated to consumer groups  Prioritize resources between competing sessions  CDB-Level Resource Plan  Specifies how resources are allocated to PDBs  Prioritize resources between competing PDBs

Confidential29 Oracle Resource Manager - 12c Multitenant CDB Resource Plan Directive  CPU Shares  CPU Utilization Limit  Parallel Servers Limit Example Pluggable Database CPU Shares Guaranteed CPU CPU Limit Parallel Servers Limit OLTP 3 3/4 = 75% 100% 100% DWH 1 1/4 = 25% 60% 100%

Confidential30 Oracle Resource Manager - 12c Multitenant  Obtain information about default CDB resource plan  Obtain information about default PDB directive s s

Confidential31 Example of CDB-Level Resource Plan Resource Plan “Daytime_CDB_PLAN” PDB “OLTP” PDB “DWH” Directive 2 Guaranteed CPU: 25% Maximum CPU: 60% Directive 1 Guaranteed CPU: 75% Maximum CPU: 100% Pluggable Database CPU Shares Guaranteed CPU CPU Limit Parallel Servers Limit OLTP 3 3/4 = 75% 100% 100% DWH 1 1/4 = 25% 60% 100%

Confidential32 s Creating CDB Resource Plan Pluggable Database CPU Shares Guaranteed CPU CPU Limit OLTP 3 3/4 = 75% 100% DWH 1 1/4 = 25% 60%

Confidential33 s Creating CDB Resource Plan (Cont’d)

Confidential34 Enabling/Disabling CDB Resource Plan  Manually enabling resource plan  Manually disabling resource plan s s

Confidential35 Enabling/Disabling CDB Resource Plan (Cont’d) s Automatically Enable/Disable CDB Resource Plan

Confidential36 PDB Level Memory Resource Management  Not available in 12c Release 1  12c Release 2 - Memory parameters can be set at PDB level  SGA_TARGET  DB_CACHE_SIZE  DB_SHARED_POOL_SIZE  PGA_AGGREGATE_LIMIT  PGA_AGGREGATE_TARGET  SGA_MIN_SIZE New in 12c Release 2

Confidential37 PDB Level I/O Resource Management  Not available in 12c Release 1  12c Release 2 Introduced the following new parameters:  MAX_IOPS - limits number of I/O operations per second  MAX_MBPS - limits megabytes for I/O operations per second  Default : 0 (no limit)  If Oracle waits due to I/O limit “resmgr: I/O rate limit” wait event will appear  Cannot be set in a Non CDB

Confidential38 How Many Resources Actually Being Used by PDBs?  Option #1 - DBA_HIST_RSRC_PDB_METRIC  Displays historical resource manager metrics by PDB  Option #2 - AWR_ROOT_RSRC_PDB_METRIC (underlying AWR table)  Option #3 - AWR Reports

Confidential39 Maintenance Tasks in Oracle Multitenant  ENABLE_AUTOMATIC_MAINTENANCE_PDB parameter  Can be used to enable/disable running of maintenance tasks  Default: true  Can be set at either CDB or PDB levels  AUTOTASK_MAX_ACTIVE_PDBS parameter  Maximum number of PDBs that can schedule maintenance tasks concurrently  Default: 2 (two PDBs and the CDB root can run tasks at the same time)  Can be set at CDB level only  Both parameters introduced in 12c Release 2

Confidential41 Why RAC & Multitenant?  Single Instance & Multitenant challenges  Not Scalable = Limited Consolidation Solution  Instance Down Downtime for all PDBs  RAC makes Multitenant better  Scalable = True consolidation solution  Available = Instance Down  PDBs continue running on other nodes

Confidential42 RAC & Multitenant s  Multitenant fully supports RAC  PDBs can be opened on specific instances

Confidential43 RAC & Multitenant PDBs workload distributed across RAC instances Services Single SGA per CDB Instance Node1 CDB Instance 1 Node2 CDB Instance 2 Multitenant Container Database (CDB)

Confidential44 RAC & Multitenant PDBs workload distributed across RAC instances Services Single SGA per CDB Instance Node1 CDB Instance 1 Node2 CDB Instance 2 Node3 CDB Instance 3 Multitenant Container Database (CDB)

Confidential45 RAC & Multitenant s Preferred Instance Available Instance

Confidential46 RAC & Multitenant s PDB opened only in Preferred instance PDB automatically starts in the other instance Shutting down the preferred instance

Performance Monitoring for Multitenant Environments

Confidential48 Multitenant & AWR – Oracle 12c Release 1  Snapshots taken only at CDB-Level  AWR data reside in cdb$root container

Confidential49 Multitenant & AWR – Oracle 12c Release 1 (Cont’d) s

Confidential50  AWR reports are available only at CDB level  AWR Management Operations only at CDB level  AWR data retention  Snapshot schedule  Taking manual snapshots  Purging snapshot data  Unplugged PDB does not contain AWR information Multitenant & AWR – Oracle 12c Release 1 (Cont’d)

Confidential51 Multitenant & AWR – Oracle 12c Release 1 (Cont’d)

Confidential52 Multitenant & AWR – Oracle 12c Release 2  Snapshots can be taken either at CDB or PDB level  Snapshot data reside in SYSAUX tablespace of each PDB  It is possible to create a report at PDB-level AWR report  AWR management operations at either CDB or PDB level  New Parameter : AWR_PDB_AUTOFLUSH_ENABLED  Specifies whether to enable automatic AWR snapshots for PDBs  Default : false (automatic AWR snapshots are disabled for PDBs)  Can be set at CDB or PDB level

Confidential53 Multitenant & AWR – Oracle 12c Release 2 (Cont’d)

Confidential54 Workload Analysis using OEM ASH Analytics

Confidential55 Workload Analysis using Foglight for Oracle

Confidential58 References  Introduction to the Multitenant: Architecture (Documentation) http://docs.oracle.com/database/122/CNCPT/introduction-to-the-multitenant-architecture.htm#CNCPT89234  Oracle Multitenant (White Paper) http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf  Oracle Multitenant: New Features in Oracle Database 12c Release 12 (White Paper) http://www.oracle.com/technetwork/database/multitenant/overview/multitenant-wp-12c-2078248.pdf  Mike Dietrich Blog (Master Product Manager, Database Upgrade & Migrations - Oracle) https://blogs.oracle.com/UPGRADE/ Note: All diagrams and illustrations are used by permission of Oracle

Collaborate 17 - Database consolidation using the oracle multitenant architecture

More Related Content

What's hot

Similar to Collaborate 17 - Database consolidation using the oracle multitenant architecture

More from Pini Dibask

Recently uploaded

Collaborate 17 - Database consolidation using the oracle multitenant architecture