search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Ora2Pg migration Speed reducing after 20-30 mins of migration start time. #1722

Closed ashvin1991 closed 5 months ago

ashvin1991 commented 6 months ago

We are trying to migrate some tables(including BLOBs) from the Oracle(19c) database to the PostgreSQL database(14+) database using the Ora2pg migration tool but have low/reduced data copy speed. The Oracle database has a RHEL 7.9 OS with 4 CPU and 16GB RAM where the CPU is shared with other Apps as well. The PostgreSQL server is configured with 4 CPU(dedicated), and 16 GB RAM. Both Oracle and PostgreSQL servers support parallel-multicore processing but even then migration speeds are as low as mentioned below : Starts with acceptable speed but within 30 mins of migration it slows down. e.g. [2023-12-2 14:06:02][=> ] 116843/2656809 total rows (4.4%) - (78 sec., avg: 1497 recs/sec), TABLE_NAME in progress [2023-12-2 14:15:00][==> ] 273564/2656809 total rows (10.3%) - (616 sec., avg: 444 recs/sec), TABLE_NAME in progress With the recommendation from ora2pg official documentation, we tried to alter below parameters:

  1. JOBS
  2. ORACLE_COPIES
  3. PARALLEL_TABLES
  4. PARALLEL_MIN_ROWS
  5. LOB_CHUNK_SIZE
  6. LONGREADLEN
  7. DATA_LIMIT, but couldn't reach the optimized speed for tables sizing between 1GB to 4GB and records ranging between 300000 to 4000000.
darold commented 5 months ago

You should separate the migration of tables without BLOB and tables with BLOB (or huge CLOB).