How to migrate from Oracle to Amazon Aurora

Introduction

In this lesson, you migrate a legacy Oracle database to a cloud-native database with Amazon Aurora. First, you create an Oracle database and load it with sample data to help with the walkthrough. Then you create a database in Aurora and migrate the data from your Oracle database by using AWS DMS. At the end of this lesson, you should feel confident in your ability to migrate from a legacy database to a cloud-native database with Aurora.

Break Free from Legacy Databases - How to Migrate from Oracle to Amazon Aurora (31:37)

Time to complete: 3045 minutes

Why use Aurora?

Aurora is a fully managed relational database provided by AWS. It has MySQL-compatible and PostgreSQL-compatible engines but was purpose-built for the cloud for better performance. You get faster performance and better availability than with legacy database options.

With Aurora, database operations are managed by AWS, leaving your team free to focus on innovation. Aurora handles cluster scaling, instance failover, data backups, and software updates. Rely on the efficiencies of the AWS Cloud to use a faster, cheaper, and more reliable database option.

Lesson contents

In this lesson, you learn how to migrate a legacy Oracle database to a fully managed, cloud-native database using Aurora. This lesson has eight steps.

  • In this module, you create an Oracle database in Amazon RDS. This serves as a source database for performing a migration to a cloud-native database with Aurora.


    To get started, navigate to the Amazon RDS console. On the main page, choose Create database to create a new database.

    (click to zoom)

    This initiates the database creation wizard. In the Engine options section, choose Oracle as your Engine type. Then choose Oracle Standard Edition Two and use the default version. Switch the License to license-included so that you don't need to bring your own Oracle license.

    (click to zoom)

    The database creation wizard includes templates to make it easier to configure the settings for your Amazon RDS database. Because this database is being used as an example, you can choose the Dev/Test settings.

    (click to zoom)

    In the Settings section, give your database a name and set the master username and password. Make sure you write these down because you need them to connect to your database and create additional users.

    (click to zoom)

    Next, choose the database instance size. Choose Burstable classes, and then choose the db.t3.small instance class for your instance.

    (click to zoom)

    Next, configure the storage options for your Amazon RDS database. You can use the default options with 20 GiB of General Purpose (SSD) storage.

    (click to zoom)

    The next section in the Amazon RDS database creation wizard is about connectivity. You must specify the Amazon Virtual Private Cloud (Amazon VPC) in which your database resides, as well as the network subnet and security groups for your database instance.

    You can use the default VPC in your AWS account. In the Additional connectivity configuration subsection, under Publicly accessible, choose Yes so that you can connect to your database from your own computer. Then choose to create a new VPC security group and give it a name.

    (click to zoom)

    There are some additional configuration options you can configure, including settings for backups, monitoring, maintenance, and automated upgrades. The default settings work for the migration example in this lesson.

    Choose Create database to create your database.

    (click to zoom)

    As Amazon RDS is provisioning your infrastructure and initializing your database, the Status of your database is Creating.

    When your database is ready to use, its Status is Available.

    (click to zoom)
    (click to zoom)

    Choose your database name in the DB Identifier column to see details about your database. There is an Endpoint property in the details. Save this value for the next module when you connect to your database.

    (click to zoom)

    In this module, you created an Oracle database to be used for a migration to a cloud-native relational database using Aurora. In the next module, you load your Oracle database with sample data to prepare for the migration to Aurora.

  • In this module, you load your Oracle database with sample data to migrate to Aurora.

    AWS provides sample database schemas for use in testing AWS DMS. You use one of these sample databases in this lesson. Download the zip file containing the sample databases.

    For this lesson, use the sample sports ticketing application in the AWS Database Migration Service example repo. This application is located at oracle/sampledb/v1 of the .zip file you downloaded. Unzip the .zip file and change to the oracle/sampledb/v1 directory in your terminal.

    Additionally, you need to install the SQL*Plus Instant Client to connect to your Oracle database. Follow the instructions to install the SQL*Plus Instant Client. If you are using OS X, you might find it easiest to install SQL*Plus using Homebrew.


    After you have installed SQL*Plus, connect to your Oracle database by entering the following command in your terminal.

    sqlplus <username>/<password>/@<endpoint>/ORCL

    Be sure to replace <username> and <password> with the username and password that you configured in the Amazon RDS database creation wizard. Replace <endpoint> with the endpoint of your Oracle database from the Amazon RDS console.

    If your connection was successful, your terminal should show a connection to your Oracle database as follows.

    (click to zoom)

    There is a file called install-rds.sql that handles the work of creating the sports ticketing sample database and loading it with data. To execute the install-rds.sql file and prepare your sample database, execute the following command in SQL*Plus.

    @install-rds.sql

    If you see an error that SQL*Plus was unable to open one of the files, you may not have launched SQL*Plus from the correct directory. Exit SQL*Plus, change to the oracle/sampledb/v1 directory in the .zip file you downloaded, and reconnect to SQL*Plus.

    After you execute the command, the script is executed. The output in your terminal reflects the script commands. It could take up to 45 minutes for the script to finish.

    After the script has executed, ensure that the data was loaded properly.

    First, execute the following query in SQL*Plus to view the tables created by the script.

    SELECT table_name, owner FROM all_tables WHERE owner = 'DMS_SAMPLE';

    You should see sixteen tables in the results, including MLB_DATA, NAME_DATA, and NFL_DATA.

    (click to zoom)

    Next, ensure that your tables have rows by doing a count. You can count all the records in the PERSON table with the following command.

    SELECT count(*) FROM person;

    You should see the following output, indicating there are 7,055,276 records in the PERSON table.

    (click to zoom)

    Finally, execute the following command to see the database triggers that were configured by the script.

    SELECT trigger_name, trigger_type FROM sys.all_triggers WHERE owner = 'DMS_SAMPLE';

    You should see that there are three triggers configured in your database.

    (click to zoom)

    As part of the script, you created tables and loaded records. You also used advanced features of Oracle such as triggers and views. These triggers and views can make migrations difficult. Fortunately, AWS SCT automates this process and you use AWS SCT in a future module in this lesson.

    In this module, you loaded your Oracle database with example data to be used for the migration. After loading your data, you ran a few SQL queries to understand the data and objects that were created in your database.

    In the next module, you create an Aurora database to be used as the target for your migration.

  • In this module, you create an Aurora database that serves as the target for your migration from a legacy database. Aurora is a cloud-native relational database with superior performance and availability compared to legacy databases. Aurora has MySQL-compatible and PostgreSQL-compatible options. In this module, you use the PostgreSQL-compatible version of Aurora.


    To begin, navigate to the Amazon RDS console. You should see the Oracle database that you created in a previous module. Choose Create database

    (click to zoom)

    This initiates the database creation wizard. In the Engine options section, choose Amazon Aurora as your Engine type. Then choose Amazon Aurora with PostgreSQL compatibility as the Edition.

    (click to zoom)

    The database creation wizard includes templates to make it easier to configure the settings for your Amazon RDS database. Because this database is being used as an example, you can use the Dev/Test settings.

    (click to zoom)

    In the Settings section, give your database a name, and set the master username and password. Make sure you write these down because you need them to connect to your database and create additional users.

    (click to zoom)

    Next, choose the database instance size. Switch to Burstable classes, and then choose the db.t3.medium instance class for your instance.

    (click to zoom)

    Next, configure options about availability and durability. If you are creating a production deployment, you should configure an Aurora Replica in a different Availability Zone for better availability and durability. Because this is a tutorial, you can choose Don't create an Aurora Replica.

    (click to zoom)

    The next section in the Amazon RDS database creation wizard is about connectivity. You must specify the Amazon Virtual Private Cloud (VPC) in which your database resides, as well as the network subnet and security groups for your database instance.

    You can use the default VPC in your AWS account. In the Additional connectivity configuration subsection, under Publicly accessible choose Yes so that you can connect to your database from your own computer. Then choose to create a new VPC security group and give it a name.

    (click to zoom)

    In the Additional configuration section, for the Initial database name, enter dms_sample. This is the same schema that you will transfer from your sample Oracle database.

    (click to zoom)

    Scroll to the bottom and choose Create database to create your database.

    (click to zoom)

    Amazon RDS begins creating your database. As Amazon RDS provisions your infrastructure and initializes your database, the Status of your database is Creating.

    When your database is ready to use, its Status is Available.

    (click to zoom)
    (click to zoom)

    Choose your database name in the DB identifier column to see details about your database. There is an Endpoints section that shows the Writer and Reader endpoints for your Aurora database. Save the value for your Writer endpoint because you need it in the next module.

    (click to zoom)

    In this module, you created an Aurora database with PostgreSQL compatibility to serve as the target database for migrating from a legacy database.

    In the next module, you use AWS SCT to move your schema from your legacy database to your cloud-native database.

  • In this module, you use AWS Schema Conversion Tool (AWS SCT) to convert your schema from your Oracle database to your cloud-native Aurora PostgreSQL database.

    In heterogeneous migrations, one of the most difficult tasks is migrating your schema. If you are using custom features of your database, such as triggers or views, their syntax might not be compatible with your new database. AWS SCT helps with this process.


    Before you start with AWS SCT, you need to install Java Database Connectivity (JDBC) drivers for both your source Oracle database and your target PostgreSQL database. Download the Oracle JDBC driver, and download the PostgreSQL JDBC driver.

    After you have downloaded both drivers, install AWS SCT by following the installation instructions for your operating system.

    After you have downloaded and opened AWS SCT, you should see a wizard to create a new project. Give your project a name and indicate that you're migrating a Transactional database (OLTP). Choose Oracle as the Source database engine. Then choose Next.

    (click to zoom)

    Next, enter the connection details for your source Oracle database. Use the following values:

    • Server name: Use the Endpoint value listed in the Amazon RDS console for your database.
    • Server port: Enter 1521.
    • Oracle SID: Enter ORCL.
    • Username: Use the username you entered in the Amazon RDS database creation wizard.
    • Password: Use the password you entered in the Amazon RDS database creation wizard.
    • Oracle driver path: Find the file with the Oracle JDBC driver from earlier in this module. It should be in your Downloads folder.

    After you have entered the connection details, choose Test connection to ensure you can connect to the database.

    (click to zoom)

    If the connection is successful, choose Next to move to the next step.

    (click to zoom)

    On the next page, choose a schema to analyze. Choose DMS_SAMPLE and then choose Next.

    (click to zoom)

    AWS SCT analyzes your schema and creates an assessment. See the section of the assessment that describes a migration to Aurora (PostgreSQL compatible) where it notes that 100% of database storage objects and 96% of database code objects can be converted automatically.

    You can scroll further down in the assessment to see additional details from the analysis. When you are ready, choose Next to move to the next step.

    (click to zoom)

    Next, configure the connection to your target Aurora database. In the Target database engine dropdown, choose Amazon Aurora (PostgreSQL compatible). Then use the following information for connection details:

    • Server name: Enter the value for the Writer endpoint in the Amazon RDS console.
    • Server port: Enter 5432.
    • Database: Enter dms_sample, which is the initial database name you created in your database.
    • Username: Enter the value you entered in the database creation wizard.
    • Password: Enter the value you entered in the database creation wizard.
    • Aurora driver path: Choose the path where you downloaded the PostgreSQL driver earlier in this module.

    After you have entered the connection details, choose Test connection to test your connection.

    (click to zoom)

    If you connected successfully, choose Finish to finish the configuration.

    (click to zoom)

    You should see the main view of AWS SCT. On the left is your source Oracle database. On the right is your target Aurora database.

    Find the DMS_SAMPLE schema on the left side. Right-click it and choose Convert schema.

    (click to zoom)

    After the schema has been converted, find dms_sample on the right side in your Aurora database. Right-click it and choose Apply to database.

    (click to zoom)

    AWS SCT works on applying the schema to your target database. While this process is in progress, your screen should appear as follows.

    When the process is complete, your target database has the database schema objects from your source database.

    (click to zoom)

    In this module, you learned how to install and configure AWS SCT, which helps during database migrations to automate any schema changes that are required. In this module, you converted an Oracle schema to a PostgreSQL-compatible schema in Aurora.

    In the next module, you create a replication instance in AWS DMS.

  • In this module, you create a replication instance in AWS DMS.

    AWS DMS is a service that copies data from an existing database into an AWS cloud-native database. A replication instance is an Amazon EC2 instance that can host replication tasks in AWS DMS. In a future module, you set up a replication task.


    To create a replication instance, go to the Replication instances section of the AWS DMS console. Choose Create replication instance to begin the replication instance creation wizard.

    (click to zoom)

    In the Replication instance configuration section, give your replication instance a name and description. Then choose your instance class. In this lesson, the amount of data is small. You can choose the dms.t2.micro instance class.

    Then choose an engine version for AWS DMS. Finally, choose the amount of allocated storage for your replication instance.

    (click to zoom)

    As you continue in the Replication instance configuration section, choose a VPC for your replication instance. Choose the same VPC in which you provisioned your Oracle and Aurora databases to ease network access for the replication instance.

    There are options for Multi AZ configuration and whether your replication instance should be publicly accessible. Keep the default options for both of these.

    (click to zoom)

    Next, open the Advanced security and network configuration section. For the VPC security group(s) configuration, choose the same security group that you attached to your Oracle database.

    (click to zoom)

    When you're ready, choose Create to create your replication instance in AWS DMS.

    (click to zoom)

    After you choose Create, AWS provisions your replication instance. It shows a Status of Creating while AWS provisions and initializes your instance.

    When your replication instance is ready to go, its Status is Available.

    (click to zoom)
    (click to zoom)

    While you are waiting for your replication instance to be available, go to the Security Groups section in the Amazon EC2 console. You need to add a rule to your security groups to allow your replication instance to access your databases.

    First, find the security group you attached to your Oracle database instance and your replication instance, and choose it.

    (click to zoom)

    Choose to Edit inbound rules for your security group.

    (click to zoom)

    Your security group has an existing rule that allows for access to your Oracle instance from the IP address you used to create the database. Choose Add rule to add an additional rule. For Type, choose Oracle-RDS. For Source, enter the security group that is attached to your Oracle database and your replication instance.

    Your screen should look as follows. Choose Save rules to save the updated rules for your security group.

    (click to zoom)

    Now do the same thing for your Aurora security group. Go back to the Security Groups section in the Amazon EC2 console. Find the security group you attached to your Aurora database instance and choose it.

    (click to zoom)

    Choose to Edit inbound rules for your security group.

    (click to zoom)

    Add a rule to your security group. The Type should be PostgreSQL and the Source should be the security group that you attached to your replication instance.

    Then choose Save rules to save the rules to your security group.

    (click to zoom)

    When your replication instance is available and you have updated the rules for your security group, you may move on to the next module.


    In this module, you created a replication instance in AWS DMS. The replication instance is used to host the replication tasks that migrate data from your legacy database to your cloud-native database in Aurora. You also updated your security groups to allow access from your replication instance to your Oracle and Aurora database instances.

    In the next module, you create endpoints for your source and target databases for AWS DMS.

  • In this module, you create endpoints for your source and target databases for AWS DMS.


    First, create the endpoint for your target database. This is the Oracle database you created in Amazon RDS.

    Navigate to the Endpoints section of the AWS DMS console. Choose Create endpoint to create a new endpoint.

    (click to zoom)

    In the endpoint creation wizard, choose to create a Source endpoint. Choose the check box to Select RDS DB instance, and choose the Oracle database you created in Amazon RDS.

    (click to zoom)

    In the Endpoint configuration section, choose the Source engine of oracle. This completes most of the configuration information for you. Enter your password to complete the configuration.

    (click to zoom)

    Before you save your endpoint, you should test the connection to ensure it was configured correctly. Open the Test endpoint connection section to test your connection.

    Select the replication instance you want to use, and choose Run test. After a few seconds, you should see a Status of successful. This indicates you configured your security group and endpoint correctly. Choose Create endpoint to save your endpoint.

    (click to zoom)

    Next, you need to create a target endpoint for your Aurora database. In the Endpoints section, choose Create endpoint again.

    (click to zoom)

    This time, create a target endpoint. Choose Target endpoint in the Endpoint type configuration section. Then choose the Select RDS DB instance check box and choose the Aurora database you created.

    (click to zoom)

    This configures most of the information in the Endpoint configuration section. Enter your password to complete the configuration.

    (click to zoom)

    Be sure to test your endpoint connection as you did with your source endpoint. If you configured your security group access correctly, you should have a successful connection.

    Choose Create endpoint to create your endpoint.

    (click to zoom)

    Before moving on to the next module, you should have two endpoints configured: one for your source Oracle database and one for your target Aurora database. Make sure that you have tested both endpoints and can successfully connect to both databases. Then move on to the next module.


    In this module, you created endpoints to connect to your databases. In the next module, you use those endpoints to create a replication task that copies data from your source database to your target database.

  • In this module, you create a replication task in AWS DMS.

    A replication task migrates data from a source database to a target database. In your case, you are moving data from a legacy relational database to a cloud-native relational database in Aurora.


    To get started, navigate to the Database migration tasks section of the AWS DMS console. Choose Create task to create a new replication task.

    (click to zoom)

    In the Task configuration section, set up the parameters of your replication task. Give your task a name and choose the replication instance you created in an earlier module. Then choose the source endpoint for your legacy database and your target endpoint for your cloud-native database in Aurora.

    You need to choose a migration type. There are two migration types:

    1. Migrate existing data, which performs a one-time process to copy data from your source database to your target database.
    2. Replicate ongoing changes, which copies all ongoing operations from your source database to your target database.

    In this walkthrough, you migrate existing data to your new Aurora database. For the migration type, choose Migrate existing data.

    (click to zoom)

    In the Task settings section, there is a setting called Target table preparation mode. Choose Truncate. If you were to use the default option, Drop tables on target, the tables you created with AWS SCT would be removed.

    (click to zoom)

    In the Table mappings section, tell AWS DMS which tables you want to copy over and how to map them to your target database.

    Enter DMS_SAMPLE for the Schema name and the wildcard character % for the Table name. This copies all tables in the DMS_SAMPLE schema to your new database.

    (click to zoom)

    The schema in the source Oracle database is in uppercase, but the schema you've created in PostgreSQL is in lowercase. You can add a transformation to help with this.

    In the Transformation rules section, choose Add new transformation rule. For Target, choose Schema from the dropdown. Use the % wildcard character for the Schema name. For the Action, choose Make lowercase.

    (click to zoom)

    Scroll to the bottom of the wizard and choose Create task to create the replication task.

    (click to zoom)

    The replication task is shown in the Database migration tasks section with a Status of Creating.

    After the replication task is created and the load finishes, the Status is Load complete.

    (click to zoom)
    (click to zoom)

    You have successfully migrated your data to your Aurora database. You can connect to your database by using psql or other PostgreSQL clients to ensure that your data is available.


    In this module, you created a replication task in AWS DMS to migrate your existing data from Oracle to a cloud-native database in Aurora.

    In the next module, you complete the migration and clean up the resources you created.

  • If you have followed all the steps in this lesson, you have created a cloud-native relational database using Aurora. You have also created an Oracle database with sample data. You learned how to use AWS SCT to help with schema migrations to a new database engine. Finally, you learned how to use AWS DMS to migrate your data to your new database.

    In this final module, you learn the steps to complete your migration and to clean up your AWS DMS resources.

    When your initial migration is complete and all data is synced to your new database, you are ready to use your new database as your primary database. Change your application code to read and write from your new database.

    After you have switched to using your primary database and are confident in the results, delete your AWS DMS infrastructure.


    First, stop and delete the database migration task to replicate your data. Navigate to the Database migration tasks section of the AWS DMS console. Choose the task you want to remove and choose Delete.

    (click to zoom)

    Next, navigate to the Endpoints section of the AWS DMS console. Choose both your source endpoint and your target endpoint, and then choose Delete.

    (click to zoom)

    Then go to the Replication instances section of the AWS DMS console. If your replication instance is not being used for any other replication tasks, choose it and then choose Delete.

    (click to zoom)

    Next, delete the Amazon RDS databases you used in this lesson. Navigate to the Amazon RDS console, choose your Oracle database, and then choose Delete.

    (click to zoom)

    Now, choose the Writer instance of your Aurora database cluster and then choose Delete. In an actual migration, you would not delete this database because it would be your production database going forward

    (click to zoom)

    In this module, you learned how to migrate your application to use your new database. You also learned how to clean up the resources you created in this lesson.

In this lesson, you migrated a legacy database to a cloud-native relational database using Aurora. When moving from a legacy database to a cloud-native database, there may be inconsistencies in the schema and code objects between the two database engines. With AWS SCT, migrating between database engines is much safer. Additionally, AWS DMS makes it easier to move your data from one database to another.