Oracle DB migration from On premises to AWS RDS Oracle using DMS and export-import- Steps/Runbook

 Export-Import steps/runbook

1 Request for migration user creation in source DB.

2 Request application team to provide the application schema which are in scope of migration. 

3 Analyze the Source DB's schema, objects, dependencies and others.

4 Extract Tablespace DDL, SYS Function's PVF DDL, user profiles DDL.

5 Extract User's DDL and their respective non oracle maintained Roles DDL.

6 Create VPC security group, subnet and other networking components

7 Create an RDS DB in target. 

8 Request to create S3 Bucket/EFS and create IAM role/policy to enable S3_INTEGRATION /EFS_INTEGRATION between S3 to AWS RDS

9 Create option group for S3_ingegration or EFS_INTEGRATION and attach to AWS RDS

10 Request for Firewall port opening from source to target (AWS RDS/S3/EFS filesystem)

11 Test the transfer of files from S3 to RDS data pump directory.

12 Request source DBA to take export of source DB schema and place the backup dumps into shared path (if DataSync is there)

13 Transfer the dumps to S3 if EFS is not enabled.

14 Download the dumps from S3 to RDS datapump directory( if EFS is not enabled)

15 Execute the extracted Tablespace DDL, SYS Function's PVF DDL (update the master username), user profiles DDL in AWS RDS mentioned in Step 5 and passthrough using rdsadmin.util. 

16 Execute extracted user's DDL and their respective non-oracle maintained Roles in AWS RDS mentioned in Step no -6

17 Import the dump into RDS

18 Review the import log for any issue, if any objects are missing, re-import them from backup.

19 Reset the sequence value based on the source DB.

20 Compare the rows, no of objects/constraints from source to AWS RDS. 

21 Hand over the DB to application team for sanity check. 

AWS DMS migration steps/runbook-

Sr. No Steps

1 Request for migration user creation in source DB.

2 Request application team to provide the application schema which is in scope of migration. 

3 Analyze the Source DB's schema, objects, dependencies and others.

4 Execute the support collector scripts to analyze DB specifically for AWS DMS.

5 Extract Tablespace DDL, SYS Function's PVF DDL, user profiles DDL.

6 Extract User's DDL and their respective non oracle maintained Roles DDL.

7 Create VPC security group, subnet and other networking components

8 Create an RDS DB in target. 

9 Create AWS Replication instance

10 Request for Firewall port opening from source to target (AWS RDS/DMS)

11 Create Migration task based on the migration type and LOBS/partitions available in source DB.

12 Create Source and target Endpoints in AWS

13 Request source DBA to provide CDC grants to migration user in source DB.

14 Create a scripts with available list of tables in source DB to enable supplemental log .

15 Execute the extracted Tablespace DDL, SYS Function's PVF DDL (update the master username), user profiles DDL in AWS RDS and  passthrough using rdsadmin.util..

16 Execute extracted user's DDL and their respective non-oracle maintained Roles in AWS RDS

17 Export only metadata for all objects from source DB and import into RDS using DB link or SQL developer.

18 Validate any Identity columns are there or not, if available, make them null 

19 Disable triggers and referential constraints in AWS RDS.

20 Run the migration task

21 Validate the CloudWatch log for DMS task.

22 Resolve any issue if you find in task CloudWatch log.

23 Enable settings in DMS to stop migration task after "StopTaskCachedChangesApplied"

24 Enable triggers and referential constraints in AWS RDS.

25 Reset the sequence value based on the source DB.

26 Validate the CloudWatch log for DMS task.

27 Compare the rows, no of objects/constraints from source to AWS RDS. 

28 Hand over the DB to application team for sanity check.