Thread: Oracle to Postgres
Howdy,
--
We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,
Sam
Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Sam,
I see lots of good info when Googling "oracle to postgres migrationn" in particular, there exists a tool, Ora2Pg.
---- Keith
On Thu, Jul 11, 2024, 18:02 Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Thanks for all the advice on the conversion stuff!
For the resource sizing I was chasing more CPU and memory analysis then giving recommendations on right-sizing the VM.
On Fri, Jul 12, 2024 at 10:53 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, Jul 11, 2024 at 8:02 PM Sam Stearns <sam.stearns@dat.com> wrote:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
The appliance doesn't tell you how much space your database uses???Anyway, we converted an 8TB on-prem Oracle 12 db (heavy on CLOBs) to RDS Postgresql 12; the resulting db was about 6TB. I'm sure that vanilla PG would be about the same size (or smaller now, since compression on dupes-allowed b-tree indices is much better now than in PG 12).
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
As Keith said, Google is your friend.That's how I found ora2pg, which we used in our conversion project (for the data only; someone had to rewrite all the stored procedures).If you use ora2pg, remember to tell it to convert all of the NUMERIC(38,0) columns to BIGINT.
Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Hi Sam
There are alot of open source and proprietary solutions available, but you should choose between them carefully as cost is not the only factor.\
EDB's MTK (Migration Toolkit) and Migration Portal are solid products for this purpose.
Regards
Kashif Zeeshan
On Fri, Jul 12, 2024 at 5:02 AM Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
On Thu, Jul 11, 2024 at 9:18 PM Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
Hi SamThere are alot of open source and proprietary solutions available, but you should choose between them carefully as cost is not the only factor.\EDB's MTK (Migration Toolkit) and Migration Portal are solid products for this purpose.RegardsKashif ZeeshanOn Fri, Jul 12, 2024 at 5:02 AM Sam Stearns <sam.stearns@dat.com> wrote:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Attachment
ora2pg for object migration
sqllines for data migration
above tools are tested
2024년 7월 12일 (금) 오전 5:02, Sam Stearns <sam.stearns@dat.com>님이 작성:
Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
저는 한국의 20년차 dba 입니다. 현재 oracle /tibero db , podstgres,/edb/mysql /mariadb , 서로 migration 할수 있는 java 된 프로그램 만들었습니다.
기능은 source db 와 targetdb 의 스키마 연동 ( table생성 ,pk 와 pk index 자동 생성 기능 , source db target db data migration 기능 까지 가능 합니다.
아직까지는 다양한 스키마로 테스트 안되었습니다. 저에게 oracle 11 버전 dump 파일을 보내주시면 테스트 해보고 소스 제공해 드리겠습니다.
2024년 7월 12일 (금) 오후 8:39, Muhammad Waqas <waqas.m@bitnine.net>님이 작성:
ora2pg for object migrationsqllines for data migrationabove tools are tested2024년 7월 12일 (금) 오전 5:02, Sam Stearns <sam.stearns@dat.com>님이 작성:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Ora2Pg Support Chatbot and Converter may be of some help too.
On Fri, Jul 12, 2024 at 9:13 AM ji hwan song <song7357@gmail.com> wrote:
저는 한국의 20년차 dba 입니다. 현재 oracle /tibero db , podstgres,/edb/mysql /mariadb , 서로 migration 할수 있는 java 된 프로그램 만들었습니다.기능은 source db 와 targetdb 의 스키마 연동 ( table생성 ,pk 와 pk index 자동 생성 기능 , source db target db data migration 기능 까지 가능 합니다.아직까지는 다양한 스키마로 테스트 안되었습니다. 저에게 oracle 11 버전 dump 파일을 보내주시면 테스트 해보고 소스 제공해 드리겠습니다.2024년 7월 12일 (금) 오후 8:39, Muhammad Waqas <waqas.m@bitnine.net>님이 작성:ora2pg for object migrationsqllines for data migrationabove tools are tested2024년 7월 12일 (금) 오전 5:02, Sam Stearns <sam.stearns@dat.com>님이 작성:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Regards,
Avi Vallarapu
Avi Vallarapu
CEO
HexaCluster Corp.
We did the safe thing, and sized the RDS Postgresql the same as the Linux system running Oracle 12. Turned out to be massively over-sized.
Your mileage will certainly vary, though.
PG12 used a lot less CPU and RAM than PG12.
On Thu, Jul 11, 2024 at 9:45 PM Sam Stearns <sam.stearns@dat.com> wrote:
Thanks for all the advice on the conversion stuff!For the resource sizing I was chasing more CPU and memory analysis then giving recommendations on right-sizing the VM.On Fri, Jul 12, 2024 at 10:53 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Thu, Jul 11, 2024 at 8:02 PM Sam Stearns <sam.stearns@dat.com> wrote:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
The appliance doesn't tell you how much space your database uses???Anyway, we converted an 8TB on-prem Oracle 12 db (heavy on CLOBs) to RDS Postgresql 12; the resulting db was about 6TB. I'm sure that vanilla PG would be about the same size (or smaller now, since compression on dupes-allowed b-tree indices is much better now than in PG 12).
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
As Keith said, Google is your friend.That's how I found ora2pg, which we used in our conversion project (for the data only; someone had to rewrite all the stored procedures).If you use ora2pg, remember to tell it to convert all of the NUMERIC(38,0) columns to BIGINT.--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Thank you, Ron!
On Fri, Jul 12, 2024 at 11:12 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
We did the safe thing, and sized the RDS Postgresql the same as the Linux system running Oracle 12. Turned out to be massively over-sized.Your mileage will certainly vary, though.PG12 used a lot less CPU and RAM than PG12.On Thu, Jul 11, 2024 at 9:45 PM Sam Stearns <sam.stearns@dat.com> wrote:Thanks for all the advice on the conversion stuff!For the resource sizing I was chasing more CPU and memory analysis then giving recommendations on right-sizing the VM.On Fri, Jul 12, 2024 at 10:53 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Thu, Jul 11, 2024 at 8:02 PM Sam Stearns <sam.stearns@dat.com> wrote:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
The appliance doesn't tell you how much space your database uses???Anyway, we converted an 8TB on-prem Oracle 12 db (heavy on CLOBs) to RDS Postgresql 12; the resulting db was about 6TB. I'm sure that vanilla PG would be about the same size (or smaller now, since compression on dupes-allowed b-tree indices is much better now than in PG 12).
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
As Keith said, Google is your friend.That's how I found ora2pg, which we used in our conversion project (for the data only; someone had to rewrite all the stored procedures).If you use ora2pg, remember to tell it to convert all of the NUMERIC(38,0) columns to BIGINT.--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
ora2pg certainly migrated all the data objects that we cared about (including CLOB and XML types). That was 2-3 years ago.
Didn't ask it to migrate plsql.
On Mon, Jul 22, 2024 at 8:38 AM Peter Garza <peter.garza@dat.com> wrote:
On Thu, Jul 11, 2024 at 9:18 PM Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:Hi SamThere are alot of open source and proprietary solutions available, but you should choose between them carefully as cost is not the only factor.\EDB's MTK (Migration Toolkit) and Migration Portal are solid products for this purpose.RegardsKashif ZeeshanOn Fri, Jul 12, 2024 at 5:02 AM Sam Stearns <sam.stearns@dat.com> wrote:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
Attachment
Hi Sam,
EnterpriseDB Migration Toolkit though won't analyze but can do migration of both objects and data. Migration Portal from EDB is good for assessing and migrating Schema. It provides you opportunity to reassess failed objects after manual repair as well.
You may use the Migration portal for schema objects and then MTK for data migration.
ora2pg is a free tool and good for both but have not used much.
About Analysis for resources, we have noticed that after migration db takes much less space as compared to Oracle.
Regards,
Muhammad Ikram
On Fri, Jul 12, 2024 at 5:02 AM Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
--
Muhammad Ikram
Thank you, Ron and Muhammad!
On Tue, Jul 23, 2024 at 4:21 AM Muhammad Ikram <mmikram@gmail.com> wrote:
Hi Sam,EnterpriseDB Migration Toolkit though won't analyze but can do migration of both objects and data. Migration Portal from EDB is good for assessing and migrating Schema. It provides you opportunity to reassess failed objects after manual repair as well.You may use the Migration portal for schema objects and then MTK for data migration.ora2pg is a free tool and good for both but have not used much.About Analysis for resources, we have noticed that after migration db takes much less space as compared to Oracle.Regards,Muhammad IkramOn Fri, Jul 12, 2024 at 5:02 AM Sam Stearns <sam.stearns@dat.com> wrote:Howdy,We have a project to migrate our Oracle databases that are hosted on an Oracle Database Appliance to Postgres hosted on a Linux VM. Questions:
- Is there a tool out there that we can use to analyze resource sizing on the Appliance that will give resource sizing recommendations for the VM?
- Is there a schema conversion tool or similar out there that we can use to convert Oracle ==> Postgres?
Thanks,Sam--Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com
--Muhammad Ikram
Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com