Migrating From Oracle To Postgres - Carahsoft
Migrating from Oracle to PostgresFor more information on how your organization can migrate existingapplications to Postgres please contact EDB at sales@enterprisedb.com 2016 EnterpriseDB Corporation. All rights reserved.1
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 2016 EnterpriseDB Corporation. All rights reserved.2
Who is EDB? 2013 EDB All rights reserved 8.1.3
Productroad-mapEnterprise-classfeatures, tools &compatibilityServices& training24/7supportControlEnabling commercialadoption of reliabilityLow costAdvancedfeaturesNo vendorlock-in 2016 EnterpriseDB Corporation. All rights reserved.Fastdevelopmentcycles4Thousandsof developers
EDB is aMarketLeaderThe Gartner report, MagicQuadrant for OperationalDatabase ManagementSystems, by Donald Feinberg,Merv Adrian, Nick Heudecker,Adam Ronthal, and TerilynPalanca was publishedOctober 12, 2015. 2016 EnterpriseDB Corporation. All rights reserved.5
Why Migrate? 2013 EDB All rights reserved 8.1.6
Is keeping the“IT Lights” oncosting youbusiness andyour future? 2016 EnterpriseDB Corporation. All rights reserved.Change the“IT” Lights7
Finding Money in ITRelative Cost for IT Stack onalize for StabilityMiddle-ware Database Bloat Virtual MachineOperating System 2016 EnterpriseDB Corporation. All rights reserved.8High ValueInvest for Brand
Little Money ForNew Initiatives RevenueDBMSCustomerEngagement tCompetitiveDisadvantage 2016 EnterpriseDB Corporation. All rights reserved.9
You Have The Money! BMSCustomerEngagement t 2016 EnterpriseDB Corporation. All rights reserved.Transform10t
You’ve Been Here Before “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 toomuch in Solaris to move now.” “I built my career and this organization on Solaris!”
The Key to Unlocking the Money:EDB Postgres Advanced Server 2013 EDB All rights reserved 8.1.12
EDB ility 2016 EnterpriseDB Corporation. All rights reserved.13
Reducing the Risk of ChangeAdvanced Server reducesRaises risks TechnicalRe-TrainingIntegrationData CompatibilityCompatible Developerfeatures: PL/SQLReplicate to and fromOracleCode CompatibilityDBA features: WorkloadMgr & Diagnostics,XA Resource ManagerTools for Mission-criticalappsCompatible tools:SQL*Plus, SQL*LoaderForeign Data Wrappers forExternal DBs 2016 EnterpriseDB Corporation. All rights reserved.14
Database Compatibility for Oracle SQL extension support Features Decode, NVL, Substr, NVL2, Date/time functions Packages DDL syntax support Stored procedures FunctionsPL/SQL support – native language REF Cursors, Implicit and explicit cursors Triggers Looping, variable declarations, conditionalstatements Hints Hierarchical Queries Collections: Associative Arrays, Varrays, Nestedtables Synonyms – Public and Private Pragmas Sequences Named parameters Rownum User Defined Exceptions Users/Roles Explicit Transaction Control(within sp) Dynamic SQL Materialized Views PartitioningTools EDB*Plus – SQL*Plus look-a-like EDB*Loader – SQL*Loader equivalentOracle-like Data Dictionary ALL , DBA , USER views Wait Events System and session waits Statspack-like reporting 2016 EnterpriseDB Corporation. All rights reserved.PL/SQL supplied packages Data types 1514 DBMS6 UTLBlobs, Clobs, XMLTYPE, VARCHAR2,NUMBER, CHAR, Integer
Code Compatibility – PL/SQLAdvanced Server executes PL/SQL Stored Procedures, Triggers, Packagesand understands extensions like DUAL, Rownum, Decode, NVL(2) andSubstr(). Interpreter Debugger CompilerEmulationTranslationLayer on PL/pgSQL 2016 EnterpriseDB Corporation. All rights reserved.16
Code Compatibility – Pro*CecpgPlus (Pro*C support)my program.pgcecpgPlus precompilermy program.cC compilermy progam.oecpglibLinkerother libraries 2016 EnterpriseDB Corporation. All rights reserved.17my program
Code Compatibility - OCIOpen Client Library - OCL (OCI support)ApplicationProgramsSame AppsApplicationProgramsPublished APIOCI Compatible APIOracle’s OCI-LibOpen Client LibraryUPIlibpqWire ProtocolsWire ProtocolsOracle Call Interface stack EDB’s Open Client Library 2016 EnterpriseDB Corporation. All rights reserved.18
Compatibility for DevelopersQuery HintsPackages- 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.19- UTL ENCODE- UTL FILE- UTL HTTP- UTL MAIL- UTL SMTP- UTL URL- Custom
Compatibility for DBAs FunctionsDRITA(Dynamic RuntimeInstrumentation Tools Architecture)working with Wait events Statspack-like AWR Reports edbreport()stat db rpt()stat tables rpt()statio tables rpt()stat indexes rpt()statio indexes rpt()edb system waitsedb session waitsedb session wait history 2016 EnterpriseDB Corporation. All rights reserved. get snaps()sys rpt()sess rpt()sessid rpt()sesshist rpt()purgesnap()truncsnap() Catalog Views edb system waits edb session waits edb session wait history 50 Dictionary Views20
Compatibility for Developers and DBAs EDB*Loader EDB*Plus 2016 EnterpriseDB Corporation. All rights reserved.21
EDBMigration Toolkit 2016 EnterpriseDB Corporation. All rights reserved.22
How to get started 2013 EDB All rights reserved 8.1.23
Applications Inventory 5B company – Example 100 - 150 total applicationsinventory 60 - 90 Apps are CustomAppsCustom application footprint 2 dual socket, quad coreservers 16 cores 16 cores/app x 50apps 800 coresApps ofWeb & MobileInnovation20% of all AppsApplication of Differentiation60% of all AppsCustom AppsInitial Target: 50 AppsApplication of Record20% of all Apps 2016 EnterpriseDB Corporation. All rights reserved.24Custom AppsOracle, SAP,MS
Staged Application Migration Process50 AppsPool50%30%Days/App20 Days40 Days60 Days# of Apps25 Apps15 Apps10 AppsNet Savings 3.7 Million 2.0 Million 1.2 Million140%109%84%3yr (Net) ROI 2016 EnterpriseDB Corporation. All rights reserved.2520%
Iterative and Agile Motion ApproachPrioritize andPlan MigrationFind the Sweet SpotMigrateBuild the Team 2016 EnterpriseDB Corporation. All rights reserved.26Deploy
Steps to Migrate 2013 EDB All rights reserved 8.1.27
Steps to Migrate Prepare Environment Obtain and installnecessary software Establish connectivitybetween systems Port Application Change connection information Recompile /Link application Test Migrate Schema Use Migration Toolkit inschema only mode Resolve issues here beforegoing to next phase Migrate Data Integration Establish co-existence strategy Setup database links to otherdatabases Setup replication Use command line MTK Use copyViaDBLinkOra Uses OCI to connect toOracle Use parallel threads Export Oracle data to flatfiles, load usingEDB*Loader 2016 EnterpriseDB Corporation. All rights reserved.Verify application functionalityAddress issuesTest performanceTune Rollout System becomes production28
How EDB can help 2013 EDB All rights reserved 8.1.29
EDB can help you unlock money inyour IT stack to enable you toinnovate and compete 2016 EnterpriseDB Corporation. All rights reserved.30
Applications InventoryApps ofWeb & MobileInnovation20% of all AppsApplication of DifferentiationCustom Apps60% of all AppsInitial Target:50 Custom AppsISV AppsApplication of RecordCustom AppsOracle, SAP,MS Prop. Apps20% of all Apps 2016 EnterpriseDB Corporation. All rights reserved.31
Database Migration AssessmentOracle1. Analyze Oracle schema layout forsupported features in PPAS2. Review features used in Oracleclient applicationDifficult3. Supported features and recommended fixes4. Overall composite score and migration planwith workarounds and time estimates32 2016 EnterpriseDB Corporation. All rights reserved.Easy32
Global Training Services Online Live and interactive training delivered to remote participants by trained EDBpersonnel (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 EDBtrained personnel Marketed and sold by EDB or EDB Partner (licenses EDB materials) 1-5 days scheduled as needed 2016 EnterpriseDB Corporation. All rights reserved.33
EDB Postgres Deployment Services 2016 EnterpriseDB Corporation. All rights reserved.34
Summary 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 2016 EnterpriseDB Corporation. All rights reserved.35
The Time is NowTransform your IT Core to Fund Customer EngagementInnovation“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.36
2016 EnterpriseDB Corporation. All rights reserved.37
Migrating from Oracle to Postgres For more information on how your organization can migrate existing applications to Postgres please contact EDB at sales@enterprisedb.com
performance diagnostics (including an Oracle-style wait interface), migration tools, heterogeneous replication solutions (e.g. replicating from Oracle to Postgres), and other development / administration aids. The above are just a few reasons why migrating from MySQL to Postgres Plus makes sense. With an understanding of why such a move might be
Postgres is conforming to SQL-standard nested transactions vs savepoints Postgres does know schemes Postgres does support grant for colums, not just revoke on Postgres it is possible to restrict the connection limit per user Logins can be restricted to IP adresses in Postgres 1&1 Internet AG 2013 Database migration 6/26
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Migrating from Oracle Business Intelligence 12c or the Previous Release of Oracle Analytics Server 3-13 Creating the Export Bundle 3-13 Upload and Restore the Export Bundle in Oracle Analytics Server 3-14 Migrating from Oracle Business Intelligence 11g 3-14 Migrating using the Console 3-14. iv. Running a Pre-Upgrade Readiness Check2-15
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Migrating a SQL Server Database to Amazon Aurora MySQL (p. 93) Migrating an Amazon RDS for SQL Server Database to an Amazon S3 Data Lake (p. 110) Migrating an Oracle Database to PostgreSQL (p. 130) Migrating an Amazon RDS for Oracle Database to Amazon Redshift (p. 148) Migrating MySQL-Compatible Databases (p. 179)
here as postgres) using the display command psql -c "\du postgres" to establish a baseline for granted administrative privileges. Based on the output below, the postgres superuser can create roles, create databases, manage replication, and bypass row level security: psql -c "\du postgres" List of roles Role name Attributes
mastery of it : writing . In a recent survey, academic staff at the University identified the interrelated skills of essay-writing and reasoning as the two most important skills for success in higher education; when asked which skills students most often lacked, essay-writing was again at the top of their list. Needless to say, writing ability is also highly prized by employers. The purpose of .
