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.