Migrate an on-premises Oracle database to Amazon RDS for Oracle

Created by Baji Shaik (AWS) and Pavan Pusuluri (AWS)

Environment: PoC or pilot	Source: Databases: Relational	Target: Amazon RDS for Oracle
R Type: Replatform	Workload: Oracle	Technologies: Migration; Databases
AWS services: Amazon RDS; AWS DMS

Summary

This pattern describes the steps for migrating on-premises Oracle databases to Amazon Relational Database Service (Amazon RDS) for Oracle. As part of the migration process, you create a migration plan and consider important factors about your target database infrastructure based on your source database. You can choose one of two migration options based on your business requirements and use case:

1. AWS Database Migration Service (AWS DMS) – You can use AWS DMS to migrate databases to the AWS Cloud quickly and securely. Your source database remains fully operational during the migration, which minimizes downtime to applications that rely on the database. You can reduce migration time by using AWS DMS to create a task that captures ongoing changes after you complete an initial full-load migration through a process called change data capture (CDC). For more information, see Migrate from Oracle to Amazon RDS with AWS DMS in the AWS documentation.

2. Native Oracle tools – You can migrate databases by using native Oracle tools, such as Oracle and Data Pump Export and Data Pump Import with Oracle GoldenGate for CDC. You can also use native Oracle tools such as the original Export utility and original Import utility to reduce the full-load time.

Prerequisites and limitations

Prerequisites

• An active AWS account

• An on-premises Oracle database

• An Amazon RDS Oracle database (DB) instance

Limitations

• Database size limit: 64 TB

Product versions

• Oracle versions 11g (versions 11.2.0.3.v1 and later) and up to 12.2 and 18c. For the latest list of supported versions and editions, see Amazon RDS for Oracle in the AWS documentation. For Oracle versions supported by AWS DMS, see Using an Oracle database as a source for AWS DMS in the AWS DMS documentation.

Architecture

Source technology stack

• On-premises Oracle databases

Target technology stack

• Amazon RDS for Oracle

Source and target architecture

The following diagram shows how to migrate an on-premises Oracle database to Amazon RDS for Oracle by using AWS DMS.

The diagram shows the following workflow:

1. Create or use an existing database user, grant the required AWS DMS permissions to that user, turn on ARCHIVELOG mode, and then set up supplemental logging.

2. Configure the internet gateway between the on-premises and AWS network.

3. Configure source and target endpoints for AWS DMS.

4. Configure AWS DMS replication tasks to migrate the data from the source database to the target database.

5. Complete the post-migration activities on the target database.

The following diagram shows how to migrate an on-premises Oracle database to Amazon RDS for Oracle by using native Oracle tools.

The diagram shows the following workflow:

1. Create or use an existing database user and grant the required permissions to back up the Oracle database by using Oracle Export (exp) and Import (imp) utilities.

2. Configure the internet gateway between the on-premises and AWS network.

3. Configure the Oracle client on the Bastion host to take the backup database.

4. Upload the backup database to an Amazon Simple Storage Service (Amazon S3) bucket.

5. Restore the database backup from Amazon S3 to an Amazon RDS for Oracle database.

6. Configure Oracle GoldenGate for CDC.

7. Complete the post-migration activities on the target database.

Tools

• AWS Database Migration Service (AWS DMS) helps you migrate data stores into the AWS Cloud or between combinations of cloud and on-premises setups.

• Native Oracle tools help you perform a homogeneous migration. You can use Oracle Data Pump to migrate data between your source and target databases. This pattern uses Oracle Data Pump to perform the full load from the source database to the target database.

• Oracle GoldenGate helps you perform logical replication between two or more databases. This pattern uses GoldenGate to replicate the delta changes after the initial load by using Oracle Data Pump.

Epics

Plan the migration

Task	Description	Skills required
Create project documents and record database details.	Document your migration goals, migration requirements, key project stakeholders, project milestones, project deadlines, key metrics, migration risks, and risk mitigation plans. Document critical information about your source database, including RAM, IOPS, and CPUs. You will later use this information to determine the appropriate target DB instance. Validate the versions of your source and target databases.	DBA
Identify storage requirements.	Identify and document your storage requirements, including the following: Calculate the storage allocated for the source DB instance. Gather the historical growth metrics from the source DB instance. Forecast future growth for the target DB instance. Note: For General Purpose (gp2) SSD volumes, you get three IOPS per 1 GB of storage. Allocate storage by calculating the total number of read and write IOPS on the source database.	DBA, SysAdmin
Choose the proper instance type based on compute requirements.	Determine the compute requirements of the target DB instance. Identify performance issues. Consider the factors for determining the appropriate instance type: CPU utilization of the source DB instance IOPS (read and write) for the source DB instance Memory footprint on the source DB instance	SysAdmin
Identify network access security requirements.	Identify and document the network access security requirements for your source and target databases. Configure the appropriate security groups for enabling the application to communicate with the database.	DBA, SysAdmin
Identify the application migration strategy.	Determine and document the migration cutover strategy. Determine and document your application’s recovery time objective (RTO) and recovery point objective (RPO), and then plan for the cutover accordingly.	DBA, SysAdmin, App owner
Identify migration risks.	Assess the database and document migration specific risks and mitigations. For example: Identify no-logging tables and highlight the risk of data loss in the event of recovery. Extract the source database users and privileges, and highlight the conflicts with Amazon RDS privileges. Review the alert log for any Oracle-specific errors and warnings. Identify the supported and unsupported features of the target DB instance. Review the deprecated features of the target DB version engine.	DBA

Configure the infrastructure

Task	Description	Skills required
Create a VPC.	Create a new Amazon Virtual Private Cloud (Amazon VPC) for the target DB instance.	SysAdmin
Create security groups.	Create a security group in your new VPC to allow inbound connections to the DB instance.	SysAdmin
Create an Amazon RDS for Oracle DB instance.	Create the target DB instance with the new VPC and security group, and then start the instance.	SysAdmin

(Option 1) Use native Oracle or third-party tools to migrate data

Task	Description	Skills required
Prepare the source database.	Create a Data Pump directory or use an existing one. Create a migration user and grant permissions to perform the Data Pump extract. Extract roles, users, and tablespaces from the source database as a SQL script. Transfer the extracted Data Pump dump to the target DB instance data pump directory.	DBA, SysAdmin
Prepare the target database.	Confirm that all the database options (for example, text and Java) are installed or enabled on the target Amazon RDS for Oracle DB instance. Create a Data Pump directory or use an existing one. Create a migration user and grant permissions to perform the Data Pump import. Create the required tablespaces, users, and roles on the target DB instance. Import the transferred Data Pump export dump to the target database. Create any indexes excluded during import or object creation. Create any constraints excluded during import. Validate or recompile invalid objects. Rebuild the invalid indexes. Validate the database object counts between the source and the target databases. Resolve any discrepancies found between object counts.	DBA, SysAdmin

(Option 2) Use AWS DMS to migrate data

Task	Description	Skills required
Prepare the data.	Clean the data in the source database. Create a replication instance. Create a source endpoint and target endpoint. Identify the number of tables and objects to be migrated.	DBA
Migrate the data.	Drop foreign key constraints and triggers on the target database. Drop secondary indexes on the target database. Configure AWS DMS full-load task settings from the source database to the target database. Enable foreign keys. Enable AWS DMS CDC to replicate ongoing changes. Enable triggers. Update the sequences. Validate the source and target data.	DBA

Cut over to the target database

Task	Description	Skills required
Switch the application clients to the new infrastructure.	Stop all application services and client connections pointing to Oracle. Run the AWS DMS tasks. Set up a rollback task (for example, reverse CDC from the Amazon RDS database to the on-premises Oracle database). Validate the data. Start the application services on the new target database by configuring Amazon Route 53 to the new Amazon RDS for Oracle DB instance. Add Amazon CloudWatch monitoring to your new Amazon RDS for Oracle DB instance.	DBA, SysAdmin, App owner
Implement your rollback plan.	Stop all application services pointing to the Amazon RDS for Oracle DB instance. Roll back the changes to the source on-premises Oracle database by using an AWS DMS task. Stop the AWS DMS tasks running from the on-premises Oracle database to the Amazon RDS for Oracle database. Configure the applications back on the source Oracle database. Confirm the rollback deployment is complete.	DBA, App owner

Close out the migration project

Task	Description	Skills required
Clean up resources.	Shut down or remove the temporary AWS resources, such as the AWS DMS replication instance and S3 bucket.	DBA, SysAdmin
Review project documents.	Review your migration planning documents and goals, and then confirm that you completed all required migration steps.	DBA, SysAdmin, App owner
Gather metrics.	Record key migration metrics, including how long it took to complete the migration, the percentage of manual vs. tool-based tasks, cost savings, and other relevant metrics.	DBA, SysAdmin, App owner
Close out the project.	Close out the migration project and capture feedback about the effort.	DBA, SysAdmin, App owner

Related resources

References

• Strategies for Migrating Oracle Databases to AWS (AWS whitepaper)

• AWS Database Migration Service (AWS DMS documentation)

• Amazon RDS Pricing (Amazon RDS documentation)

Tutorials and videos

• Getting Started with AWS Database Migration Service (AWS DMS documentation)

• Amazon RDS resources (Amazon RDS documentation)

• AWS Database Migration Service (DMS) (YouTube)