Subject: Oracle to postgres migration

From: Rajesh Kumar <[email-masked]>
Date: Mon, 27 Jan 2025 14:42:22 +0530

Hi team,

I am trying to migrate from oracle to postgres.

I have been asked to provide an estimation for effort days. Anybody has any
document related to estimation? And steps.

Where do I start with? Anybody has any documentation related to ora2pg
migration ?

A little help is appreciated

---

From: Kashif Zeeshan <[email-masked]>
Date: Mon, 27 Jan 2025 14:15:31 +0500

Hi Rajesh

You can use EDB's Migration ToolKit (MTK) and following is the link to the
documentation.

https://www.enterprisedb.com/docs/migration_toolkit/latest/

On Mon, Jan 27, 2025 at 2:12 PM Rajesh Kumar <[email-masked]>
wrote:

> I have been asked to provide an estimation for effort days. Anybody has
> any document related to estimation? And steps.
>

The time depends on the size of the data needed to migrate.

Thanks
Kashif Zeeshan

---

From: Raphael Salguero Aragón<[email-masked]>
Date: Mon, 27 Jan 2025 10:21:26 +0100

Hi Rajesh,

ora2pg is a good starting point to get an overview about the complexity.
But the effort for manual conversion also depends on your experiences and
skills. That’s something you can adjust with the cost factors using ora2pg.
Data migration is a different aspect. That depends on the db size, your
object types and the migration method.

I would suggest to get started with ora2pg first.

Best regards
Raphael

Kashif Zeeshan <[email-masked]> schrieb am Mo. 27. Jan. 2025 um

---

From: Julien Rouhaud <[email-masked]>
Date: Mon, 27 Jan 2025 17:22:31 +0800

Hi,

> Where do I start with? Anybody has any documentation related to ora2pg

ora2pg is probably the best tool for your task.  And yes it does provide
estimates for the migration efforts, see
https://ora2pg.darold.net/documentation.html#Migration-cost-assessment.

In general the ora2pg documentation is really good, you should find the answer
to all your questions there.

---

From: Rajesh Kumar <[email-masked]>
Date: Mon, 27 Jan 2025 15:00:44 +0530

Size is 300gb, have lob objects. I prefer ora2pg. Does EDB MTK costs?

Mostly I need to know what are all the things I need to ask oracle people
to start withj

On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <[email-masked]> wrote:

> any

---

From: Avinash Vallarapu <[email-masked]>
Date: Mon, 27 Jan 2025 15:03:28 +0530

Hi,

> Mostly I need to know what are all the things I need to ask oracle people
You can also use the Ora2Pg AI Chatbot, so that you can get responses if
you are stuck while using Ora2Pg.

https://ora2pgsupport.hexacluster.ai/

-- 
Regards,
Avinash Vallarapu
CEO
HexaCluster (www.hexacluster.ai)
Try our new Database Migration Service: www.hexarocket.com

---

From: manish yadav <[email-masked]>
Date: Mon, 27 Jan 2025 09:43:30 +0000 (UTC)

You may try EDB Migration portal (https://migration.enterprisedb.com/)  for migration assessment which is freely available.  EDB MTK to be used for data migration which is under subscription plan.

Thanks and Regards,

Manish Yadav

On Monday 27 January, 2025 at 03:04:00 PM IST, Avinash Vallarapu <[email-masked]> wrote: 

Hi,

On Mon, Jan 27, 2025 at 3:01 PM Rajesh Kumar <[email-masked]> wrote:
> Mostly I need to know what are all the things I need to ask oracle people to start withj
You can also use the Ora2Pg AI Chatbot, so that you can get responses if you are stuck while using Ora2Pg.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 27 Jan 2025 05:09:42 -0500

I migrated a 12TB Oracle db that was mostly LOB objects into an 8TB PG
database.  LOBs loaded into bytea columns.
One thing which I did not do, but should have, was have ora2pg convert
NUMBER(38,0) values to BIGINT.

We just used ora2pg to convert data; the app developer rewrote all of the
stored procedures, functions, triggers, etc.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Rajesh Kumar <[email-masked]>
Date: Mon, 27 Jan 2025 15:41:59 +0530

Thank you all. As mush as more info is always appreciated by dearest admins

---

From: Rajesh Kumar <[email-masked]>
Date: Mon, 27 Jan 2025 15:43:33 +0530

With regards to lo, is there any difficulty if we have rowsize > 1gb

---

From: Raphael Salguero Aragón<[email-masked]>
Date: Mon, 27 Jan 2025 14:07:57 +0100

Hi Rajesh

Rajesh Kumar <[email-masked]> schrieb am Mo. 27. Jan. 2025 um

> With regards to lo, is there any difficulty if we have rowsize > 1gb
For most cases, I would recommend to migrate lobs > 1gb into
pg_largeobjects. The way of accessing those lobs will change (also for the
application)
This could be done with a bit of python scripting. I’m not sure if there is
a option within ora2pg meanwhile.

Regarding the sizes in general, you can check out below article:
https://www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained

Best regards
Raphael

>>> We just used ora2pg to convert data; the app developer rewrote all of
>>> the stored procedures, functions, triggers, etc.
>>>
>>> On Mon, Jan 27, 2025 at 4:31 AM Rajesh Kumar <
>>> [email-masked]> wrote:
>>>

---

From: "Wong, Kam Fook (TR Technology)" <[email-masked]>
Date: Mon, 27 Jan 2025 18:28:54 +0000

Rajesh,

We have done probably 1 thousand plus of Oracle DB migration to Postgres (and we still have Oracle and SQL Servers).  But I don’t have the documentation to share – one I don’t have it.  Two, even if I have it I can’t share it due to company policy.  In a high level here are a few things to chew on (others please add and correct)

1.  Schema migration – you can find a 3rd party tool.
2.  Data migration – same as above.  If you are replicating your data online/ongoing from Oracle to Postgres with zero production downtime, be ready for “a lot/extremely busy” challenges.  You need a team just for this around the clock (lobs, data conflict resolution, performance, cascade delete and etc)
3.  Querries/store proc/trigger migration – you can find a 3rd party tool but you still need manual changes, tuning, and logic verification.  Plus Scale testing.
4.  Partition table migration – you should tackle this problem early on if you have daily partition pruning.
5.  Cron job/DBMS scheduler job – we use pg_con extension.
6. Infrastructure sizing – make sure you size them correctly.
7.  Parameters configuration in Postgres – you will learn and face the challenges (vs Oracle init/pfile).
8.  Query performance tuning – Same concept but you will burn to learn quickly.
9.  Oracle AWR is no longer available.  One to two years ago I wasn’t able to find a comparable product.  We hire a brilliant contractor/consultant to write our custom snap that runs continuously (and prunes off the aged data).  We also use 3rd party db tools and those alone often time is not sufficient to troubleshoot a challenging problem.
10. Optimizer – good luck.  Find some good articles and study them (swim or drown).  There a only a handful of stuff you can tweak (I am still learning but there are expert-level gurus via this Posting that can help you).  But you don’t have the 1099 trace anymore.
11.  Query hint – Oracle has hundreds of hints that you can use – this is a lifetime learning for those in Oracle DB fields but Postgres query hint is very minimal.  And your hand it tight when there are production query performance issue.
12.  Profile query – I am not sure about the open source Postgres.  We are still working with AWS Aurora Postgres internal development team to enhance their QPM product.
13.  Query plan flipping – I can’t speak for open source Postgres.  But AWS Aurora Postgres finally track query plan id on 14.11 and above.
14.  And more that I missed.

Thank you
Kam
p/s:  We didn’t use pg_largeobjects. We use byteA.  We ran into issues with > ~ 500 MB (out of memory) and we ended up “chunking” them into multiple rows for any lob size that is bigger than > 500 MB.  (developer code changes).

From: Raphael Salguero Aragón <[email-masked]>
Sent: Monday, January 27, 2025 7:08 AM
To: Rajesh Kumar <[email-masked]>
Cc: Ron Johnson <[email-masked]>; Pgsql-admin <[email-masked]>
Subject: [EXT] Re: Oracle to postgres migration

External Email: Use caution with links and attachments.

Hi Rajesh

Rajesh Kumar <[email-masked]<mailto:[email-masked]>> schrieb am Mo. 27. Jan. 2025 um 11:13:

With regards to lo, is there any difficulty if we have rowsize > 1gb
For most cases, I would recommend to migrate lobs > 1gb into pg_largeobjects. The way of accessing those lobs will change (also for the application)
This could be done with a bit of python scripting. I’m not sure if there is a option within ora2pg meanwhile.

Regarding the sizes in general, you can check out below article:
https://www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained<https://urldefense.com/v3/__https:/www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained__;!!GFN0sa3rsbfR8OLyAw!Yt68Lgn7aqIhuS_1QNhxOPNgjVm4dhSDEG4IOJuNdxuA14-UjygDpSVcRDpUw-7rThGjdyWYDQUg3MI-VV2oT9Zr_QYLMGTpPjwtX9LjNA$>

Best regards
Raphael

On Mon, 27 Jan 2025, 15:41 Rajesh Kumar, <[email-masked]<mailto:[email-masked]>> wrote:

On Mon, 27 Jan 2025, 15:40 Ron Johnson, <[email-masked]<mailto:[email-masked]>> wrote:
I migrated a 12TB Oracle db that was mostly LOB objects into an 8TB PG database.  LOBs loaded into bytea columns.
One thing which I did not do, but should have, was have ora2pg convert NUMBER(38,0) values to BIGINT.

We just used ora2pg to convert data; the app developer rewrote all of the stored procedures, functions, triggers, etc.

On Mon, Jan 27, 2025 at 4:31 AM Rajesh Kumar <[email-masked]<mailto:[email-masked]>> wrote:

On Mon, 27 Jan 2025, 14:52 Julien Rouhaud, <[email-masked]<mailto:[email-masked]>> wrote:
Hi,

ora2pg is probably the best tool for your task.  And yes it does provide
estimates for the migration efforts, see
https://ora2pg.darold.net/documentation.html#Migration-cost-assessment<https://urldefense.com/v3/__https:/ora2pg.darold.net/documentation.html*Migration-cost-assessment__;Iw!!GFN0sa3rsbfR8OLyAw!Yt68Lgn7aqIhuS_1QNhxOPNgjVm4dhSDEG4IOJuNdxuA14-UjygDpSVcRDpUw-7rThGjdyWYDQUg3MI-VV2oT9Zr_QYLMGTpPjz82ppWUw$>.

---

From: "Wong, Kam Fook (TR Technology)" <[email-masked]>
Date: Mon, 27 Jan 2025 19:07:31 +0000

Adding to the list:

14.  Study up locking (better yet test it yourself and select * from pg_locks/pg_stat_activity) and commit/auto commit and the behaviors of app impact.
15.  Study up autovacuum (vs Oracle stats gathering) and the various parameters that trigger the autovaccum to run.  And you should consider set up monitoring the autvacuum/why it didn’t run/why it was out of your expectations.

Thank you
Kam
From: Wong, Kam Fook (TR Technology)
Sent: Monday, January 27, 2025 12:29 PM
To: Raphael Salguero Aragón <[email-masked]>; Rajesh Kumar <[email-masked]>
Cc: Ron Johnson <[email-masked]>; Pgsql-admin <[email-masked]>
Subject: RE: [EXT] Re: Oracle to postgres migration

Rajesh,

Thank you
Kam

From: Raphael Salguero Aragón <[email-masked]<mailto:[email-masked]>>
Sent: Monday, January 27, 2025 7:08 AM
To: Rajesh Kumar <[email-masked]<mailto:[email-masked]>>
Cc: Ron Johnson <[email-masked]<mailto:[email-masked]>>; Pgsql-admin <[email-masked]<mailto:[email-masked]>>
Subject: [EXT] Re: Oracle to postgres migration

Hi Rajesh

Best regards
Raphael

---

From: Sam Stearns <[email-masked]>
Date: Tue, 28 Jan 2025 08:05:38 -0800

We're in the middle of a migration, also.  That's a great overview, Kam.
Thank you.  We've got schema and data migrated using Ora2pg.  We're now
looking at using HexaRocket to keep Postgres in sync with Oracle.  Do you
have any advice on HexaRocket or other sync tools?

Thanks,

Sam

On Mon, Jan 27, 2025 at 11:07 AM Wong, Kam Fook (TR Technology) <
[email-masked]> wrote:

> Adding to the list: 14. Study up locking (better yet test it yourself and
> select * from pg_locks/pg_stat_activity) and commit/auto commit and the
> behaviors of app impact. 15. Study up autovacuum (vs Oracle stats
> gathering) and the various parameters
> ZjQcmQRYFpfptBannerStart
> This Message Is From an External Sender
> This message came from outside your organization.
>
> ZjQcmQRYFpfptBannerEnd
>
> Adding to the list:
>
>
> 14.  Study up locking (better yet test it yourself and select * from
> pg_locks/pg_stat_activity) and commit/auto commit and the behaviors of app
> impact.
> 15.  Study up autovacuum (vs Oracle stats gathering) and the various
> parameters that trigger the autovaccum to run.  And you should consider set
> up monitoring the autvacuum/why it didn’t run/why it was out of your
> expectations.
>
>
>
> Thank you
>
> Kam
>
> *From:* Wong, Kam Fook (TR Technology)
> *Sent:* Monday, January 27, 2025 12:29 PM
> *To:* Raphael Salguero Aragón <[email-masked]>; Rajesh
> Kumar <[email-masked]>
> *Cc:* Ron Johnson <[email-masked]>; Pgsql-admin <
> [email-masked]>
> *Subject:* RE: [EXT] Re: Oracle to postgres migration
>
>
>
> Rajesh,
>
>
>
> We have done probably 1 thousand plus of Oracle DB migration to Postgres
> (and we still have Oracle and SQL Servers).  But I don’t have the
> documentation to share – one I don’t have it.  Two, even if I have it I
> can’t share it due to company policy.  In a high level here are a few
> things to chew on (others please add and correct)
>
> 2.  Data migration – same as above.  If you are replicating your data
> online/ongoing from Oracle to Postgres with zero production downtime, be
> ready for “a lot/extremely busy” challenges.  You need a team just for this
> around the clock (lobs, data conflict resolution, performance, cascade
> delete and etc)
>
> 3.  Querries/store proc/trigger migration – you can find a 3rd party tool
> but you still need manual changes, tuning, and logic verification.  Plus
> Scale testing.
>
> 9.  Oracle AWR is no longer available.  One to two years ago I wasn’t able
> to find a comparable product.  We hire a brilliant contractor/consultant to
> write our custom snap that runs continuously (and prunes off the aged
> data).  We also use 3rd party db tools and those alone often time is not
> sufficient to troubleshoot a challenging problem.
> 10. Optimizer – good luck.  Find some good articles and study them (swim
> or drown).  There a only a handful of stuff you can tweak (I am still
> learning but there are expert-level gurus via this Posting that can help
> you).  But you don’t have the 1099 trace anymore.
>
> 11.  Query hint – Oracle has hundreds of hints that you can use – this is
> a lifetime learning for those in Oracle DB fields but Postgres query hint
> is very minimal.  And your hand it tight when there are production query
> performance issue.
>
> 12.  Profile query – I am not sure about the open source Postgres.  We are
> still working with AWS Aurora Postgres internal development team to enhance
> their QPM product.
>
> 13.  Query plan flipping – I can’t speak for open source Postgres.  But
> AWS Aurora Postgres finally track query plan id on 14.11 and above.
>
> p/s:  We didn’t use pg_largeobjects. We use byteA.  We ran into issues
> with > ~ 500 MB (out of memory) and we ended up “chunking” them into
> multiple rows for any lob size that is bigger than > 500 MB.  (developer
> code changes).
>
>
>
> *From:* Raphael Salguero Aragón <[email-masked]>
> *Sent:* Monday, January 27, 2025 7:08 AM
> *To:* Rajesh Kumar <[email-masked]>
> *Cc:* Ron Johnson <[email-masked]>; Pgsql-admin <
> [email-masked]>
> *Subject:* [EXT] Re: Oracle to postgres migration
>
>
>
> *External Email:* Use caution with links and attachments.
>
>
>
> Hi Rajesh
>
>
>
> Rajesh Kumar <[email-masked]> schrieb am Mo. 27. Jan. 2025
> um 11:13:
>
> <https://urldefense.com/v3/__https:/www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained__;!!GFN0sa3rsbfR8OLyAw!Yt68Lgn7aqIhuS_1QNhxOPNgjVm4dhSDEG4IOJuNdxuA14-UjygDpSVcRDpUw-7rThGjdyWYDQUg3MI-VV2oT9Zr_QYLMGTpPjwtX9LjNA$>
>
>
>
> Best regards
>
> Raphael
>
>
>
>
>
> https://ora2pg.darold.net/documentation.html#Migration-cost-assessment
> <https://urldefense.com/v3/__https:/ora2pg.darold.net/documentation.html*Migration-cost-assessment__;Iw!!GFN0sa3rsbfR8OLyAw!Yt68Lgn7aqIhuS_1QNhxOPNgjVm4dhSDEG4IOJuNdxuA14-UjygDpSVcRDpUw-7rThGjdyWYDQUg3MI-VV2oT9Zr_QYLMGTpPjz82ppWUw$>
> .
>

-- 

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>