Migrating Cron/Scheduled Jobs to AWS Lambda for Amazon RDS for Oracle

By optimizing cloud computing expenses, AWS Lambda delivers several financial benefits:

• Eliminating resource over-provisioning to reduce cloud bills
• Offering fully variable costs that mitigate financial risks
• Streamlining infrastructure to lessen the need for large DevOps budgets

Additionally, migrating cron jobs to AWS Lambda is straightforward, as organizations can replace traditional scheduled tasks with event-driven, automated functions that scale seamlessly without requiring extensive infrastructure management.

AWS Lambda is a serverless compute service that allows you to run code without provisioning or managing servers, executing code in response to events, and automatically managing the underlying compute resources.

What are the Key Benefits of AWS Lambda for Your Amazon RDS for Oracle Databases

Serverless Architecture

Lambda eliminates the need to manage servers, allowing you to focus on writing and deploying code without worrying about infrastructure provisioning, maintenance, or scaling.

Automatic Scaling

You can use this service to scale your code up or down based on demand, ensuring optimal performance and cost-efficiency, even during peak loads.

Pay-as-you-go Pricing

You only pay for the compute time you consume, with no charges when your code isn’t running, making it a cost-effective solution.

Event-Driven Architecture

Lambda functions can be triggered by various events, such as HTTP requests, S3 object uploads, DynamoDB stream events, or other AWS services, enabling the creation of event-driven applications.

Simplified Development

This service simplifies the development process by allowing you to write and deploy code in a variety of languages (including Python, Node.js, Java, Go, etc.) without needing to manage the underlying infrastructure.

High Availability and Reliability

Lambda is designed for high availability and reliability, running your code across multiple AWS Availability Zones, ensuring that your applications are always available.

Integration with Other AWS Services

Using Lambda alongside other AWS services is seamless, allowing you to build complex applications and workflows by combining the capabilities of different services.

Code Reusability

Lambda layers allow you to reuse code and dependencies across multiple Lambda functions, reducing code duplication and improving maintainability.

Although AWS Lambda has a lot of functionality, in this guide, you’ll specifically learn how to move Amazon RDS for Oracle Database-related custom cron jobs and the post-refresh steps in AWS Lambda.

Before we get started, here are some assumptions and considerations to keep in mind as you follow along:

• You have privileges to create procedures, functions, and packages in the database.
• You are a user in AWS with the appropriate IAM roles and permissions to execute the Lambda.
• You have the details of a Database user with the required privileges to execute the database objects that are stored in the AWS Secrets Manager to use them in the Lambda function.
• An Amazon Simple Storage Service(S3) gateway endpoint in the Amazon Virtual Private Cloud (VPC) to allow you to deploy the Lambda function along with the Amazon RDS for Oracle Database instance.
• An Amazon S3 bucket to upload the .zip file to.
• A security group and subnets to use in the RDS for Oracle database and Lambda function, and that will allow you to enable the needed port to connect to the database using SQL*Plus or cx_Oracle from the function.
• A Lambda execution role
• A Lambda function using the Lambda layer and execution role
• Connection string for Oracle RDS.

Here is what this architecture should look like:

Connect to Your Database Instance with a Lambda Function

For connecting to your Amazon RDS for Oracle DB instance from a Lambda function for running SQL scripts, you can use options such as JDBC and ODBC drivers, or Oracle Client libraries.

Your AWS CloudFormation custom resource is capable of calling a Lambda function. You’ll be able to run SQL scripts as soon as you finish the Amazon RDS for Oracle Database creation process.

To connect with an RDS for Oracle DB instance from Lambda, you create a Lambda function using a Lambda layer. This layer requires the following executables:

• Oracle client tools such as SQL*Plus and Oracle client libraries
• The cx_Oracle Python module enabling access to Oracle Database

To implement this solution, you create the following:

• Download the appropriate Linux and Python version of the cx_Oracle package.
• Download the Oracle instant client and tools in .zip format for your Linux OS. Specifically, you need the instant client Basic Light package, as it contains the cx_Oracle and SQL*Plus libraries.
• Also get the SQL*Plus package if you want to use the SQL*Plus utility to run SQL scripts against the RDS for Oracle Instance.

Make sure you have these libraries according to the Oracle version.

Next, create a .zip file from the files we downloaded.

1. This .zip or archive file includes the Oracle client libraries, cx_Oracle, and custom Lambda Python scripts.
2. Upload this zip file in the Lambda function in the Lambda layer.
3. Create a CloudWatch event rule to trigger the Lambda function on a schedule like cron or deploy it manually when required.

Here are a few examples of Python scripts with the usage of database login from the AWS secret, usage of dbms output, usage of email once the job is done, and usage of procedure execution from it to the temp directory.

Example of a script connecting to the Oracle database and executing a procedure named testvj:

import cx_Oracle

import os

import sys

import json

def lambda_handler(event, context):

con = cx_Oracle.connect('/@rds.amazonaws.com/db')

print(con.version)

cur = con.cursor()
 
cur.callproc('testvj')

con.close()

Example of a script using the AWS secret, which has the database connection username and password. Note: The below get_secret function can be fetched from your AWS Secret.

import boto3

import base64

from botocore.exceptions import ClientError

import cx_Oracle

import json

def get_secret():

secret_name = "xxxx"

region_name = "xxxx"

# Create a Secrets Manager client

session = boto3.session.Session()

client = session.client(

service_name='secretsmanager',

region_name=region_name

)

# In this sample we only handle the specific exceptions for the 'GetSecretValue' API.

# See https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_GetSecretValue.html

# We rethrow the exception by default.

try:

get_secret_value_response = client.get_secret_value(

SecretId=secret_name

)

except ClientError as e:

if e.response['Error']['Code'] == 'DecryptionFailureException':

# Secrets Manager can't decrypt the protected secret text using the provided KMS key.

# Deal with the exception here, and/or rethrow at your discretion.

raise e

elif e.response['Error']['Code'] == 'InternalServiceErrorException':

# An error occurred on the server side.

# Deal with the exception here, and/or rethrow at your discretion.

raise e

elif e.response['Error']['Code'] == 'InvalidParameterException':

# You provided an invalid value for a parameter.

# Deal with the exception here, and/or rethrow at your discretion.

raise e

elif e.response['Error']['Code'] == 'InvalidRequestException':

# You provided a parameter value that is not valid for the current state of the resource.

# Deal with the exception here, and/or rethrow at your discretion.

raise e

elif e.response['Error']['Code'] == 'ResourceNotFoundException':

# We can't find the resource that you asked for.

# Deal with the exception here, and/or rethrow at your discretion.

raise e

else:

# Decrypts secret using the associated KMS CMK.

# Depending on whether the secret is a string or binary, one of these fields will be populated.

if 'SecretString' in get_secret_value_response:

secret = get_secret_value_response['SecretString']

return secret

else:

decoded_binary_secret = base64.b64decode(get_secret_value_response['SecretBinary'])

return decoded_binary_secret

# Your code goes here.

def lambda_handler(event, context):

print('Starting Lambda')

username = (json.loads(get_secret())['username'])

print('Username is ' + username)

key = (json.loads(get_secret())['password'])

host = (json.loads(get_secret())['host'])

db = (json.loads(get_secret())['dbname'])

con = cx_Oracle.connect(username + '/' + key + '@' + host + '/' + db)

print(con.version)

cur = con.cursor()

cur.callproc('testvj')

con.close()

Example of using notifications:

def lambda_handler(event, context):

print('Starting Lambda')

username = (json.loads(get_secret())['username'])

print('Username is ' + username)

key = (json.loads(get_secret())['password'])

host = (json.loads(get_secret())['host'])

db = (json.loads(get_secret())['dbname'])

con = cx_Oracle.connect(username + '/' + key + '@' + host + '/' + db)

print(con.version)

cur = con.cursor()

cur.callproc("dbms_output.enable", (None,))

cur.callproc('build_notify_proc')

#cur.callproc('build_notify_proc1')

statusVar = cur.var(cx_Oracle.NUMBER)

lineVar = cur.var(cx_Oracle.STRING)

message = []

while True:

cur.callproc("dbms_output.get_line", (lineVar, statusVar))

if statusVar.getvalue() != 0:

break

print(lineVar.getvalue())

message.append(lineVar.getvalue())

con.close()

if message != []:

client = boto3.client('sns')

response = client.publish(

TargetArn='arn:aws:sns:us-east-1:xxxxxx:Emailtest',

Message=json.dumps({'default': json.dumps(message)}),

MessageStructure='json'

)

Cron/Scheduling

There are several ways to schedule the Lambda function.

One method is using CloudWatch. Here’s how you would configure CloudWatch to invoke the Lambda functions.

To configure CloudWatch to invoke the Lambda functions:

1. Open the Functions page on the Lambda console.
2. Choose the Lambda function.
3. Under Designer, choose Add trigger.
4. Set the trigger type to CloudWatch Events/EventBridge.
5. For Rule, choose Create a new rule.
6. Fill in the Rule name and Rule description.
7. For rule type, select Schedule expression.
8. In the Schedule expression field, enter a cron expression. For example, cron(0 12 ? * MON-FRI *).
9. Choose Add.

Moving Forward with Lambda

Migrating your Oracle database cron jobs to AWS Lambda represents more than a technical upgrade. By eliminating server maintenance overhead and paying only for actual compute time, your organization can redirect resources from infrastructure management to business-critical initiatives.

The serverless approach transforms how your database operations scale and respond to demand. Instead of maintaining dedicated servers for periodic tasks, Lambda functions execute precisely when needed, automatically scaling based on workload requirements. This shift reduces both operational complexity and infrastructure costs while improving reliability through AWS’s built-in redundancy.

For Oracle environments running on AWS RDS, this migration path offers immediate benefits: simplified deployment processes, reduced maintenance windows, and enhanced integration with other AWS services.