How to propagate change from one database to another database

From: Guang Mei <zlmei_at_hotmail.com>
Date: Thu, 25 May 2000 20:50:45 GMT
Message-Id: <10508.106778@fatcity.com>

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

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com Received on Thu May 25 2000 - 15:50:45 CDT