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
1. Add a flag column in these 10 tables, (possible value: modified,
inserted, etc) to indicate that record either has been updated or
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.
1. Do not change the current table structure. But create a new table
create table DataChangeStamp(
ChangeType varchar2(10), -- possible value: modified,
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?
Sent via Deja.com http://www.deja.com/
Before you buy.