Introduction

The Oracle Public Cloud allows users to create an Autonomous Database to store application-data.

When connected to the Oracle Cloud Infrastructure Console you have an insight view on the database details, the ability to scale up/down, stop and start the database and to activate the Service Console.

Monitoring Autonomous Database activity using the OCI Service Console

When activating the Service Console, a new browser tab will be opened, and the Service Console presented.

The first page of the console shows resource statistics for CPU Utilization, Running SQL statements, Average SQL statement response time and SQL statement execution per second.

Next, we navigate to the Activity page…

The Activity page while in the Monitor tab, is showing more detailed statistics on Database Activity, CPU Utilization, Running Statement and Queued Statements

When navigating to the Monitored SQL tab detailed information on running and queued statements is provided

After selecting one of the SQL statements in the page you can get even more detailed information

When selecting Plan Statistics, detailed insight is given in the SQL Execution plan as the statement is being processed

When selecting Parallel, detailed information is shown of the part of the statement that gets executed in parallel

Monitoring availability of an Autonomous Database using EM13c

As many organizations are using Oracle Enterprise Manager Cloud Control to monitor the availability of their datacenter components, we would want to include the availability monitoring of Autonomous Databases within the Oracle Public Cloud as well.

The following example shows a way to do this using a Generic Service in EM13c.

The Generic Service we are going to create will be based on running a Custom Script that will use the OCI Database Service API to get the Autonomous Database Lifecycle status. If the Lifecycle Status is equal to ‘AVAILABLE’ the script exits with return code 0, otherwise the script will exit with return code 1.

Connect to the EM13c console using an administrator that is authorised to create a Generic Service.

From the Targets menu select Services

Within the Services page select Create > Generic Service – Test Based

Enter a Name for the Generic Service (we use DBATPD01 Availability in our example)

By default, the correct Time Zone has been selected, change if needed…

Press [Next]

Test type: Select Custom Script
Name: Enter a name for the Service Test to be created
Description: Enter a description for the Service Test to be created
Collection Frequency (Minutes): 5 minutes

Command Script
Command Line: /home/oracle/oci/testAutonomousDBavailability.sh eu-frankfurt-1 ocid1.autonomousdatabase.oc1.eu-frankfurt-1.abtheljrhwske5wfsrpsqespj5yjunk3btjdjlc52rdd262inyaa

We will explain the Command Line contents later

Credentials
Username: Enter the user name we want to connect to the server to execute the Command Line
Password: Enter the password for the user

Press [Next]

The Generic Service test will be run by a Beacon and therefore we need to select one. As we have located the script that gets executed by the Command Line, on our OMS server we will use the EM Management Beacon (This Beacon is available out of the box).

Use the [Add] button to select the Beacon.

Press [Next]

Press [Finish] to finalize the Generic Service creation

For the first couple of minutes the Generic Service will be shown with the Pending status

After a while the Generic Service status will be shown, in our case the Autonomous Database is Up and Running

Obviously, you can now start to further configure the Generic Service by adding a Service Level Agreement, include it in Incident Rules etc.

If we would now stop Autonomous Database DBADTP01 we will see the Generic Service status change to Down

To test this, we open the Cloud Infrastructure console and stop the database

After a moment the database will show as Stopped

When looking at the Generic Service within the EM13c console we will notice the following:

Explaining the Generic Service Command Line

The Generic Service Command Line contains the following:

/home/oracle/oci/testAutonomousDBavailability.sh eu-frankfurt-1 ocid1.autonomousdatabase.oc1.eu-frankfurt-1.abtheljrhwske5wfsrpsqnsnjndpjunk3bjlc525yrdd2inyaa

A script called testAutonomousDBavailability.sh is executed and given 2 arguments:

Region: This is the Oracle Cloud Region where the Autonomous Database has been created (in our example that is eu-frankfurt-1)

Autonomous Database ID: This is the OCID that is found in the Database Detail page within the Cloud Infrastructure console

Select Show to show the OCID or simply select Copy to get the OCID to the Clipboard

testAutonomousDBavailability.sh contents

#!/bin/bash

region=$1
autonomousDatabaseId=$2

cd /home/oracle/oci

. ./oci-curl.sh

DBstatus=`oci-curl database.$region.oraclecloud.com get "/20160918/autonomousDatabases/$autonomousDatabaseId" | grep "lifecycleState"`

#echo $DBstatus

if [[ $DBstatus == *"AVAILABLE"* ]]; then
  exit 0;
else
  exit 1;
fi

The script does the following:

• Change current directory to /home/oracle/oci
• Next, we ‘source’ the oci-curl.sh scriptThe oci-curl.sh script can be found in the Oracle Cloud Infrastructure Documentation

• Next, we call oci-curl to get the lifecycle status of the Autonomous Database.oci-curl database.$region.oraclecloud.com get “/20160918/autonomousDatabases/$autonomousDatabaseId” | grep “lifecycleState”

For more information on working with oci-curl please take a look at https://oemgc.wordpress.com/2018/12/14/managing-autonomous-datawarehouse-using-oci-curl/

Disclaimer
Please notice that all statements made by me and information provided on this blog are mine and not necessarily those of Oracle Corporation.