Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle to Postgres migration with ora2pg | Linux Open Alternative Day 2020 | Alicja Kucharczyk

Oracle to Postgres migration with ora2pg | Linux Open Alternative Day 2020 | Alicja Kucharczyk

Ora2Pg is the most popular tool for Oracle migrations to PostgreSQL. It is free, rich in features, stable and has a long history of development. But you would need some time to familiarize yourself with the instrument itself. I want to show you how (in the most painless way) you start your journey with migration

More Decks by Azure Database for PostgreSQL

Other Decks in Technology

Transcript

  1. Alicja Kucharczyk
    EMEA Global Blackbelt OSS Data Tech
    Specialist

    View full-size slide

  2. FEATURES
    • Automatic Oracle database discovery
    • Automatic creation of migration projects
    • Oracle database migration cost assessment
    • Automatic database schema export
    • Full and automatic data export
    • Automatic conversion of PL/SQL to PLPGSQL
    • Oracle Spatial to PostGis export

    View full-size slide

  3. INSTALLATION
    • Hard Way: http://ora2pg.darold.net/documentation.html#INSTALLATION
    • Easy way: Docker! https://github.com/AwdotiaRomanowna/ora2pgWorkshop

    View full-size slide

  4. Docker
    yum update -y
    yum install docker -y
    systemctl start docker
    systemctl status docker
    docker pull georgmoser/ora2pg-docker
    mkdir /data
    docker run -it --privileged -v /data:/data georgmoser/ora2pg-docker /bin/bash
    ora2pg --version

    View full-size slide

  5. Connect to Oracle

    View full-size slide

  6. MIGRATION PROJECT
    ora2pg --init_project my_db_mig --project_base /full/path/to/project
    /full/path/to/project/my_db_mig/
    ├── config/
    │ └── ora2pg.conf
    ├── data/
    ├── export_schema.sh
    ├── reports/
    ├── schema/
    │ ├── dblinks/ functions/ grants/ mviews/ packages/
    │ ├── partitions/ procedures/ sequences/ synonyms/
    │ └── tables/ tablespaces/ directories/ triggers/ types/ views/
    └── sources/
    ├── functions/ mviews/ packages/ partitions/
    └── procedures/ triggers/ types/ views/

    View full-size slide

  7. MIGRATION ASSESSMENT
    ora2pg -c /etc/ora2pg.conf -t SHOW_REPORT –estimate_cost --dump_as_html > report.html

    View full-size slide

  8. ASSESSMENT
    NEVER TRUST THE ASSESSMENT!
    Always take a look at the code!

    View full-size slide

  9. Challenges
    You should be fine with:
    tables, constraints, indexes, sequences, triggers, views, tablespaces, grants, types, partitions,
    synonyms, database links (on cloud require modernization), materialized views (refresh) …
    Only procedures, functions and packages could be a problem

    View full-size slide

  10. Tables
    Usually pretty simple
    What to keep in mind:
    q PostgreSQL has more data types
    q NUMBER(1) or CHAR(1) to BOOLEAN?
    q VARCHAR2(2000 CHAR) to TEXT?
    q Inet, jsonb, time etc.
    Partitions aren't an issue any more

    View full-size slide

  11. Partitioning
    CREATE EXTENSION pg_partman WITH SCHEMA partman
    SELECT partman.create_parent('github.events', 'created_at', 'native', 'hourly');
    SELECT run_maintenance(p_analyze := false);
    SELECT cron.schedule('@hourly', $$SELECT partman.run_maintenance(p_analyze := false)$$);
    UPDATE partman.part_config SET retention_keep_table = false, retention = '1 month'
    WHERE parent_table = 'github.events';

    View full-size slide

  12. Data
    q Data is usually simple
    q The devil is in the details

    View full-size slide

  13. ./export_schema.sh
    ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
    ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf
    ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf
    ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf
    ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf
    ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf
    ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf
    ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf
    ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf
    ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf
    ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf
    ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf
    ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf

    View full-size slide

  14. AZURE MIGRATION RESOURCES
    Azure Database Migration Guide
    DMS homepage
    How to migrate using dump and store
    How to migrate using export and import
    How to migrate online

    View full-size slide