Re: Seeking suggestion and advice: how to propagate change from one database to another database

gmei_at_my-deja.com wrote:

> Hi:
>
> I am looking for suggestions and advice on how to move data from one
> oracle database to another on daily basis.
>
> We have a production oracle db (let's call it "prod") on a unix box
> that runs 7x24. It supports a web front end application for shopping.
> Most of the data in the database will be loaded into memory of an
> application server when the application starts.
>
> We also need to provide the capability for people in the shop to change
> data in the database. They do not want to change the data in "prod"
> instance directly. They want to set up a separate unix box and have a
> copy of "prod" running (let's call it "prod_mirror"). They would like
> to make changes on "prod_mirror", QA the change and then propagate all
> the changes in "prod_mirror" to the "prod" instance. The changes
> involve about 10 tables (product, price, catalog, etc). Now I am trying
> to find a "good" way to accmpolish this goal. I could think of two
> options now:
>
> Option A:
> 1. Add a flag column in these 10 tables, (possible value: modified,
> inserted, etc) to indicate that record either has been updated or
> inserted.
> 2. Create a db link from "prod_mirror" to "prod"
> 3. Write an oracle package and move the flagged record in these 10
> tables from "prod_mirror" to "prod" when propagating all the changes.
> 4. Clean up the flag in these 10 tables afterwards.
>
> Option B:
> 1. Do not change the current table structure. But create a new table
> create table DataChangeStamp(
> TableName varchar2(30),
> ID Number,
> ChangeType varchar2(10), -- possible value: modified,
> inserted, etc
> ChangedBy varchar2(30),
> ChangeDate Date)
> 2. Whenever there is a change in data in "prod_mirror", there will be a
> new record created in DataChangeStamp table.
> 3. Create a db link from "prod_mirror" to "prod"
> 4. Write an oracle package and move the changed records in these 10
> tables from "prod_mirror" to "prod" using the info from DataChangeStamp
> when one propagates all the changes.
> 5. Clean up DataChangeStamp afterwards.
>
> I am leaning toward Option B. What do you think? Any other options?
>
> Thanks.
>
> Guang
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Yes ! Ever heard of replication!! Probably snapshot replication will be enough for you.. Received on Thu May 25 2000 - 00:00:00 CDT