Monitor Amazon RDS Custom for Oracle with Amazon CloudWatch metrics

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying Operating System (OS) and database environment. With Amazon RDS Custom for Oracle, you can customize the database server host and OS and apply special patches or change database software settings to support third-party applications that require privileged access. You can create your automation and run custom automation scripts for reporting, management, or diagnostic tools.

Monitoring is an important part of maintaining the reliability, availability, and performance of Amazon RDS Custom for Oracle. In this post, we discuss the integration of Amazon CloudWatch with an RDS Custom for Oracle environment. This enables you to monitor the health of your RDS Custom DB instances and observe changes to the infrastructure and databases workloads. You can monitor metrics over a specific time period and set CloudWatch alarms to receive notifications. You can perform one or more actions based on the value of the metric relative to a threshold that you set.

Solution overview

As a part of this solution, you monitor the following metrics from the Amazon RDS Custom environment:

Infrastructure – This includes the metrics CPUUtilization, FreeableMemory, and FreeStorageSpace. These metrics help you determine the acceptable values of the CPU or RAM consumption and the available free storage space in the data volume of the RDS instance.

Workload – This includes the metrics ReadIOPS, WriteIOPS, ReadThroughput, WriteThroughput, and DatabaseConnections. You can check the value of the IOPS and throughput metrics depending on disk specification and server configuration.

Performance – This includes the metrics ReadLatency, WriteLatency, and DiskQueueDepth. By understanding the performance metrics of the database workload, you can make informed decisions about the performance of your RDS Custom environment.

The following diagram illustrates the solution architecture.

arch

The solution fetches data from the AWS/EBS, AWS/EC2, and Custom namespaces in CloudWatch for the infrastructure and performance-related information. This data is transformed into graphs on the CloudWatch console by using mathematical expressions and publishing those to CloudWatch. The data related to database connections and memory is collected via a shell script installed in the host and is pushed into the custom namespace. This data is populated in the custom CloudWatch dashboard for visualization. Additionally, we demonstrate creating CloudWatch alarms on a few metric widgets to receive alerts over email.

The high-level steps for implementing this solution are as follows:

Set up an RDS Custom for Oracle environment.
Identify the data volume names.
Download the custom monitoring scripts.
Modify the scripts and code.
Create a custom dashboard in CloudWatch.
View metrics on the dashboard.
Create alarms on the metrics.

Prerequisites

To follow along with this post, you should have familiarity with the following AWS services:

Amazon RDS Custom for Oracle
Amazon CloudWatch
Amazon Elastic Compute Cloud (Amazon EC2)
AWS Command Line Interface (AWS CLI)

CloudWatch dashboards are customizable pages on the CloudWatch console that you can use to monitor your resources in a single view. You can create dashboards from the CloudWatch console or using the AWS CLI or PutDashboard API operation.

To access CloudWatch dashboards, you can follow the principle of least privilege and add a custom policy that includes the following AWS Identity and Access Management (IAM) permissions:

cloudwatch:GetDashboard , cloudwatch:GetMetricData and cloudwatch:ListDashboards to be able to view dashboards
cloudwatch:PutDashboard , cloudwatch:PutMetricData and cloudwatch:PutMetricAlarm to be able to create or modify dashboards and alarms

In this post, we use the API call “PutDashboard” with the AWS CLI to create a dashboard. This requires you to have the latest AWS CLI version installed or updated.

Set up an RDS Custom for Oracle environment

To make sure that you have the right permissions in place, follow the instructions in Setting up your environment for Amazon RDS Custom for Oracle.

To use RDS Custom, create an IAM instance profile named AWSRDSCustomInstanceProfileForRdsCustomInstance. Also, create the IAM role AWSRDSCustomInstanceRoleForRdsCustomInstance for the instance profile. Then, add the role to your instance profile.

Additionally, you must create and connect to a DB instance for Amazon RDS Custom for Oracle.

Identify the data volume names

Log in to the RDS Custom instance as ec2-user and run the following command to identify the volumes related with the RDS database. We check for /rdsdbdata because it’s the mount point for the data volumes for the custom environment. The RDS Custom instance used for this post is configured with 400 GB of storage, which is distributed in 4 * 100 GB volumes.

lsblk -o +SERIAL

lsblk

Alternatively, you can identify the volumes from the Amazon EC2 console by searching for the one with the name RDS Custom Resource ID.

Use the following code to identify the resource ID of the database. You must use the DB instance identifier.

aws rds describe-db-instances --db-instance-identifier <Name of your DB instance> | grep DbiResourceId

dbid

On the Amazon EC2 console, navigate to the EC2 instance with the same name as the resource ID that you obtained, and choose the Storage tab to identify the attached volumes. Generally, sda1 is the root volume for the OS, and xvdf is allocated as the binary volume hosting the Oracle and RDS binaries.

ec2 instance

Download and install the custom monitoring scripts

Download the following three files from this GitHub repository in a directory.

custommetrics.sh: This script collects memory, TCP connections at port 1521 (Oracle listener port), to publish in the custom CloudWatch namespace.
dash.json: This is the dashboard configuration file. It fetches raw data from the AWS/EC2, AWS/EBS, and Custom namespaces, and it performs mathematical calculations to display CloudWatch metrics on the dashboard.
installer.sh: This script will automate the installation process.

For example, I created a directory called “automate” under /home/ec2-user and placed all three files there.

[ec2-user@ip-- automate]$ pwd
home/ec2-user/automate
[ec2-user@ip-- automate]$ ls -ltrh
total 32K
-rwxrwxr-x 1 ec2-user ec2-user 3.2K Oct 12 06:47 custommetrics.sh
-rw-rw-r— 1 ec2-user ec2-user 23K Oct 16 06:29 dash.json
-rwxrwxr-x 1 ec2-user ec2-user 1.7K Oct 16 06:39 installer.sh
[ec2-user@ip-- automate]$

Make installer.sh is executable:

chmod +x installer.sh

Run this script and it will ask for the 4 Volume ID attached to the RDS instance, the EC2 instance ID, the preferred name of the dashboard. and the preferred name of the CloudWatch namespace to hold the data from EC2 instance.

Make sure the volume ID is in the format vol-xxxxx.

. installer.sh

This script performs the following activities:

Update the EC2 instance ID in the custommetric.sh and dash.json files
Update the EC2 instance ID and volume ID in the respective files
Install the custommetric.sh in crontab
Create the dashboard in CloudWatch

Note that the custommetric.sh script is setup in crontab to run every 60 seconds. This script uses the put-metric-data AWS CLI command to send data to CloudWatch. 1 million API requests per month are included in the Free Tier. However, the remaining ones are charged based on the Region-specific rates. For more details, refer to CloudWatch pricing.

output

View metrics in the CloudWatch dashboard

Navigate to CloudWatch Console and select the dashboard name that you provided in the previous step to see the metrics.

The following screenshot shows the dashboard and its metrics.

inframetriccw

workloadcw

perfcw

You can view an animated dashboard that replays CloudWatch metric data that was captured over time. This can help you see trends, make presentations, or analyze issues after they occur. Visit this link to learn more about animated dashboard.

Create an alarm on CloudWatch metrics

A metric alarm watches a single CloudWatch metric or the result of a math expression based on CloudWatch metrics. The alarm performs one or more actions based on the value of the metric or expression relative to a threshold over a number of time periods. The action can be sending a notification to an Amazon Simple Notification Service (Amazon SNS) topic, performing an Amazon EC2 action or an Amazon EC2 Auto Scaling action, or creating an OpsItem or incident in AWS Systems Manager.

Let’s create an Amazon SNS topic, which enables you to receive notifications over email and text message. For this post, we create an Amazon SNS topic called RDSCustomOracle and use a work email ID to receive notifications. We use the same Amazon SNS topic while working with CloudWatch alarms.

Create an Amazon SNS topic:

aws sns create-topic --name <Name of the SNS topic>

Subscribe to the topic and specify the email protocol and an email address for notification-endpoint:

aws sns subscribe --topic-arn <ARN of the SNS topic created> --protocol email --notification-endpoint <Email ID>

Confirm the subscription before the email address can start to receive messages.
1. Check your email inbox and choose Confirm subscription in the email from Amazon SNS.
2. Amazon SNS opens your web browser and displays a subscription confirmation with your subscription ID.

Now you can create a CloudWatch alarm on any of the dashboard widgets.

1. Collect the metrics from the three namespaces—AWS/EC2, AWS/EBS, and CWAgentCustom. You can use the following query to identify the available metrics in the individual namespaces:

aws cloudwatch list-metrics --namespace "AWS/EC2"

Choose any metric and use the following code to create a CloudWatch alarm. Be mindful of the namespace, dimensions, and metric name.

The following are two examples of CloudWatch alarms.

The first alarm is for the number of database connections in the Oracle instance:

aws cloudwatch put-metric-alarm --alarm-name Connection_monitor --alarm-description "Test Alarm when DB Exceed 500" --metric-name TCP_connection --namespace CWAgentCustom --statistic Average --period 60 --threshold 200 --comparison-operator GreaterThanThreshold --dimensions "Name=Instance,Value=i-XXXX" --evaluation-periods 2 --alarm-actions arn:aws:sns:us-east-1:ABCD:RDSCustomOracle

The next alarm is for high CPU utilization:

aws cloudwatch put-metric-alarm --alarm-name cpu_monitor --alarm-description "Test Alarm when CPU exceeds 60 percent" --metric-name CPUUtilization --namespace AWS/EC2 --statistic Average --period 300 --threshold 70 --comparison-operator GreaterThanThreshold --dimensions "Name=InstanceId,Value=i-XXXX" --evaluation-periods 2 --alarm-actions arn:aws:sns:us-east-1:ABCD:RDSCustomOracle

The following screenshot shows the email that we receive.

emailcw

(Optional) Manual modification of the scripts

There are situations when you might need to manually update and run the scripts.

Scenario 1 – Replacement of EC2 Instance

The underlying EC2 instance catered to RDS Custom for Oracle or self-managed Oracle on EC2 changes if you modify the instance to a higher or lower instance class. The AWS automation component can also replace the unhealthy underlying EC2 instance as a part of recovery action or maintenance.

In both cases, the instance ID of the underlying EC2 instance will change. In this situation, you can check the new EC2 instance ID from the AWS Console. You must update the EC2 instance ID in the following scripts and run it.

Shell Script

sed -i "s/<Old EC2 Instance ID>/<new EC2 Instance ID>/g" custommetrics.sh

-- Example  –-
sed -i "s/i-YYYY/i-XXXX/g" custommetrics.sh

You don’t need to reschedule this in crontab as it’s already installed.

Dashboard file

sed -i "s/<Old EC2 Instance ID>/<new EC2 Instance ID>/g" dash.json

-- Example -–
sed -i "s/i-YYYY/i-XXXX/g" dash.json

Update the existing dashboard using the modified dash.json file either from the Console or using AWS CLI.

aws cloudwatch put-dashboard --dashboard-name <Existing Dashboard Name> --dashboard-body file://dash.json --region us-east-1

-- You get the following output if your API call successfully passed and created the dashboard --

{
"DashboardValidationMessages": []
}

Scenario 2 – Replacement of Amazon Elastic Block Store (Amazon EBS) Volumes

The volumes allocated to the EC2 instance may get replaced due to underlying hardware failure. In this situation, AWS automation will replace the impaired volume with a new one. This will cause the change in the volume ID.

In these cases, you must update the dashboard to fetch data from the updated volume ID.

You can update the new volume ID in the dashboard configuration file and update the dashboard using the Console or AWS CLI.

sed -i "s/<Old Volume>/<New Volume>/g" dash.json

--Install the Dashboard—-

aws cloudwatch put-dashboard --dashboard-name <Existing Dashboard Name> --dashboard-body file://dash.json --region us-east-1

-- You get the following output if your API call successfully passed and update the dashboard --

{
"DashboardValidationMessages": []
}

Scenario 3 – Addition of Amazon EBS Volumes

For this current use case using RDS Custom for Oracle, we have four Amazon EBS volumes. In this case, you have more than four data volume and you want to monitor the same, but you can modify this per your use case in the JSON file and add volumes similarly. You can use any JSON editor to validate the format after the editing completes.

For example, if you have five volumes, then you must add the fifth volume and replace the other volume IDs for each widget. The following is an example of one widget with five volumes:

{
	"height": 6,
	"width": 6,
	"y": 6,
	"x": 0,
	"type": "metric",
	"properties": {
		"metrics": [
			[ "AWS/EBS", "VolumeReadOps", "VolumeId", "vol-MMMM", { "id": "m1", "visible": false } ],
			[ "...", "vol-NNNN", { "id": "m2", "visible": false } ],
			[ "...", "vol-OOOO", { "id": "m3", "visible": false } ],
			[ "...", "vol-PPPP", { "id": "m4", "visible": false } ],
			## Adding the fifth volume below and modifying the math expression with Fifth volumeID.
			[ "...", "vol-GGGG", { "id": "m5", "visible": false } ],
			[ { "expression": "SUM([m1,m2,m3,m4,m5])/60", "label": "SUM(VolumeReadOps)/Period", "id": "e1" } ]
		],
	"view": "timeSeries",
	"stacked": false,
	"region": "eu-west-1",
	"stat": "Sum",
	"period": 60,
	"title": "Read Throughput (IOPS)"
	}
}

You add the newly added volume ID in the math expression to calculate the aggregated values.

Conclusion

In this post, we discussed how to use CloudWatch and its dashboard for RDS Custom for Oracle and how set up CloudWatch alarms for proactive monitoring. Additionally, we talked about a few examples of metrics-related alarms and demonstrated the setup process to improve your monitoring capabilities.

As a final takeaway, make sure that you understand the usage of individual CloudWatch metrics. In a situation where the performance falls outside of your pre-established baseline, you might need to make changes to optimize your database availability for your workload. For example, you might need to change the instance class of your DB instance, or you might need to tune the queries causing high CPU utilization in the environment.

Use this link to find more posts about monitoring databases with Amazon CloudWatch.

We welcome your feedback. If you have questions or suggestions, leave them in the comments section.

About the Author

Arnab Saha is a Senior Database Specialist Solutions Architect at Amazon Web Services. Arnab specializes in Amazon RDS, Amazon Aurora , AWS DMS and Amazon Elastic Block Store. He provides guidance and technical assistance to customers thus enabling them to build scalable, highly available and secure solutions in AWS Cloud.

Radhika Chakravarty is a Database Solutions Architect with Amazon Web Services. She works with customers and partners by providing technical assistance to design and implement cloud migration projects, helping them to migrate and modernize their existing databases to AWS Cloud.

Rohan Vashi is a cloud support engineer at Amazon Web Services. He’s passionate about helping customers solve their AWS issues. He specializes in network monitoring and observability and enjoys helping customers get the best cloud experience possible through AWS.

AWS Database Blog