Auto-refreshing Oracle Autonomous Database Refreshable Clones

Understanding the role of Refreshable Clones

Autonomous database Refreshable clones are a very useful feature to maintain a clone of an Oracle Autonomous Database in Oracle Cloud Infrastructure. If you want to know more about the Autonomous Database, please have a look here.

Clones can serve various use cases, from having a test database with the same data as the source one, to monitoring & re-bill costs among multiple entities that want to access the same data in a read-only mode. Before venturing into other use cases such as Disaster Recovery, or real-time replication, consider other replication strategies like Dataguard, Active Dataguard, or GoldenGate. There are also some limitations that you might read in the documentation notes.

The beauty of this feature is rooted in its remarkable simplicity. We call it a refreshable clone because you can simply refresh the database clone's data so that your test data does not become obsolete.

When I say simple, it means you just need a couple of steps to update the clone's data (or metadata) (see link).

Why auto-refreshing?

As of today, there is no way to automate the refreshing process from directly the OCI User Interface. The second pain point is that failing to perform a clone refresh within a week renders your clone non-refreshable.Said differently, your clone's data won't be able to reflect the changes happening in your source database. In the following paragraphs, I will share how one can overcome those challenges.

Working with OCI CLI

We will assume that you have created a serverless autonomous database and its refreshable clone from the UI. This takes less than 10 minutes and a few clicks.

From there we will use the OCI CLI as it simplifies the interaction with the OCI API.

For simplicity, we will start by using the Cloud Shell to demonstrate the script, but Cloud Shell is not intended to grant you root access and access to the crontab. That means you'll need to access a virtual machine on which you installed the OCI CLI in order to both execute the script and automate its execution via cronjobs.

First, let's check when was our last clone's date/time before refreshing it. We can find the information on the clone's information page (Autonomous Database Details):

Now we must find out how to refresh it without hitting the refresh button. We open the cloud shell or access our OCI CLI, and we'll use the handy OCI shell's interactive mode. The interactive mode is a great way to learn what is the right command to use and what are the right parameters, including parameters specific to your environment, because it offers auto-completion (discover the interactive mode here).

To enter the interactive mode, we type the following :

walid_haje@cloudshell:~ (eu-paris-1)$ oci -i

By using tab key, Cloud Shell will help us find the right commands and even custom parameters. If any doubt we just type --help and it will output a command.

The command to refresh the autonomous database clone is the following :

> oci db autonomous-database manual-refresh --autonomous-database-id <yourcloneOCID> --time-refresh-cutoff "2023-08-27 11:00"

Make sure you replace the last parameter with the clone's OCID (its unique identifier that can be found in the console), and also use the --time-refresh-cutoff parameter with a cutoff time inferior or current to the current timestamp in UTC.

If all goes well, we shouldn't receive any error message, and the clone's refresh point timestamp should immediately change both on the UI (see screenshot earlier) and from the CLI.

Auto-refresh script

Now we need to automate the previously tested command by creating a script in the home directory that will be executed regularly.

First we need to identify the path of the OCI CLI by executing the following CLI command :

which oci

In my case it returns : /home/oci/bin/oci

Then we create the script :

cd ~

vim auto-refresh-clone.sh

Here is the content of the script :

#!/bin/bash
current_date_time=$(/usr/bin/date +"%Y-%m-%d %H:%M")
current_date_timeC="$(date -d "1 minute ago" +"%Y-%m-%d %H:%M")"
/home/oci/bin/oci db autonomous-database manual-refresh --autonomous-database-id <yourcloneOCID> --time-refresh-cutoff "${current_date_timeC}"

Quick explanation: current_date_time is the current time in the following format : 2023-08-27 18:31

<yourcloneOCID> should be replaced again with the clone's OCID.

Why that format ? This is one of the accepted format of the manual-refresh command. In fact, we are specifying a time that corresponds to the timestamp to which the Autonomous Database refreshable clone will be refreshed.

Quick side note here : Initially I didn't want to specify any timestamp, I just wanted to refresh it at the time of the command call. However, for a mysterious reason, without specifying a --time-refresh-cutoff , the whole command failed with an error message specifying that it was a mandatory parameter (which is not described according to https://docs.oracle.com/en-us/iaas/tools/oci-cli/3.31.1/oci_cli_docs/cmdref/db/autonomous-database/manual-refresh.html ).

Not only do you need to specify a timestamp, but that timestamp must be strictly inferior to the current timestamp. That is why I created a corrected timestamp which is one minute before the current timestamp (variable current_date_timeC).

From there we script the usual manual-refresh command.

Important note : ideally you'll want to dynamically find the oci command's path in a variable so that the script can be later executed from elsewhere. For example in the OCI compute instance, my OCI directory will rather be /usr/loca/bin/oci .

That's it for the script, we save it and make it executable ( chmod +x auto-refresh-clone.sh ).

Then we execute the script, first manually, in order to observe and verify its result. Before that, we try leaving half of our screen with the clone database UI to watch it updating in real-time, the left logo turning orange with an updating status :

The refresh point is now up-to-date, which can also be verified by looking at the Refresh point date & time.

Another way to check is obviously to look at the JSON output of the manual-refresh command and check specifically the "time-of-last-refresh" value.

Now we have a working script with dynamic parameters, but that can only be called manually from the OCI Cloud Shell. We need to move it to a compute instance in order to automate its execution via a cronjob. We can download the script by writing down the file's name and downloading it from the OCI Shell :

Automating script execution

The only step left is to create a cron job that will execute the script regularly. To be more specific, the script only makes sense if we execute it at most once every 6 days to make sure it keeps refreshable and refreshed - remember our initial problem statement. We will re-create (or upload) the script into our compute instance on which we have root / crontab access.
For the sake of demonstrating the concept, I decided to run the script every hour by entering the following instructions in the crontab file (command : crontab -e ) and perform a sudo service crond restart.

0 * * * * /home/opc/auto-refresh-clone.sh

I verify that the cronjob is set up by using crontab -l command. If you need a refresher on cronjobs, here is an interesting documentation.

To make sure it was running I added a second line to the crontab that runs every 15 minutes (or every hour if you're more patient :-) , and displays a message

0 * * * * /home/opc/auto-refresh-clone.sh
*/15 * * * * echo "This crontab entry runs every 15 minutes"

...which is well executed by checking the following logs :

That's it, we verify later that my clone got refreshed as expected and we're done !

We now have an ever-refreshing clone of Autonomous database.

I hope you'll find this blog post useful. Thanks again for reading me !

Walid Hajeri

Disclaimer : Views are my own , none of the ideas expressed in this post are shared, supported, or endorsed in any manner by my current employer.

Photo credits : Alesia Kozik: https://www.pexels.com/photo/pile-of-pumpkins-6022234/