Re: Oracle Replication / Migration Question

"Randy" <rvandehe_at_enzy.com> wrote in message news:af46bb2c.0301240533.2295e8b2_at_posting.google.com...
> >
> > I'm a bit confused. There seem to be two things going on here. First,
> > you need to populate ARCHIVE with all the relevant data that is already
> > in PROD, and then clean out PROD. That's a one-off job.
> >
> > Next, you seem to be saying that as records are added into PROD, you
> > want those replicated to ARCHIVE, with a periodic cleanout of those
> > records from PROD. That's an on-going thing.
> >
> > Is that right?
>
>
> That is exactly right. We'd copy PROD to ARCHIVE. Then we'd delete
> old information from PROD. Then from that point forward, we want to
> replicate all updates and inserts to ARCHIVE.

OK, sorry to clarify again. But how come you are allowed to perform updates on rows which have been copied to ARCHIVE? If the deal was to periodically transfer records into ARCHIVE from PROD and then delete them from PROD, I don't see how they can then be updated.

And if you mean that a new record is inserted, and later modified with UPDATEs, then -being a new record- it won't have been transferred to ARCHIVE yet, and therefore the question of 'replicating' updates won't need to arise.

[snip]

> Correct. ARCHIVE doesn't need to be kept in near-real-time sync.
> Replication might be overkill, but will it even work? as I don't
> want to replicate DELETES.
>
> Also, I haven't looked in to SQL Loader yet, but export/import
> doesn't look like it will work because 1) common records between PROD
> and ARCHIVE will error on the import because they already exist in
> ARCHIVE
Well, this is why I'm asking all these questions. Will they 'already exist'? The deal was: when a record in PROD passes its 'sell by' date, it is copied to ARCHIVE and deleted from PROD. No? In which case, once the initial data load into ARCHIVE has taken place, and relevant PROD rows deleted, there won't be a situation of a row in PROD duplicating a row in ARCHIVE. Either it's current, in which case it's in PROD. Or it's old, and is in ARCHIVE. But it can't be in both, if what you've written and what I've understood you to mean are even approximately close.

> and 2) updates (in PROD) to common records between PROD and
> ARCHIVE will not migrate to ARCHIVE using import.

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:

"Basically, we don't want to lose our history data, but we don't want it in the current instance of Oracle"

Now either data is history (=in ARCHIVE), or it's current (=in PROD).

So how come it can suddenly be both??

I'm not picking on you, btw: just trying to clarify the problem before attempting to provide a solution!

Regards
HJR Received on Fri Jan 24 2003 - 08:53:10 CST