129 ) Oracle to Redshift - Migration steps Overview

Oracle to Redshift - Migration steps Overview

• Step 1: Schema Conversion & Type Mapping — Translate Oracle DDL to Redshift-compatible formats using automated tools like AWS SCT while adjusting data types and encodings for UTF-8.

• Step 2: Staging & In-Flight Transport — Export data from Oracle via Data Pump or AWS DMS into compressed flat formats, utilizing S3 manifest files for secure staging.

• Step 3: ETL-Level Integrity Control — Enforce constraints and perform deduplication in the pipeline since Redshift does not actively enforce primary or foreign keys.

• Step 4: Post-Load Automated Reconciliation — Run a multi-tiered validation process comparing row counts, numeric aggregates, and cryptographic hash sums between both databases.

• Step 5: Temporal & Character Normalization — Standardize all timestamps to UTC and handle invalid UTF-8 sequences to prevent data drift or load failures.

1. Schema Conversion & Type Mapping

This diagram visualizes the translation process. Oracle's rigid, proprietary schema definition must be intelligently mapped into Redshift's distributed, columnar architecture. This is not a 1:1 copy; it is a transformation (Conversion) using tools like AWS SCT, resulting in new DDL optimized for MPP.

2. Staging & In-Flight Transport

This diagram focuses on the movement of the data payload itself. To maintain integrity while crossing the network, the data is extracted (via DMS or Data Pump) into neutral, compressed files (CSV/Parquet) and landed in Amazon S3. Crucially, an S3 Manifest File is generated, acting as a manifest of exactly which files constitute the complete dataset. The Redshift COPY command then uses this manifest to load the data precisely, ensuring no files are missed or duplicated.

3. ETL-Level Integrity Control

This diagram illustrates how we compensate for Redshift's lack of constraint enforcement. Since Redshift won't prevent duplicate or invalid data during the COPY command, we must enforce integrity after the data lands but before it reaches the production table. The data is loaded into a Staging Table first. We then run an ELT (Extract, Load, Transform) process, such as SQL scripts using window functions (ROW_NUMBER()), to identify and filter out duplicates and handle NULL keys before the final INSERT into the target table.

4. Post-Load Automated Reconciliation

This diagram shows the final integrity check. After the data is in the production Redshift schema, an automated audit process (the Reconciliation Framework) executes a multi-tiered comparison. It runs identical queries against both Oracle and Redshift, starting with simple Row Counts, moving to Numeric Sums/Averages (for financial data integrity), and finally, for high-stakes data, performing Bitwise Hash Comparisons (using MD5/SHA-256) of entire row sets to guarantee absolute fidelity.

5. Temporal & Character Normalization

This diagram highlights two specific, common causes of data corruption: Timezones and Character Encoding. Oracle databases often run in local timezones (e.g., EST) and may use legacy encodings (e.g., Windows-1252). Redshift must operate in UTC and requires UTF-8. The diagram shows the transformation layer during extraction (Step 2) that explicitly converts all timestamps to UTC and cleanses/replaces invalid characters (using ACCEPTINVCHARS logic) to ensure the data loaded into Redshift is standardized and error-free.