X

Best Practices from Oracle Development's A‑Team

Automated CLI Scripts to Scale Autonomous Database CPUs

Vivek Singh
Principal Solutions Architect

Oracle Autonomous Database is a family of self-driving, self-securing, and self-repairing cloud services. These databases eliminate human labor, human error, and manual tuning, reducing cost and complexity and ensuring higher reliability, security, and more operational efficiency. Currently, Autonomous Database is offered in two configurations – Autonomous Data Warehouse and Autonomous Transaction Processing.

This blog talks about how the OCI Command Line Interface can be used to manage autonomous databases. It describes the process of how you can run the CLI Scripts from an OCI Compute VM, how you can automate the process, and how to use Instance Principals so you do not need a user’s private keys for authentication. It gives examples of a few commands for managing automated databases and gives references so you can easily implement other commands. It describes the following commands:

  • To start an ADW or ATP database
  • To stop an ADW or ATP database
  • To increase or decrease the base OCPUs of an ADW or ATP database

Use Case

The client has a workload that is not uniformly distributed throughout the day. Most of the workload is from 1:00 AM in the night till 6:00 AM in the morning. Rest of the day the workload is much lighter. The client will like to scale up the ATP OCPUs just before 1:00 AM and scale OCPUs down after 6:00 AM. Moreover, they have a UAT instance that is used for acceptance testing and load tests. UAT is used only on the days when testing has to be performed. It is not used on most of the days.

As a result of their requirements the client will like the following:

  • To scale up the OCPUs of their production ATP at 12:45 AM, Monday through Friday.
  • To scale down the OCPUs of their production ATP at 6:15 AM Monday through Friday.
  • Scale up and scale down of OCPUs should be down programmatically, without any human involvement.
  • Have the ability to programmatically shut down and restart the UAT ATP

A simple way to implement this is by using OCI CLI scripts that are automated using cron jobs. These scripts should use OCI Instance Principals for authentication. The rest of the blog describes how this is implemented.

 

OCI Command Line Interface

The OCI Command Line Interface is a small footprint tool that you can use to complete Oracle Cloud Infrastructure tasks. The CLI provides the same core functionality as the Console, plus some additional commands. Some of these, such as the ability to run scripts extend the Console's functionality.

You can run OCI CLI on any  Mac, Windows, or Linux computer running Python that has access to Oracle Cloud Infrastructure public services. Although you can run OCI CLI from your desktops or laptops we recommend that you run it from an OCI VM, especially if you want to manage production databases. This has several advantages:

  • The CLI does not have to be installed on any person’s laptop or desktop.
  • You can restrict access to the VM to authorized administrators only.
  • You can use OCI Instance Principal to run the CLIs without using someone’s personal credentials.
  • It will make sure that you are not dependent on a person to run your scripts.
     

In this blog, I use an OCI VM running Linux for running the OCI CLI scripts. To manage an autonomous database using OCI CLI scripts you will need to do the following:

  • Identify a Compute VM running Linux for managing the autonomous database.
  • Install OCI CLI on the Compute VM.
  • Create a dynamic group & policy to allow your Compute to manage the autonomous database.
  • Create script files and cron jobs to manage the autonomous database.

 

Identify a Compute VM Running Linux for Managing Autonomous Database

You should identify a Linux VM from where you will run the automated CLI Scripts. You do not need a dedicated VM to run these scripts. But, if there are other administrator scripts that you use, it may be worth dedicating a VM for running the admin scripts.

 

Install OCI CLI on the Compute

The details to install OCI on a Linux Compute in OCI are given here. Or you can just follow the Quick Start guide to install the CLI.  For the use case discussed in this blog, you will not be running the CLI scripts using a user’s credentials, thus you can skip the directions regarding creating a user, and creating certificates, etc. If you are using Oracle Linux 7 Compute, you can also just install OCI CLI by connecting to your VM using ssh and then using yum:

$sudo yum install python36-oci-cli

 

Create OCI CLI Config File

After installing the OCI CLI, you need to create the config file. The config file must be created in ~/.oci folder. The config file needs to have the following three lines:

[DEFAULT]
Tenancy=< tenancy ocid >
Region=< your region >

 

The config file should be present in ~/.oci folder, and only the owner should have rw permissions, as shown below:

[opc@vm .oci]$pwd
/home/opc/.oci
[opc@vm .oci]$ls -l 
-rw-------. 1 opc opc 118 May 7 15:43 config
[opc@vm .oci]$

 

 

Calling OCI CLI from a Compute Using Instance Principal

Next, you need to create a dynamic group, and a policy that will allow you to use OCI CLI from a compute VM using Instance Principals. The compute from where you will run the CLI scripts should be a member of the Dynamic Group. The policies should grant the dynamic group permissions to manage autonomous database family. If you are not sure how to do this, please see my other blog Calling OCI CLI Using Instance Principal.

 

Create Scripts to Start, Stop, Update Autonomous Database

The OCI CLI to manage autonomous database is given here - click on Command Line Reference, Database Service (db), and then on autonomous-database.

Below are sample scripts to start, stop, and update the autonomous databases. These scripts are valid both for AWD and ATP databases. Make sure the path to OCI CLI is included in the PATH variable. In my system, the OCI CLI was installed at $HOME/bin directory. Depending on the method you used to install OCI CLI, it may be installed at a different location. 

Script to start autonomous database:

#!/bin/bash
PATH=$PATH:$HOME/bin
export PATH
OCI_CLI_AUTH=instance_principal
export OCI_CLI_AUTH
oci db autonomous-database start --autonomous-database-id < adb ocid > --auth instance_principal

 

Script to stop autonomous database:

#!/bin/bash
PATH=$PATH:$HOME/bin
export PATH
OCI_CLI_AUTH=instance_principal
export OCI_CLI_AUTH
oci db autonomous-database stop --autonomous-database-id < adb ocid > --auth instance_principal

 

Script to change number of OCPUs in autonomous database:

#!/bin/bash
PATH=$PATH:$HOME/bin
export PATH
OCI_CLI_AUTH=instance_principal
export OCI_CLI_AUTH
oci db autonomous-database update --autonomous-database-id < adb ocid > --cpu-core-count < number of desired OCPUs > --auth instance_principal

 

 

Create CRON Job to Run the Scripts

The last step is to create a cron job to execute these scripts as per your desired schedule. Your Linux administrator should be able to create the cron jobs.  I used crontab -e command to set up cron jobs as follows: 

Edit the corn table using crontab -e command from root user:

#crontab -e

 

Enter your cron commands, for example: 

SHELL=/bin/bash
# Example of job definition
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * command to be executed
  
# start autonomous database at 45 minutes past 12am on Mon ~ Fri 
45 00 * * 1,2,3,4,5 /usr/local/bin/oci-api-start-awd.sh >> /home/opc/log/dblog.log 2>&1
  
#stop autonomous database at 15 minutes past 06 hours on Mon ~ Fri
15 6 * * 1,2,3,4,5 /usr/local/bin/oci-api-stop-awd.sh >> /home/opc/log/dblog.log 2>&1
 

 

 

 

References

  1. Oracle Autonomous Database: https://www.oracle.com/database/autonomous-database.html
  2. Autonomous Data Warehouse: https://www.oracle.com/database/adw-cloud.html
  3. Autonomous Transaction Processing: https://www.oracle.com/database/atp-cloud.html
  4. OCI Command Line Interface (CLI):
     https://docs.cloud.oracle.com/en-us/iaas/Content/API/Concepts/cliconcepts.htm
  5. CLI supported OS and Python versions: https://docs.cloud.oracle.com/en-us/iaas/Content/API/Concepts/cliconcepts.htm#SupportedPythonVersionsandOperatingSystems
  6. OCI CLI Quick Start: https://docs.cloud.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm
  7. Connecting to your OCI VM:  https://docs.cloud.oracle.com/en-us/iaas/Content/Compute/Tasks/accessinginstance.htm
  8. Calling Services from an Instance: https://docs.cloud.oracle.com/en-us/iaas/Content/Identity/Tasks/callingservicesfrominstances.htm
  9. Instance Principals: https://blogs.oracle.com/cloud-infrastructure/announcing-instance-principals-for-identity-and-access-management
  10. Managing Dynamic Groups: https://docs.cloud.oracle.com/en-us/iaas/Content/Identity/Tasks/managingdynamicgroups.htm
  11. Writing authorization policies for Dynamic Groups: https://docs.cloud.oracle.com/en-us/iaas/Content/Identity/Tasks/callingservicesfrominstances.htm#Writing

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Join the discussion

Comments ( 2 )
  • Richard Garsthagen Wednesday, July 15, 2020
    I have also created an autoscaling script, it can schedule on/off and scaling operations for Autonomous DB, but also support Compute, instance pools and Database Cloud Service (VM, Bare metal).
  • Richard Garsthagen Wednesday, July 15, 2020
    I also wrote a script for schedules scaling for Autonomous DB for on/off and scaling operations, but also support compute VMs, Instance Pools, Database Cloud Service for VMs and BM.
    https://github.com/AnykeyNL/OCI-AutoScale
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha