Practical Autonomous DB with OCI CLI — Creating Primary and Cross-region Standby

Create and Manage ADB lifecycle using OCI CLI. Automate routine tasks with few simple commands

The world of cloud computing opens several routine tasks to automation, which traditionally were too complex or not possible to automate.

In this article I will demonstrate how to create Oracle Autonomous DB (ADB) primary & cross region standby

Note — All commands here are executed using OCI cloud shell. It should work the same on your local system if CLI is configured correctly.

An excellent way to learn the syntax is by using it in interactive mode. Just type “oci -i” on your cloud shell and start exploring. Read more about it here and in the documentation.

OCI CLI interactive mode

Create Primary ADB

Majority of OCI CLI commands operate on resource OCID’s (Oracle Cloud Identifiers). OCID is a unique resource identifier for every resource, workrequest etc. in OCI. All resources are created within a compartment and hence almost all OCI commands require compartment OCID.

Let’s save common OCID’s in variables for future use. Replace <REPLACE_COMPARTMENT_NAME> with actual compartment name.

# This script assumes unique compartment name within tenancy. 
# Adjust for duplicate compartments accordingly 
# Replace  Compartment name in the line below.
export compname=<REPLACE_COMPARTMENT_NAME>
export comp_id=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[] | select(.name == \"${compname}\") | .id"`
echo $comp_id

The above command queries all compartments using OCI IAM service and extracts user compartment OCID using jq.

If you are not familiar with jq and JMESPath query operators, I suggest you spend some time playing around with it. My go-to place for checking jq syntax is https://www.devtoolsdaily.com/jq_playground/

Next let’s save target subnet OCID for creating ADB with private endpoint. Usually, databases are always created with private endpoint for security reasons.

# This script assumes unique compartment name within tenancy. 
# Adjust for duplicate compartments accordingly 
# Provide name of compartment containing target subnet for ADB creation
export networkcompname=<REPLACE_COMPARTMENT_NAME>
export ncomp_id=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[] | select(.name == \"${networkcompname}\") | .id"`
oci network subnet list --all --compartment-id $ncomp_id --query "data[].[id,\"display-name\"]" --output table

#Search for subnet with name containing string. 
#Replace <REPLACE_SUBNET_NAME> with actual subnet name
export subnetname=<REPLACE_SUBNET_NAME>
export subnet_id=`oci network subnet list --all --compartment-id $ncomp_id --query "data[?contains(\"display-name\",'$subnetname')]"|jq -r ".[].id"`
echo $subnet_id

The first command lists all subnet names and OCID in nice tabular format. It‘s provided here only for understanding. You will need to replace <REPLACE_SUBNET_NAME> with actual subnet name.

Set other parameters for DB creation in variables including password. It always a good idea to parameterize as much as possible instead of hard coding values.

export password=<SUPER_SECRET_PASSWORD>
export dbname=Calvin
export charset=AL32UTF8
export computecount=2
export computemodel=ECPU 
export storageintbs=1
export dbworkload=OLTP
export dispname=CalvintheZorg
export autoscale=TRUE
export mtlsconn=false
export license=LICENSE_INCLUDED
export maintainencesched=REGULAR
export waitforstate=PROVISIONING

Refer ADB CLI documentation for additional details on each parameter

We are all set to create ADB using CLI. Execute this command to create your ADB instance.

oci db autonomous-database create --compartment-id $comp_id \
--admin-password $password --character-set $charset \
--compute-count $computecount --compute-model $computemodel \
--data-storage-size-in-tbs $storageintbs --db-name $dbname \
--db-workload $dbworkload --display-name $dispname \
--is-auto-scaling-enabled $autoscale --is-mtls-connection-required $mtlsconn \
--license-model $license --maintenance-schedule-type $maintainencesched \
--subnet-id $subnet_id --wait-for-state $waitforstate  

ADB Provisioning in progress

Verify DB is in AVAILABLE for standby creation

export ADBocid=<INSERT_ADB_OCID>

while true
do
export status=`oci db autonomous-database get --autonomous-database-id $ADBocid|jq -r   '.data["lifecycle-state"]'`
echo "Current DB State is $status, sleeping for 10 seconds"
sleep 10
done

ADB created with private endpoint

Create Cross-region Standby Database

Let’s create a cross-region standby database to protect our primary ADB. Like primary ADB creation, we will store some OCID’s in variables. Since our primary ADB is created with private endpoint , creating cross-region standby database requires subnet-id of remote region.

Hobbes is always there for Calvin. We will name our standby DB FuzzzyHobbes.

Tip 1 — You can set remote standby display name only via CLI. This option is not available in OCI console.

Tip 2 — All commands are executed from primary region cloud shell. Remote region operations use “region” flag.

Execute the below code block after substituting variable values to create standby ADB

#List all Autonomous DB name and OCID in compartment
export compname=<INSERT_PRIMARY_COMPARTMENT_NAME>
export comp_id=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[] | select(.name == \"${compname}\") | .id"`
oci db autonomous-database list --compartment-id $comp_id --all --query "data[].[id,\"display-name\"]" --output table

#save OCID in variable based on name(case sensitive) string
export ADBName=<INSERT_PRIMARY_DB_NAME>
export ATPocid=`oci db autonomous-database list --compartment-id $comp_id --query "data[?contains(\"display-name\",'$ADBName')]"|jq -r ".[].id"`

#get required destination standby DB region
export DestRegion=<INSERT_STANDBY_CITY_NAME>
export ociregionname=`oci iam region list --query "data[?contains(\"name\",'$DestRegion')]"|jq -r ".[].name"`
echo $ociregionname

#get destination region subnet
export networkcompname=<INSERT_TARGET_NETWORK_COMPARTMENT_NAME>
export ncomp_id=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[]| select(.name == \"${networkcompname}\") | .id"`

#Set remote subnet name
export remotesubnetname=<INSERT_TARGET_SUBNET_NAME>
#save remote subnetocid
# Imp - --region flag to query remote region. 
export remsubnet_ocid=`oci network subnet list --compartment-id $ncomp_id --all --region $ociregionname --query "data[?contains(\"display-name\",'$remotesubnetname')]"|jq -r ".[].id"`

#Uncomment next 3 lines if the target remote region compartment is different from primary DB
#export compname=<INSERT_TARGET_COMPARTMENT_NAME>
#export comp_id=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[] | select(.name == \"${compname}\") | .id"`
#echo $comp_id
#Create ADB DG
export standbyDBName=<INSERT_STANDBY_DB_DISPLAY_NAME>
export remoteADBocid=`oci db autonomous-database create-adb-cross-region-data-guard-details  --compartment-id $comp_id --display-name $standbyDBName --source-id $ATPocid --subnet-id $remsubnet_ocid --region $ociregionname|jq -r ".data.id"`

while true
do
export status=`oci db autonomous-database get --autonomous-database-id $remoteADBocid|jq -r '.data["lifecycle-state"]'`
echo "Current DB State is $status, sleeping for 10 seconds"
echo "Ctrl+C to exit"
sleep 10
done

Our Standby DB is ready in few minutes.

FuzzzyHobbes is on standby in London

You can find the code samples on https://github.com/GitJig/PracticalOCI

The next article in this series will discuss on other daily operations such as stop and start, switchover etc. Hope this has been informative.

Share your thoughts and feedback in the comments. What other operations would you like to automate using CLI? Do you have tricks of your own?