Database Out Of Sync

Hi, I'm facing a database out of sync problem in our application and would like the communities help. We have 30 tables in Oracle database and 30 replica tables in Informix (similar schema). We have a process that unloads daily inserts/updates/deletes in Oracle and loads it into the Informix tables. These tables have gone out of sync over the years and requires to be resync'ed asap. On Informix tables, there are insert/update/delete triggers which populate the changes in a corresponding temporary table, hence any activity in the Informix tables fires these triggers and updates the same in temporary tables. A few tables have records in the order of 100,000,000 and other tables also have large number of rows. Our problem is to re-sync the Informix tables with Oracle tables in such a way so that triggers on the Informix tables are fired for the actual records Inserted/Updated/Deleted in the Informix tables (i.e. the actual data which was out of sync). This is required to be done in this fashion so that the temporary tables have only the missed out data over the years and not the full load after the re-sync has been done. This activity would require an outage on our system hence needs to be done in the most efficient manner. I would appreciate your help in guiding me towards a effective solution.

ndmathur@gmail.com napisaďż˝(a): > Hi, > > I'm facing a database out of sync problem in our application and would > like the communities help. > > We have 30 tables in Oracle database and 30 replica tables in Informix > (similar schema). We have a process that unloads daily > inserts/updates/deletes in Oracle and loads it into the Informix > tables. These tables have gone out of sync over the years and requires > to be resync'ed asap. > > On Informix tables, there are insert/update/delete triggers which > populate the changes in a corresponding temporary table, hence any > activity in the Informix tables fires these triggers and updates the > same in temporary tables. > > A few tables have records in the order of 100,000,000 and other tables > also have large number of rows. Our problem is to re-sync the Informix > tables with Oracle tables in such a way so that triggers on the > Informix tables are fired for the actual records > Inserted/Updated/Deleted in the Informix tables (i.e. the actual data > which was out of sync). This is required to be done in this fashion so > that the temporary tables have only the missed out data over the years > and not the full load after the re-sync has been done. > > This activity would require an outage on our system hence needs to be > done in the most efficient manner. > > I would appreciate your help in guiding me towards a effective > solution. > I've wrote utility for syncronising Informix databases. It is available in IIUG downloads as load2 archive, utility named dbdiff (see description in dbdiff.txt file). Possible you can use it. The only condition is that you have to get full unload of Oracle tables in Informix unl format. Then dbdiff can compare these unloads with Informix database. As a result you will obtain file with missing deletes/inserts/updates. I do not have information if this works for other people. I know that this utility works ok for me.