Ora2pg is free software that can do migration from Oracle to Postgresql.
Although its features are mostly aimed at Oracle, they also support full export from
MySQL.
Properties:
- Export schema (tables, view, mview sequences, indexes, unique, P/F key, check
constraints.) - Export group and user privileges.
- Export range/list partitions, sub partitions.
- Export only the desired tables by specifying their names
- Export predefined functions, triggers, procedures, packages, package bodies.
- Export all data or with WHERE condition
- Oracle BLOB object (PG BYTEA.)
- Export Oracle views (PG table.)
- Export Oracle user-defined types.
- Supports all platforms (MS, Linux, etc.)
- Export Oracle tables as PG foreign data wrapper tables.
- Can report Oracle database components.
- Evaluate the cost of migration from Oracle database.
- Evaluate the difficulty level of migration from Oracle database.
- Can create usable XML ktr files with Pentaho Data Integrator (formerly Kettle).
You can visit the below link for all the features.
ora2pg.darold.net/documentation.html#FEATURES
Setup
In large data migrations, all servers should be in the same network class (no routing
NAT etc., especially NAT slows it down) and a minimum speed of 1GBPS.
As the downtime decreases and the data size grows, there may be a need for a network
above 10GBPS and high IOPS capacity.
Step 1
An oracle client is needed to connect to the oracle database.
# With root user (192.168.56.1) mkdir /ora2pgsetup cd /ora2pgsetup wget https://download.oracle.com/otn_software/linux/ instantclient/213000/instantclient-basic-linux.x64-21.3.0.0.0.zip wget https://download.oracle.com/otn_software/linux/ instantclient/213000/instantclient-sqlplus-linux.x64-21.3.0.0.0.zip wget https://download.oracle.com/otn_software/linux/ instantclient/213000/instantclient-sdk-linux.x64-21.3.0.0.0.zip mkdir /opt/oracle unzip instantclient-basic-linux.x64-21.3.0.0.0.zip -d /opt/oracle unzip instantclient-sqlplus-linux.x64-21.3.0.0.0.zip -d /opt/oracle/ unzip instantclient-sdk-linux.x64-21.3.0.0.0.zip -d /opt/oracle
Step 2
.profiles are organized. While using ora2pg during installation, an oracle client
is needed. Therefore, the profiles must be set correctly, otherwise an error will be
received.
(The .profiles of both root and standard users who will use ora2pg should also be
edited)
vim ~/.profile # Dosyasına aşağıdaki satırlar eklenir. export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_3 export PATH=”/opt/oracle/instantclient_21_3:$PATH” export ORACLE_HOME=/opt/oracle/instantclient_21_3 # To take effect in current session source ~/.profile # Installation test which sqlplus # The output should be as follows. /opt/oracle/instantclient_21_3/sqlplus
Because Oracle instant client is used, ld_library_path and oracle_home
directories are the same.
Step 3
Required packages for ora2pg are installed.
apt install libdbd-pg-perl apt install libdbi-perl apt install dpkg-dev apt install debhelper apt install gcc apt install build-essential apt install libaio1 apt install make apt install libpq-dev apt install postgresql-client
Note: If ora2pg is to be installed on a postgresql database server, postgresql-client is
not needed.
Step 4
Ora2pg is downloaded and installed.
mkdir /ora2pgsetup/ora2pgsetup cd /ora2pgsetup/ora2pgsetup wget https://github.com/darold/ora2pg/archive/refs/heads/master.zip unzip ora2pg-master.zip cd ora2pg-master/ perl Makefile.PL make && make install
Ora2pg runs from the /etc/ora2pg/ora2pg.conf configuration file by default.
It can also be run from a different conf file with /usr/local/bin/ora2pg -c /
etc/ora2pg/different_1_ora2pg.conf
Step 5
First, the connections with the clients are tested.
# Oracle connection test mehmed@dataera:~$ sql system/welcome1@10.71.5.121:1521/orcl.orcl SQLcl: Release 21.3 Production on Wed Jul 20 06:07:05 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production # Connection is made through port 5434 to postgresql database server with IP 192.168.56.61. psql -h 192.168.56.61 -p 5434 -d mx -U postgres -W Password: psql (14.4 (Ubuntu 14.4-1.pgdg20.04+1), server 13.6) Type “help” for help.
Oracle :
sql system/welcome1@10.71.5.121:1521/orcl.orcl
sql (sqlcli): Terminal version of Oracle’s SQL Developer application.
Organizes the column formats in queries and writes the
output to the screen.
system: One of the most authorized users in the Oracle database.
welcome1: Password of system user
10.71.5.121: IP address of Oracle database server
orcl.orcl: instance/service name
It is seen that there is a smooth connection to both databases.
Step 6
The ora2pg.conf file is edited.
# Default conf file is copied. cp /etc/ora2pg/ora2pg.conf.dist /etc/ora2pg/ora2pg.conf # The connection information is entered in ora2pg.conf file. vim /etc/ora2pg/ora2pg.conf # Oracle connection information. (connection test with sql/ sqlplus should have been done before) ORACLE_DSN dbi:Oracle:host=10.71.5.121;sid=DB11G;port=1521 ORACLE_USER system ORACLE_PWD welcome1 Note: 12C and above dbi:Oracle:host=IP;service_name=service_ name;port=1521 # Postgresql connection information (connection test with psql must have been done). PG_DSN dbi:Pg:dbname=erpdb;host=192.168.56.61;port=5434 PG_USER postgres PG_PWD welcome1
If postgresql connection information is entered, it writes data from oracle directly to
postgresql. If it is not entered, it writes to a file, then this file is sent to the postgresql
server and imported.
The ora2pg connection test is performed.
ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg.conf Oracle Database 11g Release 11.2.0.4.0 ora2pg -t SHOW_SCHEMA -c /etc/ora2pg/ora2pg.conf SCHEMA HR SCHEMA IX SCHEMA OE SCHEMA PM SCHEMA SCOTT
Query results show that ora2pg is working properly. Since it is a test database, there
are default schemas that come with the installation.
It has many features such as changing data type, changing table name, encryption, data
transfer according to SCN, cdc_ready parameter for CDC tools.
You can find the complete list of ora2pg parameters in the ora2pg.conf file and
on the official site https://ora2pg.darold.net/documentation.html.
Example
The tables of the hr schema in Oracle are migrated to the mx database in postgresql.
Step 1
mehmed@dataera:~$ vim /etc/ora2pg/ora2pg.conf EXPORT_SCHEMA 1 SCHEMA hr CREATE_SCHEMA 1 STOP_ON_ERROR 0 SKIP fkeys pkeys ukeys indexes checks TYPE TABLE OUTPUT_DIR /data
ora2pg is updated. Connection information was given in step 6.
Step 2
# The report is generated ora2pg -t SHOW_REPORT -c /etc/ora2pg/ora2pg.conf –estimate_cost > /data/hr.ec.html ora2pg -c /../ora2pg.conf -t SHOW_REPORT --dump_as_html > /../hr.mg_as.html # With parallelism 4, the tables of the HR schema are written to the hr_tables_V01.sql file. ora2pg -c /../ora2pg.conf -t TABLE -o hr_tables_V01.sql -P 4 [========================>] 9/9 tables (100.0%) end of scanning. [========================>] 9/9 tables (100.0%) end of table export.
The report is generated and the schema is exported. This exports and converts Oracle’s
hr schema into Postgresql version. Can be updated with text editor
Creates the hr_tables_V01.sql file in the /data directory.
Note: Since we have provided the PG_DSN information, the data is migrated from
oracle and inserted directly to Postgresql. So hr_tables_V01.sql file has only
metadata information (create script). No data (insert/copy clauses).
If the parameters given when executing ora2pg conflict with those in the ora2pg.conf
file, the parameters given while executing overrides.
Step 3
# Connect to Postgresql from ora2pg machine. psql -h 192.168.56.61 -d mx -U postgres -p 5434 -W mx=# \i /data/hr_tables_V01.sql SET CREATE SCHEMA ALTER SCHEMA SET CREATE TABLE .... mx=# show search_path; search_path ------------- hr, public mx=# \dt List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- hr | countries | table | postgres .. (9 rows)
hr schema is imported to Postgresql and checked.
In order not to enter the password every time:
PGPASSWORD=”password” psql -h 192.168.56.61 -p 5434 -d mx -U postgres -f /data/hr_tables_V01.sql
Step 4
# With parallelism 8 , the tables of the hr schema are migrated to Postgresql.ora2pg -c /etc/ora2pg/ora2pg.conf -t COPY -P 8 > hr_schema_ mig.log [===>] 9/9 tables (100.0%) end of scanning. ..... [===>] 228/227 rows (100.4%) on total estimated data (4 sec.avg:57 tuples/sec) # After the data transfer, the row numbers of the source and destination are checked. ora2pg -t TEST --count_rows -c /etc/ora2pg/ora2pg.conf -P 8 ora2pg -t TEST_DATA -c /etc/ora2pg/ora2pg.conf -P 8
Note: An average of 57 rows (tuples/rows) were copied per second. This is a very
low figure.The machines we work with although have disks with SSD, it reduces the
performance because we are connected to the oracle database via VPN.
While transferring data, IOPS performance, amount of memory, number of cores and
network speed directly affect performance.
However, performance improvements may be needed in Oracle/postgresql instance
and operating system along with ora2pg.conf.
Performance will depend on the weakest link in this chain. For example, even if you
have a 128 core processor, if the parallelism is not set correctly, one core will work
100% while the other 127 cores will remain idle.
Note: Objects to be migrated from Oracle must have up-to-date statistical information.
Step 5
# The report is generated # view export ora2pg -c /etc/ora2pg/ora2pg.conf -t VIEW -o hr_views_V01.sql -P 4 # sequence export ora2pg -c /etc/ora2pg/ora2pg.conf -t SEQUENCE -o hr_sequences_V01.sql -P 4 mx=# \i /data/hr_views_V01.sql mx=# \i /data/hr_sequences_V01.sql
Other objects are specified with the “-t” option and imported via psql.