Best practices for migrating to Amazon RDS for Oracle

Right-size the Amazon RDS for Oracle DB instance based on your requirements for CPU, memory, IOPS, and storage type.

Set the correct time zone and character set.

Make sure to launch Amazon RDS in the correct virtual private cloud (VPC).

Create the security groups with correct port and IP addresses.

Provision your Amazon RDS database in a private subnet for security.

If possible, provision the DB instance by using the latest Oracle Database version, which is currently 19c. Earlier versions are nearing end of support. For more information, see Amazon RDS support for Oracle Database 19c.

If you want to use encryption, always enable it while you are provisioning the Amazon RDS database.

Create a separate option group and parameter group for each Amazon RDS database.

Check the database size, to see if you can migrate it schema by schema, instead of migrating the full database. Migrating schemas individually is less error prone and more manageable than migrating them all at once.

Export data in parallel mode, by using the Oracle Data Pump PARALLEL parameter, for better performance.

Check if the tables have large objects (LOBs). If you have large tables with LOBs, we recommend that you export those tables separately.

During the export process, avoid running long database transactions on your source database to avoid Oracle read inconsistency errors.

If you are using replication tools such as AWS DMS, Oracle GoldenGate, or Quest SharePlex, make sure that you have enough space on your on-premises server to hold archive logs for 24-72 hours, depending on how long the migration takes.

If you're migrating a very large database, we recommend that you provision a bigger Amazon RDS instance type initially, for the duration of the migration, for faster data loads. After the migration is complete, you can change the DB instance to the right-sized instance type.

Increase the size of redo log files, undo tablespaces, and temporary tablespaces to improve performance during migration, if needed.

Disable the Multi-AZ option during the import process, and enable it after migration is complete.

Disable the generation of archive logs by setting the backup retention to zero to achieve faster data load.

Prepare the target database by creating tablespaces, users, roles, profiles, and schemas in advance.

If you have large tables with LOBs, import each LOB table separately.

Check the import log files for errors, and fix any errors after the import is complete.

Check for invalid objects. If you find any, compile and fix them.

Some procedures might not compile due to lack of permissions on SYS objects that are not allowed or supported in Amazon RDS. These procedures have to be rewritten.

If you are using sequences, validate the sequence values against the source database to avoid sequence inconsistency.

Make sure that the object count in your Amazon RDS database is the same as in the source database. Validate tables, indexes, procedures, triggers, functions, packages, constraints, and other objects.

If your source database has database links to other databases, test the connectivity to confirm that the links still work.

Gather dictionary-level and schema-level statistics for optimal performance.

You might have to upgrade your Oracle client software or JDBC software based on the Amazon RDS for Oracle database version. If you've migrated to a newer version of Oracle Database, it might not support older versions of Oracle client software.

Perform functional testing.

Compare the performance of SQL queries in your source and target databases, and tune the queries as needed. Some queries might perform more slowly in the target database, so we recommend that you capture the baselines of the SQL queries in the source database.

When the application team finishes testing and confirms that your Amazon RDS database is functioning properly, you can:

Run performance tests to ensure that they meet your business expectations.

Test database failover, recovery, and restoration to make sure that you're meeting RPO and RTO requirements.

List all critical jobs and reports, and run them on Amazon RDS to evaluate their performance against your service-level agreements (SLAs).

Set up CloudWatch monitoring, and enable detailed monitoring.

Use Amazon RDS Performance Insights and the Oracle Enterprise Manager (OEM) Management Agent to monitor your database.

Set up alerts by using SNS topics.

Set up automatic backups using AWS Backup. You can also use Oracle Data Pump backups or take manual snapshots.

For high availability, set up the Amazon RDS Multi-AZ feature.

If you need read-only databases, set up a Read Replica within the same or across AWS Regions according to your needs.