AWS Database Blog

Securely assess database schema migrations using AWS SCT, Amazon RDS for Oracle, and AWS Secrets Manager

Database migration is a multi-step process comprised of assess, mobilize, and modernize phases with different tools and technologies involved. You can use tools such as AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) to accelerate each of these phases.

An important part of AWS SCT is the report that it generates to help you convert your schema. When you want to determine the best target direction for your overall environment that is comprised of multiple servers, the easiest way is to create a multiserver assessment report.

A multiserver assessment report evaluates multiple servers based on input that you provide for each schema definition that you want to assess. Your schema definition contains database server connection parameters and the full name of each schema. After assessing each schema, AWS SCT produces a summary and aggregated assessment report for database migration across the selected servers.

When running a multiserver assessment report, it is not recommended to store hardcoded credentials as a plain text in connection parameters, as anyone with access to the connections file would be able to read those secrets. Additionally, it is generally required that you open encrypted connections to a database to protect server-client communications and authentication using Secure Sockets Layer (SSL) certificates. AWS SCT build 660 includes support for AWS Secrets Manager and SSL features.

In this post, we demonstrate how you can create a multiserver assessment report using an Oracle Database as a source for AWS SCT with integration of Secrets Manager for storing the database credentials and SSL to open encrypted connections to the source.

Solution overview

Our use case for this post involves AWS SCT connecting to an Amazon Relational Database Service (Amazon RDS) for Oracle database enabled with SSL encryption using database credentials that you store in Secrets Manager for generating a multiserver assessment report to a target Amazon Aurora PostgreSQL-Compatible Edition database. Secrets Manager and SSL features work independently; we show a common use case to demonstrate the integration of both these features with AWS SCT.

At a high level, the solution steps are as follows:

  1. Enable SSL encryption for an RDS for Oracle DB instance by adding the Oracle SSL option to the option group associated with an Oracle DB instance.
  2. Download, install, and launch the latest version of AWS SCT.
  3. Store database credentials in Secrets Manager.
  4. Configure JDBC drivers in AWS SCT global settings.
  5. Set up AWS SCT specifications to update global settings with AWS service profiles to access Secrets Manager and the SSL trust store to open encrypted connections to the source database.
  6. Create a multiserver assessment report for database migration using the AWS SCT GUI.
  7. Create a multiserver assessment report for database migration using the AWS SCT CLI.

The following diagram illustrates the architecture.

The architecture consists of the following components:

  1. An RDS for Oracle DB instance with SSL enabled
  2. Secrets Manager to store database credentials
  3. AWS SCT for generating the multiserver assessment report
  4. A sample AWS SCT target engine (for this post, an Aurora PostgreSQL database)

Prerequisites

To implement this solution, you must have an RDS for Oracle DB instance with the latest version inside a VPC. We use Oracle Database 19c (19.0.0.0) on Amazon RDS for this demonstration.

Enable SSL encryption for an RDS for Oracle DB instance

Amazon RDS supports SSL encryption for Oracle DB instances. With SSL, you can encrypt a connection between your AWS SCT application client and your Oracle DB instance. To enable SSL encryption for an Oracle DB instance, add the Oracle SSL option to the option group associated with the DB instance. Amazon RDS uses a second port, as required by Oracle, for SSL connections. Doing this allows both clear text and SSL-encrypted communication to occur at the same time between a DB instance and an Oracle client. For example, you can use the port with clear text communication to communicate with other resources inside a VPC while using the port with SSL-encrypted communication to communicate with resources outside the VPC.

SSL/TLS connections provide one layer of security by encrypting data that moves between your client and a DB instance. Using a server certificate provides an extra layer of security by validating that the connection is being made to an RDS DB instance. It does so by checking the server certificate that is automatically installed on all DB instances that you provision.

Add the SSL option

To use SSL, your RDS for Oracle DB instance must be associated with an option group that includes the SSL option.

  1. Create a new option group or identify an existing option group to which you can add the SSL option.
  2. Add the SSL option to the option group.
    If you want to use only FIPS-verified cipher suites for SSL connections, set the option FIPS.SSLFIPS_140 to TRUE. For information about the FIPS standard, see FIPS support.For information about adding an option to an option group, see Adding an option to an option group.
  3. Modify an Oracle DB instance to associate the option group with it.

For information about creating a DB instance, see Creating an Amazon RDS DB instance. For information about modifying a DB instance, see Modifying an Amazon RDS DB instance.

Set up an SSL connection over JDBC

To use an SSL connection over JDBC, you must create a keystore, and trust the Amazon RDS root CA certificate.

  1. To create the keystore in JKS format, use the following command. It’s recommended to use the default keystore and run from Amazon Elastic Compute Cloud (Amazon EC2). 
    keytool -keystore clientkeystore -genkey -alias client

    You get the following output:

    876x4xx3x12x:ssl_wallet bxxudxvx$ keytool -keystore clientkeystore -genkey -alias client
Enter keystore password:  
Re-enter new password: 
What is your first and last name?
  [Unknown]:  dmaf-test-instance.xxxxxrvgexxx.us-east-1.rds.amazonaws.com
What is the name of your organizational unit?
  [Unknown]:  RDS
What is the name of your organization?
  [Unknown]:  Amazon.com
What is the name of your City or Locality?
  [Unknown]:  Seattle
What is the name of your State or Province?
  [Unknown]:  Washington
What is the two-letter country code for this unit?
  [Unknown]:  US
Is CN=dmaf-test-instance.xxxxxrvgexxx.us-east-1.rds.amazonaws.com, OU=RDS, O=Amazon.com, L=Seattle, ST=Washington, C=US correct?
  [no]:  yes

    Next, take the following steps to trust the Amazon RDS root CA certificate.

  2. Download the root certificate that works for all AWS Regions and put the file in a directory.
    You get the following output:

    876x4xx3x12x:ssl_wallet bxxudxvx$ pwd
/Users/bxxudxvx/Downloads/ssl_wallet
876x4xx3x12x:ssl_wallet bxxudxvx$ ls -lrth
total 16
-rw-r--r--@ 1 bxxudxvx  staff   1.4K May  9 19:29 rds-ca-2019-root.pem
-rw-r--r--  1 bxxudxvx  staff   2.5K May 10 07:49 clientkeystore
  3. Convert the certificate to .der format using the following command (replace the file name with the one you downloaded): 
    openssl x509 -outform der -in rds-ca-2019-root.pem -out rds-ca-2019-root.der

    You get the following output:

    876x4xx3x12x:ssl_wallet bxxudxvx$ openssl x509 -outform der -in rds-ca-2019-root.pem -out rds-ca-2019-root.der
876x4xx3x12x:ssl_wallet bxxudxvx$ ls -lrth
total 24
-rw-r--r--@ 1 bxxudxvx  staff   1.4K May  9 19:29 rds-ca-2019-root.pem
-rw-r--r--  1 bxxudxvx  staff   2.5K May 10 07:49 clientkeystore
-rw-r--r--  1 bxxudxvx  staff   1.0K May 10 07:53 rds-ca-2019-root.der
  4. Import the certificate into the keystore using the following command: 
    keytool -import -alias rds-root -keystore clientkeystore.jks -file rds-ca-2019-root.der

    You get the following output:

    876x4xx3x12x:ssl_wallet bxxudxvx$ keytool -import -alias rds-root -keystore clientkeystore.jks -file rds-ca-2019-root.der
Enter keystore password:  
Re-enter new password: 
Owner: CN=Amazon RDS Root 2019 CA, OU=Amazon RDS, O="Amazon Web Services, Inc.", ST=Washington, L=Seattle, C=US
Issuer: CN=Amazon RDS Root 2019 CA, OU=Amazon RDS, O="Amazon Web Services, Inc.", ST=Washington, L=Seattle, C=US
…
Trust this certificate? [no]:  yes
Certificate was added to keystore
  5. Confirm that the keystore was created successfully: 
    keytool -list -v -keystore clientkeystore.jks
  6. Enter the keystore password when you’re prompted for it.

You get the following output:

876x4xx3x12x:ssl_wallet bxxudxvx$ keytool -list -v -keystore clientkeystore.jks
Enter keystore password:  
Keystore type: PKCS12
Keystore provider: SUN

Your keystore contains 1 entry

Alias name: rds-root
Creation date: 10-May-2022
Entry type: trustedCertEntry

Owner: CN=Amazon RDS Root 2019 CA, OU=Amazon RDS, O="Amazon Web Services, Inc.", ST=Washington, L=Seattle, C=US
Issuer: CN=Amazon RDS Root 2019 CA, OU=Amazon RDS, O="Amazon Web Services, Inc.", ST=Washington, L=Seattle, C=US
…
876x4xx3x12x:ssl_wallet bxxudxvx$ ls -lrth
total 32
-rw-r--r--@ 1 bxxudxvx  staff   1.4K May  9 19:29 rds-ca-2019-root.pem
-rw-r--r--  1 bxxudxvx  staff   2.5K May 10 07:49 clientkeystore
-rw-r--r--  1 bxxudxvx  staff   1.0K May 10 07:53 rds-ca-2019-root.der
-rw-r--r--  1 bxxudxvx  staff   1.4K May 10 07:54 clientkeystore.jks

For step-by-step instructions, refer to Setting up an SSL connection over JDBC and test using the code example.

Download, install, and launch the latest version of AWS SCT

You must download and install AWS SCT either on your local computer, a server in your data center, or an Amazon Elastic Compute Cloud (Amazon EC2) instance. Make sure your AWS SCT version is the latest one or with build #1.0.660 or higher. For step-by-step instructions, refer to Installing, verifying, and updating AWS SCT.

In our use case, we installed AWS SCT on a Microsoft Windows instance on Amazon Workspaces.

Store database credentials in Secrets Manager

AWS SCT can use database credentials that you store in Secrets Manager. You can fill in all the values in the database connection dialog box from Secrets Manager. To use Secrets Manager, make sure that you store AWS profiles in the AWS SCT. For more information about using Secrets Manager, refer to the AWS Secrets Manager User Guide.

On the Secrets Manager console, create a new secret to store the database credentials. AWS SCT supports secrets that have the following structure.

In this structure, the username and password values are required, and all other values are optional. Make sure that the values that you store in Secrets Manager include all database credentials.

For more information about storing AWS profiles, see Storing AWS service profiles in the AWS SCT.

You can also set the default profile for an AWS SCT project. Doing this associates the AWS credentials stored in the profile with the project.

Configure JDBC drivers in AWS SCT global settings

AWS SCT requires JDBC drivers to connect to your source and target databases. To configure the driver path in AWS SCT, complete the following steps:

  1. Download and install JDBC drivers.
  2. Navigate to the global settings on the Settings page.
  3. Choose Drivers in the navigation pane, and enter the file path to the JDBC driver for your source and target database engines.

For this post, I added an Oracle driver.

Make sure you “Test Connection” to validate the credentials stored in Secrets Manager to verify that AWS SCT can connect to your database. For detailed steps, refer to Using AWS Secrets Manager.

Set up AWS SCT specifications to update global settings with an SSL trust store

AWS SCT requires SSL settings to connect to your source database. To configure a trust store in AWS SCT, complete the following steps:

  1. Navigate to the global settings on the Settings page.
  2. Choose Security in the navigation pane, and add the trust stores by choosing Select existing trust store.
  3. Provide values for Trust store name, File path, and Trust store password.

For this post, I added the trust store that was created in the previous steps.

Create a multiserver assessment report for database migration using the AWS SCT GUI

To determine the best target direction for your overall environment, create a multiserver assessment report. You can use AWS SCT to create a multiserver assessment report for your source Oracle database and target Aurora PostgreSQL database. This solution also supports Amazon Aurora MySQL-Compatible Edition.

You don’t need to create a new project in AWS SCT to perform a multiserver assessment. Before you get started, make sure that you have prepared a CSV file with database connection parameters as detailed below. Also, make sure that you have installed all required database drivers and set the location of the drivers in the AWS SCT settings. For more information, see Installing the required database drivers.

  1. In AWS SCT, choose File, New multiserver assessment.
    The New multiserver assessment dialog box opens.
  2. Choose Download a connections file example to download an empty template of a CSV file with database connection parameters.

To provide connection parameters as input for multiserver assessment report, use a CSV file as shown in the following example.

You can create a new CSV file or download a template for a CSV file from AWS SCT and fill in the required information. Make sure that the first row of your CSV file reflects the header with column names.

The following are header columns related to SSL and Secrets Manager:

  • Use SSL – If you use SSL to connect to your source database, enter true.
  • Trust store – The trust store to use for your SSL connection.
  • Key store – The keystore to use for your SSL connection.
  • SSL authentication – If you use SSL authentication by certificate, enter true.
  • Secret Manager Key – The name of the secret that stores your database credentials in Secrets Manager. To use Secrets Manager, make sure that you store AWS profiles in the AWS SCT. For more information, see Using AWS Secrets Manager.

For a detailed description for each of the header columns, refer to preparing an input CSV file.

Name,Description,Server IP,Port,Service Name,SID,Source Engine,Schema Names,Login,Password,Target Engines,Secret Manager Key,Use Windows Authentication,Use SSL,Trust store,Key store,SSL authentication
Oracle_SSL_2484,Test RDS Instance,dmaf-test-instance.xxxxxrvgexxx.us-east-1.rds.amazonaws.com,2484,DMAFDB,,ORACLE,DMS_SAMPLE,dms_sample,dms_sample,AURORA_POSTGRESQL;AURORA_MYSQL,,,true,client-trust-store-key,,false
Oracle_TCP_1521,Demo RDS Instance,dmaf-test-instance.xxxxxrvgexxx.us-east-1.rds.amazonaws.com,1521,DMAFDB,,ORACLE,DMS_SAMPLE,dms_sample,dms_sample,AURORA_POSTGRESQL;AURORA_MYSQL,,,,,,
Oracle_SSL_SM_2484,Sample RDS Instance,,,DMAFDB,,ORACLE,DMS_SAMPLE,,,AURORA_POSTGRESQL;AURORA_MYSQL,ALL.SOURCE.ORACLE_19,,true,client-trust-store-key,,false

The preceding example uses a semicolon to separate the two target database migration platforms. Also, three use cases are set up in the preceding example with Name:

  • Oracle_SSL_2484 – Uses SSL trust store client-trust-store-key to open encrypted connections to the source database on port 2484
  • Oracle_TCP_1521 – Opens clear text connections to source database on port 1521
  • Oracle_SSL_SM_2484 – Uses SSL trust store client-trust-store-key to open encrypted connections to source database and retrieve database credentials, server, and port details from the Secrets Manager secret ALL.SOURCE.ORACLE_19
  1. Enter values for Project name, Location (to store reports), and Connections file (a CSV file).
  2. Choose Run.
  3. When the multiserver assessment report generation is complete, choose Open Report to view the aggregated summary assessment report.

The multiserver assessment generates two types of reports:

  • An aggregated report of all source databases
  • A detailed assessment report of target databases for each schema name in a source database

Reports are stored in the directory that you chose for Location in the New multiserver assessment dialog box.

To access the detailed reports, you can navigate the subdirectories, which are organized by source database, schema name, and target database engine, as shown in the following example from the CMD prompt:

D:\Users\bxaxu\Downloads\sct-projects\Oracle_SSL_SM_Demo>tree

├───Oracle_SSL_2484
│   └───DMS_SAMPLE
│       ├───MYSQL
│       └───POSTGRESQL
├───Oracle_SSL_SM_2484
│   └───DMS_SAMPLE
│       ├───MYSQL
│       └───POSTGRESQL
└───Oracle_TCP_1521
    └───DMS_SAMPLE
        ├───MYSQL
        └───POSTGRESQL

Create a multiserver assessment report for database migration using the AWS SCT CLI

To use the AWS SCT CLI to create a multiserver assessment report, you must create an AWS SCT CLI input file with an .scts extension. This file contains the AWS SCT CLI commands and required configuration:

  • SetGlobalSettings gives the settings for Oracle driver file path
  • CreateAggregatedReport gives the settings of the project directory path, project name, and connections file path

The following snippet shows the implementation:

SetGlobalSettings
   -settings: '[{
				   "name":"oracle_driver_file",
				   "value":"D:\Users\bxaxu\Downloads\instantclient-basic-windows.x64-19.12.0.0.0dbru\instantclient_19_12\ojdbc8.jar"
			   }]'
/
CreateAggregatedReport
   -directory: 'D:\Users\bxaxu\Downloads\sct-projects'
   -projectName: 'Oracle_SSL_SM_CLI_Demo'
   -connectionsFile: 'D:\Users\bxaxu\Downloads\sct-projects\input.csv'
/

The .scts input file includes all the mentioned functions, which can be called with a single command:

java -Xmx4G -Xms2G -jar "C:\Program Files\AWS Schema Conversion Tool\app\AWSSchemaConversionToolBatch.jar" -type scts -script D:\Users\bxaxu\Downloads\sct-projects\aggregated.scts

You can configure AWS SCT with different memory performance settings. Increasing memory speeds up the performance of your conversion. For more information, refer to Configuring additional memory.

You get the following output:

D:\Users\bxaxu>java -Xmx4G -Xms2G -jar "C:\Program Files\AWS Schema Conversion Tool\app\AWSSchemaConversionToolBatch.jar" -type scts -script D:\Users\bxaxu\Downloads\sct-projects\aggregated.scts
2022-05-10 11:19:08.168 [   1]     GENERAL INFO    Attached GC listener to G1 Young Generation
2022-05-10 11:19:08.168 [   1]     GENERAL INFO    Attached GC listener to G1 Old Generation
2022-05-10 11:19:11.072 [   1]     GENERAL INFO    user.timezone=Asia/Calcutta timezone.offset=+05:30
2022-05-10 11:19:11.080 [   1]     GENERAL INFO    JVM Arguments:
        -Xmx4G
        -Xms2G
2022-05-10 11:19:15.634 [   1]     GENERAL INFO    AWS Schema Converison Tool version 1.0 build 660
2022-05-10 11:19:15.636 [   1]     GENERAL INFO    global_settings:
    DEFAULT_PROFILE_GENERAL=proserve-account-dmaf-tester

…

    s3-timeout=1000
    security.certificate={"keys":[],"trusts":[{"name":"client-trust-store-key","store":{"path":"D:\\Users\\bxaxu\\Downloads\\sct-projects\\rds-root\\clientkeystore.jks"},"password":"xxXmyXlX/OkT5sRHWaKxxxxxUl01WJxxxTrSnC3TjeOCqw\u003d\u003d","info":[{"alias":"rds-root","date":"Aug 22, 2024 10:38:50 PM","valid":true}]}]}
    show-all-files=false
…

2022-05-10 11:30:36.238 [   1]     GENERAL INFO    Connecting to Oracle database finished in 0:00:00.103 sec with memory consumption of 842.48 MB (842.48 MB .. 842.48 MB).
2022-05-10 11:30:36.239 [   1]     GENERAL INFO    Connecting to Oracle database statistics:
    GENERAL: 0:00:00.104 sec
2022-05-10 11:30:36.344 [   1]     GENERAL INFO    [CreateAggregatedReport]:  Status succeeds: 3
2022-05-10 11:30:36.345 [   1]     GENERAL INFO    [CreateAggregatedReport]:  Status errors: 0
2022-05-10 11:30:36.345 [   1]     GENERAL INFO    [CreateAggregatedReport]:  Status total: 3
2022-05-10 11:30:36.346 [   1]     GENERAL INFO    [CreateAggregatedReport]:  finished
2022-05-10 11:30:36.346 [   1]     GENERAL INFO    CreateAggregatedReport finished in 0:11:20.026 sec with memory consumption of 842.48 MB (275 MB .. 1.96 GB).
2022-05-10 11:30:36.346 [   1]     GENERAL INFO    CreateAggregatedReport statistics:
    LOADER: 0:00:02.471 sec
    GENERAL: 0:11:17.554 sec
2022-05-10 11:30:36.346 [   1]     GENERAL MANDATORY Log session finished.

Clean up

To avoid incurring future charges, clean up the manually created resources you made as part of this post.

Delete the secrets in Secrets Manager that are used to store database connection details. For instructions, refer to Delete a secret.

Additional references

The post Generate an assessment report for a fleet of database servers using the AWS SCT multiserver assessor demonstrates on how to configure the AWS SCT multiserver assessor to generate an aggregated report.

The post Convert database schemas and application SQL using the AWS Schema Conversion Tool CLI demonstrates on how to use the AWS SCT CLI to convert database schema object code, application SQL, and PL/SQL code into PSQL in application files, as part of the migration process from an Oracle database hosted on Amazon EC2 to Aurora PostgreSQL.

Conclusion

In this post, we demonstrated how to create a multiserver assessment report using an Oracle Database as a source for AWS SCT and integrate Secrets Manager. We enabled SSL encryption for an RDS for Oracle DB instance by adding the Oracle SSL option to the RDS option group, then we downloaded and installed AWS SCT, stored and retrieved the database credentials from Secrets Manager, and configured AWS SCT to update global settings. Finally, we generated a multiserver assessment report for database migration using the AWS SCT GUI and AWS SCT CLI options.

Leave your thoughts or questions in the comments section.

About the authors

Bhanu Ganesh Gudivada is a Database Consultant with the Professional Services team at AWS based out of Hyderabad, India and specializes in database migrations. He is helping and enabling customers to build high-available, cost-effective database solutions and migrate their commercial engines to AWS cloud. Curious to learn and implement new technologies around the databases and orchestrate migrations through automation.

Jeevan Shetty is a Consultant with the AWS Professional Services Team. He has been supporting and enabling customers to migrate their database from OnPrem data center to AWS cloud and also in migration from commercial database engines to open source database in Amazon.

Santhosh Kumar Adapa is a Database Consultant with the Professional Services team at AWS. He works as a database migration specialist to help Amazon customers in design and implementing scalable, secure, performant, and robust database solutions in the cloud.

HariKrishna Boorgadda is a Senior Consultant with the Professional Services team at Amazon Web Services. He focuses on database migrations to AWS and works with customers to design and implement Amazon RDS and Aurora architectures.