How to migrate from Oracle to Amazon Aurora

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

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.

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.

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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.

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

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

Choose to Edit inbound rules for your security group.

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.

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.

Choose to Edit inbound rules for your security group.

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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