migrating from oracle to postgres - carahsoft · migrating from oracle to postgres. for more...
TRANSCRIPT
© 2016 EnterpriseDB Corporation. All rights reserved. 1
Migrating from Oracle to Postgres
For more information on how your organization can migrate existing applications to Postgres please contact EDB at [email protected]
© 2016 EnterpriseDB Corporation. All rights reserved. 2
Agenda
• Who is EDB?• Why migrate? • Finding the money to migrate• How to get started• Steps to migrate• How EDB can help• Summary• Q&A
© 2013 EDB All rights reserved 8.1. 3
Who is EDB?
© 2016 EnterpriseDB Corporation. All rights reserved. 4
POSTGRESinnovation
ENTERPRISEreliability
24/7 support
Services & training
Enterprise-class features, tools &
compatibility
Certifications
Productroad-map
Control
Thousands of developers
Fast development
cycles
Low cost
No vendor lock-in
Advanced features
Enabling commercial adoption of Postgres
© 2016 EnterpriseDB Corporation. All rights reserved. 5
EDB is a Market Leader
The Gartner report, Magic Quadrant for Operational Database Management Systems, by Donald Feinberg, Merv Adrian, Nick Heudecker, Adam Ronthal, and Terilyn Palanca was published October 12, 2015.
© 2013 EDB All rights reserved 8.1. 6
Why Migrate?
© 2016 EnterpriseDB Corporation. All rights reserved. 7
Is keeping the “IT Lights” on costing you business and your future?
Change the “IT” Lights
© 2016 EnterpriseDB Corporation. All rights reserved. 8
Finding Money in ITRelative Cost for IT Stack Components
Analytics
High ValueInvest for Brand
Mobile
Applications
Middle-ware
$ Database Bloat $
Virtual Machine
Operating System
Commodity Rationalize for Stability
© 2016 EnterpriseDB Corporation. All rights reserved. 9
Little Money ForNew Initiatives
Competitive Disadvantage
$
t
$Revenue
DBMS
CustomerEngagement
© 2016 EnterpriseDB Corporation. All rights reserved. 10
t
TransformDBMS
Revenue
DBMS
Customer Engagement
Revenue
DBMS
Customer Engagement
$
t$ $
$
Transform
You Have The Money!
• “Nothing beats the reliability of Solaris.”
• “We will lose customers if we move off Solaris.”
• “I could not sleep at night if we moved off Solaris.”
• “We cannot run our business on Linux. We have invested too much in Solaris to move now.”
• “I built my career and this organization on Solaris!”
You’ve Been Here Before
© 2013 EDB All rights reserved 8.1. 12
The Key to Unlocking the Money:
EDB Postgres Advanced Server
© 2016 EnterpriseDB Corporation. All rights reserved. 13
Security
Tools
Performance
Compatibility
EDB POSTGRES
ENTERPRISE
© 2016 EnterpriseDB Corporation. All rights reserved. 14
Raises…
Reducing the Risk of Change
Technical
Data Compatibility
Code Compatibility
Tools for Mission-critical apps
Re-Training
Compatible Developer features: PL/SQL
DBA features: Workload Mgr & Diagnostics,
Compatible tools: SQL*Plus, SQL*Loader
Integration
Replicate to and from Oracle
XA Resource Manager
Foreign Data Wrappers for External DBs
Advanced Server reduces risks…
© 2016 EnterpriseDB Corporation. All rights reserved. 15
Database Compatibility for Oracle
• SQL extension support• Decode, NVL, Substr, NVL2, Date/time functions• DDL syntax support
• PL/SQL support – native language • REF Cursors, Implicit and explicit cursors• Looping, variable declarations, conditional
statements• Collections: Associative Arrays, Varrays, Nested
tables
• Pragmas• Named parameters
• User Defined Exceptions• Explicit Transaction Control(within sp)
• Tools• EDB*Plus – SQL*Plus look-a-like• EDB*Loader – SQL*Loader equivalent
• Oracle-like Data Dictionary• ALL_, DBA_, USER_ views
• Wait Events• System and session waits
• Statspack-like reporting
• Features• Packages
• Stored procedures
• Functions• Triggers
• Hints• Hierarchical Queries
• Synonyms – Public and Private
• Sequences• Rownum
• Users/Roles• Dynamic SQL
• Materialized Views
• Partitioning
• PL/SQL supplied packages• 14 DBMS• 6 UTL
• Data types• Blobs, Clobs, XMLTYPE, VARCHAR2,
NUMBER, CHAR, Integer
© 2016 EnterpriseDB Corporation. All rights reserved. 16
Advanced Server executes PL/SQL Stored Procedures, Triggers, Packages and understands extensions like DUAL, Rownum, Decode, NVL(2) and Substr().
Code Compatibility – PL/SQL
Emulation
Translation
Layer on PL/pgSQL
Compiler ✔Interpreter ✔Debugger ✔
© 2016 EnterpriseDB Corporation. All rights reserved. 17
my_program.pgc
ecpgPlus pre-compiler
C compiler
Linker
ecpgPlus (Pro*C support)
Code Compatibility – Pro*C
ecpglib
other libraries
my_progam.o
my_program.c
my_program
© 2016 EnterpriseDB Corporation. All rights reserved. 18
Open Client Library - OCL (OCI support)
Code Compatibility - OCI
Application Programs
Oracle’s OCI-Lib
Published API
UPI
Wire Protocols
Oracle® Call Interface stack
Application Programs
Open Client Library
OCI Compatible API
libpq
Wire Protocols
EDB’s Open Client Library
Same Apps
© 2016 EnterpriseDB Corporation. All rights reserved. 19
Compatibility for Developers
Query Hints Packages
- UTL_ENCODE- UTL_FILE- UTL_HTTP- UTL_MAIL- UTL_SMTP- UTL_URL
- Custom
- DBMS_ALERT- DBMS_CRYPTO- DBMS_JOB- DBMS_LOB- DBMS_LOCK- DBMS_OUTPUT- DBMS_PIPE- DBMS_PROFILER- DBMS_SCHEDULER- DBMS_SQL- DBMS_RANDOM- DBMS_RLS- DBMS_UTILITY
- Default Optimizer Hints- Access Method Hints- Join Order Hints- Join Relation Hints- Global Hints- Append Hints
© 2016 EnterpriseDB Corporation. All rights reserved. 20
DRITA (Dynamic Runtime Instrumentation Tools Architecture)
working with Wait events …
Compatibility for DBAs• Functions− get_snaps()− sys_rpt()− sess_rpt()− sessid_rpt()− sesshist_rpt()− purgesnap()− truncsnap()
• Statspack-like AWR Reports− edbreport()− stat_db_rpt()− stat_tables_rpt()− statio_tables_rpt()− stat_indexes_rpt()− statio_indexes_rpt()− edb$system_waits− edb$session_waits− edb$session_wait_history
• Catalog Views− edb$system_waits− edb$session_waits− edb$session_wait_history
− ~50 Dictionary Views
© 2016 EnterpriseDB Corporation. All rights reserved. 21
Compatibility for Developers and DBAs
• EDB*Plus • EDB*Loader
© 2016 EnterpriseDB Corporation. All rights reserved. 22
EDBMigration Toolkit
© 2013 EDB All rights reserved 8.1. 23
How to get started
© 2016 EnterpriseDB Corporation. All rights reserved. 24
Applications Inventory$5B company – Example
• 100 - 150 total applications inventory
• 60 - 90 Apps are Custom Apps
Custom application footprint• 2 dual socket, quad core
servers • 16 cores• 16 cores/app x ~ 50apps
= 800 cores
Apps ofInnovation
20% of all Apps
Application of Differentiation60% of all Apps
Application of Record
20% of all Apps
Initial Target: 50 Apps
Web & Mobile
Custom Apps
Custom Apps
Oracle, SAP, MS
© 2016 EnterpriseDB Corporation. All rights reserved. 25
Staged Application Migration Process
50 Apps
Pool
Days/App
# of Apps
Net Savings
3yr (Net) ROI
50% 30% 20%
20 Days 40 Days 60 Days
25 Apps 15 Apps 10 Apps
$3.7 Million $2.0 Million $1.2 Million
140% 109% 84%
© 2016 EnterpriseDB Corporation. All rights reserved. 26
Iterative and Agile Motion Approach
Find the Sweet Spot
Prioritize and Plan Migration
Build the Team
Migrate Deploy
© 2013 EDB All rights reserved 8.1. 27
Steps to Migrate
© 2016 EnterpriseDB Corporation. All rights reserved. 28
Steps to Migrate• Prepare Environment
− Obtain and install necessary software
− Establish connectivity between systems
• Migrate Schema− Use Migration Toolkit in
schema only mode− Resolve issues here before
going to next phase
• Migrate Data− Use command line MTK
− Use copyViaDBLinkOra− Uses OCI to connect to
Oracle
− Use parallel threads− Export Oracle data to flat
files, load using EDB*Loader
• Port Application− Change connection information− Recompile /Link application
• Test− Verify application functionality− Address issues− Test performance − Tune
• Integration− Establish co-existence strategy− Setup database links to other
databases− Setup replication
• Rollout− System becomes production
© 2013 EDB All rights reserved 8.1. 29
How EDB can help
© 2016 EnterpriseDB Corporation. All rights reserved. 30
EDB can help you unlock money in your IT stack to enable you to innovate and compete
© 2016 EnterpriseDB Corporation. All rights reserved. 31
Applications Inventory
Apps ofInnovation
20% of all Apps
Application of Differentiation60% of all Apps
Application of Record
20% of all Apps
Initial Target: 50 Custom Apps
Web & Mobile
Custom Apps
Custom Apps
Oracle, SAP, MS Prop. Apps
ISV Apps
© 2016 EnterpriseDB Corporation. All rights reserved. 32
Database Migration Assessment
32
Oracle
1. Analyze Oracle schema layout for supported features in PPAS
2. Review features used in Oracle client application
3. Supported features and recommended fixes
Difficult Easy
4. Overall composite score and migration plan with workarounds and time estimates
© 2016 EnterpriseDB Corporation. All rights reserved. 33
• Online− Live and interactive training delivered to remote participants by trained EDB
personnel (or a contractor)− 1-5 days− Scheduled in advance; schedules are published at EnterpriseDB.com
• On-Demand− Pre-recorded training sessions− 4-14 hours− Available at EnterpriseDB.com
• Onsite− Delivered at the customer site by EDB
trained personnel − Marketed and sold by EDB or EDB Partner (licenses EDB materials)− 1-5 days scheduled as needed
Global Training Services
© 2016 EnterpriseDB Corporation. All rights reserved. 34
EDB Postgres Deployment Services
© 2016 EnterpriseDB Corporation. All rights reserved. 35
• DBMS Migrations to Open Source are the Next Wave − Migrations are no longer impossible− Others have done – you can do it too!
• Compatibility tools are key− Faster− Cheaper− Less risk
• EnterpriseDB provides− Oracle compatibility− Automated tools to migrate from Oracle
(data, schema, stored procedures)− Automated tools to migrate from MySQL, Sybase, SQL Server
(data, schema)− Migration Expertise
Summary
© 2016 EnterpriseDB Corporation. All rights reserved. 36
The Time is NowTransform your IT Core to Fund Customer Engagement Innovation
“With EDB Postgres Advanced Server, we found a solution that not only provided exceptional performance,but made it easy to migrate existing data ... we were better able to control software costs.”Steve Akers, CIO, Genscape
“Open-source relational DBMSs (RDBMSs) have matured and today can be considered byinformation leaders, DBAs and application development management as a standardinfrastructure choice for a large majority of new enterprise applications.”Gartner Report , The State of Relational Open Source DBMSes, 2015
© 2016 EnterpriseDB Corporation. All rights reserved. 37