Incremental and Iterative Data Warehouse Migration Into Exadata

Case Study: Incremental And Iterative Data Warehouse Migration Into Exadata

Overview

A leading telecom had just acquired Exadata for their data warehouse but they had a very limited maintenance window to migrate all their data from the legacy system. Traditional migration methods could not accommodate the short window that the business permitted and online migration methods were not technically or economically feasible. Itzdata created a custom hybrid incremental/iterative migration approach and reduced the window to meet the customer’s expectations.

Situation

The leading telecommunications company in Guatemala needed to improve the performance of its data warehouse in order to consolidate several data marts and perform analytics over larger more detailed data sets. The customer did a proof of concept process with several appliance vendors and finally chose Oracle Exadata after Itzdata’s engineers optimized the database to run on Exadata achieving speeds 10X to 100X faster than the original system.

The source platform was IBM AIX running Oracle Database version 11.2.0.3 and the destination was Oracle Exadata X4-2 running Oracle Database version 11.2.0.4. The migration procedure had to be performed in the smallest possible maintenance window and be transparent to end users.

Issues

There is an endian format difference between source and destination operating systems making them incompatible. Traditional migration methods including export/import and transportable tablespaces were not fast enough for the customer’s business while other online migration methods like using Oracle GoldenGate required additional licensing costs and were not technically feasible without extensive modifications to the database.

Solution

Since the data warehouse database used the Partitioning and Compression for OLTP functionality available for Oracle Database, Itzdata’s specialists designed an incremental/iterative approach that consisted in transferring data during a longer period through export/import using dblinks based on dates. Afterwards, a methodology was defined to update sequences, procedure code and enable constraints. As a last step, a quality control and audit mechanism was implemented.

Implementation

The complete process followed a methodology:

  1. After Exadata hardware and software installation, Itzdata configured the infrastructure to adapt to the client’s needs and created the destination database.
  2. An initial load of metadata was performed and scripts were written and implemented to transfer the data in an incremental/iterative manner, checking data quality and logging its transfer in order to keep controls.
  3. Data was continually transferred into Exadata until there was only a single month of data difference compared to the production system.
  4. Platform migration was finally performed in a much smaller window than originally estimated because only the remaining data was updated. Sequences were updated and final metadata was loaded and quality controlled. Triggers and constraints were enabled and applications were redirected to the new destination. In the last step, procedures were executed and ETL loads started running normally as they did in the legacy system.

Results

  • The migration procedure was successful, executing in a smaller window than originally estimated.
  • All data was transferred and restructured in order to make its maintenance easier.
  • Queries on the new infrastructure ran much faster and the amount of data for analysis increased as the business started to have longer data retention periods.