Migrate Amazon RDS for Oracle to Amazon RDS for PostgreSQL with AWS SCT and AWS DMS using AWS CLI and AWS CloudFormation

Configure AWS SCT to run from the AWS CLI.

1. Configure the source and target environment configuration details in the database_migration.txt file by using the following format:

#source_vendor,source_hostname,source_dbname,source_user,source_pwd,source_schema,source_port,source_sid,target_vendor,target_hostname,target_user,target_pwd,target_dbname,target_port
ORACLE,myoracledb.cokmvis0v46q.us-east-1.rds.amazonaws.com,ORCL,orcl,orcl1234,orcl,1521,ORCL,POSTGRESQL,mypgdbinstance.cokmvis0v46q.us-east-1.rds.amazonaws.com,pguser,pgpassword,pgdb,5432

2. Modify the AWS SCT configuration parameters according to your requirements in the following files: project_settings.xml, Oracle_PG_Test_Batch.xml, and ORACLE-orcl-to-POSTGRESQL.xml.

Run the run_aws_sct.py Python script.

Run the run_aws_sct.py Python script by using the following command:

$ python run_aws_sct.py database_migration.txt

The Python script converts the database objects from Oracle to PostgreSQL and creates SQL files in PostgreSQL format. The script also creates the Database migration assessment report .pdf file that provides you with detailed recommendations and conversion statistics for database objects.

Create objects in Amazon RDS for PostgreSQL.

Create an AWS DMS replication instance.

Sign in to the AWS Management Console, open the AWS DMS console, and create a replication instance that is configured according to your requirements.

For more information, see Creating a replication instance in the AWS DMS documentation and How do I create an AWS DMS replication instance in the AWS Support documentation.

Create the source endpoint.

On the AWS DMS console, choose Endpoints and then create a source endpoint for the Oracle database according to your requirements. 

Note: The extra connection attribute must be numberDataTypeScale with a -2 value.

For more information, see Creating source and target endpoints in the AWS DMS documentation.

Create the target endpoint.

On the AWS DMS console, choose Endpoints and then create a target endpoint for the PostgreSQL database according to your requirements.  

For more information, see Creating source and target endpoints in the AWS DMS documentation.

Configure the AWS DMS replication details to run from the AWS CLI.

Configure the AWS DMS source and target endpoints and replication details in the dms-arn-list.txt file with the source endpoint ARN, target endpoint ARN, and the replication instance ARN by using following format:

#sourceARN,targetARN,repARN
arn:aws:dms:us-east-1:123456789012:endpoint:EH7AINRUDZ5GOYIY6HVMXECMCQ
arn:aws:dms:us-east-1:123456789012:endpoint:HHJVUV57N7O3CQF4PJZKGIOYY5
arn:aws:dms:us-east-1:123456789012:rep:LL57N77AQQAHHJF4PJFHNEDZ5G

Run the dms-create-task.py Python script to create the AWS DMS tasks.

1. Run the dms-create-task.py Python script by using the following command:

$ python dms-create-task.py database_migration.txt dms-arn-list.txt <cft-stack-name> <migration-type>

2. Depending on your migration type, you can use the following commands to create three types of AWS DMS tasks:

3. The AWS CloudFormation stack and AWS DMS tasks are created 

Check that AWS DMS tasks are ready.

In the AWS console, check that your AWS DMS tasks are in Ready status in the Status section.

Start the AWS DMS tasks.

Run the dms-start-task.py Python script by using the following command:

$ python dms-start-task.py start ‘<cdc-start-datetime>’

Note: The start date and time must be in the 'DD-MON-YYYY' or 'YYYY-MM-DDTHH:MI:SS' timestamp data type formats (for example, ‘01-Dec-2019’ or ‘2018-03-08T12:12:12’)

You can review the AWS DMS task status in the Table statistics tab of your migration tasks on the Tasks page of the AWS DMS console.

Validate the data.

For more information, see AWS DMS data validation in the AWS DMS documentation.

Stop the AWS DMS tasks.

Run the Python script by using the following command:

$ python dms-start-task.py stop

Note: AWS DMS tasks might stop with a failedstatus, depending on the validation status. For more information, see the troubleshooting table in the Additional information section.