Business Summary of the Solution:
The U.S. Navy manages GIS information for environmental surveys. Data residing in several regional database were to be consolidated into a central repository. Accomplishing this required securely migrating 10’s of millions of records. Migration efforts are fraught with data consistency challenges and this was no exception. To meet those challenges, thorough analysis of existing data sets was needed and repeated testing of the migration process to ensure data quality. Keen understanding of Oracle performance optimization was necessary to perform the migration in the allocated window.
Technical Summary:
The solution implemented was comprised of several Oracle unload and load routines, mostly written as shell scripts relying on Oracle’s Data Pump for exporting and importing and Oracle stored procedures to transform data prior to load. The custom transformation stored procedures used metadata, populated during the business analysis phase, to discover and report on any conflicting keys to ensure data consistency during the live run.
Tech Stack:
Database Technologies
- Oracle Databases – 10g running on Windows and Solaris
- Oracle Data Pump
- Oracle Store Procedures
- PL/SQL
Deployment Platforms
- Solaris
- Windows 2003 Server