by Johannes Ahrends

There are several reasons why you might want to duplicate or clone your Oracle database. The most important one is for high availability and disaster recovery reasons. But many, many databases are created for testing, development or benchmarks. Like in StarWars you might need an army of clones – not fighters but databases to fulfill the requirements from various teams.

Duplication

The simplest way to clone a database is to copy the files from one location to another.

With RMAN you can use the following procedure to clone / duplicate your database:

a)      copy the spfile from the source database to the target

b)      create or copy the password file to the target

c)       make the rman backup of your source available for the target

d)      startup the instance with “nomount” option

e)      connect with RMAN to both the source (named target) and the new database (auxiliary) and execute the duplicate command:

rman target sys/manager@PRODDB auxiliary sys/manager@NEWDB

RMAN> DUPLICATE TARGET DATABASE TO <newDB> NOFILENAMECHECK;

This procedure allows you to create an identical copy of your source database on another server. And with the flexibility of RMAN you are able to use a different naming convention for your files as well (e.g. SET NEWNAME FOR DATAFILE).

With Oracle 11g it’s no longer necessary to have a backup available but the source database can be used to create the new database. The preparation tasks are similar to the one above except that task “c” is not required. The command used for the duplication changes to:

rman target sys/manager@PRODDB auxiliary sys/manager@NEWDB

RMAN> DUPLICATE TARGET DATABASE TO <newDB> FROM ACTIVE DATGABASE
     NOFILENAMECHECK;

There are two possible pitfalls with this procedure:

a)      You add some additional load to the source database which is probably your production. So keep an eye on parallelism.

b)      All archived redologs are required for the whole process. So if your archived redologs are removed in the meantime the duplication will fail.

From my perspective this procedure fits the best for the following tasks:

  • Setup of a standby database (you can simply add “FOR STANDBY” to the duplication command for a data guard configuration)
  • Setup of an identical copy for testing (e.g. performance tests, quality assurance)

But if you need to setup a couple of duplicates / clones for testing you need some terabyte of space for all the different versions and if there are requests for staging the space requirements will be overwhelming.

CloneDB

The idea is to avoid the heavy storage usage because most copies of the database do not drastically change the content but only needs read access. Snapshot clones or copy on write (c.o.w.) procedures allow the creation of an entire Oracle database with minimum storage footprint as only changed data is stored in local files while all read access one common storage. The storage manufacturers already provide C.O.W. mechanism for ages (e.g. NetApp, Oracle ZFS) but those are expensive and need some local intervention when a database needs to be cloned.

With Oracle 11g Release 11.2.0.2 (yes with that patch) “clonedb” was announced and is now part of every Oracle Database – without addition costs!!!!

With cloneDB you can create unlimited number of clones of one production database. The source or base for the clones (so the one you are reading from) is an RMAN datafile copy. This copy has to be stored on a Direct NFS storage and all subsequent cloned database directly access this backup. With Oracle 11g you have to relink your database kernel with the direct NFS driver to access the clone and the parameter “clonedb=true” has to be set in the targets spfile but that’s basically all you have to do to get the advantage of a full blown Oracle database with minimum space requirements.

My colleague, Sebastian Winkler, has written a nice document on how to setup CloneDB with Oracle 11g. This can be found on http://www.carajandb.com/en/blogs/blog-swinkler-en/139-clonedb-quick-n-easy.

Oracle 12c Multitenant Database

With the Multitenant Option in Oracle 12c a second approach exists for cloning pluggable databases. Snapshot clones of PDBs can be created instantaneously if the storage is supporting C.O.W. The command is pretty simple:

CREATE PLUGGABLE DATABASE <pdbnew> FROM <sourceDB> SNAPSHOT CLONE;

Great command but what about the “storage supporting C.O.W.”? Do you need again an ZFS or another more or less expensive storage. Not at all: you can simply use ACFS – the Oracle Automatic Storage Management Cluster Filesystem. With Oracle 12c it is supported to use ACFS as filesystem for Oracle data files and this storage supports C.O.W. So even simpler as with the Oracle 11g CloneDB you should now be able to setup as many PDBs in one CDB as you like (up to the current max of 252).

Delphix

Over the last months I had the great pleasure to work with an exciting product in the area of database cloning named “Delphix”. The company is based in Menlo Park California and offers a solution which is easy to use and able to create hundreds of database clones within minutes. The idea behind Delphix is similar to CloneDB except that they do not need a RMAN database copy but only a compressed backup – which saves storage for the initial database setup. Instead of the Direct NFS with Oracle CloneDB they are using there proprietary solution but the goal is the same: setup an Oracle (or Microsoft SQL-Server) database with virtually their own data files but in fact share the files if they are read and only write local copies for the DML and DDL stuff. The advantage of Delphix over CloneDB is the ease of use: it comes with an appliance and with a Web interface. The database is automatically provisioned and started from that interface. The disadvantage is – it’s not free of charge. But from my current view it’s the best cloning software available in the market.

Conclusion

We are still at the very beginning of database cloning but it’s a fantastic opportunity as databases are growing more and more rapidly while development teams are more agile then ever creating new software version nearly every week. Serving those teams with an up to date copy of the database is essential.

There are still some challenges: What about sensitive data? Is it possible to mask or change sensitive data? If a couple of databases are going to be created it might be possible to have a staging database first with masking enabled. Delphix claims that they are able to mask the data “on the fly”. And tools like data redaction or data encryption are available for an individual approach. It’s a huge opportunity and an exciting time for Oracle database administrators.