Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oH3ms-0006hf-12 for pgsql-admin@arkaria.postgresql.org; Thu, 28 Jul 2022 13:42:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oH3mq-0003X9-CN for pgsql-admin@arkaria.postgresql.org; Thu, 28 Jul 2022 13:42:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oH3mp-0003X0-Ue for pgsql-admin@lists.postgresql.org; Thu, 28 Jul 2022 13:42:48 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oH3mn-0006N3-6x for pgsql-admin@lists.postgresql.org; Thu, 28 Jul 2022 13:42:47 +0000 Received: by mail-oi1-x230.google.com with SMTP id l188so2459685oia.4 for ; Thu, 28 Jul 2022 06:42:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=O4iKoSaEJzFDpxYrKaGfVbplnfU3zwa4SCAikP+5X2Q=; b=AFNuNWfsdfZNceeBiSOk/jvftwYvJw4ig6NPmB8Ut20vLj9DaOiXVcYkHnYcdiOM4q HQqKjmLxOBtRTDOlsbnFUn1YxU8PcutG/08/b2dBBYrJeDonvRQpWYWW/tiKZhQEOUwb 5QqyVDtcZ3dSZ4Okp/VTxclIFumXJsMpB1pcGSkQxUhUuue7qElHVkrsFBKEBS45a1lY xOaWZZGFkzDO1gqDegkShfZAtCuqulzoFP1M6m5YCAWqEDUngdQFNua8w07hpelo+upk oyXbXfT56rjf5daLfBulltrK4RbTmIKs7Y0/oMu2GkfSmCeYboK4cjYhUyK5xRkQfAin JCDg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=O4iKoSaEJzFDpxYrKaGfVbplnfU3zwa4SCAikP+5X2Q=; b=6Si7l02P7XgxGssYddvmEqWKY0LsyOWtpiCeuSoOPbktMD5DYRzJ1XcYBzdBtsFYvC vjUiuDYIWYIzT5EhpKD5kPfkkuQxu9j8t4hUnsO8hcnb8r+SeL+DOI4pA2AVXEielLae gcwzJdeWb309KGfjZmbICgoILtbJuKohnDSYlr6vi+Db0PP1yqss0ANZHUos74lipTMD KyZLzePtbwYqH16wTU9+gyQWF8ZL2MKKYSESI4lxdyvkm5ipq3T3InVLrrTaPSQW/1uB Ms92nQLVQPFzCDmJCbQAuSz6Kjbcv9H1hLSrJ8BUgVE5rVW8OaabBmpyi2w2oAcfJD95 aCTg== X-Gm-Message-State: AJIora875zJCsYmSnqrfroRpTf23XAzFVHmk1icKWnfBBnwaT+68geJI Fty2dPQOR1XUhSdlao4d4AdIjLH5gwQ= X-Google-Smtp-Source: AGRyM1uTc7l1TSxkESL6r8CQx/vCcIHOPalrPs4lorjp+JkDbJq6dSyWsFq2rC9cM1okzBFSzaG26Q== X-Received: by 2002:a05:6808:b02:b0:33b:d59:8442 with SMTP id s2-20020a0568080b0200b0033b0d598442mr3169951oij.240.1659015762993; Thu, 28 Jul 2022 06:42:42 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id kv7-20020a056870fb8700b0010e81e27b99sm190308oab.17.2022.07.28.06.42.42 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 28 Jul 2022 06:42:42 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------kcYmRav8IvlXfFrJDOQJ4EAi" Message-ID: <1add0fb2-20fc-f3c4-9092-07780811b945@gmail.com> Date: Thu, 28 Jul 2022 08:42:41 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.9.1 Subject: Re: Oracle data to PG Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <31df7c7aa56a44cf8e23aca8b3f80513@MITMB5.helios.med.uni-muenchen.de> From: Ron In-Reply-To: <31df7c7aa56a44cf8e23aca8b3f80513@MITMB5.helios.med.uni-muenchen.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------kcYmRav8IvlXfFrJDOQJ4EAi Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 —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. -- Angular momentum makes the world go 'round. --------------kcYmRav8IvlXfFrJDOQJ4EAi Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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—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.

--
Angular momentum makes the world go 'round.
--------------kcYmRav8IvlXfFrJDOQJ4EAi--