pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOracle data to PG
7+ messages / 7 participants
[nested] [flat]
* Oracle data to PG
@ 2022-07-28 08:55 Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
0 siblings, 3 replies; 7+ messages in thread
From: Dischner, Anton @ 2022-07-28 08:55 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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-co...
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?
best
Anton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle data to PG
@ 2022-07-28 12:18 Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
parent: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2 siblings, 3 replies; 7+ messages in thread
From: Juan José Santamaría Flecha @ 2022-07-28 12:18 UTC (permalink / raw)
To: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Jul 28, 2022 at 10:56 AM Dischner, Anton <
Anton.Dischner@med.uni-muenchen.de> wrote:
>
>
> 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.
>
>
>
Why don't you have the Oracle people dump the data you need into a CSV file
and COPY it into Postgres?
Regards,
Juan José Santamaría Flecha
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [EXT] Re: Oracle data to PG
@ 2022-07-28 12:28 Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
parent: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Pierson Patricia L (Contractor) @ 2022-07-28 12:28 UTC (permalink / raw)
To: juanjo.santamaria@gmail.com <juanjo.santamaria@gmail.com>; Anton.Dischner@med.uni-muenchen.de <Anton.Dischner@med.uni-muenchen.de>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Dumping to CVS is fine for small simple tables. Watch for BLOB/LOBs. May need
to have Oracle DBAs write a plsql proc to dump
From: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
Sent: Thursday, July 28, 2022 6:19 AM
To: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXT] Re: Oracle data to PG
On Thu, Jul 28, 2022 at 10:56 AM Dischner, Anton
<Anton.Dischner@med.uni-muenchen.de
<mailto:Anton.Dischner@med.uni-muenchen.de> > wrote:
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.
Why don't you have the Oracle people dump the data you need into a CSV file
and COPY it into Postgres?
Regards,
Juan José Santamaría Flecha
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle data to PG
@ 2022-07-28 13:29 Pepe TD Vo <pepevo@yahoo.com>
parent: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Pepe TD Vo @ 2022-07-28 13:29 UTC (permalink / raw)
To: juanjo.santamaria@gmail.com, "Dischner, Anton" <Anton.Dischner@med.uni-muenchen.de>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
As Anyon said.I use SQL Developer dump into csv and import to Postgres plsql or pgadmin.
Sent from Yahoo Mail on Android
On Thu, Jul 28, 2022 at 8:19, Juan José Santamaría Flecha<juanjo.santamaria@gmail.com> wrote:
On Thu, Jul 28, 2022 at 10:56 AM Dischner, Anton <Anton.Dischner@med.uni-muenchen.de> wrote:
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.
Why don't you have the Oracle people dump the data you need into a CSV file and COPY it into Postgres?
Regards,
Juan José Santamaría Flecha
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle data to PG
@ 2022-07-28 13:33 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Scott Ribe @ 2022-07-28 13:33 UTC (permalink / raw)
To: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>; +Cc: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>; pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Jul 28, 2022, at 6:18 AM, Juan José Santamaría Flecha <juanjo.santamaria@gmail.com> wrote:
>
> Why don't you have the Oracle people dump the data you need into a CSV file and COPY it into Postgres?
And if you can't get even that minimal level of cooperation from the Oracle people: dump it yourself. Install Oracle client tools, or use ODBC and your choice of tool.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle data to PG
@ 2022-07-28 13:42 Ron <ronljohnsonjr@gmail.com>
parent: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2 siblings, 0 replies; 7+ messages in thread
From: Ron @ 2022-07-28 13:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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-co...
>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Oracle data to PG
@ 2022-07-29 13:12 Mladen Gogala <gogala.mladen@gmail.com>
parent: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2 siblings, 0 replies; 7+ messages in thread
From: Mladen Gogala @ 2022-07-29 13:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/28/22 04: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-co...
>
> 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?
>
> best
>
> Anton
>
Hi Anton,
Long time no see. Ora2pg can produce just the "CREATE TABLE/INDEX/VIEW"
statements from Oracle, translated to PgSQL vernacular. You will have to
be able to log onto the original Oracle instance, albeit as an
application owner.
Furthermore, Oracle has a free ODBC gateway which can be used to connect
to Postgres. What you need is an Oracle listener, not an entire
instance. Another thing that can be exceedingly useful is this:
https://osalvador.github.io/ReplicaDB/
You can extract Oracle data into CSV files and do COPY in PgSQL. I know
it works with Snowflake. There is no reason for it not to work witg
PgSQL as well. You will need some scripting, but it can be done.
Be aware of some key differences: Oracle doesn't store NULL values in
indexes. PostgreSQL does. The corollary is different behavior with the
unique, multi-column indexes. There are many more index types in PgSQL,
I find Bloom indexes exceptionally useful.
Good luck!
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-07-29 13:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-28 08:55 Oracle data to PG Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2022-07-28 12:18 ` Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
2022-07-28 12:28 ` Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-28 13:29 ` Pepe TD Vo <pepevo@yahoo.com>
2022-07-28 13:33 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-07-28 13:42 ` Ron <ronljohnsonjr@gmail.com>
2022-07-29 13:12 ` Mladen Gogala <gogala.mladen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox