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.