Re: Oracle Replication / Migration Question

> And again, I have to say that I don't understand what you're trying to do,
> because I can't see how a row can be in both places. You originally said:
>

Yes, a record will be in both PROD and ARCHIVE at the same time. The reason being, is that the criteria (timing) for deleting old records from PROD from will be different from how often we replicate. Plus, there are some master tables that we will not ever delete from PROD but the updates must replicate to ARCHIVE. Here are a couple of examples.

1. an order is placed today in prod. It exists in PROD and not ARCHIVE. It is replicated to ARCHIVE this weekend. The purge from PROD process runs this weekend too for example. However, it only deletes orders older than 6 months. Therefore that order exists in both systems. However, the purge from PROD process may only run every month, where the replicate process should run more frequent. Actually, the more frequent the replicate process runs the better, so long as to not impact the performance of PROD too much.
2. The customer master record will never be purged from PROD. However, the outstanding customer balance field on the customer master table must be updated in the archive system. In fact, all time independant tables (e.g. master tables) will exist in both system.

IMO, replication is the best solution, provided that a) we can avoid replicating deletes and b) we can schedule the replication process to not run near-real-time, so as to not affect the performance of PROD too much. I thought of using insert/update triggers on each table, but this is a canned system with several hundred tables, plus the ARCHIVE system will hopefully be on another server.

Thanks for your replys. I'm more of a Sql Server guy than an Oracle guy.

-Randy Received on Fri Jan 24 2003 - 16:29:05 CST