Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Keep your clone's data up-to-date with Refreshable Clones in Autonomous Database

Nilay Panchal
Senior Product Manager

One of the most widely used features in Autonomous Database on Shared Infrastructure (ADB-S) today is the ability to clone your database, no matter how big or small, with little to no effort.

This week, we are souping up ADB's cloning abilities to include Refreshable Clones!

A refreshable clone is a read-only clone that stays "connected" to, and has the ability to pull in data (ie. refresh) from, its source database with a simple click of a button. Until now, if you needed to update your clone's data from its source, you had two options:

  1. Move new data from the source database to the clone (via data pump, database links etc.)
  2. Create a new clone from the source database


Refreshable clones take away the friction of these above options by enabling you to refresh your clone's data by simply hitting a refresh button and providing the source database's timestamp to refresh to (aptly termed the refresh point); the clone automatically looks at your source database's logs and pulls in all the data up to your inputted timestamp.

Here are some example use cases where this clone type can be useful to your team:

  • Providing a routinely updated clone to a different business unit within your organization for reporting and analysis 
  • Creating billing or workload separation for your databases between business units within the organization
  • Providing up-to-date read-only test database environments to internal teams

Of course, I look forward to learning about how our users make use of this functionality in various different scenarios.


With this context, let me walk you through a simple example of using a refreshable clone showing how powerful this feature can be!


Step 1: Setting up an Autonomous Database

After logging into my Oracle Cloud account, I navigate to my existing ADB instance conveniently named "sourceDB". If you missed the memo and haven't created your first Autonomous Database yet, here is a quickstart tutorial on ADB to get you up to speed.

To insert a line of data before we clone, I navigate to my SQL Developer Web (SDW) worksheet via the Tools tab my database's OCI console.


I then create a table named "refreshclonetests" with a single row of data in it, before we proceed to clone the database. Note that I perform this action at 12:20 am UTC.



Step 2: Creating a refreshable clone from the Autonomous Database instance

I now jump into my source database's list of actions to select create clone.


I select the new Refreshable Clone option. Notice the text describing it; A refreshable clone must be refreshed every 7 days or less, else it falls too far out of sync from the source and can no longer be refreshed.

For this example, I name my clone "refreshclone". As easy to remember as it gets.


I proceed to select the number of OCPUs for my refreshable clone; there is no storage selection necessary. Since this is a read-only clone that only brings in data from its source database, the amount of storage selected in TB is automatically the same as that of the source.

There is also no Admin password option for the refreshable clone, as that is taken from the source when refreshed.


Clicking the create clone option starts provisioning the clone. Once provisioned, you can see useful clone information on the OCI console, including the source database that is attached to and the refresh point timestamp of the source to which the clone was refreshed.


Opening up SQL Developer Web once again, via the refreshable clone's OCI console, and querying the database shows the table refreshclonetests that I created in the source, with the single row of data that I inserted.


Step 3: Inserting additional data into the source database

Switching back to the source database SDW worksheet, I insert and commit an additional row into the source database. We now have 2 rows in the source but only a single row in the refreshable clone.

Notice the second row I inserted at 12:38 AM UTC.



Step 4: Refreshing the clone to view new data

Now here's the fun bit; while in my refreshable clone I hit the "Refresh" button in the banner. This banner also displays the date and time before which we must refresh the clone (which is 7 days after the last refresh was performed), before it would lose the ability to sync with the source.

Also, notice the "Disconnect Clone from Source Database" option under "More Actions" here. At any point, you may choose to disconnect your clone from its source making it a regular, standalone, read/write database. This, of course, is a one-way operation and after disconnecting you will no longer be able to refresh data into the clone. If 7 days have gone by and you can no longer refresh your clone, you may still disconnect your clone from its source.


The popup asks for a "refresh point" timestamp of the source database to which we want to refresh. This makes refreshes consistent and intelligible, as it definitively refreshes to an exact timestamp of the source.

Since I inserted the second row into the source at about 12:38 AM UTC, I input 12:39 AM UTC as my refresh point and hit refresh clone.


While the clone is refreshing it goes into the "Updating" state. During a refresh, connections are not dropped and any running queries on the clone simply wait until the refresh is completed. The refresh may take several seconds to several minutes depending on how long it has been since the last refresh and the number of changes that have come in since then.


Once the refresh is completed, we can see exactly what timestamp of the source the clone has been refreshed to in the clone's information.


In the clone's SDW worksheet, we can now run a select query on the "refreshclonetests" table and instead of a single row, we now see both rows of data from the source! The data in the clone has been seamlessly updated to reflect that which is in the source.


Wrapping Up

With this Refreshable Clones feature, you can now keep cloned databases updated without any tedious manual export process. As new data comes into your source database each day, it can easily be refreshed into all its connected refreshable clones with a few button clicks.

For more on Refreshable Clones, refer to the complete documentation. As with everything OCI, your refreshable clone can also be refreshed via simple REST API calls (see API documentation). Better still, with the ability to call cloud REST APIs from a controlling ADB instance, you can quickly schedule automate your data refreshes to fit right into your data pipeline, without having to deploy any servers!



Like what I write? Follow me on the Twitter! 🐦

Join the discussion

Comments ( 2 )
  • Soumya Sundar das Wednesday, October 28, 2020
    Hi Nilay,

    Very informative post. Thanks for sharing. Is it possible to automate the clone refresh process lets say every sunday in a week ?I understand there is an API available "AutonomousDatabaseManualRefresh", but anything to automate the whole process without any manual intervention?

    Would love to hear on this.

  • Nilay Panchal Wednesday, October 28, 2020
    Thanks for the question Soumya, glad this was helpful. Yes, as you found already, you can currently automate the refresh of a refreshable clone by standing up a server/scheduler and calling the "AutonomousDatabaseManualRefresh" REST API in it. You may use any of the native SDKs, that OCI provides, to call the refresh API in your language of choice.

    I recently also blogged about our new PL/SQL SDK which you can run out of an Autonomous Database itself, so no need for a standalone server. You can use the database scheduler to schedule jobs to call the refresh API on your refreshable clone.


    Lastly, we do aim to have an in-built automatic refresh option in refreshable clones that will likely be available sometime next year.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.