Home » Server Options » Replication » which soln is best for data propogation from master to app DB? (Oracle 10G)
which soln is best for data propogation from master to app DB? [message #427044] Tue, 20 October 2009 14:21 Go to next message
varshasantosh2004
Messages: 7
Registered: October 2009
Junior Member
Hi,
We have a master database, Oracle 10g which is updated daily using batch processes.
We have a application specific DB ( Oracle 10g) which is almost same as above master DB (Table names are same, but number of colunms are different. For e.g.: If the master table has 10 columns, this will have 4 columns with the same table names and column names) + some application specific tables.

We want to update this application DB from the master DB 3-4 times a day.
The tables have about 35 M records. (atleast 6-8 tables)

Now we are creating DB links from application DB to master DB and scheduler runs the queries to copy the data from master db using views and inserts it to application DB tables.

e.g.
truncate table a;
insert into tab_a select col1, col2, col3 from tab_b@dblink

The above process takes lot of time and is not advisable as during truncation, our web interface will be hitting the DB.

So considering these scenerios, what is the best way for us to keep the above two DBs in Sync.?

Can we use stream replication given different table structures between master and application DB and also apply "where" condition?

Pleae advise any different ways of achieving the same.

Thanks a lot in advance.

Thanks,
San
Re: which soln is best for data propogation from master to app DB? [message #427081 is a reply to message #427044] Wed, 21 October 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make it simple, use a materialized view unless you find something incompatible with it.

Regards
Michel
Re: which soln is best for data propogation from master to app DB? [message #427208 is a reply to message #427081] Wed, 21 October 2009 09:46 Go to previous messageGo to next message
varshasantosh2004
Messages: 7
Registered: October 2009
Junior Member
Hi, Thanks for your reply. MVs may not be suitable for us because, we have few additional columns which are UI specific and there will be updations from UI. We dont want to update master DB and want to keep seperate copy of table. Please suggest any other way other than using MVs. Thanks a lot.

Thanks,
San
Re: which soln is best for data propogation from master to app DB? [message #427294 is a reply to message #427208] Wed, 21 October 2009 15:46 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
. We dont want to update master DB and want to keep seperate copy of table


Okay. As per Mr Mic go with read only materialized view (I mean basic replication not advanced replication)

- Babu
Re: which soln is best for data propogation from master to app DB? [message #428373 is a reply to message #427044] Wed, 28 October 2009 02:47 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello,

sorry for my breaking into this thread. In fact I asked a question on materialized view in the beginning of this month but got no answer. Michel, I just read your suggestion about using a materialised view. I wanted to ask you if it would be of some gain in performance if I created materialised views in another tablespace than the source one (where my base tables/views reside)?

regards,
Didier
Previous Topic: when going for materialised views
Next Topic: impact of triggers on replication
Goto Forum:
  


Current Time: Thu Mar 28 13:45:05 CDT 2024