In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

• quickly build a suite of database objects,
• utilise it for a series of tests,
• then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional license fees, there are some great opportunities there…but that is the topic for another post.

Introduction

Every so often, we get a request to duplicate a database for one of our customers using an Oracle Database Appliance (ODA). The process for doing that is relatively straightforward, but there are a couple of nuances along the way so I thought I’d write it up as a blog post in case it’s of use to others. Obviously, I have obfuscated any customer-specific information to protect their identity.

Configuration

The first nuance is to understand what database is being used as the source for the clone. Generally, a request for cloning will be something like this:

“We are creating a new environment which needs a new database to be set up. Please copy P1_SRV_T and restore as P1_SRV_F”.

Introduction

Every so often, we get a request to duplicate a database for one of our customers using an Oracle Database Appliance (ODA). The process for doing that is relatively straightforward, but there are a couple of nuances along the way so I thought I’d write it up as a blog post in case it’s of use to others. Obviously, I have obfuscated any customer-specific information to protect their identity.

Configuration

The first nuance is to understand what database is being used as the source for the clone. Generally, a request for cloning will be something like this:

“We are creating a new environment which needs a new database to be set up. Please copy P1_SRV_T and restore as P1_SRV_F”.

Sometimes doing a CREATE TABLE AS SELECT is all we need to copy the data from an existing table.  But what if we want more than that ?  What if we really want to clone that table to match the original as closely as possible.  We had a question along these lines on AskTOM today.  A standard CTAS copies the NOT NULL attributes and the data types, but not really much else.  We know that Data Pump will take care of it, but that is more complex than a simple CTAS.

So here is a simple routine to wrap the Data Pump calls so that the CTAS can be achieved with just as simple a command.  A database link pointing back to the same database is all we need.

I mentioned in a previous post that I would be revisiting some of my existing multitenant articles to include some of the features introduced in the 12.1.0.2 patch. Here’s one of them.