How to Migrate Oracle DB to AWS RDS Using Oracle dump and DMS

Scenario

This guide assumes that an EC2 instance in an IDC VPC is the on-premise server with the Oracle DB engine installed. The source DB is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production on the on-premise server with the SID TEST and schema TESTUSR (around 1GB). The user is scott and the password is tiger.

Full Load

1. Access the Oracle DB on the source server with the sqlplus client tool.
2. Use the following queries to identify the database name and schema owner:
   • SELECT NAME FROM v$database;
   • SELECT DISTINCT(OWNER) FROM ALL_ALL_TBLES;
3. Create a dump file for the TESTUSR schema in the TEST DB with the following command, and transfer it to RDS:
   • SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'DUMP';
   • cd /
   • pwd
   • sudo mkdir oracle
   • sudo mkdir oracle/dumpfile
4. Make the ec2-user the owner of the oracle directory and dump file directory with the following command:
   • sudo chown -R ec2-user:oracle oracle/
5. Create a dump file for the source DB with the following command:
   • expdp scott/tiger dumpfile=cdc.dmp directory=dump schemas=TESTUSR job_name=cdc logfile=cdc.log FLASHBACK_SCN=${SCN 번호}
6. Check the dump file directory for the file.
7. Create a VPC endpoint for S3 to communicate with the instance located in the private subnet.
8. Upload the dump file to S3 with the following command from the on-premise server:
   • aws s3 cp /oracle/dumpfile/cdc.dmp s3://[BUCKET NAME]/cdc.dmp
9. Check that the dump file was copied to the S3 bucket.
10. Create a role in the IAM console.
11. In the RDS console, select the RDS and go to the Option groups tab to select the option group applied to the target RDS.

Summary

This guide covers the process of migrating an Oracle DB to AWS RDS using an Oracle dump and DMS. The first step is to perform a full load of the source DB and transfer it to RDS. This is done by creating a dump file for the TESTUSR schema in the TEST DB, transferring it to S3, and then creating a role in the IAM console. Finally, the option group is selected in the RDS console to apply to the target RDS.