RE: How to propagate change from one database to another database

Guang,
I support the opinion of Michael. Solution is available thru updatable snapshots which is easy and requires little maintenance from your side. Oracle does this nicely.

Maheshwara Rao, L., Teki
E Mail : teki01_at_mediaone.net

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Michael Sun Sent: Thursday, May 25, 2000 7:46 PM
To: Multiple recipients of list ORACLE-L Subject: Re: How to propagate change from one database to another database

The 'prod_mirror' you mentioned is called staging database in most shops. You should look into Oracle Replication (either updatable snapshot or multimaster replication, depends on your specific needs). The underpinning or Oracle Replication
is database link, tho'. So you are not off target at all, just don't reinvent the wheel when there is one outta there.

Michael

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 25, 2000 5:55 PM

> 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
>
> --
> Author: Guang Mei
> INET: zlmei_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

--
Author: Michael Sun
  INET: mikeny31_at_speakeasy.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may