Inconsistent export

[darold]

Inconsistent export

[darold]

The default serializable transaction mode should do the work. Are you using multiprocess to extract data ?

[lpenet]

No, I am using a single call to ora2pg.

My conf looks like :

TRANSACTION committed
SKIP check
USER_GRANTS 1
EXPORT_SCHEMA 0
DROP_FKEY 1

For data export, ora2pg is called with a command like

ORA2PG="ora2pg -q -s $ORACLE_DSN -u $ORACLE_USER -w $ORACLE_PWD"
$ORA2PG -t INSERT -c $ORA2PG_CONF -o $DB_DATA_SQL

[lpenet]

Well, maybe I was too quick to blame ourselves. Even if our data producing app does not use transactions to insert coherent data, ora2pg should get a coherent view of all tables if it uses a transaction. So, I reopen...

[darold]

Using TRANSACTION serializable should be enough. Or maybe I have to explicitly open a transaction in Oracle before setting "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE". I was thinking that DBD::Oracle was opening a transaction by default, but maybe I'm wrong.

[lpenet]

Well, if you can give me a patch that explicitly create a transaction, I will gladly test it.

[lpenet]

When I use the following patch :

$ diff -c /usr/local/share/perl/5.12.4/Ora2Pg.pm Ora2Pg.pm.old 
*** /usr/local/share/perl/5.12.4/Ora2Pg.pm  2013-12-10 13:38:38.595083346 +0100
--- Ora2Pg.pm.old   2013-12-10 13:38:30.854993343 +0100
***************
*** 529,537 ****
  {
    my ($self, $outfile) = @_;

-         my $transaction_query = $self->{dbh}->prepare("START TRANSACTION");
-   $transaction_query->execute;
- 
    # Init with configuration OUTPUT filename
    $outfile ||= $self->{output};
    if ($self->{output_dir} && $outfile) {
--- 529,534 ----
***************
*** 573,580 ****
        }

    }
!   $myrollback_query = $self->{dbh}->prepare("ROLLBACK);
!   $myrollback_query->execute;
  }


--- 570,576 ----
        }

    }
! 
  }

... it works ok.

Hope this helps...

[darold]

Thanks for the patch. I've detected a regression with the Oracle connection on data export and fixed it. Could you please test latest code. If it don't solves the issue I will apply your patch but I think it may be enough.

Best regards,

[wilemyvu]

I'm having the same issue. After I finally imported data into postgres and started adding FKs, some of them failed because table A references a non-existent record in table B.

I noticed this at the export stage, as ora2pg was showing export of more than 100% records, but I thought maybe it was a counting error.

Anyway, the transaction level is serialised and it's 1 thread (I think, as I never used the multi-job parameters for ora2pg) and I'm getting inconsistent data.

And I'm using the 872ad5b commit build.

Darold, would you suggest just applying the Ipenet's patch?

Tried disabling AutoCommit in dbh and commenting out begin_work. That way, theoretically, the data export should be in one long transaction, if I didn't miss anything in the docs.

[darold]

Ora2Pg can show en export upper than 100% or lower because it the row count is base on an estimation from the Oracle statistics. If the number of rows is still growing during the export you will then have more than 100%.

Let me know if your workaround with autocommit and begin_work solves your issue.

About the last source code, use the following:

wget https://github.com/darold/ora2pg/archive/master.zip

then

unzip master.zip
cd ora2pg-master/
perl Makefile.PL
make
sudo make install

This will install latest development code.

Turning AutoCommit off didn't solve it. Trying readonly level now.

[lpenet]

It was a quick and dirty patch. It works, but is not elegant nor efficient.

I hope Darold will provide you something better.

Ludovic

On 2 avril 2014 13:01:31 UTC+02:00, Yuri Ushakov [email protected] wrote:

I'm having the same issue. After I finally imported data into postgres
and started adding FKs, some of them failed because table A references
a non-existent record in table B.

I noticed this at the export stage, as ora2pg was showing export of
more than 100% records, but I thought maybe it was a counting error.

Anyway, the transaction level is serialised and it's 1 thread (I think,
as I never used the multi-job parameters for ora2pg) and I'm getting
inconsistent data.

And I'm using the 872ad5b commit
build.

Darold, would you suggest just applying the Ipenet's patch?

---

Reply to this email directly or view it on GitHub:
#45 (comment)

Envoyé de mon téléphone Android avec K-9 Mail. Excusez la brièveté.
|
| AVANT D'IMPRIMER, PENSEZ A L'ENVIRONNEMENT.
|

[wilemyvu]

Well, my bad. There were missing records, that's true, but not because of transaction isolation, but because I missed a few errors about LOB's being over 1mb. I mean I saw them, but I thought they were being truncated, but it turns out these are precisely the records that didn't make it into the exported dump. Oh well, at least it's resolved, sorry for the trouble.

[console-beaver]

I believe we are hitting the same issue when doing parallel extraction from oracle (ora2pg -P 32) with the most recent ora2pg code (wget https://github.com/darold/ora2pg/archive/master.zip as of two days ago). Some of the records are missed during extraction as evidenced by errrors when creating FK constraints. The source oracle database doesn't have any transactional activity (all apps are down) during extraction. Anything specifically we need to check in our ora2pg.conf file for the parallel extraction to work correctly? Please let us know.

[console-beaver]

Just to add to the comment above - the source oracle database has the same FK constrainsts enabled and the missing records are in there. However, they never makes it into extraction output file. So I assume there might be some issues with parallel extraction logic or our config file.Any suggestion where to start looking at is greatly appreciated!

[darold]

It is best to open a new issue than awake a dead one. I think you are missing some errors reported by Ora2Pg or some OOM, you might reduce the number of process or reduce DATA_LIMIT value.

[console-beaver]

Created a new issue #766, thanks! We don't have any OOM errors, the system has plenty of memory.