In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to
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.
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.
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”.
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.
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.
SQL> !cat dbren.sql
declare
begin
SQL> !cat dbren.sql
declare
begin
Recent comments
1 year 44 weeks ago
2 years 4 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 14 weeks ago
2 years 35 weeks ago
3 years 3 weeks ago
3 years 33 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago