# Migrating an Amazon RDS for Oracle Database to an Amazon S3 Data Lake This walkthrough helps you understand the process of migrating data from Amazon Relational Database Service (Amazon RDS) for Oracle to Amazon Simple Storage Service (Amazon S3) using AWS Database Migration Service (AWS DMS). Most organizations use Online Transaction Processing (OLTP) database engines to host their transactional workloads. These engines are optimized for high-transaction volumes such as an online order processing application. However, these engines typically perform poorly for analytical applications, such as business intelligence or building predictive models using machine learning. For these use cases, a popular solution is to build a data lake for analysis. In this document, we build a data lake in Amazon S3 using data hosted in an RDS for Oracle database. Amazon S3 is the largest and most performant cloud storage service. With Amazon S3, you can build a cost-effective, secure data lake. Amazon S3 provides 99.999999999% (11 9s) of data durability and makes it possible to store and manage both structured and unstructured data at unlimited scale. To illustrate the process, we use AWS DMS to migrate data from an example database. AWS DMS is a managed service that helps migrate between heterogeneous sources and targets. In our case, we migrate an Oracle database to Amazon S3. AWS DMS support not only the migration of your existing data, but also ensures that the source and target are synchronized for ongoing transactions. **Topics** + [ ## Why use AWS DMS? ](#oracle-s3-data-lake-why-dms) + [ ## Example data set ](#oracle-s3-data-lake-data-set) + [ ## Solution overview ](#oracle-s3-data-lake-solution-overview) + [ # Prerequisites for migrating an RDS for Oracle database to an Amazon S3 data lake ](oracle-s3-data-lake-prerequisites.md) + [ # Step-by-step Amazon RDS for Oracle database to Amazon S3 data lake migration walkthrough ](oracle-s3-data-lake-step-by-step.md) + [ # Step-by-step Amazon RDS for Oracle database to Amazon S3 data lake migration conclusion ](oracle-s3-data-lake-conclusion.md) ## Why use AWS DMS? You can use a SQL-level mechanism to source ongoing changes. This approach impacts your source database performance or requires that you implement additional logic. For example, you can use SQL filters on last updated timestamps or add triggers to capture DML changes. In contrast, AWS DMS mines changes from the database transaction logs, which are generated by the database for recovery purposes. AWS DMS then takes those changes, converts them into the target format, and applies them to the target. This process minimizes overhead on the source and provides near-real time replication to the target. In the rest of this document, we guide you through the steps that you take to migrate the example Oracle database into Amazon S3. In the next sections, we describe the characteristics of the database. Then we build the replication resources in AWS DMS that we use to migrate the database, paying close attention to matching the AWS DMS configuration with our particular use case. ## Example data set For this walkthrough, we use the [Sales History Oracle sample data set](https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/introduction-to-sample-schemas.html#GUID-5EAB7534-C5BA-47F8-BEFD-7803C078304B). The Sales History schema includes 8 tables. The largest table is the sales table which is a fact table with 5 billion rows. The total size of this source database is about 200 GB and has about 20 years worth of sales history data in 96 partitions. The remaining tables are mostly smaller dimension tables. The following diagram shows the data model for our sample use case. ![\[A high-level diagram of the source data set.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-oracle-s3-data-lake-data-set.png) Note that the costs and sales tables don’t have primary keys. However, the sales table is partitioned on a date column. This date column is important to sequence the latest version of a sales record for analysis purposes. The company loads data into its data warehouse regularly to gather statistics for these reports. The company also runs reports on different distribution channels through which its sales are delivered. When the company runs special promotions on its products, it analyzes the impact of the promotions on sales. It also analyzes sales by different geographical regions. The company in our use case does high volume of business, so it runs business statistics reports and uses machine learning algorithms to aid in decision-making. Most of this analysis is time-sensitive, and they analyze past data trends to get insights on business operations. The company’s data scientists want to explore the data to decide which data to use for model training. Once this data discovery phase is complete, the data will be used to build predictive models using machine learning algorithms. Once the data is migrated to S3, it is used for training machine learning (ML) models using AWS ML managed-services. These models will be used for demand product forecasting and inventory replenishment. The business also requires that the initial transfer of data from Oracle to Amazon S3 must complete within an 8 hour window. ## Solution overview The following diagram shows the architecture of a migration from RDS for Oracle to Amazon S3 using AWS DMS. ![\[An architecture diagram of the migration from Oracle to an Amazon S3 data lake.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/oracle-s3-data-lake-architecture-diagram.png) The Amazon RDS for Oracle database contains the example sales history data set. AWS Database Migration Service (AWS DMS) contains several components used to host the replication engine. Amazon S3 provides storage for the data lake tables and downstream applications for machine learning and analytics consume the data lake information. To run this walkthrough, create the following resources in AWS DMS. + Replication instance — An AWS managed instance that hosts the AWS DMS engine. You control the type or size of the instance based on the workload you plan to migrate. + Source endpoint — An endpoint that provides connection details, data store type, and credentials to connect to a source database. For this use case, we configure the source endpoint to point to the Amazon RDS for Oracle database. + Target endpoint — AWS DMS supports several target systems including Amazon RDS, Amazon Aurora, Amazon Redshift, Amazon Kinesis Data Streams, Amazon S3, and so on. For this use case, we configure Amazon S3 as the target endpoint. + Replication task — A task that runs on the replication instance and connects to endpoints to replicate data from the source database to the target database. In the rest of this document, we show how to configure each of these components to migrate the sales history data set. We start with the prerequisites to complete this walkthrough, and then continue with the step-by-step migration procedure and conclusion. # Prerequisites for migrating an RDS for Oracle database to an Amazon S3 data lake The following prerequisites are required to complete this walkthrough: + Understand Amazon Relational Database Service (Amazon RDS), the applicable database technologies, and SQL. + Create a user with AWS Identity and Access Management (IAM) credentials that allows you to launch Amazon RDS and AWS Database Migration Service (AWS DMS) instances in your AWS Region. For information about IAM credentials, see [Create an administrative user](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.SettingUp.html#create-an-admin). + Understand the Amazon Virtual Private Cloud (Amazon VPC) service and security groups. For information about using Amazon VPC with Amazon RDS, see [Amazon VPC VPCs and Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.html). For information about Amazon RDS security groups, see [Controlling access with security groups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html). + Understand the supported features and limitations of AWS DMS. For information about AWS DMS, see [What is Database Migration Service?](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html). + Understand how to work with Oracle as a source and Amazon S3 data lake as a target. For information about working with Oracle as a source, see [Using an Oracle database as a source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html). For information about working with Amazon S3 as a target, see [Using Amazon S3 as a target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html). + Understand the supported data type conversion options for Oracle and Amazon S3. For information about data types for Oracle as a source, see [Source data types for Oracle](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.DataTypes). For information about data types for Amazon S3 as a target (Parquet only), see [Target data types for S3 Parquet](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.DataTypes). + Audit your source Oracle database. For each schema and all the objects under each schema, determine whether any of the objects are no longer being used. Deprecate these objects on the source Oracle database, because there’s no need to migrate them if they aren’t being used. For more information about AWS DMS, see [Getting started with Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html). To estimate what it will cost to run this walkthrough on AWS, you can use the AWS Pricing Calculator. For more information, see [https://calculator.aws/](https://calculator.aws/). To avoid additional charges, delete all resources after you complete the walkthrough. # Step-by-step Amazon RDS for Oracle database to Amazon S3 data lake migration walkthrough The following steps provide instructions for migrating an Amazon RDS for Oracle database to an Amazon S3 data lake. These steps assume that you have already prepared your source database as described in previously. **Topics** + [ # Step 1: Create an AWS DMS Replication Instance ](oracle-s3-data-lake-step-1.md) + [ # Step 2: Configure a Source Amazon RDS for Oracle Database ](oracle-s3-data-lake-step-2.md) + [ # Step 3: Create an AWS DMS Source Endpoint ](oracle-s3-data-lake-step-3.md) + [ # Step 4: Create a Target Amazon S3 Bucket ](oracle-s3-data-lake-step-4.md) + [ # Step 5: Configure an AWS DMS Target Endpoint ](oracle-s3-data-lake-step-5.md) + [ # Step 6: Create an AWS DMS Task ](oracle-s3-data-lake-step-6.md) + [ # Step 7: Run the AWS DMS Task ](oracle-s3-data-lake-step-7.md) # Step 1: Create an AWS DMS Replication Instance An AWS DMS replication instance hosts the software migrates data between the source and target. The replication instance also caches the transaction logs during the migration. The CPU and memory capacity of the replication instance influences the overall time needed for the migration. Make sure that you consider the specifics of your particular use case when you determine the size of your replication instance. A full load task consumes a lot of memory if it is run multithreaded. For more information, see [Choosing the right replication instance for your migration](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Types.html). For our use case, we have a limited time window of 8 hours to complete the full load, and the sales table that includes 197 GB of data. Our goal is to fit into the 8 hour window. Therefore, we scale the replication instance to accommodate these requirements. Each type of instance class has different CPU, memory, and I/O capacity. Sizing the replication instance should be based on factors like data volume, transaction frequency, large objects (LOBs) within storage of the data migration, and so on. We initially chose a DMS t3.medium instance running the latest AWS DMS engine version. This instance completed the migration in 18 hours. We then upgraded to a DMS c5.12xlarge instance. This instance size, combined with the proper task configuration, brought the full load time to under 8 hours. We also upgraded the storage of the replication instance to 200 GB, and as a result, 600 IOPS were available for our replication instance. By default, DMS allocates 50 GB of storage to a replication instance. This may not be sufficient for use cases where more tasks are running on same replication instance or when running tasks with parallel load for large tables. With 600 IOPS, we saved several minutes of migration time. For more information about storage volume performance and burst I/O credits, see [General Purpose SSD (gp2) volumes](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-purpose.html#EBSVolumeTypes_gp2). Because we replicate production data in this walkthrough, we use the Multi-AZ deployment option for our replication instance for high availability. Also, we didn’t make this replication instance publicaly accessible for additional security. For information about best practices for using AWS DMS, see [Database Migration Service Best Practices](https://d0.awsstatic.com/whitepapers/RDS/AWS_Database_Migration_Service_Best_Practices.pdf). **To create an AWS DMS replication instance** 1. Sign in to the AWS Management Console, and open the [AWS DMS console](https://console.aws.amazon.com/dms/v2). 1. Choose **Replication instances**, then choose **Create replication instance**. 1. On the **Create replication instance** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/oracle-s3-data-lake-step-1.html) 1. Choose **Create**. # Step 2: Configure a Source Amazon RDS for Oracle Database In this step, we configure the source Oracle database. Make sure that AWS DMS can access the database transaction logs and capture the data changes. Also, make sure that you set permissions for AWS DMS to access tables and database catalogs. AWS DMS can help users to migrate historical data from Oracle source database and also replicate the ongoing changes to a centralized data lake. To use Oracle as a source for AWS DMS, you must turn on the `ARCHIVELOG MODE`. Make sure that your database server retains the archive logs as long as AWS DMS requires them. If you configure your task to begin capturing changes immediately, then you should only need to retain archive logs for a little longer than the duration of the longest running transaction. Retaining archive logs for 24 hours is usually sufficient. If you configure your task to begin from a point in time in the past, then archive logs must be available from that time forward. For more information about turning on the `ARCHIVELOG MODE` and ensuring log retention for your Oracle database, see the [Oracle documentation](http://docs.oracle.com/database/121/ADMIN/archredo.htm#ADMIN11335). To capture change data, AWS DMS requires that you turn on supplemental logging on your source database. Minimal supplemental logging must be turned on at the database level. AWS DMS also requires that you turn on identification key logging. This option causes the database to place all columns of a row’s primary key in the redo log file whenever you update a row that contains a primary key. This occurs even if there is a change of value in any of the columns other than the primary key columns. You can set this option at the database or table level. + Create or configure a database user AWS DMS. We recommend that you use a user with the minimal privileges required by AWS DMS for your connection. AWS DMS requires the following privileges. ``` CREATE SESSION SELECT ANY TRANSACTION SELECT on V_$ARCHIVED_LOG SELECT on V_$LOG SELECT on V_$LOGFILE SELECT on V_$DATABASE SELECT on V_$THREAD SELECT on V_$PARAMETER SELECT on V_$NLS_PARAMETERS SELECT on V_$TIMEZONE_NAMES SELECT on V_$TRANSACTION SELECT on ALL_INDEXES SELECT on ALL_OBJECTS SELECT on ALL_TABLES SELECT on ALL_USERS SELECT on ALL_CATALOG SELECT on ALL_CONSTRAINTS SELECT on ALL_CONS_COLUMNS SELECT on ALL_TAB_COLS SELECT on ALL_IND_COLUMNS SELECT on ALL_LOG_GROUPS SELECT on SYS.DBA_REGISTRY SELECT on SYS.OBJ$ SELECT on DBA_TABLESPACES SELECT on ALL_TAB_PARTITIONS SELECT on ALL_ENCRYPTED_COLUMNS SELECT on <> ``` + For tables with primary key, turn on supplemental logging at key level. ``` ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ``` + Data warehouse databases can have fact tables without a primary key. In our sample database, the `SALES` table doesn’t have a primary key. This table is part of a full load and CDC task, however it does not have primary key or unique indexes. So we can add supplemental logging on all columns of the table. ``` ALTER TABLE SH.SALES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` For more information, see [Working with an Oracle database as a source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Amazon-Managed). # Step 3: Create an AWS DMS Source Endpoint In this step, we configure a source endpoint. AWS DMS uses this endpoint to connect to the source database to read data as well as changes to the data via transaction logs. You can use Extra Connection Attributes for the source endpoint to configure how AWS DMS captures changes to the data. After you configure the AWS Database Migration Service (AWS DMS) replication instance and the source RDS for Oracle instance, ensure connectivity between these two instances. To ensure that the replication instance can access the server and the port for the database, make changes to the relevant security groups and network access control lists. For more information about your network configuration, see [Setting up a network for a replication instance](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.VPC.html). AWS DMS can stream the changes to the data from `REDO` logs using either Logminer or Binary reader protocols. You can choose this protocol when you create your source endpoint. For detailed comparison on which mode to pickup for CDC replication, see [Using Oracle LogMiner or Binary Reader for CDC](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.CDC). Logminer option is easier to set up. However, since our source Oracle database workload involves ETL jobs that result in high volume of transactions, we choose Binary Reader since it offers better performance for ongoing replication. After you completed the network configurations, you can create a source endpoint. **To create a source endpoint** 1. Sign in to the AWS Management Console, and open the [AWS DMS console](https://console.aws.amazon.com/dms/v2). 1. Choose **Endpoints**, then choose **Create endpoint**. 1. On the **Create endpoint** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/oracle-s3-data-lake-step-3.html) 1. Choose **Create endpoint**. # Step 4: Create a Target Amazon S3 Bucket Before you create the target endpoint, you create an Amazon S3 bucket for the target data lake. To create the Amazon S3 bucket, do the following: 1. Sign in to the AWS Management Console, and open the [Amazon S3 console](https://s3.console.aws.amazon.com/s3/home). 1. Choose **Create bucket**. 1. For **Bucket name**, enter `s3-datalake`. 1. For ** AWS Region**, choose the region that hosts your AWS DMS replication instance. 1. Keep the default values in the other fields and choose **Create bucket**. You can also plan to optimize the storage cost from Amazon S3 using [Intelligent-Tiering](https://aws.amazon.com/s3/storage-classes/intelligent-tiering/) and [Lifecycle policies](https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-lifecycle-mgmt.html) when storing huge volume of data. Now, you have the Amazon S3 bucket for your data lake. Next, you can create a target endpoint for this bucket. # Step 5: Configure an AWS DMS Target Endpoint In this section, we walk through the configuration for setting up target data lake AWS DMS endpoint. You will also select appropriate options to store files in data lake. To use Amazon S3 as an AWS Database Migration Service (AWS DMS) target endpoint, create an IAM role with write and delete access to the AWS DMS bucket. Then add `dms.amazonaws.com` as a trusted entity in this IAM role. For more information, see [Prerequisites for using Amazon S3 as a target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.Prerequisites). When you use AWS DMS to migrate data to an Amazon Simple Storage Service (Amazon S3) data lake, you can change the default task behavior, such as file formats, partitioning, file sizing, and so on. This leads to minimizing post-migration processing and helps downstream applications consume data efficiently. You can customize task behavior using endpoint settings and extra connection attributes (ECA). Most of the AWS DMS endpoint settings and ECA settings overlap, except for a few parameters. In this section of walkthrough, we configure AWS DMS endpoint settings. ## Choose File Format For this walkthrough, we use the Parquet file format to help the data scientists consume data for data exploration and data discovery activities. Apache Parquet is a columnar format, which is built to support efficient compression and encoding schemes providing storage space savings and performance benefits. Specify the following endpoint settings. ``` DataFormat=parquet ParquetVersion=PARQUET_2_0 ``` ## Determine File Size By default, during ongoing replication AWS DMS task write calls to Amazon S3 are triggered either if the file size reaches 32 KB or if the previous file write was more than 60 seconds ago. These settings ensure that the data capture latency is less than a minute. However, this approach creates numerous small files in target Amazon S3 bucket. Because we migrate our source Sales History database schema for a machine learning use case, some latency is acceptable. However, we need to optimize this schema for cost and performance. During the data discovery phase performed by the data scientists, it is helpful to have large files for efficient analysis using the tools of their choice. We recommend that you set the size of the target file to at least 64 MB. Specify the following endpoint settings: `CdcMaxBatchInterval=3600` and `CdcMinFileSize=64000`. These settings ensure that AWS DMS writes the file until its size reaches 64 MB or if the last file write was more than an hour ago. **Note** Parquet files created by AWS DMS are usually smaller than the specified `CdcMinFileSize` setting because Parquet data compression ratio varies depending on the source data set. The size of CSV files created by AWS DMS is equal to the value specified in `CdcMinFileSize`. ## Turn on S3 Partitioning Partitioning in Amazon S3 structures your data by folders and subfolders that help efficiently query data. For example, if you receive sales record data daily from different regions, and you query data for a specific region and find stats for a few months, then you can partition data by \$1Product/source/region\$1, year, and month. The following example shows the path In Amazon S3 for our use case. ``` s3://////-- s3://s3-datalake - s3://s3-datalake/Oracledb - s3://s3-datalake/Oracledb/Sales - s3://s3-datalake/Oracledb/Sales/Products/ - s3://s3-datalake/Oracledb/Sales/Products/LOAD00000001.parquet - s3://s3-datalake/Oracledb/Sales/Customer - s3://s3-datalake/Oracledb/Sales/Customer/LOAD00000001.parquet - s3://s3-datalake/Oracledb/Sales/Sales/Products/20222-10-23/ - s3://s3-datalake/Oracledb/Sales/Sales/Products/2022-10-23/20221023-013830913.parquet - s3://s3-datalake/Oracledb/Sales/Sales/Products/2022-10-24/20221024-175902985.parquet ``` Partitioning provides performance benefits because data scanning will be limited to the amount of data in the specific partition based on the filter condition in your queries. For our sales data example, data scientists' queries might look as follows: ``` SELECT FROM WHERE = AND = ``` When performing data exploration, the data scientists can consume the incremental load using partitions. Partitioning the data helps read only latest data from the Amazon S3 bucket. In this case, you explore the latest data and use it for training the models to determine latest sales trends. The following code example shows how to turn on partitioning for ongoing changes. ``` bucketFolder=Oracledb DatePartitionedEnabled=true DatePartitionSequence=YYYYMMDD DatePartitionDelimiter=DASH ``` **Note** The date partition delimiter is chosen as `DASH` because it creates prefixes in the format `YYYY-MM-DD` rather than `YYYY/MM/DD` format. The advantage of using `DASH` is that it makes the 3 console view better with the files from each date (`YYYY-MM-DD`) being a single folder rather than having different folders for Year, month, and date. This will also let users query for a particular date in a simpler manner. ## Serialize Ongoing Replication Events A common challenge when using Amazon S3 as a target involves identifying the ongoing replication event sequence when multiple records are updated at the same time on the source database. AWS DMS provides two options to help serialize such events for Amazon S3. You can use the `TimeStampColumnName` endpoint setting or use transformation rules to include the Oracle System Change Number (SCN) column. The `TimeStampColumnName` setting adds another `STRING` column to the target file created by AWS DMS. During the ongoing replication, the column value represents the commit timestamp of the event in the Oracle database. For the full load phase, the column values represent the timestamp of data transfer to Amazon S3. The second option adds another column to include Oracle SCN. You can use this field when the source database might have transactions that are occurring within a microsecond or if the source database doesn’t offer microsecond level precision. Because the sales history table doesn’t have a primary key column, we add the `Timestamp` column according to the option to add **TimeStampColumnName** which will serve as a unique identifier during data exploration and model training phases of machine learning. We chose the option of timestamp over Oracle SCN because partitioning the data by timestamp will help data scientists for data exploration based on various criteria such as seasonal demand or product promotions. This setting is done at a task level. Make sure that you repeat it for each task separately that migrates data from the Oracle database endpoint. For more information about this option, see [Step 6: Create an AWS DMS Task](oracle-s3-data-lake-step-6.md). **To create a target endpoint** 1. Sign in to the AWS Management Console, and open the [AWS DMS console](https://console.aws.amazon.com/dms/v2). 1. Choose **Endpoints**, then choose **Create endpoint**. 1. On the **Create endpoint** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/oracle-s3-data-lake-step-5.html) 1. Expand the **Endpoint settings** section, choose **Wizard**, and then choose **Add new setting** to add the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/oracle-s3-data-lake-step-5.html) 1. Choose **Create endpoint**. # Step 6: Create an AWS DMS Task Before you create the replication task, it is important to understand the workload on the source database and usage pattern of the tables being replicated. This helps plan an effective migration approach and minimize any configuration or workload related issues. In this section, we first review the important considerations and then learn how to configure our walkthrough DMS task accordingly by applying table mappings and task settings. ## Considerations Before Creating an AWS DMS Task **Size and number of records** The volume of migrated records affects the full load completion time. It is difficult to predict the full load time upfront, but testing with a replica of a production instance should provide a baseline. Use this estimate to decide whether you should parallelize full load by using multiple tasks or by using the parallel load option. To speed up the full load of large tables such as sales table in our use case, we can increase the number of tables and partitions loaded in parallel up to 49. The default value for the number of tables and partitions loaded in parallel is eight. For more information about parallel load task settings, see [Full-load task settings](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html). The `MaxFullLoadSubTasks` parameter controls number of tables or partitions loaded in parallel during full load. **Transactions per second** While full load is affected by the number of records, the ongoing replication performance relies on the number of transactions on the source Oracle database. Performance issues during change data capture (CDC) generally stem from resource constraints on the source database, replication instance, target database, and network bandwidth or throughput. Knowing average and peak TPS on the source and recording CDC throughput and latency metrics helps baseline AWS DMS performance and identify an optimal task configuration. For more information, see [Replication task metrics](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Monitoring.html#CHAP_Monitoring.Metrics.Task). In this walkthrough, the source database is a data warehouse where transaction volume is not always high because the data is loaded on a periodic basis from the Online Transaction Processing (OLTP) layer. Also, we run a heterogeneous data migration using the AWS DMS parallel load to migrate large tables with improved performance. For more information, see [Using parallel load for selected tables, views, and collections](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.ParallelLoad). This approach requires a replication instance with higher compute capacity if the data volume is huge. We chose the compute intensive c5 class replication instance to speed up the process. If you are not sure about your data volumes or performance expectations from the migration task, start with general t3 class instances, and then migrate to c5 class instances for compute intensive tasks or r5 class instances for memory intensive tasks. You should monitor the task metrics continuously and choose the appropriate instance class that best suits your needs. **Unsupported data types** Identify data types used in tables and check that AWS DMS supports these data types. For more information, see [Source data types for Oracle](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.DataTypes). Validate that the target Amazon S3 has the corresponding data types. For more information, see [Target data types for S3 Parquet](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.DataTypes). After you run the initial load test, validate that AWS DMS converted data as you expected. You can also initiate a pre-migration assessment to identify any unsupported data types in the migration scope. For more information, see [Specifying individual assessments](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.AssessmentReport1.html#CHAP_Tasks.AssessmentReport1.Individual). **Source Database Workload** Running AWS DMS replication tasks for large tables can add to the workload on the source database especially during the full load phase when AWS DMS reads whole tables from source database without any filters to restrict rows. When you use filters in AWS DMS task table mapping, confirm that appropriate indexes exist on the source tables and indexes are actually being used during full load. Regularly monitor the source database to identify any workload related issues. For more information, see [Using table mapping to specify task settings](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.html). **Note** The previous list isn’t complete. For more information, see [Best practices](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html). Combining the considerations from the previous list, we start with a single task that migrates all eight tables in parallel. Based on the full load run time and resource utilization metrics on the source Oracle database instance and replication instance, we used AWS DMS parallel load option to further improve full load performance. ## Task Configuration In this walkthrough, we migrate the incremental changes to the fact tables to the data lake. To do so, we use the Full Load \$1 CDC option. For more information about the AWS DMS task creation steps and available configuration options, see [Creating a task](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html). We will first focus on the following settings. **Table mappings** Use selection rules to define the schemas and tables that the AWS DMS task migrates. For more information, see [Selection rules and actions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html). In this walkthrough, we are migrating all the tables (`%`) in the sales history `SH` schema. Another option is to include each table explicitly in the table mappings. However, that increases operational overhead by requiring repeated configurations. If we plan to add new tables to source database in future under the sales history schema, we should include all tables (`%`) in table mapping. **Note** Mapping rules are applied at the task level. Make sure that you add a mapping rule to each task that replicates data to your data lake. For our use case we need a single task. **LOB settings** AWS DMS handles large binary objects (LOBs) columns differently compared to other data types. For more information, see [Migrating large binary objects (LOBs)](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.LOBS). A detailed explanation of LOB handling by AWS DMS is out of scope for this walkthrough. However, remember that increasing the `LOB Max Size` increases the task’s memory utilization. Because of that, we recommended that you don’t set `LOB Max Size` to a large value. For more information about LOB settings, see [Task Configuration](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdssqlserver2s3datalake.steps.createtask.html#chap-rdssqlserver2s3datalake.steps.createtask.configuration). The source data warehouse schema in this walkthrough doesn’t include LOB data. When you migrate LOB columns, make sure that you perform analysis on these columns. Because AWS DMS doesn’t support Full LOB mode for Amazon S3 endpoints, we need to identify a suitable `LOB Max Size`. **Parallel load** Though, we used significantly large instance class in previous run, overall improvement wasn’t significant because the data volume is relatively large. The sales fact table includes 5 billion records. To further optimize the performance, we used parallel-load ranges option. For more information, see [Using parallel load for selected tables, views, and collections](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.ParallelLoad). The following code example shows the mapping rule that we used. As you can see, we defined 16 boundaries to cover data from 1998 to 2026 in 16 ranges. With this option, full load finished in about 6.5 hours. As a result, we reduced the time taken to complete full load to almost one third as compared to initial load. ``` { "rules": [ { "rule-type": "selection", "rule-id": "653647496", "rule-name": "653647496", "object-locator": { "schema-name": "SH", "table-name": "SALES" }, "rule-action": "include", "filters": [] }, { "rule-type": "table-settings", "rule-id": "653647497", "rule-name": "653647497", "object-locator": { "schema-name": "SH", "table-name": "SALES" }, "parallel-load": { "type": "ranges", "columns": [ "TIME_ID" ], "boundaries": [ [ "1998-01-01 00:00:00" ], [ "2000-01-01 00:00:00" ], [ "2002-01-01 00:00:00" ], [ "2004-01-01 00:00:00" ], [ "2006-01-01 00:00:00" ], [ "2008-01-01 00:00:00" ], [ "2010-01-01 00:00:00" ], [ "2012-01-01 00:00:00" ], [ "2014-01-01 00:00:00" ], [ "2016-01-01 00:00:00" ], [ "2018-01-01 00:00:00" ], [ "2020-01-01 00:00:00" ], [ "2022-01-01 00:00:00" ], [ "2024-01-01 00:00:00" ], [ "2026-01-01 00:00:00" ] ] } } . . . ] } ``` You can also use the `partitions-auto` option instead of ranges option because the `SALES` table is already partitioned. In our testing, we found that with the ranges option, full load finishes faster. So, we chose ranges option. **Other task settings** Choose **Enable CloudWatch Logs** to upload the AWS DMS task run log to Amazon CloudWatch. You can use these logs to troubleshoot issues because they include error and warning messages, start and end times of the run, configuration issues, and so on. To diagnose performance issues, you can use changes to the task logging setting, such as enabling debug or trace. **Note** CloudWatch log usage is charged at standard rates. For more information, see [Amazon CloudWatch pricing](https://aws.amazon.com/cloudwatch/pricing/). For **Target table preparation mode**, choose one of the following options: **Do nothing**, **Truncate**, and **Drop**. Use **Truncate** in data pipelines where the downstream systems rely on a fresh dump of clean data and do not rely on historical data. In this walkthrough, we choose **Do nothing** because we want to control the retention of files from previous runs. For **Maximum number of tables to load in parallel**, enter the number of parallel threads that AWS DMS initiates during the full load. You can increase this value to improve the full load performance and minimize the load time when you have numerous tables. Because we have several partitions that AWS DMS can load in parallel, we used the maximum value of 49. **Note** Increasing this parameter induces additional load on the source database, replication instance, and target database. **To create a database migration task** 1. Sign in to the AWS Management Console, and open the [AWS DMS console](https://console.aws.amazon.com/dms/v2). 1. Choose **Database migration tasks**, then choose **Create task**. 1. On the **Create database migration task** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/oracle-s3-data-lake-step-6.html) 1. Keep the default values for other parameters, and choose **Create task**. AWS DMS runs the task immediately. The **Database migration tasks** section displays the status of the migration task. # Step 7: Run the AWS DMS Task After you create your AWS Database Migration Service (AWS DMS) task, do a test run to identify the full load run time and ongoing replication performance. You can validate that initial configurations work as expected. You can do this by monitoring and documenting resource utilization on the source database, replication instance, and target database. These details make up the initial baseline and help determine if you need further optimization. After you started the task, the full load operation starts loading tables. You can see the table load completion status in the **Table Statistics** section and the corresponding target files in the Amazon S3 bucket. The following image shows table statistics with c5.12xlarge replication instance with parallel-load ranges option. The full load completed in 6.5 hours. This means that we achieved our goal of completing full load in less than 8 hours. ![\[The results of the migration from Oracle to an Amazon S3 data lake.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/oracle-s3-data-lake-migration-results.png) We also monitored the CloudWatch metrics such as compute, memory, network to identify the resource usage of AWS DMS instances. You have to identify the resource constraint and scale-up to the AWS DMS instance class that serves your workloads better. You could also scale-down the AWS DMS instance to a t3 or r5 instance class based on the transaction volume for your ongoing replication task. Because we turned on the parallel-load option, the I/O load on the replication instance is expected to increase. We described in [Step 1: Create an AWS DMS Replication Instance](oracle-s3-data-lake-step-1.md) that you should monitor the **Write IOPS** and **Read IOPS** metrics in CloudWatch to make sure that the total IOPS (write \$1 read IOPS) doesn’t exceed the total IOPS available for your replication instance. If it does, make sure that you allocate more storage to scale for better I/O performance. For more information, see [Monitoring replication tasks using Amazon CloudWatch](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Monitoring.html#CHAP_Monitoring.CloudWatch). We covered most prerequisites that help avoid errors related to configuration. If you observe issues when running the task, then see [Troubleshooting migration tasks in Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html), [Best practices for Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html), or reach out to AWS Support for further assistance. Optionally, you could choose to validate the successful completion of the data migration by querying the Amazon S3 data through Athena console. You can run count or aggregation queries on key metric columns and compare with the source database to validate the migration task. AWS DMS also provides data validation features to verify successful migration of the data. For more information, see [Data validation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Validating.html). After you completed the migration, validate that your data migrated successfully and delete the cloud resources that you created. # Step-by-step Amazon RDS for Oracle database to Amazon S3 data lake migration conclusion In this walkthrough, we covered all steps that you need to take to migrate a sales history data warehouse from Oracle to an Amazon S3 data lake. Our example company can use this data lake for machine learning and analysis use cases. We achieved the crucial business requirements by using AWS DMS. Try out these steps to migrate your data to an Amazon S3 data lake and explore how you can centralize your data with a low-cost solution. To learn more about AWS DMS service, see [Database Migration Service Documentation](https://docs.aws.amazon.com/dms/index.html).