Re: [EXT] Re: Oracle to postgres migration

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) <
kamfook(dot)wong(at)thomsonreuters(dot)com> 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 <raphael(dot)salguero(at)enterprisedb(dot)com>; Rajesh
> Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
> *Cc:* Ron Johnson <ronljohnsonjr(at)gmail(dot)com>; Pgsql-admin <
> pgsql-admin(at)lists(dot)postgresql(dot)org>
> *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)
>
> 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 <raphael(dot)salguero(at)enterprisedb(dot)com>
> *Sent:* Monday, January 27, 2025 7:08 AM
> *To:* Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
> *Cc:* Ron Johnson <ronljohnsonjr(at)gmail(dot)com>; Pgsql-admin <
> pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Subject:* [EXT] Re: Oracle to postgres migration
>
>
>
> *External Email:* Use caution with links and attachments.
>
>
>
> Hi Rajesh
>
>
>
> Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com> 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, <rajeshkumar(dot)dba09(at)gmail(dot)com>
> wrote:
>
> Thank you all. As mush as more info is always appreciated by dearest
> admins
>
>
>
> On Mon, 27 Jan 2025, 15:40 Ron Johnson, <ronljohnsonjr(at)gmail(dot)com> 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 <rajeshkumar(dot)dba09(at)gmail(dot)com>
> wrote:
>
> 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, <rjuju123(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Mon, Jan 27, 2025 at 02:42:22PM +0530, Rajesh Kumar wrote:
> > 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 ?
>
> 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$>
> .
>
> In general the ora2pg documentation is really good, you should find the
> answer
> to all your questions there.
>
>
>
>
> --
>
> Death to <Redacted>, and butter sauce.
>
> Don't boil me, I'm still alive.
>
> <Redacted> lobster!
>
>

--

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>