Comments (17)
The default serializable transaction mode should do the work. Are you using multiprocess to extract data ?
from ora2pg.
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
from ora2pg.
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...
from ora2pg.
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.
from ora2pg.
Well, if you can give me a patch that explicitly create a transaction, I will gladly test it.
from ora2pg.
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...
from ora2pg.
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,
from ora2pg.
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?
from ora2pg.
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.
from ora2pg.
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.
from ora2pg.
Turning AutoCommit off didn't solve it. Trying readonly
level now.
from ora2pg.
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.
|
from ora2pg.
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.
from ora2pg.
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.
from ora2pg.
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!
from ora2pg.
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.
from ora2pg.
Created a new issue #766, thanks! We don't have any OOM errors, the system has plenty of memory.
from ora2pg.
Related Issues (20)
- Typo: wrong database handle name in subroutine _count_source_rows HOT 2
- ORA-00942 error with "-t TEST_COUNT" if Oracle table needs to be quoted HOT 1
- Question: Is it possible to use a different/arbitrary DBI driver for ora2pg? HOT 2
- The "-t TEST_COUNT" is not properly using the real Oracle row counts when comparing
- Bug in section [TEST UNIQUE CONSTRAINTS COUNT] of the "-t TEST" option
- Multi table copy. Error: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION ...) at C:/strawberry/perl/vendor/lib/DBD/Oracle.pm line 364 HOT 1
- "Permission denied in schema public" with Postgres 15 HOT 2
- Is the DELETE option available from the command line?
- Small enhancement: make differences reported by the "-t TEST" option's output easier to identify by adding a line prefix HOT 2
- Comparisons using the "-t TEST" option sometimes double counts the number of NOT NULL constraints
- problem during execution of ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt HOT 2
- Progress bar shows wrong totals and percentage when using WHERE
- Using the COPY Option using DSN with no host or port HOT 1
- pg_initial_command ignored when using TEST_DATA HOT 2
- problem during execution of ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt - part 2
- [MySQL] Regression with hash partitions
- Bug introduced with Commit 5d0ad02
- [Oracle] NOT NULL constraint shows twice in the exported schema HOT 1
- not able to generate error free procedure migration from oracle to postgres HOT 2
- Issues with SHOW_TABLE not showing all tables in oracle HOT 12
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from ora2pg.