oracle® database migration to edb postgres - daseq · pdf fileoracle® database...
TRANSCRIPT
© 2016 EDB. All rights reserved. 1
Oracle® Database Migration to EDB Postgres Sandra Wiecki, Director Product Marketing
© 2016 EDB. All rights reserved. 2
EDB Postgres Platform
© 2016 EDB. All rights reserved. 3
Database Compatibility for Oracle® • PL/SQL • OCI support
• Stored procedures • Function packages • SQL Syntax
• System catalog
EDB Postgres Advanced Server
! Easier migrations ! Preserve your investment
© 2016 EDB. All rights reserved. 4
Collect and analyze both database server and application info.
PRE-ASSESSMENT DESIGN, BUILD & RUN MIGRATION ASSESSMENT
Access Schema
Analyze using tools
Identify dependencies, incompatibilities, and workarounds
Further analysis where required (developer access may be required)
Document migration plan
Service-based plan implementation with a central consultant overseeing delivery
Technical training
Technical Account Manger
Periodic Architectural Health Checks (AHC)
Database Migration Project Phases
Presales Professional Services engagement
© 2016 EDB. All rights reserved. 5
Collect and analyze both database server and application info.
PRE-ASSESSMENT DESIGN, BUILD & RUN MIGRATION ASSESSMENT
Access Schema
Analyze using tools
Identify dependencies, incompatibilities, and workarounds
Further analysis where required (developer access may be required)
Document migration plan
Service-based plan implementation with a central consultant overseeing delivery
Technical training
Technical Account Manger
Periodic Architectural Health Checks (AHC)
Database Migration Project Phases
Presales Professional Services engagement
© 2016 EDB. All rights reserved. 6
Collect and analyze both database server and application info.
PRE-ASSESSMENT DESIGN, BUILD & RUN MIGRATION ASSESSMENT
Access Schema
Analyze using tools
Identify dependencies, incompatibilities, and workarounds
Further analysis where required (developer access may be required)
Document migration plan
Service-based plan implementation with a central consultant overseeing delivery
Technical training
Technical Account Manger
Periodic Architectural Health Checks (AHC)
Database Migration Project Phases
Presales Professional Services engagement
© 2016 EDB. All rights reserved. 7
Collect and analyze both database server and application info.
PRE-ASSESSMENT DESIGN, BUILD & RUN MIGRATION ASSESSMENT
Access Schema
Analyze using tools
Identify dependencies, incompatibilities, and workarounds
Further analysis where required (developer access may be required)
Document migration plan
Service-based plan implementation with a central consultant overseeing delivery
Technical training
Technical Account Manger
Periodic Architectural Health Checks (AHC)
Database Migration Project Phases
Presales Professional Services engagement
© 2016 EDB. All rights reserved. 8
Customer Input
Technical questionnaire for each candidate
Availability for follow-up questions
Analysis of responses
Follow-up questions
Identification of Oracle features, which require additional investigation
Initial triage of databases
Identification of duplicate schemas and schema dependencies
Estimation of Migration Assessment work
Pre-Assessment Phase
Key Staff:
Presales & Consultants
EDB Input Output
Key Staff:
DBAs
© 2016 EDB. All rights reserved. 9
Database schema: • data-less dump or • access via SSH / VPN Application team access
Detailed analysis of schema (approx. 3-5 days per schema)
Follow-up questions
Migration assessment documentation: • issues • solutions • high-level deployment
plan
Migration Assessment Phase
Customer Input EDB Input Output
Key Staff: DBAs, application team
Key Staff: Consultants
© 2016 EDB. All rights reserved. 10
Design, Build, Run
Senior Consultant focused on design
Consultants implementing test & production systems
Technical Account Manager
Technical Training
Architectural Health Checks
Design Transition Operation
© 2016 EDB. All rights reserved. 11
Design, Build, Run Transition
Migration Load Map Create Schema Migration Data Move Stored Procedures View Migration Trigger Migration Document Code Issues
Testing Unit Testing Execution Sanity Testing System Testing Integration & Functional Performance Tuning Acceptance Testing QA Acceptance
Packaging Generate Maint. Scripts Test Maint. Scripts Generate Install Scripts Test Install Scripts Documentation
Delivery Execution Post Migration Support
Design Transition Operation
© 2016 EDB. All rights reserved. 12
• Designated Postgres technical resource • Working closely with your IT organization • Take advantage of EDB features and functionality
• Support escalations and advocacy • Roadmap development and visibility • Quarterly visits
• Semi-dedicated resource
Technical Account Manager (TAM)
© 2016 EDB. All rights reserved. 13
• Comprehensive certification: − EDB Postgres Associate Certification (5 days) − EDB Postgres Professional Certification (+3 days)
• Available: − In person − Online − On-Demand
Technical Training
© 2016 EDB. All rights reserved. 14
• Identifies: − Short-term opportunities − Strategic & Tactical opportunities
• Checking: − Operating system − Hardware configuration − Database settings − Backup strategies − General database practices − Upgrade requirements − High Availability options
• Enables better strategic decision-making going forward
Architectural Health Check
© 2016 EDB. All rights reserved. 15
Database Compatibility for Oracle includes: • SQL extension support
− Decode, NVL, Substr, NVL2 − Date/time functions: add_months, extract,
next_day
• PL/SQL support − REF Cursors, Implicit and explicit cursors − Looping, variable declarations, conditional
statements − Collections: Associative Arrays, Varrays,
Nested tables − Bulk binding − Named parameters − User Defined Exceptions − Explicit Transaction Control
− within a stored procedure
• Tools − EDB*Plus – SQL*Plus look-a-like − EDB*Loader – SQL*Loader equivalent − EDB*Wrap – similar to the PL/SQL wrapper
• Features − Packages − Stored procedures − Functions − Triggers − Hints − Database Links − Hierarchical Queries − Synonyms – Public and Private − Sequences − Rownum − Object types
− Create type … as object − Create type … as table − Create type …as varray − Constructor and collection methods
− Users/Roles − Dynamic SQL − Profiles for Passwords
© 2016 EDB. All rights reserved. 16
Database Compatibility (cont.) • Data Types
− Integer, number, char, double precision, float, varchar2, blob, clob, xmltype, rowid
• Oracle-like Data Dictionary − ALL_, DBA_, USER_ views − Most commonly accessed views
• Diagnostics - DRITA − System and session waits
− Not exposed in PostgreSQL − Part of Advanced Server
− Statspack-like reporting
• Support for Functions: − REGEXP_INSTR − REGEXP_COUNT − REGEXP_SUBSTR
• Custom constructor methods for Objects
• Oracle compatible Materialized Views
© 2013 EnterpriseDB. All rights reserved.
• Package Support for: − DBMS_ALERT − DBMS_CRYPTO − DBMS_JOB − DBMS_LOB − DBMS_LOCK.sleep − DBMS_MVIEW − DBMS_OUTPUT − DBMS_PIPE − DBMS_PROFILER − DBMS_RANDOM − DBMS_RLS − DBMS_SCHEDULER − DBMS_SESSION − DBMS_SQL − DBMS_UTILITY
• Package Support for: − UTL_HTTP for web server communications − UTL_URL − UTL_TCP − UTL_FILE − UTL_MAIL − UTL_RAW − UTL_SMTP − UTL_ENCODE
© 2016 EDB. All rights reserved. 17
Database Comparison: Oracle and EDB Postgres http://www.enterprisedb.com/database-comparison-oracle-and-edb-postgres
Blog: Comparing EDB Postgres and Oracle http://www.enterprisedb.com/postgres-plus-edb-blog/gary-carter/comparing-edb-postgres-and-oracle
EDB Postgres Advanced Server http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server
Resources