I wrote a very simple AWS Lambda function to demonstrate how to connect an Oracle database, gather the tablespace usage information, and send these metrics to CloudWatch. First, I wrote this lambda function in Python and then I had to re-write it in Java. As you may know, you need to use cx_oracle module to connect Oracle Databases with Python. This extension module requires some libraries which are shipped by Oracle Database Client (oh God!). It’s a little bit tricky to pack it for the AWS Lambda. Good thing is, I found a great document which explains all necessary steps.
Here’s the main class which will be used by Lambda function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
package com.gokhanatil.samplelambda; import com.amazonaws.services.cloudwatch.AmazonCloudWatch; import com.amazonaws.services.cloudwatch.AmazonCloudWatchClientBuilder; import com.amazonaws.services.cloudwatch.model.Dimension; import com.amazonaws.services.cloudwatch.model.MetricDatum; import com.amazonaws.services.cloudwatch.model.PutMetricDataRequest; import com.amazonaws.services.cloudwatch.model.StandardUnit; import com.amazonaws.services.lambda.runtime.*; import java.lang.Math; import java.sql.*; import java.util.Map; import java.util.Properties; public class Monitoring implements RequestHandler<Map<String, Object>, String> { public String handleRequest(Map<String, Object> input, Context context) { final AmazonCloudWatch cw = AmazonCloudWatchClientBuilder.defaultClient(); try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e1) { e1.printStackTrace(); } Connection conn; Properties connectionProps = new Properties(); connectionProps.put("user", System.getenv("DB_USER")); connectionProps.put("password", System.getenv("DB_PASSWORD")); try { conn = DriverManager.getConnection("jdbc:oracle:thin:@" + System.getenv("DB_HOSTNAME") + ":" + System.getenv("DB_PORT") + ":" + System.getenv("DB_DATABASE"), connectionProps); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics"); while (rs.next()) { Dimension dimension = new Dimension().withName("Tablespace") .withValue(System.getenv("DB_DATABASE") + ":" + rs.getString(1).toUpperCase()); MetricDatum datum = new MetricDatum().withMetricName("Space Used (pct)").withUnit(StandardUnit.None) .withValue((double) Math.round(rs.getDouble(2) * 100)).withDimensions(dimension); PutMetricDataRequest request = new PutMetricDataRequest().withNamespace("Databases") .withMetricData(datum); cw.putMetricData(request); } } catch (SQLException e) { e.printStackTrace(); } return "{'result': 'success'}"; } } |
Here’s the step by step explanation:
Line 15: I created a class named Monitoring – this will be the handler class. To be able to test it, it accepts JSON objects, this is why it implements “RequestHandler
Line 16: Definition of the function required to handle requests
Line 18: Taken from the AWS documents. It’s the object which we use to push metrics to CloudWatch
Line 20-24: Loading the JDBC class to be able to connect an Oracle database
Line 26-29: Defining the connection and credentials. As you see I fetch the required parameters from environment because Lambda let you define env variables
Line 32-33: Connecting to the database
Line 35: Creating a statement object to run queries
Line 37: As a sample, I query dba_tablespace_usage_metrics to get the usage percentage for tablespaces
Line 38: Loop for each record
Line 40-47: Preparing the metric I want to push to CloudWatch – The metric will stored as “Space Used (pct) for each tablespace in the “Databases” namespace in the customer metrics
Line 49: Pushes the metric data
Line 53-55: Mandatory exception handling block
Line 57: Returns a result (success)
You many notice a lot of “System.getenv” calls. I used it to read the environment parameters from the Lambda function.
You can download the Maven project from my github repository. After you build the maven project, you need to upload the JAR file to your S3 bucket:
|
1 |
aws s3 cp target/samplelambda-0.1-jar-with-dependencies.jar s3://yourbucketname/ |
I used this JAR file to create a lambda function on AWS Lambda console. My package name is “com.gokhanatil.samplelambda”, my class name is “Monitoring”, and the function name is “handleRequest”, this is why I entered “com.gokhanatil.samplelambda.Monitoring::handleRequest” as handler. Please note that, you need to give required permissions to grant access to CloudWatch (ie CloudWatchFullAccess), and AWSLambdaVPCAccessExecutionRole. You also need to enter the VPC information (Subnets: pick two subnets that can reach your Oracle instance, Security groups: select one that gives you access to your Oracle instance).
To schedule this job to run periodically, you can use “CloudWatch events”. This event will be triggered every 15 minutes, and launch the lambda function.
After enabling the lambda function, new metrics starts to appear in the CloudWatch dashboard. It’s possible to create an alarm for these metrics using the console or AWS CLI commands.
I hope it helps.
Yasir