using oracle multitenant to efficiently manage development and test databases

Using Oracle Multitenant to efficiently manage development and test databases

Marc Fielding

Alex Gorbachev

October 2014

About Marc • Principal Consultant with

Pythian’s Advanced Technology Group

• 13+ years Oracle production systems experience starting with Oracle 7

• Blogger and conference presenter pythian.com/news/author/fielding

• Occasionally on twitter: @mfild

2 © 2014 Pythian

About Alex

• CTO, The Pythian Group

• Blogger

• OakTable Network member

• Oracle ACE Director

• BattleAgainstAnyGuess.com

© 2014 Pythian 3

© 2014 Pythian 4

WHO IS PYTHIAN?

• 200+ leading brands trust us to keep their systems fast, up & secure

• Utterly elite DBA & SysAdmin workforce, 9 Oracle ACEs, 2 ACE

directors, 5 Microsoft MVPs, 1 Cloudera Champion of Big Data

• Oracle, SQL Server, MySQL, Netezza, Cassandra, Hadoop and

MongoDB plus UNIX SysAdmin and Apps DBA

• Big Data Services counter includes architects, R&D, data science

and operations capabilities in one easy to buy vehicle

• Zero lock-in, utility billing model, easily blended into existing teams

38% Pythian has grown an average of 38% every year for

the past five years

345 Pythian employs more than 345 leading minds in

26 countries worldwide.

The Problem © 2014 Pythian 5

Photo: J. McIntyre via Flickr

Database challenges

© 2014 Pythian 6 Photo: lzee~by~the~Sea via Flickr

What we need • Higher density

– Better use of infrastructure investment

– Better use of software licenses

• Less overhead – Automated, repeatable processes

– Self-service

• Better management – Usage tracking

© 2014 Pythian 7

Enabling technologies: Oracle Multitenant

© 2014 Pythian 8

Oracle Instance

PDB

Shared SGA

PDB PDB PDB PDB PDB

PDB PDB PDB PDB PDB PDB

PDB PDB PDB PDB PDB PDB

The storage problem

© 2014 Pythian 9 Photo: V. Matassa via Flickr

Enabling technologies: storage cloning

© 2014 Pythian 10

Master

“Golden”

Image

…

Incremental

Change

Records

…

…

…

…

…

Other resources

• CPU time

• Memory

• I/O capacity

• Network

© 2014 Pythian 11

Photo: kc_jake via Flickr

Resource management • Database resource manager

– CPU priority and limits

– Parallel query restrictions

– Restrict large, runaway queries

• I/O restrictions – IORM in Exadata

– Storage and OS-level

• Oracle multitenant limits – Storage space

– Temp space

• Accounting and costing

© 2014 Pythian 12

Backup and recovery

• Even if it’s dev/test, data matters

• RMAN backups

– At the PDB and CDB levels

• Storage clones

– Don’t forget I/O to move to other device

• Back up after provisioning

– Cloned PDBs are unrecoverable before backups

© 2014 Pythian 13

High availability

© 2014 Pythian 14 Photo: Ben Short via Flickr

Self service • Obvious next step in reducing

overhead

• Developers managing their own DB

• OEM implementation exists

• Business logic = custom

• Example Oracle APEX app – Multitenant Self-Service

Provisioning

© 2014 Pythian 15

Photo: D.Alves via flickr

Oracle MSSP application • Built using Oracle APEX

• Currently in beta

• Example only

• Only two user types

• Limited resource management – CPU priority

– Maximum storage size

• Cloning support (but not enforcement)

© 2014 Pythian 16

Data cloning methods • Single command:

– create pluggable database db1_clone1 from

db1 snapshot copy;

• Actual actions depend on filesystem type – ZFS appliance and NetApp

• Database-initiated SNMP calls with stored credentials

– ACFS

• Requires full clusterware stack

– CloneDB on NFS or local filesystem

© 2014 Pythian 17

Cloning via CloneDB • Existed since 11.2.0.2 for non-PDB

• In 11gR2: direct NFS only, backed by a full RMAN backup

• In 12.1.0.2: supports local (non-NFS) filesystems, and RMAN backup not required

• Uses filesystem-level sparse files

• Just one DB change – alter system set clonedb=true;

© 2014 Pythian 18

Cloning with ACFS • Lots of sanity checks (good!)

• Put source DB into read-only mode if not already – Sessions doing data changes get ORA-16000 error

• Invokes acfsutil via oradism shadow process acfsutil snap create -w YYYYYYYY /acfsmounts/snapclones

• Creates symbolic links for each file ln –s /acfsmounts/snapclones/.ACFS/snaps/XXXXX/datafile/o1_mf_system_xxxx_.dbf /acfsmounts/snapclones/DEMOCDB/YYYYY/datafile/o1_mf_system_yyyy_.dbf

• Recreates the tempfile

• Creates the new pluggable database

• Enable writes to source DB (if enabled before)

© 2014 Pythian 19

Manual ACFS cloning • Close and reopen source database read only

• Run clone command manually acfsutil snap create -w testclone1 /acfsmounts/snapclones

mkdir -p /acfsmounts/snapclones/DEMOCDB/testclone1/datafile

• Set up symbolic links for datafile in /acfsmounts/snapclones/.ACFS/snaps/XXXXX//datafile/*;

do ln -s $datafile /acfsmounts/snapclones/DEMOCDB/testclone1/datafile/$(basename $datafile);

done

• Plug back in as a clone using new location create pluggable database newpdb from mypdb file_name_convert=('mypdb', 'newpdb') nocopy;

• Close and reopen source database

• Open cloned database

© 2014 Pythian 20

Issues and recommendations • Oracle managed files

• ACFS clones entire mountpoints

• Error messages are very generic ORA-65169: error encountered while attempting to copy file

ORA-17517: Database cloning using storage snapshot failed on file

Check tracefile, and ACFS log at $CRS_HOME/log/(hostname)/acfs/commands/acfscmds.log

• Issues with oradism process

© 2014 Pythian 21

A sample implementation • VirtualBox VM running Oracle Linux 6

• Oracle Grid Infrastructure 12.1.0.2 with 1 node

• ACFS filesystem mounted at /acfsmounts/snapclones

• Oracle 12.1.0.2 (non-RAC)

• CDB called DEMOCDB

• Oracle REST Data Services 2.0.9 (aka APEX listener) in standalone mode

• Oracle Multitenant Self-Service Provisioning, beta 2

© 2014 Pythian 24

Trying it out yourself

• VM or dedicated hardware

• OS: Oracle Linux 6 recommended

• Latest Oracle 12c software

• CloneDB or ACFS storage

• In the cloud: ACFS clusterware complications

© 2014 Pythian 26

Wrapping up • More databases = need a smarter approach

– Less infrastructure

– Less administrative effort

• Key enabling technologies – Oracle Multitenant

– Storage cloning

– Self-service

• Tailored to business requirements

• Download and try it out yourself

© 2014 Pythian 27

Thanks and Q&A

© 2014 Pythian 28

[email protected]

[email protected]

1-877-PYTHIAN

pythian.com/blog

http://is.gd/PythianFacebook

@mfild @alexgorbachev @pythian

http://linkedin.com/company/pythian

We’re hiring!

pythian.com/careers

Self-service provisioning app

download on OTN:

tinyurl.com/otndownload

mailto:[email protected]

mailto:[email protected]



http://linkedin.com/company/pythian

using oracle multitenant to efficiently manage development and test databases

Technology

oracle apexcurrently

oracle aces

oracle multitenantto

oracle mssp applicationbuilt

conference presenter

years345 pythian employs

data science

storage problem