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

From: Tom Zamani <tomz_at_redflex.com.au>
Date: 2000/05/26
Message-ID: <8gkecd$3bk$1@perki.connect.com.au>#1/1

I have done Something like what you are trying to do. This is what I think.
-1 for each table creat a trigger and a Audit table in the audit table make sure you have timestamp ,a Flag, action type as well as all the columns in the transactional tables.

eg transaction table product.(ID number ,name varchar2(100), price number) create table audit_product (ID number ,name varchar2(100), price number, FLAG varchar2(20), record_dtm date, Action varchar2(10) )

-2 create a shell script which creates a file for each table.

    something like

                                     set pagesize 0
                                    set feedback off
                                    spool uo1/tmp/movedata/insert.sql
                                    select 'insert into product
values('||ID||',''''||name||''''||','||price||';') from table
                                    where falg ='NOT  LOADED TO PROD'
                                    and action type='INSERT'
                                    order by timestamp;
                                    update table set flag='DONE' where
action_type='insert';
                                    spool off;

-3 create a cron job which executes the scripts, then executes the .SQL files on the prod database.

This is very simillar to your option B.
Tom

<gmei_at_my-deja.com> wrote in message news:8gk3hk$rfi$1_at_nnrp1.deja.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
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri May 26 2000 - 00:00:00 CDT