1 copyright © 2013, oracle and/or its affiliates. all ...days... · oracle multitenant database...
TRANSCRIPT
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 1
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 2
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.
Oracle Multitenant
Oracle Server Technologies - PTS
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 4
Agenda
Rethinking Architecture for the Database Cloud
Multitenant Architecture
Multitenant Capabilities
Multitenant Manageability
Upgrading to Multitenant
Use Cases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 5
Private Database Cloud Architectures Using Oracle Database 11g
Dedicated Databases
share servers and OS
Virtual Machines
share servers
Schema Consolidation
share servers, OS and database
Increasing Consolidation
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 6
Private Database Cloud Architectures Using Oracle Database 12c
Dedicated Databases
share servers and OS
Virtual Machines
share servers
Pluggable Databases
share servers, OS and database
Increasing Consolidation
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 7
Oracle Database Architecture Requires memory, processes and database files
System Resources
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 8
New Multitenant Architecture Memory and processes required at multitenant container level only
System Resources
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 9
New Multitenant Architecture Memory and processes required at multitenant container level only
System Resources
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 10
Agenda
Rethinking Architecture for the Database Cloud
Multitenant Architecture
Multitenant Capabilities
Multitenant Manageability
Upgrading to Multitenant
Use Cases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 11
Oracle Multitenant
New database architecture that allows a Container Database (CDB) to
hold zero, one, or many Pluggable Databases (PDBs)
Oracle Database 12c Release 1 supports both the new multitenant
architecture and the old “non-CDB” architecture
PDB is fully compatible with a non-CDB
Rethinking Database Architecture
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 12
Rethinking Database Architecture
Oracle Data Dictionary contains both Oracle Metadata and Application
Metadata
Both are held in the same dictionary tables
This “intermingling” of metadata makes upgrading an application, and
moving it around, complicated
Before 12.1 - Common Data Dictionary
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 13
Common Data Dictionary Before 12.1: intermingled over time
Database Created
Data D
ictio
nary
User Data
Meta D
ata
Mature Database
Data D
ictio
nary
User Data
Meta D
ata
Tables, Code, Data added
Data D
ictio
nary
User Data
Meta D
ata
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 14
OBJ$ TAB$ SOURCE$
…
Oracle Data and User Data
OBJ$ TAB$ SOURCE$
…
EMP DEPT
…
The data dictionary is
intermingled with the
customer’s metadata
OBJ$ TAB$ SOURCE$
…
Pluggable Databases
fixes this with
in-database
virtualization
Only the definition of
the Oracle system
remains
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 15
Horizontally Partitioned Data Dictionary
OBJ$ TAB$ SOURCE$
…
EMP DEPT
…
OBJ$ TAB$ SOURCE$
…
Oracle-supplied
objects such as
views, PL/SQL, etc.,
are shared across
all PDBs using
object “stubs”
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 16
Root
PDB
CDB Architecture
So essentially,
The part with just the
“customer stuff” is the
PDB (Pluggable
Database).
The part with just the
“Oracle stuff” is called
the Root.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 17
Multitenant Architecture Components of a Multitenant Container Database (CDB)
Pluggable Databases (PDBs)
PDBs
Root
CDB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 18
Multitenant Architecture
Multitenant architecture can currently
support up to 252 PDBs
A PDB feels and operates identically to a
non-CDB
You cannot tell, from the viewpoint of a
connected client, if you’re using a PDB or
a non-CDB
Database
Link
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 19
CDB Architecture
PDBs share common SGA and
background processes
System administrators can connect to
CDB root container to administer single
system image
Client foreground sessions see only the
single PDB they connect to
and see it just like a non-CDB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 20
CDB Architecture – Scalability
Add PDBs to a CDB whose memory is
pre-sized to take full advantage of what
the machine offers
Normal SGA block management is
graceful as PDBs compete for memory
Compare this with adding the non-CDB
that then prevents all non-CDBs fitting
into memory – you fall over the paging
“cliff”
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 21
OLTP benchmark comparison
Only 3GB of memory vs. 20GB memory used for 50 databases
Pluggable databases scaled to over 250 while separate database instances maxed at 50
Pluggable Databases vs Separate Databases Highly Efficient: 6x Less H/W Resource, 5x more Scalable
0
5
10
15
20
1 10 20 50 80 110 140 170 200 230 250
Separate
Pluggable
# Databases
Me
mo
ry U
tiliz
ed
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 22
Files in the CDB
Each PDB has its own set of tablespaces
including SYSTEM and SYSAUX
PDBs share UNDO, REDO
and control files, (s)pfile
By default the CDB has a single TEMP
tablespace but PDBs may create their
own
Namespaces
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 23
Creating CDBs with DDL
create database newcdb
user sys identified by sys_password
user system identified by system_password
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1
enable pluggable database
seed
system datafiles size 125m autoextend on next 10m maxsize unlimited
sysaux datafiles size 100m;
Example (with Oracle Managed Files)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 24
Creating CDBs with DBCA
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 25
Provisioning a PDB with DDL
create pluggable database CRM
admin user crmadm identified by CRMpass123
roles = (DBA);
Example (with Oracle Managed Files)
Example (without Oracle Managed Files)
create pluggable database CRM
admin user crmadm identified by CRMpass123
roles = (DBA)
file_name_convert = ('/disk1/oracle/dbs/pdbseed/',
'/disk1/oracle/dbs/salespdb/');
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 26
Provisioning a PDB with EM Cloud Control 12c Example
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 27
Fast Cloning of PDBs PDBs can be cloned from PDBs within
the same or remote database
PDB carries lineage from the PDB it
was cloned from
Full clone parallelized at the
granularity of the database block
Within-CDB cloning can be done
using “SNAPSHOT COPY” option
when supported by underlying
filesystem
– ZFS, ACFS, NetApp
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 28
Cloning a PDB with DDL Examples
create pluggable database HCMBI from HCM
create pluggable database HCMBI from [email protected]
Remote (DB Link)
Local
create pluggable database HCMBI from HCM snapshot copy
Snapshot Copy
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 29
PDB Cloning with EM Cloud Control 12c Example
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 30
PDB Cloning with SQL Developer Example
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 31
Fast Provisioning of PDBs Pluggable databases can be quickly provisioned
0
5
10
15
20
25
Non CDB PDB Clone PDB using Copy-on-Write File
System
Time Taken to Provision New Database
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 32
Unplug / plug
In its unplugged state, a PDB is a self-
contained set of data files and an XML
metadata file
Simply unplug from the old CDB…
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 33
Unplug / plug …and plug in to the new CDB…
Moving between CDBs is a simple case
of moving a PDB’s metadata
An unplugged PDB carries with it lineage,
opatch, encryption key info etc
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 34
Unplug / plug Example
alter pluggable database HCM
unplug into '/u01/app/oracle/oradata/…/hcm.xml'
create pluggable database My_PDB
using '/u01/app/oracle/oradata/…/my_pdb.xml'
Plug
Unplug
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 35
Users: Local vs Common Local User
– the successors for customer-created
users in a non-CDB
– defined only in a PDB
– can administer a PDB
Common User
– defined in the root and is represented
in every PDB
– can log into any PDB where it has
“Create Session” and can therefore
administer a PDB
– The Oracle system is owned by common
users
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 36
Common Users and Privileges
A common user can be granted privileges locally in a PDB and
therefore differently in each container
A common user can, alternatively, be granted a system privilege
commonly – the grant is made in root and every PDB, present and
future
Authorization is checked in the container where SQL is attempted
considering only the privileges of the user in that container
Authorization is checked in the same way as as pre-12.1
create user c##Über_Administrator
identified by pwd container=all
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 37
Services and Sessions
Session are created essentially the same as before
Specify Listener location, port, username / password, servicename
Service definitions now have new PDB property
You can connect to a PDB only by using network authentication
Local users can create sessions only in the PDB where they are defined with Create Session privilege
A common user can create a session in any PDB where it has Create Session
Connections are established the same as pre-12.1
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 38
RAC and Services in PDBs Improved services with the control of RAC and flexibility of PDBs
ERP PDB is open on node 4 only
HCM PDB is open on node 1 & 2 only
CRM PDB is open on all the nodes
Each instance in a RAC Cluster opens
the CDB as a whole.
PDBs opened automatically where
services are defined
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 39
Agenda
Rethinking Architecture for the Database Cloud
Multitenant Architecture
Multitenant Capabilities
Multitenant Manageability
Upgrading to Multitenant
Use Cases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 40
Manage Many as One with Multitenant Backup databases as one; recover at pluggable database level
One Backup
Point-in-time recovery
At pluggable database level
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 41
Manage Many as One with Multitenant One standby database covers all pluggable databases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 42
Multitenant for Simplified Patching Apply changes once, all pluggable databases updated
Upgrade
in-place
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 43
Multitenant for Upgrades Flexible choice when patching & upgrading databases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 44
Expand Cluster to Support Flexible Consolidation Model
Services
Single SGA per
CDB Instance
Improved Agility With Changing Workloads
Node1
CDB Instance 1
Node2
CDB Instance 2
Multitenant Container Database (CDB)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 45
Expand Cluster to Support Flexible Consolidation Model
Services
Single SGA per
CDB Instance
Node1
CDB Instance 1
Node2
CDB Instance 2
Node3
CDB Instance 3
Improved Agility With Changing Workloads
Multitenant Container Database (CDB)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 46
Agenda
Rethinking Architecture for the Database Cloud
Multitenant Architecture
Capabilities Enabled
Multitenant Manageability
Upgrading to Multitenant
Use Cases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 47
Managing Multitenant Databases Manage many as one
Managed Globally
by CDBA and
Locally by PDBAs
Container Database
Pluggable DBs
Data Dictionary
Tablespaces
Schemas
Users, Roles &
Services
PDBAs
ERP HR
DB
Seed CRM
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 48
Management of Pluggable Databases Separation of Duties—CDBA vs. PDBA
CDB and PDB
CDB Only CDB Management:
Holistic database and instance
management
PDB Management:
Application centric management
Migration Storage
Management Users & Roles
Administration
System SQL and
Session
Resource Management
Backup & Recovery
Performance & Tuning
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 49
Managing Resources between PDBs
Use Resource Manager to control shared resources among PDBs
– CPU
– Exadata I/O
– Sessions
– Parallel execution servers
Resource Manager policy controls how resources are utilized
– Default configuration that works, even as PDBs are added or removed
– Hard limits, for “get what you pay for”
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 50
PDB Resources Policies
Based on two notions:
– A number of resource shares is allocated to each PDB
– A “cap” (a.k.a. maximum utilization limit) may be applied to each PDB
CPU Scheduling of PDBs
– Fine-grained scheduling of sessions performed every 100ms
– Resource Manager maintains an internal queue
– Plans applied dynamically
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 51
Manage CPU
Pluggable Database Shares Guaranteed CPU Maximum CPU
HCM 2 2/4 = 50% 100%
CRM 1 1/4 = 25% 100%
ERP 1 1/4 = 25% 100%
2 Shares 1 Share 1 Share
A CDB Resource Plan uses
shares to specify how CPU
is distributed between PDBs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 52
Manage CPU
Pluggable Database Shares Guaranteed CPU Utilization Limit Maximum CPU
HCM 2 2/4 = 50% 100%
CRM 1 1/4 = 25% 50% 50%
ERP 1 1/4 = 25% 50% 50%
A CDB Resource Plan uses
utilization limits to limit the
CPU usage for a PDB.
Note: With utilization limits,
your CPU may be under-
utilized.
2 Shares 1 Share
50%
1 Share
50%
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 53
Manage CPU
Pluggable Database Shares Guaranteed CPU Utilization Limit Maximum CPU
(Default directive) 1 50%
HCM 2 2/4 = 50% 100%
CRM default (1) 1/4 = 25% default (50%) 50%
ERP default (1) 1/4 = 25% default (50%) 50%
Configure a default directive:
the default shares and utilization
limit for PDBs.
2 Shares Default Default
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 54
Manage CPU
Pluggable Database Shares Guaranteed CPU Utilization Limit Maximum CPU
(Default directive) 1 50%
HCM 2 2/5 = 40% 100%
CRM default (1) 1/5 = 20% default (50%) 50%
ERP default (1) 1/5 = 20% default (50%) 50%
DEV default (1) 1/5 = 20% default (50%) 50%
With a default directive, you
don’t need to modify the
resource plan for each PDB
plug and unplug
2 Shares Default Default Default
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 55
Parallel Statement Queuing in PDBs
PDB Shares Parallel Server Percentage Parallel Degree Limit
ETL 2 50 4
BI 1 50 8
DW 1 50 16
Limit the Auto-DOP of
the PDB’s parallel
operations.
Limit the total number
of parallel servers the
PDB can use at a
time.
Specifies the probability
that this PDB will get to
launch the next parallel
operation
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 56
Enterprise Manager Cloud Control 12c Discovered Database 12c Targets
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 57
Enterprise Manager Cloud Control 12c CDB Home Page
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 58
Enterprise Manager Cloud Control 12c PDB Home Page
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 59
Enterprise Manager Cloud Control 12c CDB Performance Home Page
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 60
Enterprise Manager Cloud Control 12c ASH Analytics Page showing CDB and PDBs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 61
Enterprise Manager Cloud Control 12c SQL Monitoring Page showing CDB and PDBs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 62
Enterprise Manager Cloud Control 12c Administering Database Users for CDB and PDBs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 63
Enterprise Manager Cloud Control 12c Creating a Common User if CDB is Selected
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 64
Agenda
Rethinking Architecture for the Database Cloud
Multitenant Architecture
Multitenant Capabilities
Multitenant Manageability
Upgrading to Multitenant
Use Cases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 65
01001011001001001001011
10010010101001001010101
0011010100101010010
Plug as PDB
Method
Standalone
01001011001001001001011
10010010101001001010101
0011010100101010010
Data Pump /
Replicate
Method Standalone
Upgrading Non-CDB to a Pluggable DB Two Methods
PDBs
CDB Non-CDBs of version DB 12.1 or later. An XML description is used to create the PDB.
Non-CDBs of version 11.2.0.3. or later. Datafiles will be copied over as part of the migration. Use Datapump or Goldengate
CDBA
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 66
Upgrade to PDB from Oracle Database 11g Plug Non-CDB into CDB
① Upgrade 11.2 database to 12.1 in place
② Place the non CDB into read only mode
③ Connect to non CDB and generate a
description file (manifest)
④ Shutdown the non CDB
⑤ Plug in non CDB to CDB
⑥ Post-plug script to remove redundant
metadata for the Oracle system
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 67
Generate the PDB Manifest Example (part 1)
begin
DBMS_PDB.Describe(
PDB_Descr_File => '/oracle/home/hcmmeta.xml');
end;
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 68
Plug Non-CDB into CDB Example (part 2)
create pluggable database exnoncdb
as clone
using ‘/oracle/home/hcmmeta.xml’
nocopy
tempfile reuse;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
alter pluggable database exnoncdb open;
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 69
Enterprise Manager Cloud Control 12c PDB Provisioning: Migrate non-CDB to PDB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 70
Migrate using Replication
① Provision new PDB from Seed
② Replicate using technologies such as
Oracle GoldenGate or Data Pump
New in 12.1: Full Transportable
Database Export/Import makes
maximum use of transportable
tablespaces in the single expdb and
impdb commands.
(Backported to 11.2.0.3.)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 71
Agenda
Rethinking Architecture for the Database Cloud
Multitenant Architecture
Multitenant Capabilities
Multitenant Manageability
Upgrading to Multitenant
Use Cases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 72
Multitenant Database for Test and Development Fast, flexible copy and snapshot of pluggable databases
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 73
Consolidation of Disparate Applications Shared overhead of memory and processes
System Resources
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 74
Multitenant Database. Perfect for SaaS. Multi-tenancy implemented by the Database, not the Application
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 75
Exploiting the Flexibility of CDBs
Servers can host multiple CDBs
CDBs could be at different patch levels or
require different SLAs e.g.
– Gold CDB requires RAC & Data Guard
– Silver CDB requires only Data Guard
– Bronze requires only backups
PDBs can trivially move from one CDB to
another to take advantage of SLAs and
patches
Gold SLA Sliver SLA
Bronze SLA
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 76
GOLD
SILVER
BRONZE
RAC, Data Guard, Daily Incrementals
Data Guard, Daily Incrementals
Weekly Full Backups
Pick from standard sizes and service levels
Self-Service Database as a Service (DBaaS)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 77
Multitenant Database. Perfect for ISVs. Packaged apps and reference data are easily distributed
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 78
Summary
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 79
Oracle Multitenant Database What customers are saying…
“Oracle Multitenant is a step forward in that it gives us more control over our schemas and
applications; to be able to isolate them, give definitive statements on how performance is working
and be able to manage more databases better.” Carfax
“Oracle Multitenant allows us to consolidate hundreds of databases onto a RAC environment
that guarantees the separation that drove us to put them on separate servers previously.”
Logical Power
“Undoubtedly the number one most compelling feature of Oracle Database 12c is the support for
consolidation. Oracle Multitenant can share memory resources, and make management easier
because it is still a single database instance.” Pythian
“With Oracle Database 12c, we can now copy an entire database from one instance to
another using full transportable export/import. This minimizes the downtime when migrating
clients’ databases.” Accenture
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 82
Isolation and multitenancy
Fast provisioning and cloning
Secure and highly available
No application changes
Manage many as one
Greater resource utilization
Performant and scalable
Lower IT costs
Oracle Multitenant Summary
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 83