Migrating Oracle On-Premises to Amazon RDS for Oracle

May 7, 2025

By Ranjit Nagi

Migrating your Oracle database to AWS Relational Database Service (RDS) can be daunting, but with the right planning and execution, it can significantly improve performance, scalability, and cost-efficiency.

This comprehensive guide outlines a step-by-step approach to ensure a smooth and successful migration process. From evaluating your current environment to handling post-migration validation, we've got you covered.

Whether you're a seasoned database administrator or a project manager overseeing the transition, this guide will provide valuable insights and practical tips to make your Oracle migration journey seamless.

Pre-Migration Planning

Evaluate Current Environment

Assess the existing on-premises Oracle database by reviewing schema sizes, dependencies, invalid objects, performance metrics, and database configurations to identify potential challenges before migration to RDS.

Assess the existing on-premises Oracle version, storage, and performance needs.
Review top queries, alert.log, and the AWR/sp report.
Identify key dependencies such as custom applications or integrations.
Ensure Oracle license compatibility with AWS RDS.
Choose the appropriate RDS instance type (e.g., db.t3.xlarge).

Preparing for Migration

Backup Strategy

Take full backups of your Oracle database before migration.
Define the backup strategy (full, incremental, etc).

Schema Review

Analyze database schemas to identify object dependencies, invalid objects, missing privileges, and storage considerations, ensuring compatibility and a smooth migration to Oracle RDS.

Analyze existing schemas and database configurations.
Identify unsupported Oracle features in RDS.

Choosing the Oracle Cloud Migration Strategy

When choosing a migration strategy, consider data volume, schema complexity, downtime tolerance, and available resources. Options include full migration for simplicity, or phased migration (schema-by-schema) for better control, flexibility, and troubleshooting during the move to Oracle RDS.

Options for migration:

AWS Database Migration Service (DMS) for continuous replication.
Oracle Data Pump (expdp/impdp) for export/import.
Backup/Restore via Amazon S3 or RDS snapshots.

Chosen Strategy: expdp/impdp

Migration Process

Below are the high-level steps involved in the migration process.

Set up the Amazon RDS for Oracle instance.
Create the necessary tablespace, role, and profile matching source database.
Export the on-premises database, we took expdp full=y.
Create an s3 bucket in AWS and provide the necessary IAM privileges.
Set up AWScli on the on-premises server.
Copy the dumpfile to s3.
Import all the required schema in RDS.

Choosing the RDS Instance Type

Select the appropriate RDS instance type based on performance requirements, such as CPU, memory, and storage, while considering workload characteristics (OLTP vs. OLAP) and scalability needs to ensure optimal performance and cost-efficiency for the migrated Oracle database.

Source DB: 16GB memory, 2 CPUs
Chosen RDS instance: db.t3.xlarge 4 vCPUs, 16 GB of memory

License Configuration

The customer has opted for the License Included model, which means the Oracle database license is bundled with the RDS instance, eliminating the need for separate license management. This option simplifies licensing and is ideal for users who do not have existing Oracle licenses.

Option: Bring Your Own License (BYOL) or License Included
Customer opted for License Included

Handling RDS Bugs (Database Name Issue)

Oracle RDS is automatically created with the default database name "orcl” and changing DB_NAME is not supported. Workarounds include creating a snapshot of the ORCL database and restoring the snapshot with the desired database name.

Solution: Restore an RDS snapshot to a new RDS instance.

Steps:

Take a Snapshot:

Go to RDS → Databases → Select DB → Actions → Take Snapshot.
Enter snapshot name → Take Snapshot.

Restore the Snapshot:

Navigate to RDS → Snapshots → Search for snapshot.
Select snapshot → Actions → Restore Snapshot.
In "Additional Configuration" → Set "Initial Database Name" → Restore.

AWS CLI Configuration

Configure AWS CLI on the primary server by installing the CLI package, running AWS configure to set up the AWS Access Key, Secret Key, Region, and Output Format, and verifying the setup using AWS s3 ls or other relevant commands.

Steps to Configure AWS CLI on the Source Database

[oracle@pridb-dev aws]$ ./aws configure

AWS Access Key ID [****************SBW5]: ####################

AWS Secret Access Key [****************Fr1w]: ####################

Default region name [US East]: us-east-1

Default output format [json]:

Export Dump File & Upload to S3

A full database export using Oracle Data Pump (expdp) includes all schema objects, data, and metadata, allowing for the selective import (impdp) of specific schemas, tables, or objects as needed in the RDS instance.

expdp directory=expdp dumpfile=export_test.dmp logfile=export_test.log full=y

export took around 2 hours to complete, size around 250g.

Copy the export dump to S3 bucket.

./aws s3 cp /US2/export/export_dev1.dmp s3://Bucketname/ --profile=profilename

After copying the dumpfile, download the dumpfile to data_pump_dir for import.

SQL> SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(

p_bucket_name => 'bucketeandev',

p_directory_name => 'DATA_PUMP_DIR')

AS TASK_ID FROM DUAL;

To check the progress of the download job:

TASK_ID

--------------------------------------------------------------------------------

1734723110394-1906

1735051030083-1282

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1735051030083-1282.log'));

Create Tablespace, Profile, and Role in Target Database

In the RDS instance, create all the tablespace, profiles and role as similar to on-premises database. The below script can be used to get the DDL.

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)

FROM dba_tablespaces

WHERE tablespace_name = UPPER('DATA');

Import Schemas into RDS

While FULL=Y can be used for a complete import, we opted for a schema-by-schema approach to allow better control, troubleshooting, and error handling during the migration to RDS.

Below syntax was used to import schemas:

DECLARE

v_hdnl NUMBER;

BEGIN

v_hdnl := DBMS_DATAPUMP.OPEN(

operation => 'IMPORT',

job_mode => 'SCHEMA',

job_name => null,

version=>'19.0.0');

DBMS_DATAPUMP.ADD_FILE(

handle => v_hdnl,

filename => 'export_dev.dmp',

directory => 'DATA_PUMP_DIR',

filetype => dbms_datapump.ku$_file_type_dump_file);

DBMS_DATAPUMP.ADD_FILE(

handle => v_hdnl,

filename => 'BO_EAN_PRI_imp.log',

directory => 'DATA_PUMP_DIR',

filetype => dbms_datapump.ku$_file_type_log_file);

DBMS_DATAPUMP.METADATA_FILTER(v_hdnl,'SCHEMA_EXPR','IN (''PRI_RESTORE'', ''BO_USER'', ''EAN_USER'')');

DBMS_DATAPUMP.START_JOB(v_hdnl);

END;

Compiling and Validating Objects

Compile Schema

Unlike on-premises databases where utlrp.sql is used, Oracle RDS requires compiling objects using DBMS_UTILITY.COMPILE_SCHEMA('SCHEMA_NAME') for each schema to ensure all invalid objects are recompiled.

BEGIN

DBMS_UTILITY.compile_schema(schema => 'RMS_USER2');

END;

Compare Objects

After import, compare invalid vs. valid objects.

SELECT status, owner, COUNT(*) FROM dba_objects WHERE username='RMS_USER2';

Migrating Oracle to RDS requires careful planning, from handling RDS-specific limitations to schema compilation and validation. By following a structured approach—configuring AWS CLI, exporting/importing data, compiling schemas, and verifying objects—we ensure a smooth transition. Continuous monitoring and troubleshooting help maintain database integrity and performance post-migration.

Get more data engineering resources at Datavail.com.