BLOB Migration Issue

[guptasai675]

BLOB Migration Issue

[guptasai675]

Hi darold,

Could you please provide your comments .

[darold]

Hi, with 64GB of memory you shoud not experiencing an OOM even with a higher DATA_LIMIT. What do you mean by "using OID datatype" are you talking about the --blob_to_lo option? By default BLOB are translated into bytea by Ora2Pg.

When a table have BLOB and the parameter BLOB_LIMIT is set Ora2Pg will use it, so here you will export 5 row at a time which is low. What is the maximum size of a BLOB in this table?

[darold]

And also what is the ora2pg command you are using?

[guptasai675]

Hi Darold,
The Maximum length of the BLOB is 3GB
Yes am using --blob_to_lo option and below is the command were used for export
nohup ora2pg -t COPY -o data_DOC_STATUS_CONTENTS.sql -b ${WORK_DIR}/${P_CODE}/data -c ${WORK_DIR}/${P_CODE}/config/ora2pg.conf -l ${LOG_1} -d --blob_to_lo &

[darold]

I think first that you should use direct import to PostgreSQL and not output to file. You should also parallelize the export from Oracle using the -J command line option provide that you have a column of NUMBER data type that has a unique constraints (PK or unique index) defined on this table.

[guptasai675]

Yes, that's right. But we can't import directly we have some network restriction on between the both datacenters so we're exporting to a local file and trying the --J option, but we're still getting the same problem. ORA-01062: define buffer any memory

when I trigger export , the export it self aborting with following error ORA-01062: define buffer any memory

[darold]

I think the problem is not related to Ora2Pg but to Oracle, the OOM occurs at Oracle side. I don't know the Oracle version and the available memory on this server but you should look at Oracle side on what happen. You should also try to export using a WHERE clause in ora2pg.conf to extract a single row with a huge BLOB to see if this is the full extraction that has problem or just even a single line extraction reports an OOM.

[guptasai675]

That sounds good. I'll check the Oracle server end. Like you said, I'll try with a single export row.