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
1.03k stars 341 forks source link

Application stalls with "server closed the connection unexpectedly" error #1838

Open taneraruk opened 3 weeks ago

taneraruk commented 3 weeks ago

I encountered an issue when running a migration of more than 3 million records containing BLOB data, using 3-5 jobs with specified data limits. Although all data is migrated successfully to PostgreSQL, the application cannot proceed to the next steps. It appears to stall, showing the following debug logs:

[2024-10-31 15:32:59] [========================>] 1/1 tables (100.0%) end of scanning.
[2024-10-31 18:04:00] [========================>] 1078136/1 rows (107813600.0%) Table ENTITY_VALUE-part-1 (9060 sec., 118 recs/sec)                     
[2024-10-31 18:04:00] [========================>] 1078676/1 rows (107867600.0%) Table ENTITY_VALUE-part-2 (9060 sec., 119 recs/sec)                     
[2024-10-31 18:04:00] [========================>] 1078583/1 rows (107858300.0%) Table ENTITY_VALUE-part-0 (9060 sec., 119 recs/sec)                     
[2024-10-31 18:04:00] [========================>] 3235395/1 rows (323539500.0%) on total estimated data (9060 sec., avg: 357 tuples/sec)                    
FATAL: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
Aborting export...

After this point, the application doesn’t proceed further, despite all records appearing correctly in the database.

Expected Behavior: The migration should conclude, allowing the application to continue executing any subsequent steps without error or stalling.

Environment:

ora2pg Version: 24.1
PostgreSQL Version: 15
Oracle Client Version: 12.2
OS: Openshift Linux
Pod limit: 20Gb

Config parameters:

DATA_LIMIT 250
LONGREADLEN 15000000
DEFINED_PK  ENTITY_VALUE:id ENTITY_VALUE:ROUND(id)
FILE_PER_CONSTRAINT 1
FILE_PER_INDEX  1
FILE_PER_FKEYS  1
FILE_PER_TABLE  1
FILE_PER_FUNCTION   1
TRUNCATE_TABLE  1
STOP_ON_ERROR   1

#
LOB_CHUNK_SIZE  3000000
KEEP_PKEY_NAMES 1
DISABLE_PARTITION   1
USE_RESERVED_WORDS  0
DISABLE_UNLOGGED    1
REWRITE_OUTER_JOIN  1

# 
JOBS 3
ORACLE_COPIES 3

Additional Context: I verified that all data has successfully migrated to PostgreSQL. Any insights on why the application might stall at this point, or adjustments to ensure full migration completion without stalling, would be helpful.

Edit: When I check the database sessions, after 1,5 hours all data copied and connections are closed except 1. Seems application does not close all connections.

darold commented 6 days ago

Have you checked at server side if there is a crash? The message FATAL: server closed the connection unexpectedly is emitted server side. I could be no space left for example.

taneraruk commented 5 days ago

yes we checked we dba both oracle and postgresql side, there is enough space and no crash.