Transformation: on-premises Oracle to Amazon RDS for Oracle with Data Pump and DMS

#Fit check first

RDS for Oracle is managed: no OS access, no SYSDBA, and some features unavailable or different.

If something critical fails the check, look at RDS Custom for Oracle (OS access) or EC2.

#1. Create the target

• Engine version equal to or newer than source; same character set.
• Multi-AZ for production; parameter group copied from source settings that matter (processes, open_cursors, NLS).
• Option group with S3_INTEGRATION and an IAM role allowing access to a staging bucket.

#2. Initial load with Data Pump

On the source:

SELECT current_scn FROM v$database;      -- e.g. 48213377561

expdp system schemas=SALES directory=DP_DIR dumpfile=sales_%U.dmp parallel=4 \
  flashback_scn=48213377561 logfile=sales_exp.log

Record the flashback_scn. DMS CDC starts from it. Upload dump files to S3:

aws s3 cp /u01/dpdump/ s3://ora-migr-staging/sales/ --recursive --exclude "*" --include "sales_*.dmp"

On RDS, download into DATA_PUMP_DIR and import:

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
  p_bucket_name => 'ora-migr-staging', p_s3_prefix => 'sales/', p_directory_name => 'DATA_PUMP_DIR') AS task_id FROM dual;

DECLARE h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'IMP_SALES');
  DBMS_DATAPUMP.ADD_FILE(h, 'sales_%U.dmp', 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
  DBMS_DATAPUMP.ADD_FILE(h, 'sales_imp.log', 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER(h, 'SCHEMA_EXPR', 'IN (''SALES'')');
  DBMS_DATAPUMP.SET_PARALLEL(h, 4);
  DBMS_DATAPUMP.START_JOB(h);
END;
/

Read the log with rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','sales_imp.log').

#3. Catch up with DMS CDC

• Source endpoint: Oracle with supplemental logging (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; plus PK logging) and LogMiner or Binary Reader access.
• Target endpoint: the RDS instance.
• Task type: CDC only, starting at the flashback_scn from Step 2.
• Disable triggers and FK enforcement on the target during CDC where needed, so DMS can apply changes in its order.

#4. Validate

• DMS data validation on key tables; row counts on all.
• Invalid objects: SELECT owner, object_name, object_type FROM dba_objects WHERE status='INVALID'; and recompile with UTL_RECOMP via rdsadmin procedures or EXEC DBMS_UTILITY.compile_schema('SALES');.
• Application tests, especially jobs (DBMS_SCHEDULER), directories and DB links.

#5. Cut-over

1. Stop application writes on source; wait for DMS latency 0.
2. Stop the DMS task; re-enable triggers/constraints; reset sequences if DMS didn't move them (DMS doesn't replicate sequence values).
3. Point applications to the RDS endpoint (use a DNS CNAME you control).
4. Keep the source read-only for the rollback window.

Tip

Sequences are the classic cut-over bug: set each sequence on RDS above the source's current value before opening for writes.

#Checklist

• RDS limitations reviewed against the app
• Licence model decided
• Data Pump load rehearsed with timings
• DMS CDC from the export SCN, validated
• Sequences, jobs, DB links handled
• Rollback window and DNS switch planned