Exporting COPY leads to arbitrarily closed Oracle connection

[darold]

Exporting COPY leads to arbitrarily closed Oracle connection

[darold]

Thanks for the report. Please post here the exact error message reported by ora2pg, the version of ora2pg you are using and the command line used for this export.

Regards,

ora2pg v12.1
Oracle 10g

ora2pg -c data-only.conf

The config is:

ORACLE_HOME /home/ushakov/instantclient_10_2
ORACLE_DSN  dbi:Oracle:host=blah;sid=blah
ORACLE_USER blah
ORACLE_PWD  blah
USER_GRANTS     1
DEBUG               0
EXPORT_SCHEMA       0
SCHEMA              BLAH
CREATE_SCHEMA       1
COMPILE_SCHEMA      0
EXPORT_INVALID      0
TYPE                COPY
EXCLUDE     schema_version
DATA_LIMIT  10000
MODIFY_TYPE 1100-characters-removed
PRESERVE_CASE       0
OUTPUT              output.sql
BZIP2
GEN_USER_PWD        0
FKEY_DEFERRABLE     0
DEFER_FKEY  0
DROP_FKEY   0
DROP_INDEXES        0
PG_NUMERIC_TYPE     0
PG_INTEGER_TYPE     1
DEFAULT_NUMERIC bigint
KEEP_PKEY_NAMES 0
DISABLE_TRIGGERS 0
NOESCAPE    0
BINMODE             utf8
DISABLE_SEQUENCE    0
PLSQL_PGSQL 1
ORA_RESERVED_WORDS  audit,comment,cmax,cmin,oid,tableoid,xmin,xmax,ctid
FILE_PER_CONSTRAINT 0
FILE_PER_INDEX              0
FILE_PER_TABLE      0
TRANSACTION serializable
PG_SUPPORTS_WHEN            1
PG_SUPPORTS_INSTEADOF       1
FILE_PER_FUNCTION   0
TRUNCATE_TABLE      0
FORCE_OWNER 0
STANDARD_CONFORMING_STRINGS 1
JOBS                1
ORACLE_COPIES       1
ALLOW_CODE_BREAK    1
XML_PRETTY  0
FDW_SERVER  orcl
DISABLE_COMMENT         0
USE_RESERVED_WORDS  0
PKEY_IN_CREATE              0
REPLACE_AS_BOOLEAN 1500-characters-removed
BOOLEAN_VALUES      1:0
NULL_EQUAL_EMPTY    1
EXTERNAL_TO_FDW             1
ESTIMATE_COST               0
COST_UNIT_VALUE             5
DUMP_AS_HTML                0
STOP_ON_ERROR               1
TOP_MAX                     10
ALLOW_PARTITION             PARTNAME
USE_TABLESPACE              0
PG_SUPPORTS_MVIEW   1
REORDERING_COLUMNS  0

Unfortunately, when I needed the error to reproduce, it wouldn't. I got other errors on this dump - the snapshot is too old, which is normal - but not the disconnections. I'll try more COPY dumps and see if the error gets back.

[darold]

Ok, I have tested with a dump of 39GB without any problem. Note that you can use multiprocess to dump to file too, this should increase the export speed, for example if you have 8 cores on the server:

ora2pg -c data-only.conf -t COPY -j 8

I usually export table with BLOB or with lot of CLOB separately as DATA_LIMIT to 10000 can be too high and you can reach OOM. The second time I export those table using a DATA_LIMIT to 500.

For example let's say you have BINTABLE1 et BINTABLE2 that have huge BLOB entry, I would proceed as follow:

ora2pg -c data-only.conf -t COPY -j 8 -L 25000 -e "BINTABLE1,BINTABLE2" -o outfile1.sql

end for binary data:

ora2pg -c data-only.conf -t COPY -j 4 -L 100 -a "BINTABLE1,BINTABLE2" -o outfile2.sql

This is an example, look at this presentation for some other advices: http://ora2pg.darold.net/ora2pg-best-practices.pdf

Regards,

Got it. Just had to fix the undo tablespace issue first. Here it is:

[========================>] 14220517/13238128 rows (107.4%) Table PP$PROFILES (19062.4 recs/sec)
[=====>                   ]  27464693/128921597 rows (21.3%) on total data (avg: 14215.7 recs/sec)
DBD::Oracle::st fetchall_arrayref failed: ORA-03113: end-of-file on communication channel (DBD ERROR: OCIStmtFetch) [for Statement "SELECT "ID",to_char("UPDATED", 'YYYY-MM-DD HH24:MI:SS'),"STATUS","FROM_PROFILE","TO_PROFILE" FROM PAYPROC.PP$PROFILE_CONNECTIONS a"] at /usr/local/share/perl5/Ora2Pg.pm line 6450.
[====>                    ]  4364000/25172227 rows (17.3%) Table PP$PROFILE_CONNECTIONS (22848.2 recs/sec)
[=========>               ]  52636920/128921597 rows (40.8%) on total data (avg: 24793.6 recs/sec)
DBD::Oracle::db prepare failed: ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute/Describe) [for Statement "SELECT "ID","ENABLED","LOGIN","NAME","PASSWORD","CLASS_NAME","URL",to_char("CDAT", 'YYYY-MM-DD HH24:MI:SS'),"TYPE" FROM PAYPROC.PP$PROVIDERS a"] at /usr/local/share/perl5/Ora2Pg.pm line 6435.
FATAL: ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute/Describe)
Aborting export...

If it were an OOM error, do you mean on the server's or the ora2pg's side? EDIT: It is suspicious that this error only occurs on COPY, not on INSERTs.

Nope, it happened on INSERT as well. Closing issue.

[darold]

This don't seem to be an ora2pg issue. Ora2Pg is loosing the Oracle connection during data retrieving, which is only possible if the cause is external to ora2pg. Is there's a Firewall between the Oracle database and the machine where ora2pg is running? Do you have some process or oracle settings that kill too longer session ? An other external source can be the kernel OOM killer, do you have such message in the log ?

dmesg | egrep -i 'killed process'

or

grep -i "Killed process" /var/log/syslog

You can look at google search result at https://www.google.fr/#q=ORA-03113+end-of-file+on+communication+channel to try to find the reason of this isssue.

You can also try to update DBD::Oracle if you don't have a recent version.

[darold]

I've tried again to export my 30GB of data, it tooks 1h30 without any problem.

DBD::Oracle is 1.70, the latest according to CPAN. There are no killed messages in /var/log/messages. I will have to try running ora2pg on the same host as Oracle itself to avoid possible network problems. If that fails, I suspect I will have to investigate Oracle's mechanisms that could be killing the session. Thanks.