Step 1: Prepare Your Oracle Source Database Step 2: Launch and Prepare Your Aurora MySQL Target Database Step 3: Launch a Replication Instance Step 4: Create a Source Endpoint Step 5: Create a Target Endpoint Step 6: Create and Run a Migration Task

Migration High-Level Outline

To migrate your data from Oracle to Aurora MySQL using Amazon DMS, you take the following steps. If you’ve used Amazon DMS before or prefer clicking a mouse to reading, the following summary should help you kick-start your migration. To get the details about migration or if you run into questions, see the step-by-step guide.

Step 1: Prepare Your Oracle Source Database

To use Amazon DMS to migrate data from an Oracle source database requires some preparation and we also recommend a few additional steps as best practices.

Amazon DMS user — It’s a good practice to create a separate user for the specific purpose of migrating your data. This user should have the minimal set of privileges required to migrate your data. You can find specific details regarding those privileges later. If you are simply interested in testing Amazon DMS on a non-production database, any DBA user will be sufficient.
Supplemental logging — To capture changes, you must enable supplemental logging in order to use DMS. To enable supplemental logging at the database level issue the following command.
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
```
Additionally, Amazon DMS requires for each table being migrated, you set at least key-level supplemental logging. Amazon DMS automatically adds this supplemental logging for you if you include the following extra connection parameter for your source connection.
```
addSupplementalLogging=Y
```
Source database – To migrate your data, the Amazon DMS replication server needs access to your source database. Make sure that your firewall rules give the Amazon DMS replication server ingress.

Step 2: Launch and Prepare Your Aurora MySQL Target Database

Following are some things to consider when launching your Aurora MySQL instance:

For best results, we recommend that you locate your Aurora MySQL instance and your replication instance in the same VPC and, if possible, the same Availability Zone.
We recommend that you create a separate user with minimal privileges for migrating your data. The Amazon DMS user needs the following privileges on all databases to which data is being migrated.
```
ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE, SELECT
```
Additionally, Amazon DMS needs complete access to the awsdms_control database. This database holds information required by Amazon DMS specific to the migration. To provide access, run the following command.
```
ALL PRIVILEGES ON awsdms_control.* TO 'dms_user'
```

Step 3: Launch a Replication Instance

The Amazon DMS service connects to your source and target databases from a replication instance. Here are some things to consider when launching your replication instance:

For best results, we recommend that you locate your replication instance in the same VPC and Availability Zone as your target database, in this case Aurora MySQL.
If either your source or target database is outside of the VPC where you launch your replication server, the replication server must be publicly accessible.
Amazon DMS can consume a fair bit of memory and CPU. However, it’s easy enough to scale up if necessary. If you anticipate running several tasks on a single replication server or if your migration involves a large number of tables, consider using one of the larger instances.
The default storage is usually enough for most migrations.

Step 4: Create a Source Endpoint

For Amazon DMS to access your Oracle source database you’ll need to create a source endpoint. The source endpoint defines all the information required for Amazon DMS to connect to your source database from the replication server. Following are some requirements for the source endpoint.

Your source endpoint needs to be accessible from the replication server. To allow this, you will likely need to modify your firewall rules to whitelist the replication server. You can find the IP address of your replication server in the Amazon DMS Management Console.
For Amazon DMS to capture changes, Oracle requires supplemental logging be enabled. If you want Amazon DMS to enable supplemental logging for you, add the following to the extra connection attributes for your Oracle source endpoint.
```
addSupplementalLogging=Y
```

Step 5: Create a Target Endpoint

For Amazon DMS to access your Aurora MySQL target database you’ll need to create a target endpoint. The target endpoint defines all the information required for DMS to connect to your Aurora MySQL database.

Your target endpoint needs to be accessible from the replication server. You might need to modify your security groups to make the target endpoint accessible.
If you’ve pre-created the database on your target, it’s a good idea to disable foreign key checks during the full load. To do so, add the following to your extra connection attributes.
```
initstmt=SET FOREIGN_KEY_CHECKS=0
```

Step 6: Create and Run a Migration Task

A migration task tells Amazon DMS where and how you want your data migrated. When you create a migration task, consider setting migration parameters as shown following.

Endpoints and replication server — Choose the endpoints and replication server created before.

Migration type — In most cases you’ll want to choose migrate existing data and replication ongoing changes. With this option, Amazon DMS loads your source data while capturing changes to that data. When the data is fully loaded, Amazon DMS applies any outstanding changes and keeps the source and target databases in sync until the task is stopped.

Target table preparation mode — If you’re having Amazon DMS create your tables, choose drop tables on target. If you’re using some other method to create your target tables such as the Amazon Schema Conversion Tool, choose truncate.

LOB parameters — If you’re just trying Amazon DMS, choose include LOB columns in replication, Limited LOB mode, and set your max LOB size to 16 (which is 16k.) For more information regarding LOBs, read the details in the step-by-step guide.

Enable logging — To help with debugging migration issues, always enable logging.

Table mappings — When migrating from Oracle to Aurora MySQL, we recommend that you convert your schema, table, and column names to lowercase. To do so, create a custom table mapping. The following example migrates the schema DMS_SAMPLE and converts schema, table and column names to lower case.

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "DMS_SAMPLE",
        "table-name": "%"
      },
      "rule-action": "include"
    },
   {
      "rule-type": "transformation",
      "rule-id": "6",
      "rule-name": "6",
      "rule-action": "convert-lowercase",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "%"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "7",
      "rule-name": "7",
      "rule-action": "convert-lowercase",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "8",
      "rule-name": "8",
      "rule-action": "convert-lowercase",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%",
        "column-name": "%"
      }
    }
  ]
}

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Migrating an On-Premises Oracle Database to Amazon Aurora MySQL

Migration Step-by-Step Guide