OraFAQ Forum: Replication » which soln is best for data propogation from master to app DB?

Home » Server Options » Replication » which soln is best for data propogation from master to app DB? (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

which soln is best for data propogation from master to app DB? [message #427044]

Tue, 20 October 2009 14:21

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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Make it simple, use a materialized view unless you find something incompatible with it.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	when going for materialised views
Next Topic:	impact of triggers on replication

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 11:53:25 CST 2025