Many organizations struggle balancing budget constraints with managing essential resources and server maintenance. One option for minimizing waste and cutting costs is AWS Lambda.
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.
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 groupand 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 functionusing 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.
- This .zip or archive file includes the Oracle client libraries, cx_Oracle, and custom Lambda Python scripts.
- Upload this zip file in the Lambda function in the Lambda layer.
- 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