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

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

Post by g.. » Sun, 31 Dec 1899 09:00:00



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.

 
 
 

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

Post by ttrived » Sun, 31 Dec 1899 09:00:00



> 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..

 
 
 

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

Post by Gene Hube » Sun, 31 Dec 1899 09:00:00


I did it another way.

Within prod_mirror I have two copies of the data.  One copy has all
current changes.  The other copy is called savepoint and is what the
data was last time I uploaded to prod.  My procedures determine
differences between current and savepoint and move them to production.
When done, current data is moved to savepoint.

This is efficient if you don't have huge amounts of data.  It is
simple and has proven very robust and flexible.  For instance I can
restore an earlier version of savepoint data and compute a new batch
of changes and then put the savepoint data back the way it was.  If
you have a huge global change, you can just do it directly on your
prod data and both copies of you prod_mirror data and don't have to
worry about setting/unsetting flags or what happens to your table that
is accumulating differences (Option B below).

The minus operator is used heavily in computing differences.  The
whole thing uses straight sql only and not any pl/sql.

Gene Hubert

http://www.skillspoint.com - Where smart people buy training!


>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.

 
 
 

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

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.

2. Olap Select Rows/Columns Members Programmatically

3. Performance Advice - one big database or several small ones

4. migrating to Universe 9.1

5. seeking advice/suggestions in regards to Oracle auditing

6. Oracle Server on NT Intel 6 processors

7. Seeking suggestions for Relational Database Design Book

8. IL-Willowbrook-59626--Data Applications-Management-RDBMS-ORACLE-DBA Skills-Software/Programmer

9. Advice sought on database implementaion.

10. Seeking advice on database table design for storing images

11. Seeking advice on database table design for storing

12. Seeking advice on database table design for storing images

13. Creating an ADO VB6 stand-alone database program (Newbie seeks advice)