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
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
saurbhi
Amir
Gopi