Re: Oracle Replication / Migration Question

Oracle advanced replication has a feature in that you can turn off replication for a session. I don't have the doc's in front of me, but I use it on occasion when my databases get out of sync.

You can use this feature to do your deletes without replicating them to the 'Archive' site. Just turn off replication, delete from the master site, and them turn replication back on.

One question I have is what happens to your primary keys and unique indexes. Is it ever possible that a row that has been deleted from PROD, but still in Archive. would later have another row with the same key or unique index values? This wouldn't be a problem in PROD, but would obviously cause problems in Archive.

HTH,
Patrick

Originally posted by Randy
> > 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

--
Posted via http://dbforums.com