Database migration from on premises to the cloud requires planning and prework in order to be successful. Key factors such as the size of the on-premises database, network bandwidth, available downtime, and backup method should be considered while planning your database migration project. In this post, we discuss how to perform a homogeneous migration from an on-premises Oracle database to Amazon Relational Database Service (Amazon RDS) for Oracle.
To move an Oracle database to Amazon RDS for Oracle, tools such as Oracle Data Pump are useful due to its integration with Amazon Simple Storage Service (Amazon S3). Other methods such as Oracle SQL Loader can be quite effective when moving a large number of records. One of the most popular database backup strategies for Oracle databases on-premises has been Oracle Recovery Manager (RMAN), where database administrators have successfully implemented RMAN backups and restore as a part of their large-scale database migrations.
Amazon RDS does not allow you to access the operating system of the host. Therefore, RMAN restore to Amazon RDS is not a viable solution as of this writing. For our solution, we restore the RMAN backup on an Amazon Elastic Compute Cloud (Amazon EC2) instance, then we use Data Pump to export data from the EC2 server to Amazon S3 and restore that in the RDS for Oracle database.
Storage best practice
Moving a large data pump file (for example, 5 TB) requires the same size available in the Data Pump directory. As a best practice, you can delete the dumps from the Data Pump directory after importing the data without indexes and rebuild the indexes later. That way, a large dump file can be reused in the index creation.
Solution overview
In this post, we use a two-step approach to migrate the source database to Amazon RDS for Oracle. First, we use RMAN to restore the RMAN backup on an EC2 instance, then we use Data Pump to export data to Amazon S3 and restore that in the RDS for Oracle database. You can use RMAN instead of performing an Oracle expdp/impdp if you have a large database or large number of records in tables that might take much longer using Oracle Data Pump.
For this post, we assume we don’t have Oracle Golden Gate or Oracle Data Guard licenses. Therefore, to minimize the downtime of the migration, we follow the solution outlined in this post. To limit the size of backup files, you can run RMAN in compressed mode according to your license considerations. You can also apply encryption strategies before moving files from on premises to the AWS Cloud based on your licensing agreements.
The following diagram illustrates our solution architecture.
The workflow includes the following steps:
Perform a full RMAN backup to Amazon S3 using AWS Storage Gateway.
Perform an incremental backup using Storage Gateway.
Perform an incremental backup using Storage Gateway again to reduce the lag time.
The lag time in the context of an incremental backup for an Oracle database refers to the delay between the last recorded data change in the production database and the point at which that change is recorded in the backup.
Continue to perform incremental backups using Storage Gateway until the lag is very minimal (according to your requirements).
Stop any transactions on the database to get a final incremental copy that will be stored in the S3 bucket directly.
In the following steps, you incur downtime as the restore process happens on the EC2 instance.
Restore the database on the EC2 instance from the S3 bucket, which has the full backup as well all the incremental backups, using RMAN commands.
When the restore is complete, use Data Pump to move the data to the RDS instance.
Validate all the data on the RDS instance with the on-premises database server.
You should test these steps thoroughly to predict the correct downtime needed to forecast to the business users. As a general rule, the restore process can be compared to the RMAN backup process twice, with a scope for changes in backup channels and the shape of the EC2 instance that might reduce the duration of the restore.
To cut down the downtime further, you can also use AWS Database Migration Service (AWS DMS), which can logically replicate data after the RMAN backup is restored on the EC2 instance. You can then decide to account for the downtime when the data is moved to Amazon RDS for Oracle using Data Pump. For more information about reducing downtime, see Plan an Oracle migration to Amazon RDS for Oracle using Oracle Data Pump and AWS DMS.
Also, the directory structure on the on-premises side must match the EC2 server, otherwise you need to relocate the database files.
This solution guides you through creating an Oracle RMAN backup using Storage Gateway, and then restoring that backup on an EC2 server. After the backup is restored on the EC2 server, you use Data Pump to restore the database from Amazon EC2 to Amazon RDS for Oracle.
Prerequisites
For instructions on setting up Storage Gateway, refer to File Interface to AWS Storage Gateway.
Perform an RMAN backup to Amazon S3 using Storage Gateway
Complete the following steps to perform an RMAN backup:
Log in to the Oracle database server:
Run the following commands to set configuration parameters:
Run the following commands to perform the backup:
Validate the export by making sure all the backup files are in the S3 bucket.
Restore the RMAN backup to an EC2 instance using Storage Gateway
Complete the following steps to restore the RMAN backup:
Connect to RMAN:
Perform a startup mount:
Perform a preview of the restore:
Restore the database. The restore command syntax will vary depending on the situation. In this example, control files from the on-premises database have been copied to the EC2 instance before starting the restore:
Recover individual data files or tablespaces depending on your case. You will see what to recover when you issue the alter database open command. The following code shows some examples:
Open the database:
Export from Amazon EC2 to Amazon RDS for Oracle using Data Pump
After the database is restored on the EC2 instance, complete the following steps to export your data to an S3 bucket. You can also do a direct import into Amazon RDS using a database link from the EC2 server.
Perform an export using Data Pump. For this post, on our source EC2 database, we have schema1, which contains the table emp_master:
This will produce a dump file in the directory:
Install the AWS Command Line Interface (AWS CLI) on the EC2 database server and move the dump file to the S3 bucket.
You can also use a storage gateway to save time to export directly to Amazon S3. You can use this method if Storage Gateway isn’t deployed into this EC2 server.
Enable the S3 integration feature in Amazon RDS.
Move the dump file created earlier from the source EC2 instance database to the RDS Data Pump directory using the following commands:
The preceding command will generate the task ID (in our case, 1613433867971-645).
Check the status of the task using the following command with your task ID:
A successful output should look like the following code:
Verify if the file exists in the Data Pump directory using the following command:
The output will look like the following code:
You can import the dump file by using either the command line or the SQL Developer import jobs option . Also, you can use DBMS_DATAPUMP pl/sql packages to do the import. You can run the following command on a server with SQL Net port (for example, 1521) open between this server and the RDS database. The SID should be listed in the tnsnames.ora file of the server where the following command is being run.
Query the target schema to check if the rows are successfully imported.
Clean up
To avoid incurring future charges, delete the resources you created:
Delete the S3 bucket you created for the Oracle RMAN backup.
Delete your storage gateway.
Delete the EC2 server you used to host your Oracle database.
Delete the RDS for Oracle DB instance that you created to test this solution.
Conclusion
In this post, we showed you how to migrate an on-premises Oracle database to an RDS for Oracle database by using RMAN backup and following a two-step approach. There are other options to migrate on-premises databases using Oracle Data Guard and Oracle Golden Gate that would require additional licensing considerations. The solution we presented is useful in cases where the size of the source database is large enough that the Oracle Data Pump option may not be ideal and Data Guard or Golden Gate are not available. Now you can migrate your on-premises Oracle databases using RMAN backup to Amazon for RDS for Oracle following the information in this post.
About the Authors
Varun Mahajan is a Principal Solutions Architect at Amazon Web Services. He works with enterprise customers, helping them align their business goals with the art of the possible using cloud-based technologies. He enjoys working with data and solving problems using the AWS database and analytics portfolio.
Raghavarao Sodabathina is a Principal Solutions Architect at AWS, focusing on data analytics, AI/ML, and cloud security. He engages with customers to create innovative solutions that address customer business problems and accelerate the adoption of AWS services. In his spare time, Raghavarao enjoys spending time with his family, reading books, and watching movies.
Source: Read More
