Thread: Oracle data to PG

As Anyon said.

> 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
clienttools, or use ODBC and your choice of tool. 

On 7/28/22 03:55, Dischner, Anton wrote:

@font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.E-MailFormatvorlage17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}ol {margin-bottom:0cm;}ul {margin-bottom:0cm;}

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—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—a web-based service for migrating from Oracle to EDB Postgres Advanced Server 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.

P {margin-top:0;margin-bottom:0;}

Hi postgresql community,

I've this message "command ignored" on false in \if \else \endif. How can passthrough this ?

postgres@postgres# \echo :SERVER_VERSION_NAME

12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)

postgres@postgres# \if 1 \echo 'ONE' \else \echo 'NOT ONE' \endif

ONE

\echo command ignored; use \endif or Ctrl-C to exit current \if block

postgres@postgres# \if 0 \echo 'ONE' \else \echo 'NOT ONE' \endif

\echo command ignored; use \endif or Ctrl-C to exit current \if block

NOT ONE

_________________________________

Cordialement, Pascal CROZET

DBA  - METANEXT, Think, Build, Run the Cloud

• www.metanext.com • 04 78 22 74 90

Hi Pascal,

This message only appears if you execute these statements one by one interactively. When reading them from a file, no hint regarding the open if branch is given.

Setting QUIET to 0 or client_min_messages to error doesn't suppress the messing in interactive mode, unfortunately.

Best Regards,

Holger

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

@font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.E-MailFormatvorlage17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}ol {margin-bottom:0cm;}ul {margin-bottom:0cm;}

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—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—a web-based service for migrating from Oracle to EDB Postgres Advanced Server 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