How to Remotely start and stop your Autonomous Database with REST

Posted on Author Sean Stacey
Image by PublicDomainPictures from Pixabay

TL;DR In this post we’ll see how easy it is to use a shell script to start or stop your Autonomous Database remotely using a REST call…

If you just care about the script you can cut to the chase by clicking this link

Background

Oracle Cloud Ops has a policy in place for Always-Free Cloud Services where Autonomous database services are automatically paused if they are not accessed in 7 days. No data is ever lost, all backups remain intact, and most importantly, the services are NOT terminated. The database is simply paused to free up system resources. If you do not restart the paused database after 90 days cumulative, the database could then possibly be terminated.

Oracle does a great job of communicating with you when the service is about to be paused and will keep you notified by email about the pending database pause. To stop the service from being paused (or stopped) you just need to log into the database or access the database actions page.

If your database does happen to get paused, restarting the database is never a problem either, and it’s very quick and easy to start. You simply log into your cloud account, navigate to your database(s) and restart your database service. But sometimes it would be nice to simply run a script to get the database restarted. The good news is- you can! You could even choose to automate this task using a scheduler like crontab or launchd.

So in this post you’ll see how easy it is to use a shell script to start your Autonomous Database remotely using a REST call…

Some time ago there was a bash script published by Oracle called oci-curl. I learned about the script from a very helpful blog post written by Yasin Baskan: https://blogs.oracle.com/datawarehousing/managing-autonomous-data-warehouse-using-oci-curl . Yasin’s excellent post explains how to configure and run the script and even included a link to download the script from the Oracle online documentation. But recently, the script appears to have disappeared and I am no longer able to locate the code, even after a pretty extensive search. I imagine this could be because of the introduction of the very powerful oci-cli utility, but I still see the merit in being able to perform this task with a simple shell script as I find it’s more portable for my purpose.

In my research I was able to locate a blog post: https://www.ateam-oracle.com/oracle-cloud-infrastructure-oci-rest-call-walkthrough-with-curl by Michael Shanley of the Oracle A-Team that covers the fundamentals of the REST service that the oci-curl script was automating. Michael’s post provides a great tutorial on accessing the Oracle Cloud Infrastructure resources with a REST request, and includes all the tricky stuff that the script will need, but it does not specifically address accessing an Oracle Autonomous Database.

Cutting to the chase…

Using the primo information provided by Michael and Yasin I have created my own script that I’ve called initADB.sh to start or stop my Autonomous Database instances using a REST request. Armed with this script it’s pretty easy to perform other tasks too. You will also notice that the script can also start multiple database instances within a cloud tenancy.

So at a high level, here’s the steps to set this up for your own environment:

  1. Download the script
  2. Get the API Key for your cloud tenancy
  3. Get your configuration file
  4. Add your tenancy information to the script
  5. Add the location of your wallet to the script
  6. Add your Database Service Name to the script
  7. Add the OCID for your database to the script
  8. Set the script to Start or Stop your database
  9. Run your script
  10. Interpreting the output

1. Download the Script

The script is available to be downloaded from GitHub –

https://github.com/seantstacey/scripts/blob/main/initADB.sh

Here’s the script in it’s entirety, if you don’t have a GitHub account.

Btw – Don’t panic if the script looks too complex. You only need to change 7 lines of code.

Copy 


#!/bin/bash
## ##################################################################################
## SAMPLE Entries:
## user=ocid1.user.oc1..aaaaaaaa7dxxxxx-SAMPLE-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxbbbbbfake
## fingerprint=99:31:6d:c5:SA:MP:LE:f8:d3:cb:24:ef:3c:c8:fa:ke
## tenancy=ocid1.tenancy.oc1..aaa-SAMPLE-t6vmlbfkgx4jgeo5yefakeskrtdfewshgfdqwir3r4ipzfake
## region=us-ashburn-1
## key_file=/Users/homedir/adb_db_key.pem
##
##  <Cut-and-paste your tenancy values here>

## Add a value for adbList for each DB instance: "[ServiceName]|[Database-OCID]"
## adbList="ADBDEMO|ocid1.autonomousdatabase.oc1.iad.abcwcljaa-SAMPLE-ddefakef12345123456789abcdefghijklmfake 
##          DB21C|ocid1.autonomousdatabase.oc1.iad.abewcljaaab-SAMPLE-eefake12345678912rqponmlkjihgfedcbafake
           "
##
   adbList="<database service name>|<database OCID>"
##   
## Set the following variable to "start" or "stop" to START or STOP your instance:
start_or_stop="start"
#
## OPTIONAL- Control start | stop from the command-line when calling the script:
# start_or_stop=$1  
##
## OPTIONAL- Uncomment the following line to see a verbose version of the script when it runs
# set -x
## #################################################################################
## NO NEED TO MODIFY ANYTHING BELOW THIS LINE...
        
# Create host string
host="database.$region.oraclecloud.com"

# Additional headers required for a POST/PUT request
content_sha256="$(openssl dgst -binary -sha256 < /dev/null | openssl enc -e -base64)";
content_sha256_header="x-content-sha256: $content_sha256"
content_length="0"
content_length_header="content-length: $content_length"
headers="(request-target) date host"
# add on the extra fields required for a POST/PUT
headers=$headers" x-content-sha256 content-type content-length"
content_type_header="content-type: application/json";

date=`date -u "+%a, %d %h %Y %H:%M:%S GMT"`
date_header="date: $date"
host_header="host: $host"

for srvnm in $adbList
do
  printf "\n\n===========================================================================================\n"
  printf "\n ADB ServiceName: ${srvnm%%|*} "
  printf "\n Action: ${start_or_stop} \n"

  rest_api="/20160918/autonomousDatabases/${srvnm##*|}/actions/${start_or_stop}"

  # Uncomment the following line to see the REST-ENDPOINT:
  # printf "\n Rest API is: \n$rest_api\n"

  request_target="(request-target): post $rest_api"
  signing_string="$request_target\n$date_header\n$host_header"
  signing_string="$signing_string\n$content_sha256_header\n$content_type_header\n$content_length_header"
  sig=`printf '%b' "$signing_string" | openssl dgst -sha256 -sign $key_file | openssl enc -e -base64 | tr -d '\n'`

  printf " ==============================\n"

  curl -X POST --data-binary -sS https://$host$rest_api -H "date: $date" -H "x-content-sha256: $content_sha256" -H "content-type: application/json" -H "content-length: $content_length" -H "Authorization: Signature version=\"1\",keyId=\"$tenancy/$user/$fingerprint\",algorithm=\"rsa-sha256\",headers=\"$headers\",signature=\"$sig\""

done
printf "\n\n============================= $date =============================== \n\n"

2. Get the API Key for your cloud tenancy.

You will require an API Key to run the script. An API key is an RSA key pair in PEM format used for signing API requests.

If you already have an existing API Key, you can use it. Otherwise, generating a new key from the console is really easy too if you don’t already have one.

To do this:

  • go to the hamburger menu in the top-left hand corner of your screen
  • navigate to Identity & Security and then navigate to Users from the main menu window
  • double-click on a Non-Federated user that you will be using to run the script. (i.e. the username that is not prefixed by “oracleidentitycloudservice/“ –or– “Federated” = “No”)
  • Scroll down and navigate to the Resources menu on the lower left hand side of your console
  • Click on the API Keys menu item
  • Click on the Add button to generate the key. Make note of the directory where you download file. Also take note of the information about changing the permissions of the file.
  • After clicking the Add button you will see a pop-up with a Configuration File Preview. Do NOT Close the pop-up window – move onto step 3.

NOTE: Information on uploading or creating an API Key is available in the online Oracle documentation –

https://docs.oracle.com/en-us/iaas/Content/API/Concepts/apisigningkey.htm

3. Get your configuration file.

All the information the script needs for connecting to your tenancy is available in the Configuration File Preview pop-up. It’s simply a case of copying the contents of this window and pasting it into your script –

Configuration File Preview

Click the Copy link in the bottom right hand corner of the pop-up window to copy the contents into your clipboard. This information will be inserted directly into the script as is. Make sure you have copied this information before you hit the Close button.

4. Add your tenancy information to the script.


Open the script in your favorite editor and navigate to the line with the following entry (this should be line 11 in the script) –

   <Cut-and-paste your tenancy values here>

Paste the contents of the configuration file below this line in your script as is.

5. Add the location of your wallet to the script.


The only manual modification for the configuration file contents you will need to make is to enter the name and location of where you downloaded your private key file.

Modify the line that begins with:

key_file=<path to your private keyfile> # TODO

The resulting entry should look something like the following:

key_file=/Users/jsmith/adb_api_key.pem

6. Add your Database Service Name to the script.


The Database Service Name is used by the script to identify the names for the Databases you will be operating on. The API service does not dependent on this value and it can be anything that you would like to use to identify your databases. This is the name(s) used by the script to map your OCID as well as display information about the database services when you run the script.

Add the names to your script at the line with the following entry –

adbList="<database service name>|<database OCID>"

The entry should look something like this –

adbList="ATPSEAN|<database OCID>"

or even –

adbList="  
         ATPSEAN|<database OCID>
         ADWSEAN|<database OCID>
         "  

7. Add the OCID for your database to the Script

The next step is to add the OCID for your database services. As you’ll see, obtaining this is very easy as it is displayed in your Autonomous Database Details screen.

To do this:

  • go to the hamburger menu in the top-left hand corner of your screen
  • navigate to Oracle Database and navigate to Autonomous Database from the main menu window
  • double-click on the database name for the database you want to control with the script.
  • double-click on the copy shortcut beside your OCID this will place the OCID into your clipboard

You’re now ready to add the OCID to your script.

This is the second piece of the adbList entry that needs to be updated. You simply append the ocid to the adbList so it appears as below:

The completed entry should look like this –

adbList="ATPSEAN|ocid1.autonomousdatabase.oc1.iad.abewcljaaab-SAMPLE-eefake12345678912rqponmlkjihgfedcbafake"

or even –

adbList="  
         ATPSEAN|ocid1.autonomousdatabase.oc1.iad.abewcljaaab-SAMPLE-eefake12345678912rqponmlkjihgfedcbafake
         ADWSEAN|ocid1.autonomousdatabase.oc1.iad.abcwcljaa-SAMPLE-ddefakef12345123456789abcdefghijklmfake
         "  

Note: the entire string is enclosed within double-quotes.

8. Set the script to Start or Stop your database

By default, the script is configured to START your databases. But if you would prefer to use the script to STOP your databases, you can control this by changing the start_or_stop parameter to “stop” .

There is also an option to pass this setting as a parameter when you call the script, to switch to that mode, simply uncomment the line with the entry- # start_or_stop = $1

The script will operate on all database instances in the same manner. In other words it will either start or stop all of your databases.

9. Run your script.

Ready for the fun stuff? One last thing before you run the script, you will need to make sure it’s executable. I’m running on a mac, so I simply use:

chmod +x initADB.sh

To run the script, simply type:

./initADB.sh

or if you set it up to pass “start” or “stop” as a parameter:

./initADB.sh start

–or–

./initADB.sh stop

10. Interpreting the output

The output displayed by the script can be a little confusing for the uninitiated, but here’s what you should expect to see (in this example I have captured a “start” operation, the same would be present for a “stop” operation:

If the operation was successfully submitted-

You will see many lines of JSON displayed (around 200 lines per database) on your screen, but the line you’re looking for is:
“lifecycleState” : “STARTING”,

An easier way to verify if it succeeded is to simply rerun the script… it’s an idempotent operation so can be called many times with out any negative impact.

If the operation was accepted and the database is starting, when you run the script twice in succession you will see something like the following-

=====================================================================================

ADB ServiceName: ATPSEANS 
 Action: start 
 ==============================
{
  "code" : "IncorrectState",
"message" : "The operation cannot be performed because the Autonomous Database with ID ocid1.autonomousdatabase.oc1.iad.abcwcljaaabbbcccdddefakef123456789123456789abcdefghijklmfake is in the STARTING state."
}

======================================================================================

If the database is already running and does not need to be started-

======================================================================================
ADB ServiceName: ADBDEMO

Rest API is:
/20160918/autonomousDatabases/ocid1.autonomousdatabase.oc1.iad.abcwcljaaabbbcccdddefakef123456789123456789abcdefghijklmfake/actions/start
==============================
{
"code" : "IncorrectState",
"message" : "The operation cannot be performed because the Autonomous Database with ID ocid1.autonomousdatabase.oc1.iad.abcwcljaaabbbcccdddefakef123456789123456789abcdefghijklmfake is in the AVAILABLE state."
}

Wrapping up…

So at this stage you now have a script that you can run whenever you need to. You can even go down the path of setting up a scheduler like launchd or crontab to fire it up on a regular interval.

Hopefully you found this post useful. Definitely let me know if you have any questions about using the script or need help with it. I am also open to any suggestions you may have to improve or extend the script.