En resumen Installing ora2pg on Ubuntu 24.04 requires three components: Oracle Instant Client 19c (for the Oracle connection libraries), the DBD::Oracle Perl module (which links against those libraries), and ora2pg itself (installed from source — it is not on CPAN). Once installed, a single command connects to your Oracle database, scans every object in the schema, and produces an HTML report with a complexity score and an effort estimate in person-days. This post covers the full installation and the report command.
The fastest way to know whether an Oracle → PostgreSQL migration is a one-week job or a three-month project is to run the ora2pg migration report before opening a spreadsheet.
The report connects to Oracle, counts every object by type, scores the schema from A (straightforward) to C (complex), and produces an effort estimate in person-days.
It takes about twenty minutes to install and sixty seconds to run.
Índice
What the Migration Report Produces
The ora2pg SHOW_REPORT command connects to your Oracle database, scans every object in the schema, and produces a table with five columns: object type, object count, invalid object count, estimated migration cost in units, and comments on what ora2pg will do with each type.
The last line gives a total cost and a human-readable effort estimate: “22.40 cost migration units means approximately 1 person-day(s).
The migration unit was set to 5 minute(s).”
The score is two parts: a letter and a number.
The letter reflects overall complexity:
- A — migration that can largely be automated
- B — migration with code rewriting; estimated cost up to 10 person-days
- C — migration with code rewriting; estimated cost above 10 person-days
The number reflects technical difficulty:
- 1 — no stored functions, no triggers
- 2 — triggers present, no manual rewriting required
- 3 — stored functions and/or triggers, no manual rewriting required
- 4 — triggers or views that require code rewriting
- 5 — stored functions and/or triggers that require code rewriting
A schema with no stored code scores A-1. A schema with PL/SQL packages, triggers, and Oracle-specific views typically scores B-4 or B-5.
The C level is triggered when the estimated effort exceeds 10 person-days (adjustable with --human_days_limit).
Run the report before committing to a timeline.
The number it produces is the one number worth knowing before the project starts.
Prerequisites
Three things are required, in dependency order:
- Oracle Instant Client 19c — the C libraries (
libclntsh.soand related) that the Perl driver links against at compile time and loads at runtime. Without these, no Oracle connection is possible regardless of network access. - DBD::Oracle — the Perl module that provides the Oracle driver. It compiles against the Instant Client headers during installation — the devel package is required, not just the basic runtime.
- ora2pg 25.0 — installed from source via its GitHub release tarball. ora2pg is not available on CPAN;
cpanm ora2pgwill fail.
You also need network access from the Ubuntu machine to the Oracle database on port 1521 and a database user with sufficient privileges to scan the schema (details in the FAQ below).
If your Oracle server is not in DNS, add it to /etc/hosts on the Ubuntu machine before proceeding:
sudo vi /etc/hosts
# Add: 192.168.x.x your-oracle-host.localdomain your-oracle-host
Verify name resolution before moving on:
ping -c 2 your-oracle-host.localdomain
If the ping fails, the connectivity tests in Step 3 will also fail — fix DNS or /etc/hosts first.
Step 1: Install Oracle Instant Client on Ubuntu 24.04
Oracle distributes Instant Client as RPM packages only.
Ubuntu uses DEB packages.
En alien tool converts RPM to DEB — it handles the package metadata translation so the files land in the correct paths and the package manager tracks the installation.
Download three packages from the Oracle Instant Client Downloads page — use the generic Linux x86-64 RPMs for version 19.30, not the OL8 or OL9 variants (those are built for Red Hat family systems and have different dependencies):
oracle-instantclient19.30-basic-*.rpm— the full runtime shared libraries (libclntsh.so,libnnz19.so, and others)oracle-instantclient19.30-devel-*.rpm— header files and thesdk/directory required to compile DBD::Oracleoracle-instantclient19.30-sqlplus-*.rpm— thesqlplus64binary for testing Oracle connectivity independently of ora2pg
Transfer the three RPMs to your Ubuntu machine, then run:
# libaio1t64: the async I/O library required by Oracle Instant Client at runtime
# On Ubuntu 24.04 the package name is libaio1t64, not libaio1 -- the name changed in this release
# alien: converts RPM packages to DEB format for installation with dpkg
sudo apt-get install -y libaio1t64 alien
# Convert each RPM to DEB and install in one step (-i: convert and install immediately)
sudo alien -i oracle-instantclient19.30-basic-*.rpm
sudo alien -i oracle-instantclient19.30-devel-*.rpm
sudo alien -i oracle-instantclient19.30-sqlplus-*.rpm
Ubuntu 24.04 symlink fix: Ubuntu 24.04 ships the async I/O library as libaio.so.1t64 as part of the 64-bit time_t transition.
Oracle Instant Client was compiled on Red Hat systems and hardcodes libaio.so.1 in its ELF binary.
Without a symlink, sqlplus64 fails immediately with cannot open shared object file: libaio.so.1.
Create the symlink before testing:
sudo ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1
Set the three required environment variables and persist them to ~/.bashrc:
export ORACLE_HOME=/usr/lib/oracle/19.30/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
echo 'export ORACLE_HOME=/usr/lib/oracle/19.30/client64' >> ~/.bashrc
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> ~/.bashrc
echo 'export PATH=$ORACLE_HOME/bin:$PATH' >> ~/.bashrc
ORACLE_HOME tells DBD::Oracle and ora2pg where the Instant Client is installed — both tools read this at build time and at runtime.
LD_LIBRARY_PATH tells the Linux dynamic linker where to find Oracle’s shared libraries — without this, any binary that links against libclntsh.so fails with “error while loading shared libraries”.
PATH adds the Instant Client bin directory so sqlplus64 is available on the command line.
Verify the installation:
sqlplus64 -V
# Expected: SQL*Plus: Release 19.30.0.0.0 Production
If this command fails with a library error after the symlink, check that LD_LIBRARY_PATH is exported (not just set) in the current shell.
Step 2: Install DBD::Oracle and ora2pg
DBD::Oracle is the Perl database driver for Oracle. It is a C extension — it compiles from source against the Instant Client headers during installation.
Por eso ORACLE_HOME must be exported before running cpanm DBD::Oracle: the build script reads that variable to locate the headers and link against the shared libraries.
Install the build dependencies first:
# perl: the Perl runtime (may already be present on Ubuntu 24.04)
# cpanminus: a lightweight CPAN module installer -- simpler than the full CPAN shell
# make, gcc: required to compile DBD::Oracle from C source
sudo apt-get install -y perl cpanminus make gcc
Then install DBD::Oracle — ORACLE_HOME y LD_LIBRARY_PATH must be exported in the current shell before running this:
sudo cpanm DBD::Oracle
The build takes 2–3 minutes. If it fails with a message about missing headers, verify that the devel RPM was installed and that ORACLE_HOME points to the correct directory.
Now install ora2pg from the GitHub release tarball. ora2pg is not available on CPAN — cpanm ora2pg will fail with “module not found”.
The only supported installation is from source:
wget https://github.com/darold/ora2pg/archive/refs/tags/v25.0.tar.gz
tar xzf v25.0.tar.gz
cd ora2pg-25.0
# Makefile.PL generates the Makefile based on the system Perl configuration
perl Makefile.PL
# make compiles and prepares the distribution
make
# make install copies the ora2pg script and Perl modules to the system Perl paths
sudo make install
cd ..
Verificar
ora2pg --version
# Expected: Ora2Pg v25.0
Step 3: Configure the Connection
ora2pg reads /etc/ora2pg/ora2pg.conf por defecto.
The installation creates /etc/ora2pg/ora2pg.conf.dist as a heavily commented reference file with every available parameter documented.
Copy it to create your working config — the dist file serves as your baseline backup:
sudo cp /etc/ora2pg/ora2pg.conf.dist /etc/ora2pg/ora2pg.conf
sudo vi /etc/ora2pg/ora2pg.conf
The dist file is long (over 1,000 lines of comments and parameters).
Utilice /ORACLE_DSN in vi to jump directly to each parameter — do not scroll manually.
Set these five parameters:
ORACLE_DSN dbi:Oracle:host=YOUR_ORACLE_HOST;service_name=YOUR_PDB_NAME;port=1521
ORACLE_USER system
ORACLE_PWD YOUR_SYSTEM_PASSWORD
SCHEMA YOUR_SCHEMA_NAME
OUTPUT_DIR /home/YOUR_USER/ora2pg-output
ORACLE_DSN: Utilice service_name, no sid. Oracle 19c organises databases as Container Databases (CDB) with one or more Pluggable Databases (PDB). sid=ORCL connects to the CDB root — your application schema does not exist there. service_name=YOUR_PDB_NAME connects to the PDB where your schema lives. The PDB service name is visible in lsnrctl status on the Oracle server.
ORACLE_USER: Connect as SYSTEM or another DBA user, not as the schema owner. The schema owner typically lacks SELECT ON v$database, which causes the SHOW_REPORT command to fail. Using SYSTEM avoids privilege issues during the assessment phase.
SCHEMA: Limits the scan to one schema. Without this, ora2pg scans all schemas in the database, which significantly increases scan time and produces a report that mixes all schema objects.
OUTPUT_DIR: The directory where ora2pg writes export files. This directory must exist before running any ora2pg command — ora2pg does not create it automatically.
Create the output directory:
mkdir -p ~/ora2pg-output
Run two connectivity tests before proceeding to the report.
The tests verify different layers of the stack — passing both confirms that the full chain from Ubuntu to Oracle is working correctly:
# Test 1: sqlplus64 -- verifies Instant Client libraries, network routing, and Oracle listener
sqlplus64 YOUR_USER/YOUR_PASSWORD@//YOUR_ORACLE_HOST:1521/YOUR_PDB_NAME
# Expected: Connected to: Oracle Database 19c Enterprise Edition ...
# Type "exit" to leave the SQL*Plus prompt
# Test 2: ora2pg SHOW_VERSION -- verifies ora2pg connects to Oracle via DBD::Oracle
ora2pg -t SHOW_VERSION
# Expected: Oracle Database 19c Enterprise Edition Release 19.x.x.x.x
# If test 1 fails: check the Oracle listener with "lsnrctl status" on the Oracle server
# and confirm port 1521 is open between the two machines.
# If test 2 fails but test 1 passes: check that LD_LIBRARY_PATH is exported (not just set)
# in the current shell.
If connectivity works but you are unsure how to interpret the report output — or the score comes back C-level and you need a second opinion on scope — una evaluación a precio fijo cubre exactamente eso
Step 4: Run the Migration Assessment Report
Before running the report, gather fresh statistics on the Oracle schema. ora2pg derives its row counts and cost estimates from Oracle's stored statistics in the data dictionary.
If statistics are stale — which is common on schemas that have not been analysed recently — the row counts will be inaccurate and the effort estimate will be unreliable.
Run this on the Oracle server as SYSTEM or SYSDBA:
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS('YOUR_SCHEMA');
DBMS_STATS.GATHER_DATABASE_STATS;
DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/
-- Expected: PL/SQL procedure successfully completed.
GATHER_SCHEMA_STATS updates statistics for the target schema. GATHER_DATABASE_STATS updates statistics for all objects in the database.
GATHER_DICTIONARY_STATS updates the Oracle data dictionary views that ora2pg reads during the scan.
On a large database this block can take several minutes — on a small schema like HR it completes in seconds.
Then run the report from Ubuntu:
# HTML version -- recommended for reading the report
ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html > ~/ora2pg-output/migration_report.html
# SHOW_REPORT: connects to Oracle and counts every object by type in the schema
# --estimate_cost: calculates a complexity score and converts the total units to person-days
# --dump_as_html: formats the output as an HTML table -- open in a browser
# Plain text version -- useful for scripting or sending the report by email
ora2pg -t SHOW_REPORT --estimate_cost > ~/ora2pg-output/migration_report.txt
Open migration_report.html in a browser.
The plain text version is hard to read because the columns are not aligned — the HTML version formats the table cleanly and is the one to share with stakeholders.
Step 5: Read the Report Output
The report gives one row per object type.
The columns are: object type, object count, invalid object count, estimated cost in migration units, and a comments field explaining what ora2pg will do with each type.
Pay attention to four things:
The migration level — shown at the top of the report as a letter and number (for example, B-5). A-1 and A-2 schemas have no stored code and migrate largely automatically. B-4 and B-5 schemas have stored procedures or triggers that require manual rewriting in PL/pgSQL. C-level schemas exceed the person-day threshold and require a formal project scope before committing to a timeline.
The FUNCTION, PROCEDURE, PACKAGE, and TRIGGER rows — this is where manual effort concentrates. Tables, sequences, and indexes migrate with minimal manual work. Stored code does not — each function, procedure, and trigger must be reviewed and rewritten. A schema with 200 tables and zero stored procedures migrates faster than a schema with 50 tables and 30 stored procedures. The object counts in these rows are the most important numbers in the report.
The invalid object count — the third column in the table. Any object Oracle itself marks as invalid (broken dependencies, compilation errors) will not export cleanly. Fix invalid objects in Oracle before running any export — do not carry them into the migration.
The comments column — ora2pg explains what it will do with each object type and flags known issues. Read the TRIGGER comments carefully: sequence-based BEFORE INSERT triggers (used to simulate auto-increment in Oracle) export as PL/pgSQL but should be replaced with PostgreSQL IDENTITY columns or GENERATED ALWAYS AS IDENTITY, which requires a deliberate decision during the migration.
Step 6: Adjust the Effort Estimate
The default cost unit is 5 minutes per migration unit.
This figure assumes a consultant with PostgreSQL experience doing the rewriting work.
It is a reasonable baseline for scoping, but two parameters let you adjust it to match the actual team and risk appetite.
--cost_unit_value changes the number of minutes per unit. The default is 5. For an internal team doing their first Oracle → PostgreSQL migration, doubling to 10 minutes per unit is more realistic — first-time migrations always take longer than the tool suggests because of unfamiliar syntax, testing cycles, and edge cases:
ora2pg -t SHOW_REPORT --estimate_cost --cost_unit_value 10 --dump_as_html > ~/ora2pg-output/migration_report.html
# --cost_unit_value 10: each unit = 10 minutes instead of 5
# The person-day total doubles -- use this when the team has no prior migration experience
--human_days_limit sets the threshold above which the schema is classified as C-level. The default is 10 person-days. Lowering it to 5 is useful when you want to be more conservative — for example, when presenting the report to a client who needs to budget for the work:
ora2pg -t SHOW_REPORT --estimate_cost --human_days_limit 5 --dump_as_html > ~/ora2pg-output/migration_report.html
# --human_days_limit 5: any schema above 5 person-days is classified as C-level
# The underlying unit counts do not change -- only the letter classification changes
Both flags can be combined:
ora2pg -t SHOW_REPORT --estimate_cost --cost_unit_value 10 --human_days_limit 5 --dump_as_html > ~/ora2pg-output/migration_report.html
Preguntas frecuentes
What Oracle privileges does the ora2pg user need?
For a non-DBA user, the minimum grants are:
GRANT CONNECT TO ora2pg_user;
GRANT SELECT ANY DICTIONARY TO ora2pg_user;
GRANT SELECT ON v_$database TO ora2pg_user;
SELECT ANY DICTIONARY covers the DBA_* static data dictionary views that ora2pg queries for object counts. SELECT ON v_$database covers the V$ dynamic performance views — these are a separate object type in Oracle and require an explicit grant even when SELECT ANY DICTIONARY is already granted. Both grants are required; neither makes the other redundant.
Configurar USER_GRANTS 1 en ora2pg.conf to tell ora2pg to query USER_* views rather than DBA_* views when connecting as the schema owner. For most assessments, connecting as SYSTEM is simpler and avoids grant issues entirely.
Does ora2pg work with Oracle 19c Pluggable Databases?
Yes, but you must use service_name in the DSN, not sid. sid=ORCL connects to the CDB root where your application schemas do not exist. Use service_name=YOUR_PDB_NAME — the PDB service name is visible in lsnrctl status on the Oracle server.
The report shows a high cost estimate. What does that mean for the migration?
A high estimate means the schema has significant PL/SQL — stored procedures, packages, and triggers that require manual rewriting. The report does not mean migration is impossible; it means the rewriting work needs to be scoped and resourced properly. In my experience, a B-5 schema with 50–100 person-days of estimated effort is a 6–8 week engagement with the right approach — not a reason to abandon the project. The estimate is a starting point for scoping, not a final answer.
Can I run the report without connecting to a live Oracle database?
No. SHOW_REPORT connects to the Oracle data dictionary to count objects and read statistics. It cannot operate offline or against an export file. The database must be running and accessible on port 1521 from the Ubuntu machine.
En resumen
Installing ora2pg on Ubuntu 24.04 requires three components in dependency order: Oracle Instant Client 19.30 (installed via alien from the Oracle RPMs), DBD::Oracle (compiled from source via cpanminus), and ora2pg 25.0 (installed from the GitHub tarball — not available on CPAN).
Once installed, the assessment command is one line:
ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html > ~/ora2pg-output/migration_report.html
The output gives a complexity score (A/B/C + 1–5) and an effort estimate in person-days. Run it before committing to any migration timeline — the number it produces is the one number that determines how the project should be scoped and resourced.
If the report comes back B-5 or C-level and you need help interpreting the output or scoping the manual work, ponerse en contacto