Migrating Oracle databases to the AWS Cloud

Two ways to choose for Oracle Migrations:

The blog presents two main migration options: using AWS Database Migration Service (AWS DMS) or using native Oracle tools. I’ll outline both approaches.

Option 1: Using AWS Database Migration Service (AWS DMS)

1. Plan the migration

1. 1. • Create project documents and record database details
      • Identify storage requirements
      • Choose the proper instance type based on compute requirements
      • Identify network access security requirements
      • Identify the application migration strategy
      • Identify migration risks

 

2. Configure the infrastructure

1. 1. • Create a VPC
      • Create security groups
      • Create an Amazon RDS for Oracle DB instance

 

3. Prepare the data

1. 1. • Clean the data in the source database
      • Create a replication instance
      • Create source and target endpoints
      • Identify the number of tables and objects to be migrated

 

4. Migrate the data

1. 1. • Drop foreign key constraints and triggers on the target database
      • Drop secondary indexes on the target database
      • Configure AWS DMS full-load task settings
      • Enable foreign keys
      • Enable AWS DMS CDC to replicate ongoing changes
      • Enable triggers
      • Update the sequences
      • Validate the source and target data

 

5. Cut over to the target database

1. 1. • Switch application clients to the new infrastructure
      • Stop all application services and client connections to Oracle
      • Run AWS DMS tasks
      • Set up a rollback task
      • Validate the data
      • Start application services on the new target database
      • Add Amazon CloudWatch monitoring

 

6. Close out the migration project
   • Clean up resources

• • • Review project documents
    • Gather metrics
    • Close out the project

 

Option 2: Using Native Oracle Tools

1. 1. Plan the migration (Same as in Option 1)
   2. Configure the infrastructure (Same as in Option 1)
   3. Prepare the source database
      • Create a Data Pump directory or use an existing one
      • Create a migration user and grant permissions
      • Extract roles, users, and tablespaces as a SQL script
      • Transfer the Data Pump dump to the target DB instance

 

4. Prepare the target database

1. 1. • Confirm database options are installed/enabled
      • Create a Data Pump directory
      • Create a migration user and grant permissions
      • Create required tablespaces, users, and roles
      • Import the Data Pump export dump
      • Create excluded indexes and constraints
      • Validate or recompile invalid objects
      • Rebuild invalid indexes
      • Validate database object counts
      • Resolve any discrepancies

 

 

5. Cut over to the target database (Similar to Option 1, but using Oracle GoldenGate for CDC instead of AWS DMS)

 

6. Close out the migration project (Same as in Option 1)

Both options include steps for implementing a rollback plan if necessary. The choice between AWS DMS and native Oracle tools depends on specific business requirements and use cases.

 

Technical Challenges to overcome using the migration options:

Both migration options – using AWS Database Migration Service (AWS DMS) and using native Oracle tools – come with their own set of technical challenges. Let’s explore these for each option:

Challenges for using Option 1: Using AWS Database Migration Service (AWS DMS)

 

1. 1. Schema conversion challenges:
      • AWS DMS may not fully support all Oracle-specific features or data types.
      • Complex stored procedures, functions, and packages may need manual conversion.

 

2. Performance issues:

1. 1. • Large datasets can lead to extended migration times.
      • Network latency between on-premises and AWS can impact transfer speeds.

 

3. Handling of LOB data:

1. 1. • Large Object (LOB) data types may require special handling and can slow down the migration process.

 

4. Maintaining data consistency:

1. 1. • Ensuring data integrity during ongoing replication (CDC) can be challenging, especially for high-transaction environments.

 

5. Limitations with certain Oracle features:

1. 1. • AWS DMS may have limitations with Oracle-specific features like materialized views, synonyms, or certain types of indexes.

 

6. Handling of sequences:

1. 1. • Oracle sequence values may not be maintained exactly, requiring post-migration adjustments.

 

7. Permissions and security:

• • • Ensuring the AWS DMS replication instance has appropriate permissions without compromising security can be tricky.

Challenges for using Option 2: Using Native Oracle Tools

1. Manual intervention:

1. 1. • This method often requires more manual steps and scripting, increasing the risk of human error.

 

2. Storage management:

1. 1. • Exporting and importing large databases can require significant temporary storage space.

 

3. Network bandwidth:

1. 1. • Transferring large Data Pump files between on-premises and AWS can be time-consuming and may require optimized network paths.

 

4. Version compatibility:

1. 1. • Ensuring compatibility between source and target Oracle versions, especially if upgrading simultaneously.

 

5. Handling of database links:

1. 1. • Database links may need to be recreated or adjusted in the new environment.

 

6. Time zone differences:

1. 1. • Handling of TIMESTAMP WITH TIME ZONE data types may require special attention.

 

7. Maintaining referential integrity:

1. 1. • Disabling and re-enabling constraints during the migration process can be complex.

 

8. Handling of long-running transactions:

1. 1. • Long-running transactions on the source database can complicate the CDC process when using Oracle GoldenGate.

 

Common Challenges for Both Options:

1. 1. Downtime management:
      • Minimizing application downtime during the cutover phase can be challenging.

 

2. Application changes:

1. 1. • Applications may require updates to connection strings or configurations.

 

3. Performance tuning:

1. 1. • The target RDS instance may require tuning to match or exceed the performance of the source system.
   2. Testing and validation:
      • Comprehensive testing of the migrated database and applications is crucial but can be time-consuming.

 

5. Rollback planning:

1. 1. • Designing and implementing an effective rollback strategy in case of migration failure.

 

6. Handling of users and roles:

1. 1. • Mapping on-premises database users and roles to RDS Oracle, considering RDS limitations on certain privileges.

 

7. Compliance and auditing:

• • • Ensuring the migration process meets any regulatory or compliance requirements.

These challenges underscore the importance of thorough planning, testing, and having experienced database administrators and cloud specialists involved in the migration process. The choice between AWS DMS and native Oracle tools should be made based on the specific requirements of the database, the organization’s expertise, and the acceptable downtime window

Automation: Identify the opportunity and automate repeated tasks:

There are several opportunities to automate various aspects of Oracle database migration to AWS RDS. Automation can significantly reduce manual effort, minimize human errors, and speed up the migration process. Here are some key areas where automation can be applied:

1. 1. Assessment and planning:
      • Automated discovery tools can scan the source database to identify schema objects, dependencies, and unsupported features.
      • Tools like AWS Schema Conversion Tool (SCT) can automate much of the assessment process.

 

2. Schema conversion:

1. 1. • AWS SCT can automate a significant portion of schema conversion, especially for simpler schemas.
      • Custom scripts can be developed to handle repetitive conversion tasks.

 

3. Data migration:

1. 1. • AWS DMS tasks can be automated using AWS CloudFormation templates or AWS CLI scripts.
      • For native Oracle tools, scripting languages like Python or Bash can be used to automate Data Pump export/import processes.

 

4. Continuous Data Replication:

1. 1. • AWS DMS supports automated continuous replication (CDC) once set up.
      • Oracle GoldenGate processes can be automated using scripts.

 

5. Testing and validation:

1. 1. • Automated comparison tools can be used to verify data integrity between source and target databases.
      • Custom scripts can be developed to automate functional and performance testing.

 

6. Cutover process:

1. 1. • Scripts can be created to automate the sequence of steps required during cutover, including stopping applications, final data sync, and switching connection strings.

 

7. Monitoring and alerting:

1. 1. • AWS CloudWatch can be configured to automatically monitor the migration process and alert on issues.

 

8. Post-migration tasks:

1. 1. • Scripts can be developed to automate post-migration tasks like rebuilding indexes, updating statistics, and validating objects.

 

9. Infrastructure provisioning:

1. 1. • AWS CloudFormation or Terraform can be used to automate the creation of VPCs, security groups, and RDS instances.

 

10. User and role management:

1. 1. • Scripts can be developed to automate the creation of users and roles in the target RDS instance.

 

11. Application updates:

1. 1. • If application changes are needed, CI/CD pipelines can be used to automate application updates and deployments.

 

12. Rollback procedures:

• • • Automated rollback scripts can be prepared in advance to quickly revert to the source database if needed.

 

Tools and approaches for automation:

1. 1. AWS-specific tools:
      • AWS Database Migration Service (DMS)
      • AWS Schema Conversion Tool (SCT)
      • AWS CloudFormation
      • AWS Command Line Interface (CLI)

 

2. Scripting languages:

1. 1. • Python
      • Bash
      • PowerShell

 

3. Infrastructure as Code tools:

1. 1. • Terraform
      • Ansible

 

4. Continuous Integration/Continuous Deployment (CI/CD) tools:

1. 1. • Jenkins
      • AWS CodePipeline

 

5. Oracle-specific tools:

 

• • • Oracle Data Pump with automated scripts
    • Oracle GoldenGate with automated setup and monitoring

 

While automation can greatly streamline the migration process, it’s important to note that complete end-to-end automation may not be feasible for complex databases or environments with specific requirements. In these cases, a hybrid approach combining automated processes with manual oversight and intervention is often the most effective strategy