Re: Oracle data to PG

On 7/28/22 03:55, Dischner, Anton wrote:
>
> Hi Team,
>
> we are searching for a way to import a dump from an Oracle Installation
> into PostgreSQL.
>
> Ist not administrated by us, so foreing table or connect as an user (as
> ora2pg seems to need) is not an option.
>
> We had a look into
> https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative
>
> especially:
>
> * Ora2pg <http://ora2pg.darold.net/>—a robust migration tool that
> connects to an Oracle database, extracts schemas and tables and
> generates SQL scripts that can be loaded into Postgres.
> * Ora_migrator—an extension that  uses  an oracle_fdw foreign data
> wrapper to extract data from an Oracle database.
> * Orafce—this extension allows you to implement a number of Oracle
> functions in Postgres.  It also provides support for Oracle date
> formatting and additional Oracle data types.
> * EDB Migration Portal
> <https://www.enterprisedb.com/products/free-oracle-postgres-migration-tool>—a
> web-based service for migrating from Oracle to EDB Postgres Advanced
> Server
> <https://www.enterprisedb.com/products/edb-postgres-advanced-server-secure-ha-oracle-compatible>
> that features Assessment, Schema conversion and Compatibility reporting.
>
> we have to import data only.
>
> No indexes, PL/SQL or other stuff.
>
> Maybe it makes sense to install an Oracle Instance, import the data and
> then connect as a user and do the conversion.
>
> What are you hints and experiences to plan and accomplish this task?
>

We successfully used ora2pg to extract 5TB of LOB-heavy tables out of
Oracle.  Each SQL files generated was a bunch of COPY statements in stdin
format.

It's multi-process, can extract from views, and tunable based on the size of
the LOBs.

--
Angular momentum makes the world go 'round.