Re: SOS: Balancing two tables

In article <7dgj20$9u7$1_at_infosun2.rus.uni-stuttgart.de>,   "Alexander Lurk" <lurk_at_ike.uni-stuttgart.de> wrote:
> Hello Experts,
>
> I have a problem, which I have to solve on monday.
> There is a HP-UX based Oracle Database V7.3 which collects information in
> several tables.
> Another NT-based system shall get automatically in dayly periods the changes
> of about 6-8 tables.
> The contents of the tables mnay be 20.000 and more tuples, the dayly changes
> affect about 10 up to 100 rows depending on the tables. Which number of
> records forbits to make a complete copy instead of finding out and
> inserting only the changed rows?
> Which is the best way to get these changes inserted in the NT Oracle
> database?
> We have no attribute to mark the new inserts in the HP database tables. Nor
> do I know at the moment if there is a chance to use audit. Which way is the
> best to find out the differences between the two tables before the insert
> starts. And what do I have to check, when new inserts are done during this
> operation to get I think, this is a really solved problem and there should
> be solutions out there, which can help me not to invent exisitng algorithms.
>
> Please can you email me your suggestions asap? As I told before, on monday I
> have to face and solve the problem.
> Please email to tischendorf_at_arkusa.de because I do not connect to news very
> often.
>
> Thanks for any advice
>
> Manfred Tischendorf
>
>

You didn't really say if the tables on the two databases are the same or not, but why don't you look at using a read-only snapshot? That would migrate only the changed rows after the inital complete table had been copied. In fact, you could simply allow the snapshot to update a duplicate table in the target database, and use the snapshot LOG file on the source database to identify which rows have changed. Just be aware that the log file will be cleared out automatically after a successful snapshot has been made. If you check this log file in the evening, just before the snapshot occurs, you will have all the changes made during that day. Check out the Oracle Distributed Data manual (Vol I, I believe) for information regarding the snapshot process and how the log file works.

Tony Rodgers

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 26 1999 - 14:39:08 CST