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:
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:
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!
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.
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.
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.
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.
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!