Green Migration from Oracle

Greenplum Migration Approach

•

 

Strong Experiences on Oracle Migration

•

 

Automate all tasks

–

 

DDL Migration

–

 

Data Migration

–

 

PL-SQL and SQL Scripts Migration

–

 

Data Quality Tests

•

 

ETL and BI Migration

•

 

Focus on Performance

–

 

Scope is to outperform existing solution

Greenplum Competitive Advantages

Versus Oracle

–

 

MPP Architecture : Scale as You Need

–

 

Performance on Load and Queries Scenarios

–

 

Pure Column Store Solution

–

 

Less Tuning and Better Performance

–

 

Choice and Control : Appliance or Software

Model

–

 

Deep Analytics

–

 

Integration with Hadoop

–

 

Full Open Solution

Greenplum Database will solve the

following issues :

–

 

Time to Query the DWH / Data Marts

•  Because of issue to load Data, Oracle DBAs has to reduce

optimization for querying and making queries longer

•  Large Queries or Big Analytics Queries as SAS Users have

dramatic impact on the global system

–

 

Greenplum Answer

•  By Design Our solution allows Data to be loaded and Query in

parallel

•  Greenplum Advanced Workload Management allows to give in

real-time priorities to specific usages or users

•  Our Parallel Engine leverage SAS Users and can speed up SAS

Greenplum Database will solve the

following issues :

–

 

Data Volume Explosion and Time to Tune the System

•  Fine Tuning required strong expertise and are time consuming

•  No way to scale the system easily

–

 

Greenplum Answer

•  By Design our solution requires less tuning and DBA’s effort,

using the full parallelism of the system and more than 10 years of R&D Investment for large DWH Systems

•  You can expand online Greenplum System based on the way

your business is growing, moving from 4 servers to 8, or 8 to 16 depending on the data volume growth or numbers of users with no downtime

Migration from Oracle to GPDB

– Methodologies

• Scope Specific Model in which Performance is the issue

•  Split Migration Per Step with Monthly Delivery including Database Migration, ETL

and Reporting Solutions – Tools

• Database and Schema Migration, including all PL-SQL Logic

! PL-PGSQL language is very close and we had many Oracle Functions Compatibility in

4.1 and 4.2 Release

! Data Migration through Wisdom Force, including possibility for CDC from Oracle

• ETL Migration

! Informatica - Will depend on the custom code

! EMC / Greenplum is providing Fast Loader Connector for Informatica

• BO Objects

! Universe Migration

• Cubes Olap

! OLEDB Provider for Greenplum

Agenda

"  Methodologies

"  Project Concept

"  Tools to Automate Migration

"  Project Scope

"  Project Team

Methodologies - Phase 1 : Initial

Scoping

•  Initial Meeting to discuss Global Architecture and scope of the migration.

•  Send a list of questions per domain (Database, SQL Logic inside the database,

ETL/EAI, Front-End, Analytics, Infrastructure) to refine the need.

•  Conduct Workshop (1-2h Meeting) per domain at Customer’s site to validate

understanding of each module with Local Partner. Use SQL Scripts done by EMC Services to get accurate information regarding database objects.

Methodologies Phase 2 : Assesment

•  Get sample code Procedures/Functions/Views and rank it per :

–  Low Complexity

–  Medium

–  Complex

–  Very Complex

•  Provide High-Level Scope of the Migration.

•  Conduct Assessment with Local Partner (2-5 Days)

–  Get DDL of Database Model and validate automation of the code

–  Analyze ETL/EAI Process

–  Analyze Front-End Tools

–  Analyze Dependencies with third parties solutions

Methodologies Phase 3 : Scoping and

SOW

•  Provide Deep-Level Scope of the Migration

•  Define Statement of Work for the Migration Process with local Partner, including

–  High Level Project Plan

–  Define Responsibilities and Tasks

–  Engagement Goals

–  Define Target Architecture and SLA

•  Iterate and refine with Customer and local partner on the SOW proposition

Project Concept

Oracle   Migration Database Application Data  Migration

Module Life Cycle

•  Migration Oracle Models to Greenplum

•  Schemas Migration Including Primary Keys, Constraints, Distribution Keys

•  Migrate PL-SQL and SQL Queries to Greenplum syntax •  Tests SQL scripts and Validate Conversion

•  Performance tuning all relevant SQL Scripts

•  Migrate all relevant applications to use GP instead of Oracle •  BI Applications

•  ETL Applications

•  Migrate all Data from Existing Models from Oracle to Greenplum •  Validate Data Migration with Data Quality Tools and Processes

•  Automate Transfer through Greenplum Tools or third parties solutions

DDL and SQL Scripts Migration

•

 

Use SQLWays from Ispirer

–  SQLWays Commander – GUI tool for scripts conversion and DDL

•  Can automate conversion of Multiple scripts

–  SQLWays Studio – GUI tool for scripts conversion and DDL

•  GUI Interface to convert a specific script

•

 

Greenplum will purchase the license for the project

–  License included specific customizations for your project to make

automation of the code mainly perfect based on your code.

–  We will add 50 Customizations.

Data Migration

•

 

Use Informatica Wisdom Force Solution to Migrate Data

•

 

Can automate Entire Database Archive down from Oracle to

Greenplum

•

 

Fast Load Data to Greenplum using Parallel GPFDIST/

Project Scope

Database schema Migration • Migrate Models • Adjust Datatypes to Greenplum

• Adjust primary Key & distribution key • Physical design Tuning • Automate Migration of the scripts using SQLWays PL-SQL and SQL Scripts Migration • Migrate all PL-SQL Scripts • Automate Migration of the scripts using SQLWays • Validate Conversion with Comparison from Original • Perf & Tuning to

Overachieve Performance compare to Oracle Data Migration • Automate Migration of Data using Informatica WF or GP Tools • Validate Data Migration using Data Quality Tools • Replicate Data

needed by applications from Oracle (Daily Base or less) if needed Applications Migrations • ETL Migrations • BI Front-End Applications • Administration Scripts Migration Testing

• Unit and Global testing of the converted model • Tracking, prioritizing

and resolving all identified bugs and issues. • Performance Testing - Load Tests Deployment • Deployment of Converted Modules to Greenplum • Setup Administration Tasks including Backup / Restore, Statistics Update, .. • User / Logins Permission Setting • Post Production

support for a pre-defined period

Project Time Line (Detailed)– Second Month

Informatica INFORMATICA    Connector  in   Production

DWH  Workflows  

migration DWH  Workflows  migration DWH  Workflows  migration

 Data  Validation Quality  Gates  _Installation  DWH  migration  _validation ODS  migration  _{validation Validation  Issues}Fix  Data  

Reporting BO  –Testing QlickView  Installation Dashboards,    Upload  BO  –migrate   to  production

BO  –  Production   Testing,  dashboards  

Xcelsius    

Infrastructure QA  Scripts  Design  _architecture Automate  migration  _{with  SQLWays}   QA  scripts  Conversion _ConversionQA  Scripts  

Week 21-08 Week 28-08 Week 04-09

Project Time-Line

T1   17/07  –  13/08   14/08-­‐  10/09  T2   11/09-­‐  08/10  T3   09/10-­‐05/11  T4   6/11-­‐10/12  T5   Infrastructure   Setup   Design   Architecture   DWH  Migration   –  Schema  &   Data     Data  Validation   Business  Objects   Informatica  

QA  Scripts  -­‐  Conversion   QA  Scripts  -­‐  Testing  

Global  Tests,   Perfrormance    Tests     Deployment     Post  Deployment   DWH  Migration   –  Schema  &   Data    

Performance improvements - Real Story

GP  

Oracle  

Daily  Runs  

1  hour  10  min

 

3.5  hours  -­‐  8  hours  

Rollback    

40  minutes  

15  hours    

Production statistics – Daily run

#  GP:

#  Oracle:

GP  

Oracle  

29/2  

1  hour  20  min       4  hours  43  min  

28/2    

(2  weeks  period  after   rollback)

 

Production statistics - Rollback

#  GP rollback:

40 min