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)
- Mysql to postgresql migration HOT 9
- ERROR : DBI::db=HASH(0x29c1784f2e8)->disconnect invalidates 1 active statement handle HOT 3
- Wrong argument "extra_param OPAQUE" in function with OUT arguments (regression in release 24.3) HOT 1
- Wrong change in translation of "is not null" clause (regression in release 24.3) HOT 1
- Extract data query errorring out HOT 6
- Oracle column aliases (after TRIM function) are syntactically incorrect due to small bug introduced in commit 66f4fc37 HOT 1
- Issue in migrating from SQL Server to CloudSQL Postgres HOT 4
- Foreign server always generated with default port HOT 3
- Export data with CLOB is too slow HOT 7
- Generated export_schema.ps1 contains bash if else HOT 1
- Problems trying to use new parameter PARTITION_BY_REFERENCE=duplicate
- Oracle to Postgresql migration - issue with SECURITY DEFINER
- Filenames created incorrectly when exporting using the COPY option and parameter RENAME_PARTITION=1
- Getting a "FATAL: 2000000000 ... Failed to allocate OCIEnv" error when running ora2pg HOT 2
- Modify default mapping
- dlltool error HOT 5
- Issue using FDW_SERVER if the user has a dash in the name
- Add "script ending" message in certain stages to ensure that process completes vs is killed
- some Foreign keys are not generated
- What is the meaning of WARNING - we should not be there ? HOT 3
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.