msci ppt template corporate 2014 - ad...

©2014 MSCI Inc. All rights reserved. msci.com

Oracle Database 12c - Multitenant Istvan Stahl 26 March 2014

HOUG Conference


� 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

2

Who I am

http://istvanstahl.wordpress.com/


� Part I � The Consolidation Challenge

� Multitenant perspectives

� Part II � Important concepts

� Part III � Best practices

� Fast provisioning&cloning

� Rapid patching

� References � Q/A

3

Agenda


4

Part I.

The Consolidation Challenge

Multitenant perspectives


� 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

5

Multitenant – The Consolidation Challenge


6

Multitenant – Sales perspective


7

Multitenant – DBA perspective


8

How to connect? The PDB is a

Database Service

Is this a CDB? select cdb from

v$database;

Where am I? show con_name

show con_id

Open all PDB? alter pluggable

database all open; 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 :

Multitenant – My perspective


9

Oracle (new features) MySQL SQL Server Hierarchy Instance (CDB) Æ Database (PDB) Æ Schema Instance Æ Database Instance Æ Database Æ Schema

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

Multitenant – High level comparison


10

Part II.

Important concepts


11

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

� 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

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 – Create database


� Undocumented DDL clauses: 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

12

dba_objects.oracle_maintained=Y

Multitenant – Metadata link vs. Object link


� 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

13

Multitenant – AWR

CDB level AWR

PDB level AWR


14

Part II.

Best practices

Fast provisioning&cloning

Rapid patching


� Before you start playing install the latest PSU � Use hugepages on Linux � Set a hard limit for PGA using: pga_aggregate_limit

� Setup instance caging & Resource management � CPU_COUNT + resource_manager_plan � Dynamic between-pluggable resource management

� Threaded kernel mode � init.ora : threaded_execution=TRUE � listener.ora :DEDICATED_THROUGH_BROKER_LISTENER=ON

� „_datafile_write_errors_crash_instance”=FALSE � 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

15

Don’t use THP

Missing I/O management on non-Exa

No more „/ as sysdba”

kill -9

Or logon trigger

Force Logging

Multitenant – Best Practices on Linux


� 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

16

Multitenant – Snapshot Clone using ACFS


17

SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 5 PDBDE10 READ WRITE 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

Multitenant – Snapshot Clone using ACFS example


� Provisioning alternatives: � Oracle ZFS Storage Appliance

� Delphix

� ODBLRA

18

(Oracle Database Backup Logging Recovery Appliance) v2

Multitenant – Snapshot Clone alternatives


SYS@CDBDE01 > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDBDE02 READ WRITE 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.

19

Multitenant – Patching excercise (unplug)


SYS@CDBDE02 > create pluggable database pdbde02 using '/tmp/PDBDE02.xml’NOCOPY; Pluggable database created. SYS@CDBDE02 > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDBDE02 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 CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDBDE02 READ WRITE NO

20

Multitenant – Patching excercise (plugin)


SYS@CDBDE02 > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDBDE02 READ WRITE 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

21

Multitenant – Patching excercise (online move datafiles)


� 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

22

References


23

Q / A

@IstvanStahl [email protected]

msci ppt template corporate 2014 - ad...

Documents