Automate tasks in Amazon RDS Custom for Oracle using AWS Systems Manager documents

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying OS and DB environment.

You may need to modify the default configuration of an RDS Custom for Oracle instance to meet various requirements, such as enable database features or options, change database settings such as time zone or character set, change database-level parameters, or make customizations at the OS level. You can make these changes by logging in to the server either as root for OS-level changes or as sys or sysdba for database changes. However, if you have a large number of Oracle databases across multiple AWS accounts and Regions, this solution isn’t scalable, because you have to repeat these tasks on every DB instance you want to customize. To address this, you can automate the customizations performed on a DB instance.

In this post, we show how to use an AWS Systems Manager automation document to perform post-DB instance creation tasks in Amazon RDS Custom for Oracle.

Solution overview

An AWS Systems Manager document (SSM document) defines the actions that Systems Manager performs on your managed instance. Systems Manager includes many preconfigured documents that you can use by specifying parameters at runtime, or you can create your own document. An SSM document can be a command document, which is used to run commands on your managed instances, or an automation document, which you can use to interact with other AWS services to perform some actions.

Using an SSM document can help improve operational efficiency at scale, reduce errors associated with manual intervention, and automate deployment and configuration tasks. In addition, you can share SSM documents with specific AWS accounts within the same Region. They can also be tagged and referenced at runtime. With this approach, you can optimize and scale deployment across multiple RDS Custom instances across multiple accounts. SSM documents can also be part of SSM associations in the state manager.

In this post, we demonstrate two scenarios:

• Create and run an SSM document to make OS-level changes on an RDS Custom for Oracle DB instance and share the SSM document across accounts
• Create and run an SSM document to make DB-level changes on an RDS Custom for Oracle DB instance

Prerequisites

To follow along with this post, you need two RDS Custom for Oracle DB instances: one in AWS account A and another in account B. For instructions, see Working with RDS Custom for Oracle.

Create and run an SSM document to make OS-level changes on an RDS Custom for Oracle DB instance

To start exploring our first use case, we first create an SSM document in account A to create an OS user. Complete the following steps:

1. On the Systems Manager console, choose Documents in the navigation pane.
2. On the All documents tab, on the Create document menu, choose Automation.Enter a name for your document (for example, RDSCustom-add-OS-user).
3. Go to the editor tab and enter the following content to add an OS user called testssm.

In this document, we get the instance ID and the DB ID through API calls and then run a shell script to add the OS user.

description: Create OS User
schemaVersion: '0.3'
assumeRole: '{{ AutomationAssumeRole }}'
parameters:
DBInstanceId:
type: String
description: (Required) Identifies the *RDS* instance subject to action
AutomationAssumeRole:
type: String
description: (Optional) The ARN of the role that allows Automation to perform the actions on your behalf.
default: ''
mainSteps:
- name: GetDBId
action: 'aws:executeAwsApi'
onFailure: Abort
inputs:
Service: rds
Api: DescribeDBInstances
DBInstanceIdentifier: '{{DBInstanceId}}'
outputs:
- Name: DbiResourceId
Selector: '$.DBInstances[0].DbiResourceId'
Type: String
- name: GetInstId
action: 'aws:executeAwsApi'
onFailure: Abort
inputs:
Service: ec2
Api: DescribeInstances
Filters:
- Name: 'tag:Name'
Values:
- '{{GetDBId.DbiResourceId}}'
outputs:
- Name: InstanceId
Selector: '$.Reservations[0].Instances[0].InstanceId'
Type: String
- name: AddOSuser
action: 'aws:runCommand'
inputs:
DocumentName: AWS-RunShellScript
InstanceIds:
- '{{GetInstId.InstanceId}}'
Parameters:
commands:
- sudo useradd testssm
- id testssm
- sudo usermod -a -G rdsdb testssm
- id testssm
isEnd: true

5. Choose Create automation.

The document you created appears on the Documents page, on the Owned by me tab.

Now you can run the SSM document on an RDS Custom instance in AWS account A.

6. On the Documents page, select the document to run and choose Execute automation.
7. Select Simple execution.

There is also an option to select Multi-account and Region if you want to run in multiple accounts and Regions.

8. Under Input parameters, enter the value for DBInstanceId.
9. Choose Execute.

You can monitor the run details, along with step ID, numbers, start and end time, and status, as shown in the following screenshot.

10. To verify, log in to the underlying Amazon Elastic Compute Cloud (Amazon EC2) instance of the RDS Custom instance and check the OS user.

Now we can share the SSM document with account B. The ability for an organization to develop their own documents and share them across accounts is valuable. For example, the DevOps or SecOps groups can develop, create, and validate documents and share them across accounts.

11. Choose the document RDSCustom-add-OS-user and on the Actions menu, choose Modify permissions.

12. Under Modify permissions, select Private and enter the account number with which you want to share the document.
13. Choose Save.

14. Run the same SSM document in AWS account B.
15. To verify, switch to account B and on the Documents page of the Systems Manager console, navigate to the Shared with me tab.

You should see all the documents shared with this account on this tab.

16. Follow the same steps (starting from Step 6) to run the SSM document in account B.

Create and run an SSM document to make DB-level changes on an RDS Custom for Oracle DB instance database

To explore this use case, complete the following steps:

1. On the Systems Manager console, choose Documents in the navigation pane.
2. On the All documents tab, on the Create document menu, choose Automation.
3. Enter a name for your document (for example, RDSCustomChangeDBParam).
4. Go to the editor tab and enter the following content to make a database parameter change and restart the database.

In this document, we use API calls to pause RDS Custom automation and get the DB ID and instance ID. Then we use RunCommand and run shell scripts to make the database parameter change (set the Oracle DB parameter called job_queue_processes to 200) and restart the database. Finally, we use the API to resume the custom DB automation.

description: Make DB level changes
description: Stop RDS instance
schemaVersion: '0.3'
assumeRole: '{{ AutomationAssumeRole }}'
parameters:
DBInstanceId:
type: String
description: (Required) Identifies the *RDS* instance subject to action
AutomationAssumeRole:
type: String
description: (Optional) The ARN of the role that allows Automation to perform the actions on your behalf.
default: ''
mainSteps:
- name: PauseAutomation
action: 'aws:executeAwsApi'
inputs:
Service: rds
Api: ModifyDBInstance
DBInstanceIdentifier: '{{DBInstanceId}}'
AutomationMode: all-paused
- name: WaitForAvailableState
action: 'aws:waitForAwsResourceProperty'
maxAttempts: 10
timeoutSeconds: 600
onFailure: Abort
inputs:
Service: rds
Api: DescribeDBInstances
DBInstanceIdentifier: '{{DBInstanceId}}'
PropertySelector: '$.DBInstances[0].DBInstanceStatus'
DesiredValues:
- automation-paused
- name: GetDBId
action: 'aws:executeAwsApi'
onFailure: Abort
inputs:
Service: rds
Api: DescribeDBInstances
DBInstanceIdentifier: '{{DBInstanceId}}'
outputs:
- Name: DbiResourceId
Selector: '$.DBInstances[0].DbiResourceId'
Type: String
- name: GetInstId
action: 'aws:executeAwsApi'
onFailure: Abort
inputs:
Service: ec2
Api: DescribeInstances
Filters:
- Name: 'tag:Name'
Values:
- '{{GetDBId.DbiResourceId}}'
outputs:
- Name: InstanceId
Selector: '$.Reservations[0].Instances[0].InstanceId'
Type: String
- name: ChangeDBParam
action: 'aws:runCommand'
inputs:
DocumentName: AWS-RunShellScript
InstanceIds:
- '{{GetInstId.InstanceId}}'
Parameters:
commands:
- sudo su - rdsdb -c "sqlplus '/ as sysdba'"<<EOF
- alter system set job_queue_processes=200 scope=both;
- exit
- EOF
- name: RestartDB
action: 'aws:runCommand'
inputs:
DocumentName: AWS-RunShellScript
InstanceIds:
- '{{GetInstId.InstanceId}}'
Parameters:
commands:
- echo shutdown immediate | sudo su - rdsdb -c "sqlplus '/ as sysdba'"
- echo startup | sudo su - rdsdb -c "sqlplus '/ as sysdba'"
- name: ResumeAutomation
action: 'aws:executeAwsApi'
inputs:
Service: rds
Api: ModifyDBInstance
DBInstanceIdentifier: '{{DBInstanceId}}'
AutomationMode: full
isEnd: true

5. Choose Create automation.

The document you created appears on the Documents page, on the Owned by me tab.

Let’s complete two verifications before we run the SSM document on an RDS Custom instance in account A.

6. Check the value of DB parameter job_queue_processes in instance dbcust2.

As shown in the following screenshot, it’s currently set to 50.

7. Verify that the RDS Custom instance has automation enabled.

8. Select the document to run and choose Execute automation.
9. Select Simple execution.
10. Under Input parameters, enter the value for DBInstanceId.
11. Choose Execute.

You can monitor the run details, along with step ID, numbers, start and end time, and status. The following screenshot shows that the first step of pausing the custom automation is complete, and step 2 is in progress.

12. Verify the automation is paused on the custom instance, which was triggered via the SSM document.

All the steps in the SSM document are now complete.

13. To verify, log in to the underlying EC2 instance of the RDS Custom instance and check the job_queue_processes parameter.

Run Systems Manager automations across multiple Regions and accounts

You can run Systems Manager automations across multiple AWS Regions and AWS accounts. Running automations in multiple Regions and accounts or organization units reduces the time required to administer your AWS resources while enhancing the security of your computing environment.

When you run an automation across multiple Regions and accounts, you target resources by using tags or the name of an AWS resource group. The resource group must exist in each target account and Region, and the resource group name must be the same in each target account and Region.

For more information, refer to Running automations in multiple AWS Regions and accounts.

Summary

In this post, we showed how you can use SSM documents to automate post-instance creation tasks in RDS Custom for Oracle DB instances. SSM documents can be shared across AWS accounts and can also be tagged and referenced at runtime. Using SSM documents with RDS Custom helps optimize and scale deployment across multiple RDS Custom instances across multiple accounts.

To learn more about Amazon RDS Custom and automation using SSM, refer to the following resources:

• Working with Amazon RDS Custom for Oracle
• AWS System Manager documents
• Creating SSM documents
• Sharing SSM documents

Share your thoughts in the comments section.

About the Authors

Yamuna Palasamudram is a Senior Database Specialist Solutions Architect with Amazon Web Services. She works with AWS RDS team, focusing on commercial database engines like Oracle. She enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.

Nitin Saxena is a Senior Database Engineer in RDS DBS Managed Commercial Engines with Amazon Web Services. He focuses on services like RDS Oracle and RDS Custom for Oracle . He enjoys designing and developing new features on RDS Oracle and RDS Custom to solve customer problems.