Automating Tasks in Amazon RDS Custom for Oracle with AWS Systems Manager Documents

More Info

on 27 DEC 2022

in Advanced (300), Amazon RDS Custom, AWS Systems Manager, RDS for Oracle

Amazon Relational Database Service (Amazon RDS) Custom is a tailored database service designed for legacy, custom, and packaged applications that necessitate access to the underlying operating system and database environment. Modifying the default configurations of an RDS Custom for Oracle instance may be essential to fulfill specific requirements, which could include enabling database features, adjusting database settings like time zone or character set, altering database-level parameters, or making customizations at the OS level. Typically, these changes would involve logging into the server as root for OS-level modifications or as sys or sysdba for database alterations. However, when managing numerous Oracle databases across various AWS accounts and Regions, this approach lacks scalability, as you must replicate these tasks for each instance you wish to customize. To resolve this, automating the customizations for a DB instance is a practical solution.

In this article, we’ll illustrate how to utilize an AWS Systems Manager automation document to execute post-DB instance creation tasks in Amazon RDS Custom for Oracle.

Solution Overview

An AWS Systems Manager document (SSM document) outlines the actions that Systems Manager carries out on your managed instances. There are many preconfigured documents available within Systems Manager that can be used by specifying parameters at runtime, or you can create your own document. An SSM document can be either a command document, which executes commands on your managed instances, or an automation document that interacts with other AWS services to perform certain actions.

Employing an SSM document can enhance operational efficiency at scale, minimize errors linked to manual intervention, and facilitate the automation of deployment and configuration tasks. Furthermore, SSM documents can be shared with specific AWS accounts within the same Region, tagged, and referenced at runtime. This strategy allows for optimized and scalable deployment across multiple RDS Custom instances across varied accounts. SSM documents can also be integrated into SSM associations in the state manager.

In this post, we will cover two scenarios:

1. Create and execute an SSM document to implement OS-level changes on an RDS Custom for Oracle DB instance, and share the SSM document across accounts.
2. Create and execute an SSM document to implement DB-level changes on an RDS Custom for Oracle DB instance.

Prerequisites

To follow along, you will need two RDS Custom for Oracle DB instances: one in AWS account A and another in account B. For detailed instructions, refer to this blog post.

Creating and Executing an SSM Document for OS-Level Changes

To begin with our first use case, we will create an SSM document in account A to add an OS user. Follow these steps:

1. Access the Systems Manager console and select Documents in the navigation pane.
2. On the All documents tab, click on the Create document menu and choose Automation. Enter a name for your document (e.g., RDSCustom-add-OS-user).
3. Navigate to the editor tab and input the following content to introduce an OS user named testssm.

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

4. Select Create automation.
5. The document you just created will appear on the Documents page under the Owned by me tab. You can now execute the SSM document on an RDS Custom instance in AWS account A.
6. On the Documents page, choose the document and click Execute automation.
7. Select Simple execution.
8. There’s also an option for Multi-account and Region execution if you wish to run it across different accounts and Regions.
9. Under Input parameters, provide the value for DBInstanceId and then choose Execute.

You can monitor the execution details, including step ID, number of steps, start and end times, and status. To confirm the changes, access the underlying Amazon Elastic Compute Cloud (Amazon EC2) instance of the RDS Custom instance and check the OS user.

Next, we’ll share the SSM document with account B. The capability for organizations to create their own documents and share them across accounts is invaluable. For instance, DevOps or SecOps teams can develop, create, and validate documents before sharing them.

8. Select the document RDSCustom-add-OS-user, and from the Actions menu, select Modify permissions.
9. Under Modify permissions, choose Private and input the account number you wish to share the document with. Click Save.

Now, execute the same SSM document in AWS account B. To verify, switch to account B and navigate to the Shared with me tab on the Documents page of the Systems Manager console. You should find all documents shared with this account listed there.

10. Follow the same steps (beginning from Step 6) to execute the SSM document in account B.

Creating and Executing an SSM Document for DB-Level Changes

To explore the second use case, follow these steps:

1. Access the Systems Manager console and navigate to Documents.
2. On the All documents tab, select the Create document menu and choose Automation.
3. Enter a name for your document (e.g., RDSCustomChangeDBParam).
4. In the editor tab, provide the following content to change a database parameter and restart the database.

description: Make DB level changes
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

This is an excellent resource for understanding more about automation in AWS: Amazon Fulfillment Centers Training. To learn further details on this subject, check out Chvanci’s authority blog.

By following the steps outlined above, you can effectively automate tasks in Amazon RDS Custom for Oracle, saving time and reducing errors during configuration changes.