Ora2pg

Documentation

https://ora2pg.darold.net/documentation.html

Pre-Requisites

Install Full Oracle Client or Instant Client Instant Client must include the below 4 zip files wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-basic-linux.x64-23.7.0.25.01.zip wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-sqlplus-linux.x64-23.7.0.25.01.zip wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-sdk-linux.x64-23.7.0.25.01.zip wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-jdbc-linux.x64-23.7.0.25.01.zip

Perl 5.10+

export ORACLE_HOME=/home/ian/oracle/instantclient_23_7 export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH export PATH=$ORACLE_HOME:$PATH perl -MCPAN -e 'install DBD::Oracle'

Installation

---

https://github.com/darold/ora2pg/releases

tar xvf ora2pg-25.0.tar.gz cd ora2pg-25.0/ perl Makefile.PL sudo make && make install

Configuration

---

Example conf file (/etc/ora2pg/ora2pg.conf.dist)

Important Parameters:

1. Set the Oracle home directory

ORACLE_HOME /home/ian/oracle/instantclient_23_7

1. Set Oracle database connection (datasource, user, password)

ORACLE_DSN dbi:Oracle:host=localhost;service_name=XEPDB1;port=1523 ORACLE_USER system ORACLE_PWD ipswich

1. Export Oracle schema to PostgreSQL schema

EXPORT_SCHEMA 1

1. Oracle schema/owner to use

SCHEMA HR

1. Enable/disable the CREATE SCHEMA SQL order at starting of the output file.
2. It is enable by default and concern on TABLE export type.

CREATE_SCHEMA 1 COMPILE_SCHEMA 1

1. Type of export. Values can be the following keyword:
2. TABLE Export tables, constraints, indexes, ...
3. PACKAGE Export packages
4. INSERT Export data from table as INSERT statement
5. COPY Export data from table as COPY statement
6. VIEW Export views
7. GRANT Export grants
8. SEQUENCE Export sequences
9. TRIGGER Export triggers
10. FUNCTION Export functions
11. PROCEDURE Export procedures
12. TABLESPACE Export tablespace (PostgreSQL >= 8 only)
13. TYPE Export user defined Oracle types
14. PARTITION Export range or list partition (PostgreSQL >= v8.4)
15. FDW Export table as foreign data wrapper tables
16. MVIEW Export materialized view as snapshot refresh view
17. QUERY Convert Oracle SQL queries from a file.
18. KETTLE Generate XML ktr template files to be used by Kettle.
19. DBLINK Generate oracle foreign data wrapper server to use as dblink.
20. SYNONYM Export Oracle's synonyms as views on other schema's objects.
21. DIRECTORY Export Oracle's directories as external_file extension objects.
22. LOAD Dispatch a list of queries over multiple PostgreSQl connections.
23. TEST perform a diff between Oracle and PostgreSQL database.
24. TEST_COUNT perform only a row count between Oracle and PostgreSQL tables.
25. TEST_VIEW perform a count on both side of number of rows returned by views
26. TEST_DATA perform data validation check on rows at both sides.
27. SEQUENCE_VALUES export DDL to set the last values of sequences

TYPE TABLE

1. Define the following directive to send export directly to a PostgreSQL
2. database, this will disable file output. Note that these directives are only
3. used for data export, other export need to be imported manually through the
4. use of psql or any other PostgreSQL client.

PG_DSN dbi:Pg:dbname=postgres;host=host.docker.internal;port=5433 PG_USER postgres PG_PWD ipswich

1. By default all output is dump to STDOUT if not send directly to postgresql
2. database (see above). Give a filename to save export to it. If you want
3. a Gzip'd compressed file just add the extension .gz to the filename (you
4. need perl module Compress::Zlib from CPAN). Add extension .bz2 to use Bzip2
5. compression.

OUTPUT output.sql

1. Base directory where all dumped files must be written

OUTPUT_DIR /home/ian/tmp

1. Enable PLSQL to PLPSQL conversion. This is a work in progress, feel
2. free modify/add you own code and send me patches. The code is under
3. function plsql_toplpgsql in Ora2PG/PLSQL.pm. Default enabled.

PLSQL_PGSQL 1

1. If you want to use functions defined in the Orafce library and prevent
2. Ora2Pg to translate call to these function, enable this directive.
3. The Orafce library can be found here: https://github.com/orafce/orafce
4. By default Ora2pg rewrite add_month(), add_year(), date_trunc() and
5. to_char() functions, but you may prefer to use the orafce version of
6. these function that do not need any code transformation.

USE_ORAFCE 0

1. By default when using SHOW_REPORT the migration report is generated as
2. simple text, enabling this directive will force ora2pg to create a report
3. in HTML format.

DUMP_AS_HTML 0

1. Set the PostgreSQL major version number of the target database. Ex: 9.6 or 10
2. Default is current major version at time of a new release. This replace the
3. old PG_SUPPORTS_* configuration directives.

PG_VERSION 17

Test Connectivity

---

ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg.conf

Other Example Commands

---

ora2pg -t SHOW_SCHEMA -c /etc/ora2pg/ora2pg.conf ora2pg -t SHOW_ENCODING -c /etc/ora2pg/ora2pg.conf ora2pg -n HR -t SHOW_TABLE -c /etc/ora2pg/ora2pg.conf ora2pg -n HR -t SHOW_COLUMN -c /etc/ora2pg/ora2pg.conf ora2pg -n HR -t SHOW_REPORT -c /etc/ora2pg/ora2pg.conf (--dump_as_html > migration.html)

Generate Migration Template

---

mkdir -p /home/ian/oracle/ora2pg/migration ora2pg --project_base /home/ian/oracle/ora2pg/migration --init_project test_project

1. Copy ora2pg.conf file to the config directory
2. Following types will be exported: SEQUENCE SEQUENCE_VALUES TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION

                                   PROCEDURE TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY

./export_schema.sh

1. Run highlighted command at end to export the copy the data
2. Create password file and samples database.

./import_all.sh -h localhost -p 5433 -d samples -U postgres -o hr