Randy
Sorry about the late response.
I've just completed an archive system similar (I think) to what you
describe.
Ours was part of a Data Warehouse/Reporting database.
We couldn't get replication to achieve what was required without impacting
on PROD.
We ended up copying PROD into ARCHIVE overnight using
parallel 'CREATE TABLE COPY_ABC AS SELECT * FROM ABC_at_PROD' statements.
We copied about 4 GBytes in 250 tables in 15 minutes. This was then
UPDATED/INSERTED
in ARCHIVE. The UPDATE/INSERT code took a further 3-4 hours to complete.
The code required Primary Keys on all tables and as 'buckeye234' pointed out
it
would not tolerate recycling Primary Keys. The code that performed this was
generated from the data dictionary.
eric
"Randy" <rvandehe_at_enzy.com> wrote in message
news:af46bb2c.0301241429.7ca33fc1_at_posting.google.com...
> > 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 Wed Jan 29 2003 - 05:25:42 CST
