Jun 21, 2017 4:21:45 PM by Deepak Vohra
Proactive database management is based on monitoring a database and taking pre-emptive measures to keep the database running at the required level of performance. AWS (Amazon Web Services) RDS (relational database service) is a reliable and scalable managed database service for six commonly used databases: Oracle, MySQL, Aurora, PostgreSQL, SQL Server, and MariaDB. Being a managed AWS service, RDS databases are monitored using the integrated AWS CloudWatch service, a monitoring service for cloud resources and applications on AWS that collects and monitors metrics and log files, and provides alarms to enable a user to take preventive measures. CloudWatch Dashboard includes graphs for selected metrics, and Events for a monitored resource or service.
A database in RDS provides the following proactive database management features:
The various proactive database management features for Oracle database on RDS are shown in Figure1.
Figure 1. Proactive Database Management Features for a Database in RDS
This article has the following sections:
The only prerequisite for using Oracle Database on RDS and the concomitant proactive features is an AWS account, which may be created at https://aws.amazon.com/resources/create-account/, if you don’t already have one.
The enhanced monitoring feature in RDS requires a special role, which we shall create next. Select the IAM service in the AWS Dashboard or open the URL https://console.aws.amazon.com/iam in a web browser. Select Roles and click on Create New Role to create a role for RDS Enhanced Monitoring, as shown in Figure 2.
Figure 2. IAM>Roles>Create Role
In the Set Role Name page specify a Role Name (rds) as shown in Figure 3 and click on Next step.
Figure 3. Specifying a Role Name
In Select Role Type click on Select the AWS Service Roles>Amazon RDS Role for Enhanced Monitoring as shown in Figure 4.
Figure 4. Selecting Amazon RDS Role for Enhanced Monitoring
In Attach Policy select the AmazonRDSEnhancedMonitoring policy as shown in Figure 5 and click on Next step.
Figure 5. Selecting a policy for the Role
In Review click on Create Role to create the role as shown in Figure 6.
Figure 6. Creating a Role
A new role (rds) for RDS Enhanced Monitoring gets created, as shown in Figure 7.
Figure 7. ADS Enhanced Monitoring Role
For most of the proactive management features nothing special or extra needs to be configured and the RDS database automatically sends metrics and logs to CloudWatch. To create an Oracle database managed service on RDS select the RDS service in AWS Dashboard. Click on Get Started Now as shown in Figure 8.
Figure 8. RDS>Get Started Now
In Select Engine select the Oracle EE engine as shown in Figure 9.
Figure 9. Selecting the Oracle EE engine
In "Do you plan to use this database for production purposes?" select one of the options: Production or Dev/Test; Dev/Test is selected for this article, as shown in Figure 10. Click on Next step.
Figure 10. Selecting Dev/Test environment
In Specify DB Details, the DB Engine name is specified by default as oracle-ee. Select the default settings for License Model (bring-your-own-license), DB Engine Version (Oracle 220.127.116.11), DB Instance Class, Multi-AZ Deployment (available only in Production environment), Storage Type (General Purpose SSD), and Allocated Storage (10GB), as shown in Figure 11.
Figure 11. Specifying DB Details
Scroll for Settings and specify a DB Instance Identifier (orcl), which must be unique for all DB instances in an AWS account in an AWS Region. The DB instance identifier is case insensitive and stored as all lower case. Specify a Master Username and Master Password including Confirm Password and click on Next step as shown in Figure 12.
Figure 12. Specifying DB Instance settings
In Configure Advanced Settings (Figure 13) the VPC defines the Virtual Private Cloud for the virtual networking environment for the DB instance. Only VPCs with a DB Subnet are listed. Select a VPC. Select a Subnet Group. The DB Subnet group defines which subnets and IP ranges the DB instance may use in the VPC. Select Yes or No in Publicly Accessible. Selecting Yes makes RDS assign a public IP Address to the DB instance and one or more Security Groups must also be selected to configure which EC2 instances and devices may connect to the DB instance. Select an Availability Zone and select the option to create a new Security Group. In Database Options specify a Database Name (ORCL) and Database Port (1521), and select a DB Parameter Group and Option Group.
Figure 13. Advanced Settings
Select a Character Set Name and select No for Enable Encryption. Select a Backup Retention Period and a Backup Window. For Enhanced Monitoring select Enable Enhanced Monitoring option Yes as shown in Figure 14.
Figure 14. Selecting Enable Enhanced Monitoring option Yes
Select the Monitoring Role “rds” created earlier as shown in Figure 15.
Figure 15. Selecting a Monitoring Role
Selecting the Default Monitoring Role could generate an error about lack of permissions for ENHANCED_MONITORING, as shown in Figure 16, and not launch a database instance.
Figure 16. Error Launching DB Instance with Default Monitoring Role
Select a Granularity of 60 seconds and select the Maintenance settings for Auto Minor Version Upgrade and Maintenance Window and click on Launch DB Instance as shown in Figure 17.
Figure 17. Launching DB Instance
A DB Instance gets created, as shown in the message in Figure 18. Click on View Your DB Instances to navigate to the DB Instances dashboard.
Figure 18. DB Instance created
The DB instance could take a few minutes to become available and in its Status is listed as “creating” or “backing up” till it becomes available.
Some of the proactive database management features are available in the RDS Dashboard itself, as shown in Figure 18. The Alarms and Recent Events list the CloudWatch Alarms and recent events, such as the DB Instance, being created or being backed up. The Monitoring section lists metrics for CPU, Memory, Storage, Read IOPS, Write IOPS, and Swap Usage, as shown in Figure 19 also.
Figure 19. RDS Dashboard Proactive Management
The AWS CloudWatch service is integrated with RDS and metrics and logs generated by RDS Database instances are automatically sent to CloudWatch. Select the CloudWatch service from the AWS Dashboard. Click on Browse Metrics as shown in Figure 20 to browse CloudWatch metrics.
Figure 20. Selecting Browse Metrics in CloudWatch
CloudWatch collects metrics and logs for several AWS services and not just RDS. The All metrics tab lists all the metrics available in CloudWatch, as shown in Figure 21.
Figure 21. All metrics
Specify “RDS” in the Search field to list only the RDS-specific metrics, as shown in Figure 22. RDS metrics are grouped By Database Class, Across All Databases, Per-Database Metrics, and By Database Engine. Database Class is based on the EC2 Instance Type and, for the DB instance created, the Database Class is db.t2.micro.
Figure 22. Filtering CloudWatch metrics for RDS metrics
As an example, select RDS>By Database Class, as shown in Figure 23.
Figure 23. Selecting RDS Metrics>By Database Class
As an example, select SwapUsage for the db.t2.micro Database Class, and the graph for the metric gets displayed, as shown in Figure 24.
Figure 24. Displaying Graph for the Swap Usage Metric for the db.t2.micro Database Class
Similarly, to display metrics aggregated from all RDS Databases, click on RDS>Across All Databases as shown in Figure 25.
Figure 25. Selecting RDS>Across All Databases
For all RDS databases, 16 aggregated metrics get listed, as shown in Figure 26.
Figure 26. RDS Metrics aggregated across all Databases
The most useful set of metrics may be per database instance metrics; for which, select RDS>Per Database Metrics as shown in Figure 27.
Figure 27. Selecting RDS>Per Database Metrics
Select a metric for the orcl database instance which we created earlier. As an example, select SwapUsage, and the graph for the metric gets displayed, as shown in Figure 28.
Figure 28. Displaying a Graph for the SwapUsage metric for orcl RDS Database
The Graphed metrics tab lists the graphed metrics; the color for a graph may be selected from a color palette, as shown in Figure 29.
Figure 29. Selecting Graph Color
Multiple metrics may be selected to display graphs for multiple metrics, as shown in Figure 30.
Figure 30. Displaying Graphs for Multiple Metrics
CloudWatch logs for RDS database instances may be accessed by selecting Logs in the CloudWatch dashboard. Select the RDSOSMetrics log group in the CloudWatch>Log Groups as shown in Figure 31.
Figure 31. Selecting the RDSOSMetrics Log Group
From the CloudWatch>Log Groups>Stream for RDSOSMetrics select the log stream listed, as shown in Figure 32.
Figure 32. Selecting a Log Stream
The messages in the log stream get listed, as shown in Figure 33.
Figure 33. Log Stream Messages
By default all messages get listed. A single message be selected and displayed as JSON by selecting Row, as shown in Figure 34.
Figure 34. Displaying a Single Message
One of the benefits of the CloudWatch service is that an “alarm” may be created to monitor individual metrics. As example, if a particular metric is not to exceed or fall below (or some other condition) a certain threshold value a CloudWatch alarm may be created for the same. To create a CloudWatch Alarm, click on Create Alarm as shown in Figure 35.
Figure 35. CloudWatch>Create Alarm
Alternatively, select Alarms in the CloudWatch Dashboard and click on Create Alarm as shown in Figure 36.
Figure 36. CloudWatch>Alarms>Create Alarm
The Create Alarm wizard gets started. In Select Metric, the CloudWatch Metrics are listed by category. Select from the RDS Metrics as shown in Figure 37. As an example, select Per-Database Metrics.
Figure 37. RDS Metrics
Select metric/s for which a CloudWatch Alarm is to be created. As an example, select the CPUUtilization metric for the orcl RDS Database as shown in Figure 38.
Figure 38. Selecting Metric/s
For Time Range a Relative range may be selected, as shown in Figure 36, or an Absolute range may selected as shown in Figure 39. Click on Next.
Figure 39. Selecting an Absolute Time Range
Having selected the metric, define the alarm, which consists of setting an alarm threshold in the Alarm Threshold section and specifying Notification actions in the Actions section as shown in Figure 40. Specify a Name for the alarm threshold. In Whenever, specify a condition. The condition specified is CPUUtilization>=50 for 2 consecutive period(s), the period being defined in the Alarm Preview section with a default of 5 minutes.
Figure 40. Defining the Alarm
A CloudWatch alarm has three settable states, as shown in Figure 41.
OK – The metric is OK and within the defined threshold
ALARM – The metric is not OK and outside the defined threshold
INSUFFICIENT_DATA – Sufficient data not is available to determine the alarm state, which could be because the alarm has just started.
Figure 41. Selecting an Alarm Action for Notification
A CloudWatch Alarm makes use of AWS Simple Notification Service (SNS) to send a notification for which we need to create an SNS Topic, as indicated by the help message in Figure 42.
Figure 42. Sending Notification makes use a SNS Topic
To create a new SNS Topic navigate to the SNS Dashboard and Click on Create Topic as shown in Figure 43.
Figure 43. Selecting Create Topic in SNS Dashboard
In Create new topic specify a Topic name (RDSCloudWatch) and click on Create topic as shown in Figure 44.
Figure 44. Create new topic
A new SNS Topic gets created, as shown in Figure 45. Copy the Topic ARN listed in the Topic Details.
Figure 45. New SNS Topic RDSCloudWatch
The RDSCloudWatch SNS Topic gets listed in Topics, as shown in Figure 46, and the ARN may be copied from the Topics as well.
Figure 46. SNS Topics
In the Actions>Send notification to field, specify the ARN generated for the SNS Topic, as shown in Figure 47. Click on Create Alarm in the Alarm Preview section.
Figure 47. Create Alarm
The Oracle DB CPU Utilization Alarm alarm gets created, as shown in Figure 48.
Figure 48. Oracle DB CPU Utilization Alarm
Initially the alarm state cannot be determined due to insufficient data, as indicated by the INSUFFICIENT DATA alarm state, shown in Figure 49.
Figure 49. Alarm state cannot be determined due to INSUFFICIENT DATA
A CloudWatch alarm configured to send notification to an SNS Topic is of limited use, as a Database administrator would be required to monitor the SNS Dashboard constantly for the alarm state. A SNS Subscription should be created to send notification to a DBA to one of the several endpoints, which include sending via Email. To create an SNS Subscription, click on Create Subscription as shown in Figure 50.
Figure 50. Subscriptions> Create Subscription
The Create subscription dialog gets displayed as shown in Figure 51.
Figure 51. Create Subscription
In the Create subscription dialog, specify the Topic ARN and select a Protocol from the list. To send Email notifications, select Protocol as Email as shown in Figure 52. Specify Endpoint as the Email address to send notification to, and click on Create Subscription.
Figure 52. Selecting Subscription Protocol
A new subscription gets created and is initially “PendingConfirmation” as shown in Figure 53.
Figure 53. New Subscription pending confirmation
As sufficient data becomes available the alarm status becomes OK as shown in Figure 54, but the Config Status is Invalid notification, which is because the Subscription created is in Pending state till confirmed.
Figure 54. Alarm State and Config Status
Select the alarm, and the Details tab also indicates that the notification topic could not be found, as shown in Figure 55.
Figure 55. Alarm pending confirmation
When the Subscription is created, a message gets sent to the endpoint Email. Confirm the subscription in the confirmation message and a Subscription confirmed message gets displayed a shown in Figure 56.
Figure 56. Subscription confirmed
The Invalid Notification message in the Alarm Config Status and the Details tab get removed, as shown in Figure 57.
Figure 57. CloudWatch Alarm with Confirmed Status and OK State
With a CloudWatch Alarm created and configured with an SNS Topic and Subscription, if the CPUUtilization were to be >= 50, an Email notification would be sent to the subscriber.
An RDS Database generates events for the different stages of a database, such as a DB Instance being created or backed up. An Event Subscription may be created by selecting Event Subscriptions in the RDS Dashboard, as shown in Figure 58.
Figure 58. Event Subscription
Click on Create Event Subscription as shown in Figure 59.
Figure 59. Create Event Subscription
In the Create Event Subscription dialog specify a subscription Name (orcl-RDS) and select an SNS Topic (RDSCloudWatch), as shown in Figure 60.
Figure 60. Create Event Subscription
In Source Types select Instances (database), as shown in Figure 61 and select Enabled>Yes to enable the subscription.
Figure 61. Selecting Database Instances as Source Type
In Event Categories, Select All is selected by default. Alternatively, specific event categories may be selected from those listed. In Instances, Select All is selected by default and specific Database instances may be selected from those listed, as shown in Figure 62. Click on Create.
Figure 62. Selecting Event Categories and Instances
The event subscription gets created, as shown in Figure 63.
Figure 63. Event Subscription orcl-RDS
The Events generated by RDS Databases may be listed with the Events selection, as shown in Figure 64. By default all Events are listed but Events may be filtered.
Figure 64. Events
An Event Subscription’s details may be displayed by selecting the Go to Details page, as shown in Figure 65.
Figure 65. Go to Details Page
An Event Subscription may be updated with the Update button, which is not enabled till some update has been made, such as adding or removing an Event category or Database instance, as shown in Figure 66.
Figure 66. Update Option on Edit Event Subscription Details Page
The RDS Database Log files include all the logs generated by a database. Click on Logs to access the database logs as shown in Figure 67.
Figure 67. RDS Logs
The various time stamped database logs get listed, as shown in Figure 68. Click on view for a log entry to view the log entry detail as shown in Figure 68.
Figure 68. Listing Database Logs
The selected audit/ORCL log file gets displayed, as shown in Figure 69.
Figure 69. Log file audit/ORCL
The trace/alert_ORCL.log file is also available, as shown in Figure 70.
Figure 70. The trace/alert_ORCL.log File
Click on “view” button shown in Figure 70 for the trace/alert_ORCL.log to display the log file shown in Figure 71.
Figure 71. Log File trace/alert_ORCL.log
The Refresh Log button may be used to refresh the trace/alert_ORCL.log log file, as shown in Figure 72.
Figure 72. Refresh Log
The Show Monitoring drop-down provides options to Show Multi Graph View, Show Single Graph View, Show Latest Metrics View, and Enhanced Monitoring, as shown in Figure 73.
Figure 73. Show Monitoring
The Multi Graph View consists of multiple graphs, one each for CPU Utilization, DB Connections, Free Storage Space, Freeable Memory, Write IOPS, and Read IOPS, as shown in Figure 74.
Figure 74. Multi Graph View
The Single Graph View displays the graphs as a single graph for a chosen metric, as shown for CPU Utilization (Percent) in Figure 75.
Figure 75. Single Graph View
The Latest Metrics View displays the SQL, System, and Deployment metrics in the selected recent time range, as shown in Figure 76.
Figure 76. Latest Metrics View>SQL
The System metrics are shown in Figure 77.
Figure 77. Latest Metrics View>System
Enhanced Monitoring for Oracle Database became available in May 2016 and consists of 56 System metrics and Per Process metrics, and aggregated process information at a granularity of 1 second. Enhanced monitoring is not available for t1.micro and m1.small instance types. Enhanced Monitoring includes a Dashboard, shown in Figure 78, and a Process List.
Figure 78. Enhanced Monitoring
In this article we discussed the proactive database management that is provided to an Oracle database instance, or one of the other supported databases, on AWS RDS. The CloudWatch RDS Metrics, CloudWatch Logs, custom CloudWatch Alarms, RDS Events Notification, RDS Database Log files, and RDS Dashboard Monitoring are integrated with RDS and do not require much additional configuration.
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.