Guidance and Best Practices for Oracle DB from EC2 to AWS RDS for Oracle

0

Hi, I am in process of migrating Oracle database from EC2 to AWS RDS for Oracle which contains large number of complex stored procedures. I am looking for guidance and best practices in this scenario. Your help would be highly appreciated. Thanks

2 Answers
0
Accepted Answer

Hi, Migrating Oracle from EC2 or On-prem to AWS RDS for Oracle involves several steps, specially when it involves large number of stored procedures.

It is crucial to check the Data Types and Compatibility : Ensure that your Oracle database's data types and constraints (e.g., triggers, sequences, stored procedures) are fully supported by the target RDS environment.

A predefined role in Amazon RDS, called DBA, normally allows all administrative privileges on an Oracle database engine. Certain privileges are not available for the DBA role on an Amazon RDS DB instance using the Oracle engine. Refactor any procedures that rely on SYSDBA access or system-level packages to use alternative RDS-supported methods. Also, review if any stored procedure depends on external libraries, file systems.

You may use SQL Developer or DBMS_METADATA to generate the DDL scripts for stored procedures and then to run the scripts directly at target RDS Oracle instance.

You can also use AWS Schema Conversion Tool (SCT) for migrating stored procedures using SCT assessment and Automating conversions of stored procedures.

Reference: https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.ToRDSOracle.html

Remember that while AWS SCT can automate much of the conversion process, complex stored procedures may still require manual intervention and careful testing. It's crucial to have a thorough understanding of your procedures and to test extensively throughout the migration process.

AWS
answered a month ago
profile picture
EXPERT
reviewed a month ago
0

Migrating an Oracle database from EC2 to AWS RDS for Oracle, especially one with a large number of complex stored procedures, requires careful planning and execution. Here are some key guidelines and best practices to consider:

  1. Assessment and Planning:
  • Review your current database architecture, version, and edition to ensure compatibility with RDS for Oracle.
  • Analyze your database size, IOPS requirements, and throughput to select the appropriate RDS instance type.
  • Evaluate your compliance and auditing needs to ensure they can be met in the RDS environment.
  1. Migration Strategy:
  • Consider a phased approach to reduce complexity. You might first focus on core database functionality, then optimize performance and integrate additional AWS services in subsequent phases.
  • Choose between online and offline migration methods based on your downtime tolerance, Recovery Time Objective (RTO), and Recovery Point Objective (RPO) requirements.
  1. Data Migration:
  • For large databases (10 GB or more), use Oracle Data Pump. It's an excellent tool for migrating large amounts of data and is compatible with RDS for Oracle.
  • If you're already using Oracle external tables in your processes, you can consider this method as an alternative to Data Pump.
  1. Stored Procedures:
  • Review and test all complex stored procedures thoroughly in a staging environment before migration.
  • Be aware that some system-level procedures or those requiring OS access may not work in RDS due to limited access to the underlying system.
  1. Performance Optimization:
  • Use AWS Database Migration Service (DMS) for pre-migration assessments.
  • Configure parallelism appropriately in DMS to optimize migration performance.
  • Prepare the target RDS instance by pre-creating the schema and objects for better performance during data load.
  1. Testing:
  • Perform comprehensive testing of all database objects, including stored procedures, in the RDS environment.
  • Conduct performance testing to ensure the RDS instance meets your IOPS and throughput requirements.
  1. Monitoring and Validation:
  • Use AWS native monitoring tools to track the progress of your migration and the performance of your new RDS instance.
  • Validate data integrity after migration to ensure all data and stored procedures are functioning correctly.

Remember, while RDS offers many managed database features, it also has some limitations compared to running Oracle on EC2. Ensure that all your critical database functionalities are supported in the RDS environment before proceeding with the migration.
Sources
Strategies for Migrating Oracle Databases to AWS - Strategies for Migrating Oracle Databases to AWS
Strategies and best practices for very large database migrations into Amazon RDS for Oracle | AWS Database Blog
Oracle database migration strategies - AWS Prescriptive Guidance
Migrating Oracle databases to the AWS Cloud - AWS Prescriptive Guidance
Migrating data for large Oracle databases - Strategies for Migrating Oracle Databases to AWS

profile picture
answered a month ago
profile picture
EXPERT
reviewed a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions