There are lots of times when, as a DBA, you are going to get asked to make a copy of an existing database. Until now, creating these non-operational/production environments has been challenging and time consuming process for the technical teams, especially the data warehouse DBAs! What everyone has been waiting for is a way to just “right-click” and deploy an exact copy of an existing data warehouse instance (obviously you can do this programmatically too using the Cloud command line APIs but that’s not the focus of this post) . Well the wait is over….
The new cloning feature of autonomous data warehouse comes to the rescue….in a few mouse clicks it is possible to make exact copies of your data warehouse, either including or excluding the actual data depending on your precise needs. Let’s see how this works…
First step is to log in to your cloud console and go to your Autonomous Database overview screen. In the screen below we have set the “Workload” type filter to only show our Autonomous Data Warehouse instances…
As you can see we have an existing application database called “nodeappDB”. Let’s assume that we have had a request to create a new training instance of this environment so we can run a training event for some key business users. To do this we will use the new cloning feature to make a copy of our existing “nodeappDB” instance. Here we go…
If you click on the little three vertical dots on the right hand side. In the pop-up menu there is a new menu option “Create Clone”, as shown here:
which gives us access to the “Create Clone” feature on the pop-up menu…
Step 1) Now up pops our familiar “Create Autonomous Database” form except this time it says ““Create Autonomous Database Clone” and the first decision we have to make is to determine the type of clone that we want to create! Fortunately, there are two simple options:
Full Clone – this creates a new data warehouse instance complete with all our data and metadata (i.e. the definition of all the database objects such as tables, views etc).
Metadata Clone – this creates a new data warehouse that contains only our source data warehouse’s metadata without the data (i.e. the new autonomous database instance will only contain the definitions of our existing database objects such as tables, views etc).
Since we need to create a training environment the obvious choice is to create a “Full Clone” because the business users will get more from their training workshop if or instance contains a realistic data set. If we were creating a new development-type or testing-type instances then a metadata-only clone would probably be sufficient. So with that done, let’s move to…
Step 2) If we need to, we can change the compartment (and if you have no idea what a compartment is then there is more information about compartments and how to use them here)..for example, you could have a specific compartment setup for “training” which contains all your Autonomous Database training instances. So you can think of compartments as a way of organising, grouping your autonomous database instances. In this example let’s put this new “clone” in the same compartment (LABS) as our existing “nodeappDB” instance:
…with that done, now let’s move on to….
Step 3) Now we can set the Display Name and Database Name for our new instance, as shown below. As of today, a clone does not keep any relationship to its source instance so it might be a good idea to have some sort of naming convention to identify development vs. testing vs. training etc etc instances just to make your life easier in the long run!
Step 4) Next step is to set our CPU and storage resources for our new “clone“, i.e. the number of cores and the amount of storage. Note that if you specify a “Full Clone” in Step 1 then obviously the minimum storage that you can specify here is the actual space used (rounded to the next TB) by your “source” database instance. However, the great thing here is that you can set the resources you need specifically for your clone. In this case our source instance, “nodeappDB”, was configured with 16 OCPUs but as we are creating a “training” instance we can allocate fewer resources i.e. let’s just go with 4 OCPUs….
Step 5) Next we need to set a new administrator password for our cloned database. All the usual password requirements apply to ensure our new instance remains safe and secure. Quick refresher if you are unsure of the rules:
- The database checks for the following requirements when you create or modify passwords:
- The password must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.
- Note, the password limit is shown as 60 characters in some help tooltip popups. Limit passwords to a maximum of 30 characters.
- The password cannot contain the username.
- The password cannot be one of the last four passwords used for the same username.
- The password cannot contain the double quote (“) character.
- The password must not be the same password that is set less than 24 hours ago.
which brings us to the final step…
Step 6) The final step is to set the type of license we want to use for with our new autonomous database. The options are the same as for when you create a completely new autonomous database:
- Bring your existing database software licenses (see here for more details).
- Subscribe to new database software licenses and the database cloud service.
That’s it, we are all done! All we have to do is click the big blue “Create Autonomous Database Clone” at the bottom of the form to start the provisioning process…at which point the create-form will disappear and the following page will be displayed…
….and in a couple of minutes our new autonomous database will be ready for use.
So with a few mouse-clicks we have deployed an exact copy of our product nodeappDB autonomous database ready for our training workshop with our business users.
Don’t forget that you can stop this new created training instance until you are ready to run the training workshop with your business users. When the time arrives it’s a quick click on the “Start” button on the management console and you are up and running in a couple of minutes.
If the above screenshots are a little too fuzzy then you can download a PDF containing all the steps here.
But we are not quite finished if you are a DBA reading this blog post because…
If you are a technical user or a data warehouse DBA or a Cloud DBA then there a couple of additional areas that you will want to consider after creating your newly cloned data warehouse:
- What about all the optimizer statistics from my original data warehouse instance?
- What the resource rules within my newly cloned instance?
What about the Optimizer Statistics for your cloned data warehouse?
Essentially it doesn’t matter which type of clone you decide to create (Full Clone or a Metadata Clone) the optimizer statistics are copied from the source data warehouse to your newly cloned data warehouse. For for a Full Clone, where all the data from your source data warehouse is copied to your newly cloned instance you are ready to roll straight away! With a Metadata Clone, the first data load into a table will force the optimizer to update the statistics based on the new data load.
What about our resource management rules?
During the cloning process for your new data warehouse instance (Full Clone and Metadata Clone), any resource management rules in the source data warehouse that have been changed by the cloud DBA/administrator will be carried over to our newly cloned data warehouse. For more information on setting resource management rules, see Manage Runaway SQL Statements on Autonomous Data Warehouse.
So, what happens next?
Now that your newly cloned data warehouse instance is available you are ready to start connecting your data warehouse and business intelligence tools. If you are new to connecting different tools to autonomous data warehouse then take a look at our guide to Connecting to Autonomous Database.
Click here for more information on Autonomous Database.
