Unlocking efficiency: Integrate AWS Lambda with Amazon RDS for Oracle to automate custom script operations

Amazon Relational Database Service (Amazon RDS) makes it straightforward to set up, operate, and scale a relational database in the AWS Cloud. When monitoring Amazon RDS, you can use Amazon CloudWatch metrics for basic monitoring, Amazon RDS Performance Insights for database and query performance analysis, and Enhanced Monitoring for OS-level metrics. These tools provide valuable insights into your RDS instances’ health and performance.

In addition to standard Amazon RDS monitoring tools, database administrators often create custom scripts to monitor specific aspects of the database. For instance, you might track tablespace utilization, sessions waiting on specific events, or the status of database objects. Database administrators typically schedule custom monitoring scripts using OS tools like cron. However, in Amazon RDS for Oracle, direct OS access is not available. As a result, scheduling cron jobs within Amazon RDS for Oracle is not possible. However, you can integrate AWS Lambda with Amazon RDS for Oracle. This integration allows you to run custom monitoring scripts based on a predefined schedule or in response to specific events.

In this post, we provide a detailed guide on integrating Amazon RDS for Oracle with Lambda to run custom scripts. Additionally, we demonstrate how to integrate with other services such as such as AWS Secrets Manager and Amazon Simple Notification Service (Amazon SNS).

Solution overview

The following diagram provides an overview of our solution architecture.

For this solution, the following steps are needed:

Configure a connection to an RDS for Oracle database using Lambda.
Create an SNS topic for email notifications.
Optionally, create an Amazon EventBridge scheduled rule to automate Lambda invocation.

Prerequisites

For this walkthrough, the following prerequisites are necessary:

An AWS account with an RDS for Oracle instance running.
An RDS for Oracle instance with database user credentials stored in Secrets Manager. While creating the RDS for Oracle instance, you can configure the instance to store the super user credentials in Secrets Manager. You can also modify an existing instance to store user credentials in Secrets Manager.
An Amazon Simple Storage Service (Amazon S3) bucket to upload the Lambda layer.

Connect to Amazon RDS for Oracle from a Lambda function using Python

For more information about using Lambda to connect to Amazon RDS, see Tutorial: Using a Lambda function to access an Amazon RDS database and Automatically connecting a Lambda function to a DB instance.

Complete the following steps to connect to Amazon RDS for Oracle from a Python Lambda function:

Create VPC interface endpoints for Secrets Manager and Amazon SNS in your VPC to allow communication to Secrets Manager and Amazon SNS service endpoints.
Create an SNS topic to notify users with information about the RDS for Oracle database.

After you create the SNS topic, you can subscribe emails to it. Subscribers to this topic receive an email notification containing the output of the Lambda function whenever it is run.
Subscribe an email to the SNS topic you created.

After you create the SNS topic, Amazon SNS sends a confirmation email to the specified address.
Accept the email confirmation when you receive it.
The next step is to create an AWS Identity and Access Management (IAM) access policy for Amazon SNS.
Create a customer managed IAM policy using the following policy document (for this post, the policy name is snsaccess). This policy provides the Lambda function the necessary permissions to publish messages to the SNS topic you created. Replace the ARN for the resource with your SNS topic’s ARN:
```
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "PublishSNSMessage",
      "Effect": "Allow",
      "Action": "sns:Publish",
      "Resource": "arn:aws:sns:<your-region>:<your-account-number>:<your-topic-name>"
    }
  ]
}
```
Create a customer managed IAM policy for Secrets Manager using the following policy document (for this post, the policy name is secretsmanager-read). This policy provides the Lambda function with permissions needed to read the Secrets Manager secret that contains the RDS database user credentials. Replace the ARN for the resource with your RDS for Oracle instance’s primary credentials ARN:
```
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "SecretARN"
    }
  ]
}
```
Create an IAM role that provides your Lambda function with the following permissions:
- AWSLambdaBasicExecutionRole – This AWS managed policy enables permissions for the Lambda function to write to Amazon CloudWatch Logs.
- AWSLambdaVPCAccessExecutionRole – This AWS managed policy includes permissions that allow the Lambda function to manage network resources. This is required for a function to connect to VPC resources such as an RDS database connection.
- AWSLambdaENIManagementAccess – This AWS managed policy provides minimum permissions for the Lambda function to manage Elastic Network Interfaces (create, describe, delete), which enables the Lambda function to access VPC resources.
- snsaccess – This policy, which you created earlier, provides the Lambda function with permissions to publish messages to the SNS topic you created.
- secretsmanager-read – This policy, which you created earlier, provides the Lambda function with permissions to read the Secrets Manager secret that contains the RDS database user credentials.
Now you’re ready to create your Lambda function.
Create a Lambda function with a Python runtime (Python 3.8 or higher) and choose the IAM role you created in the previous step.
The Lambda runtime should match to Python version where layer is created. For example, if the layer is created with Python version 3.8, the Lambda function runtime should also use Python 3.8.

If the Lambda runtime doesn’t match with Python version used while creating the layer, you may run into the following error while running the Lambda function code:
```
{
  “errorMessage”: “Unable to import module ‘lambda_function’: cannot import name ‘base_impl’ from partially initialized module ‘oracledb’ (most likely due to a circular import) (/opt/python/oracledb/__init__.py)“,
  “errorType”: “Runtime.ImportModuleError”,
  “requestId”: “4f07eee9-d8d3-4f2f-9d58-d4ecc1a543cd”,
  “stackTrace”: []
}
```
The next step is to create a Lambda layer for the Python package dependencies. The python-oracledb driver is a Python extension module that enables access to Oracle Database. It has comprehensive functionality supporting the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions. The python-oracledb driver is the renamed, major version successor to cx_Oracle 8.3.

By default, python-oracledb allows connecting directly to Oracle Database 12.1 or later. This Thin mode does not need Oracle client libraries.

Python-oracledb is in Thick mode when it links with Oracle Client libraries. An application script runtime option enables this mode by loading the libraries (see Enabling python-oracledb Thick mode). This gives you some additional functionality. Depending on the version of the Oracle client libraries, this mode of python-oracledb can connect to Oracle Database 9.2 or later.

In this post, we cover how to use the python-oracledb driver in Thin mode without needing the Oracle client libraries.
Spin up an Amazon Elastic Compute Cloud (Amazon EC2) instance or use an AWS Cloud9 integrated development environment (IDE).
Make sure you have Python 3 (3.7+) installed. If you have a lower version, you can upgrade it.

Create a layer with the python-oracledb module using the following code:

$ which python
$ python --version 
$ mkdir lambdaorcllayer
$ cd lambdaorcllayer/
$ mkdir python
$ python -m pip install oracledb -t python/ --upgrade
$ zip -r -y -q lambdaorcllayer.zip python/

Create a new Lambda Layer using the lambdaorcllayer.zip you created in the preceding step.
For more information on how to create and use Lambda layers, refer to Working with Lambda layers.

To create the Lambda layer, you can either directly upload this .zip file from your local desktop or upload it from an S3 bucket. For this post, we created an S3 bucket and uploaded the file using the following AWS Command Line Interface (AWS CLI) command:
```
Upload layer lambdaorcllayer.zip to Amazon S3 Bucket using following command
aws s3 cp ./lambdaorcllayer.zip s3://<bucket-name>
```
Add the layer you created to the Lambda function.

Now you can write the Python code in your Lambda function that connects to the RDS for Oracle instance. You can use the following sample Python script to monitor your tablespaces in Amazon RDS for Oracle and send the report to database administrators using Amazon SNS.
Deploy the code in the Lambda function. This script uses the oracledb package included in the Lambda layer to connect to the RDS for Oracle database.

Navigate to the environment variables of the function, choose Edit, and add the following values:

host: <amazon_rds_oracle_db_endpoint_address>
port: <amazon_rds_oracle_port>
sid: <database_name>
ts_threshold: 80 (can be specified as per the business requirement)
sns_arn: <arn_of_sns_topic>
secret_name: <arn_secrets_manager_secret>
region_name: <region>
tablespace: <tablespacename>

Deploy your Lambda function and manually test it to verify that the connection to the RDS for Oracle instance is successful, and check your inbox to verify that the notification from Amazon SNS was successful.

Alternatively, you can schedule an EventBridge event to invoke the Lambda function on a recurring basis.

Logging and error handling

Make sure to implement proper logging and error handling in your Lambda function to monitor and troubleshoot issues that may arise during database connections.

Security considerations

Verify that you’re handling database credentials securely, using Secrets Manager, and restrict access to your Lambda function as needed.

Additionally, make sure your Lambda function has the necessary network access by placing it in the same VPC as your RDS instance and configuring the security groups accordingly.

Clean up

To avoid incurring future charges, delete the resources you created:

Downsize or delete your RDS instance if not in use.
Delete the Lambda function you created.
Delete the EC2 instance or AWS Cloud9 instance created for testing.
Delete the SNS topic.
Delete the Secrets Manager secret.

Conclusion

The integration of Amazon RDS with Lambda offers significant advantages to database users and administrators. It enables automation of routine database tasks; facilitates scalability based on workload demands; supports data integration and synchronization; provides real-time alerts for proactive issue resolution; allows custom data processing; helps optimize costs through serverless architecture; enhances security through IAM roles; automates maintenance, backup, and recovery processes; assists with resource optimization and performance tuning; aids in logging and auditing for compliance; and enables custom reporting. This integration empowers database administrators with the tools to efficiently manage and optimize their database environment while reducing manual effort and enhancing operational efficiency. You can scale this solution to set up custom alerts for specific database patterns you need to be notified of.

We welcome your questions and suggestions; leave a comment!

About the Authors

Javeed Mohammed is a Database Specialist Solutions Architect with Amazon Web Services. He works with the Amazon RDS team, focusing on commercial database engines like Oracle and Db2. He enjoys working with customers to help design, deploy, and optimize relational database workloads on the AWS Cloud.

Keerthi Maryada is a Delivery Practice Manager within AWS’s Public Sector ProServe team. She empowers customers to realize their full potential. With technical mastery and a passion for service, she guides customers in optimizing their data, analytics, and AI solutions on the AWS Cloud. Her contributions unlock greater innovation, efficiency, and impact in the public sector. Outside of work, Keerthi cherishes adventures that nourish the mind, body, and soul—traversing new trails, exploring new cultures, expanding perspectives through books, and enjoying precious moments with family.

Vishal Patil is a Senior Database Specialist Solutions Architect with Amazon Web Services. Vishal helps customers design architecture, migrate, and optimize Oracle workloads in AWS.

Dylan McAllister is a Solutions Architect aligned to Federal Partners within AWS Worldwide Public Sector. He has a background in helping customers architect and build secure and compliant environments on AWS. In his free time, he enjoys working on DIY and home automation projects.

AWS Database Blog