Alicja Kucharczyk EMEA Global Blackbelt OSS Data Tech Specialist

No content

No content

No content

No content

No content

No content

No content

No content

No content

No content

No content

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

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

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

Connect to Oracle

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/

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

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

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

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

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';

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

./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

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

Thank you!