HOUG Conference
Oracle Database 12c - Multitenant
Istvan Stahl
26 March 2014
©2014 MSCI Inc. All rights reserved.
msci.com
Who I am
Started with Oracle & Linux in 1999
Red Hat Certified Engineer – 2007
Oracle Certified Master 10g – 2010
Oracle Certified Master 11g – 2013
(MongoDB DBA - 2013)
http://istvanstahl.wordpress.com – outdated
Worked for:
Manufactrurers: Rába, Pepsi
Consulting: TCS, EDS
Social networking: Virgo Systems / iWiW
Financial Services: Genworth Financials
Working for MSCI
©2014 MSCI Inc. All rights reserved.
msci.com
2
Agenda
Part I
The Consolidation Challenge
Multitenant perspectives
Part II
Important concepts
Part III
Best practices
Fast provisioning&cloning
Rapid patching
References
Q/A
©2014 MSCI Inc. All rights reserved.
msci.com
3
Part I.
The Consolidation Challenge
Multitenant perspectives
©2014 MSCI Inc. All rights reserved.
msci.com
4
Multitenant – The Consolidation Challenge
Schema consolidation prior Oracle Database 12c
Application dependencies
Schema namespace collisions
Complex to operate
Key benefits of Oracle 12c Multitenant architecture
High consolidation density - reduce the number of Configuration Items
Manage many databases as one (backup, maintenance cost etc.)
Rapid patching and upgrades
Fast provisioning and cloning
©2014 MSCI Inc. All rights reserved.
msci.com
5
Multitenant – Sales perspective
©2014 MSCI Inc. All rights reserved.
msci.com
6
Multitenant – DBA perspective
©2014 MSCI Inc. All rights reserved.
msci.com
7
Multitenant – My perspective
Is this a CDB?
select cdb from
v$database;
Open all PDB?
alter pluggable
database all open;
Where am I?
show con_name
show con_id
How to connect?
The PDB is a
Database Service
Switch to a PDB?
@use con_name
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
ORA-17517: Database cloning using storage snapshot failed on file :
©2014 MSCI Inc. All rights reserved.
msci.com
8
Multitenant – High level comparison
Oracle (new features)
MySQL
SQL Server
Hierarchy
Instance (CDB)
Base platform Kernel mode
Process mode or Threaded mode
Threaded mode
Threaded mode
Transaction logging
Instance level
Instance level
Database level
Number of Databases per Instance
252
Unlimited
32.767
Characterset
Database (CDB) attribute
Table attribute
Table attribute
Database (PDB)
Schema
Instance
Database
Instance
Database
©2014 MSCI Inc. All rights reserved.
Schema
msci.com
9
Part II.
Important concepts
©2014 MSCI Inc. All rights reserved.
msci.com
10
Multitenant – Create database
init.ora :
enable_pluggable_database=TRUE
SQL> startup nomount
SQL> CREATE DATABASE ...
...
enable pluggable database;
Database created.
SQL> select status from v$instance;
STATUS
-----------OPEN
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- -------- ---------- ---------2 PDB$SEED READ ONLY NO
Multitenant is an Enterprise Edition Option
Multitenant with one single PDB is called „Singletenant” – this is „free”
We are still able to create Non-CDB databases – long term CDB only
con_id uniquely identifies a PDB within a CDB
con_id=0 – CDB
con_id=1 – CDB$ROOT
con_id=2 – PDB$SEED
Create database command itself creates some of the base structures
SQL> select count(*) from obj$;
COUNT(*)
---------1494
SQL> select ... from obj$ ... ;
TYPE
SHARING
COUNT(*)
----------------------- ------------- ---------INDEX
NONE
681
TABLE
METADATA LINK
570
LOB
NONE
89
TYPE
NONE
67
SEQUENCE
METADATA LINK
48
TABLE
OBJECT LINK
18
CLUSTER
NONE
10
SYNONYM
METADATA LINK
6
UNDEFINED
NONE
2
EDITION
NONE
1
TABLE
NONE
1
NEXT OBJECT
NONE
1
©2014 MSCI Inc. All rights reserved.
msci.com
11
Multitenant – Metadata link vs. Object link
Undocumented DDL clauses:
dba_objects.oracle_maintained=Y
alter session set ”_oracle_script”=TRUE;
create .... [ CONTAINER_DATA ] [ SHARING=OBJECT|METADATA ]
Metadata Link:
Example: OBJ$
Metadata is stored only in the root
Data is stored where it was created (CDB or PDB)
Simplifies patching
Object Link:
Example: DBA_HIST_ACTIVE_SESS_HISTORY
Both metadata & data is kept in the ROOT
PDB has object links pointing to the ROOT
AWR is stored this way
©2014 MSCI Inc. All rights reserved.
msci.com
12
Multitenant – AWR
How to generate AWR for a PDB?
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -U SYS \
-d $ORACLE_HOME/rdbms/admin -l '/tmp' -c 'PDBDE10' -b catblock_output \
my_awrrpt.sql
CDB level AWR
PDB level AWR
©2014 MSCI Inc. All rights reserved.
msci.com
13
Part II.
Best practices
Fast provisioning&cloning
Rapid patching
©2014 MSCI Inc. All rights reserved.
msci.com
14
Multitenant – Best Practices on Linux
Before you start playing install the latest PSU
Use hugepages on Linux
Don’t use THP
Set a hard limit for PGA using: pga_aggregate_limit
Missing I/O
management
on non-Exa
Setup instance caging & Resource management
CPU_COUNT + resource_manager_plan
Dynamic between-pluggable resource management
No more
„/ as sysdba” kill -9
Threaded kernel mode
init.ora : threaded_execution=TRUE
listener.ora :DEDICATED_THROUGH_BROKER_LISTENER=ON
Or logon
„_datafile_write_errors_crash_instance”=FALSE
trigger
Create additional service to autostart PDBs
Application side problems might still impact all databases (logon storm)
Profile limits, Application review
DW&OLTP in the same CDB?
Patching: create a new CDB unplug/plug the PDB’s one by one
Force
Logging
©2014 MSCI Inc. All rights reserved.
msci.com
15
Multitenant – Snapshot Clone using ACFS
History:
11.2.0.1 – ACFS „General Purpose” filesystem
11.2.0.3 – ACFS read/write Snapshots
12.1.0.1 – ACFS support for all database files
Requirements:
ASM&ADVM compatibility: 11.2.0.3.0
©2014 MSCI Inc. All rights reserved.
msci.com
16
Multitenant – Snapshot Clone using ACFS example
SQL> show pdbs
CON_ID
---------2
5
CON_NAME
-----------------------------PDB$SEED
PDBDE10
OPEN MODE
---------READ ONLY
READ WRITE
RESTRICTED
---------NO
NO
SQL > select name from v$datafile where con_id=5;
NAME
-------------------------------------------------------------------------------/u01/oradata/PDBDE10/system.266.842116047
/u01/oradata/PDBDE10/sysaux.265.842116047
SYS@CDBDE01 > alter pluggable database pdbde10 open read only force;
Pluggable database altered.
SYS@CDBDE01 > create pluggable database pdbde11 from pdbde10
file_name_convert=('/u01/oradata/PDBDE10','/u01/oradata/PDBDE11') snapshot copy;
Pluggable database created.
SYS@CDBDE01 > !ls -al /u01/oradata/PDBDE11
total 20504
drwxr-x---. 2 oracle sysdba
4096 Mar 18 16:02 .
drwxr-xr-x. 6 oracle oinstall
4096 Mar 18 09:44 ..
-rw-r-----. 1 oracle sysdba
20979712 Mar 18 16:02 pdbseed_temp01.dbf
lrwxrwxrwx. 1 oracle sysdba
117 Mar 18 16:02 sysaux.265.842116047 ->
/u01/oradata/.ACFS/snaps/F4E4A415B1101AF1E0435EC07F0A5C6D/PDBDE10/sysaux.265.842116047
lrwxrwxrwx. 1 oracle sysdba
117 Mar 18 16:02 system.266.842116047 ->
/u01/oradata/.ACFS/snaps/F4E4A415B1101AF1E0435EC07F0A5C6D/PDBDE10/system.266.842116047
©2014 MSCI Inc. All rights reserved.
msci.com
17
Multitenant – Snapshot Clone alternatives
Provisioning alternatives:
Oracle ZFS Storage Appliance
Delphix
ODBLRA (Oracle Database Backup Logging Recovery Appliance) v2
©2014 MSCI Inc. All rights reserved.
msci.com
18
Multitenant – Patching excercise (unplug)
SYS@CDBDE01 > show pdbs
CON_ID
---------2
4
CON_NAME
-----------------------------PDB$SEED
PDBDE02
OPEN MODE
---------READ ONLY
READ WRITE
RESTRICTED
---------NO
NO
SYS@CDBDE01 > select name from v$datafile where con_id=4;
NAME
------------------------------------------------------------------------------+DATA01/CDBDE01/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/system.303.842519799
+DATA01/CDBDE01/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/sysaux.304.842519799
SYS@CDBDE01 > alter pluggable database pdbde02 close;
Pluggable database altered.
SYS@CDBDE01 > alter pluggable database pdbde02 unplug into '/tmp/PDBDE02.xml';
Pluggable database altered.
SYS@CDBDE01 > drop pluggable database pdbde02 keep datafiles;
Pluggable database dropped.
©2014 MSCI Inc. All rights reserved.
msci.com
19
Multitenant – Patching excercise (plugin)
SYS@CDBDE02 > create pluggable database pdbde02 using '/tmp/PDBDE02.xml’NOCOPY;
Pluggable database created.
SYS@CDBDE02 > show pdbs
CON_ID
---------2
4
CON_NAME
-----------------------------PDB$SEED
PDBDE02
OPEN MODE RESTRICTED
---------- ---------READ ONLY NO
MOUNTED
SYS@CDBDE02 > alter pluggable database pdbde02 open;
Pluggable database altered.
SYS@CDBDE02 > select name from v$datafile where con_id=4;
NAME
------------------------------------------------------------------------------+DATA01/CDBDE01/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/system.303.842519799
+DATA01/CDBDE01/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/sysaux.304.842519799
SYS@CDBDE02 > show pdbs
CON_ID
---------2
4
CON_NAME
-----------------------------PDB$SEED
PDBDE02
OPEN MODE
---------READ ONLY
READ WRITE
RESTRICTED
---------NO
NO
©2014 MSCI Inc. All rights reserved.
msci.com
20
Multitenant – Patching excercise (online move datafiles)
SYS@CDBDE02 > show pdbs
CON_ID
---------2
4
CON_NAME
-----------------------------PDB$SEED
PDBDE02
OPEN MODE
---------READ ONLY
READ WRITE
RESTRICTED
---------NO
NO
SYS@CDBDE02 > alter system set container=pdbde02;
Session altered.
SYS@CDBDE02 > alter database move datafile
'+DATA01/CDBDE01/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/system.303.842519799' to
'+DATA01';
Database altered.
SYS@CDBDE02 > alter database move datafile
'+DATA01/CDBDE01/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/sysaux.304.842519799' to
'+DATA01';
Database altered.
SYS@CDBDE02 > select name from v$datafile where con_id=4;
NAME
-------------------------------------------------------------------------------+DATA01/CDBDE02/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/system.310.842603037
+DATA01/CDBDE02/F4DEF76F16C65490E0435EC07F0A9A96/DATAFILE/sysaux.303.842603095
©2014 MSCI Inc. All rights reserved.
msci.com
21
References
Whitepapers:
Consolidation Best Practices: Oracle Database 12c plugs you into the cloud
Oracle Multitenant
How to Accelerate Test and Development Through Rapid Cloning of Production
Databases and Operating Environments
MOS Notes:
Multitenant best Practice and Known issues (Doc ID 1604135.1)
Oracle Online Documentation
Oracle University trainings
©2014 MSCI Inc. All rights reserved.
msci.com
22
Q/A
@IstvanStahl
[email protected]
©2014 MSCI Inc. All rights reserved.
msci.com
23
© Copyright 2024 ExpyDoc
