[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-warehouse-l
Subject: Re: General questions about replication
From: "Amin Adatia" <amin_adatia () hotmail ! com>
Date: 2005-06-29 19:04:44
Message-ID: F001.005F82DE.20050629115823 () fatcity ! com
[Download RAW message or body]
You "refresh daily across a database link". Data Guard will do the same thing forever \
and also be available to do other indexes for improved searches if you want. Setup \
once and then almost forget about it is better than doing the same thing manually (OK \
via cron jobs etc)
Regards
Amin Adatia (amin@knowtech.ca)
KnowTech Solutions Inc. (www.knowtech.ca)
(613) 226-8378 Mobile (613) 864-8378
----- Original Message -----
From: Berthoff, Tom
To: Multiple recipients of list ODTUG-WAREHOUSE-L
Sent: Wednesday, June 29, 2005 3:23 PM
Subject: RE: General questions about replication
Amin,
What we have been told by several independent consultants (Oracle Warehousing, 3rd \
party consultant) is that using more heavy-duty Oracle tools such as database \
replication or DataGuard is overkill for what we need. They say that what we need is \
something relatively simple and quick to implement, and that materialized views fit \
our needs.
Tom
------------------------------------------------------------------------------
From: ml-errors@fatcity.com [mailto:ml-errors@fatcity.com] On Behalf Of Amin Adatia
Sent: Wednesday, June 29, 2005 2:18 PM
To: Multiple recipients of list ODTUG-WAREHOUSE-L
Subject: Re: General questions about replication
If you have two servers why not use Data Guard?
Regards
Amin Adatia (amin@knowtech.ca)
KnowTech Solutions Inc. (www.knowtech.ca)
(613) 226-8378 Mobile (613) 864-8378
----- Original Message -----
From: Berthoff, Tom
To: Multiple recipients of list ODTUG-WAREHOUSE-L
Sent: Wednesday, June 29, 2005 1:33 PM
Subject: General questions about replication
Hi,
I have a general question about replication.
Here's the situation:
We have a medium-sized base application database. Only a small subset of the \
entire database gets changed on a daily basis (our principle table is "accounts" with \
around 600K accounts in it; only 100-500 accounts and their related tables are \
updated daily).
We need to publish data to outside clients. The current setup has the published \
information on a separate database, which gets refreshed daily across a database \
link.
We're looking at different versions of replication to solve some of our problems, \
and have been advised that using materialized views is probably the best way to go, \
since our database size is small, the number of updates is small, and the views are \
the most easily customized solution. We have been told that database replication is \
not a good solution.
Here's question 1:
If you're using materialized views to simply create replicas of one application's \
tables in another application's database, how do you replicate the source table key \
constraints and indexes without interfering with the replication?
Example:
Source application has three tables, DEPT, EMP, and DEPT_EMP. Table DEPT_EMPS has \
two foreign keys, one to DEPT and one to EMP. I create three materialized views, one \
for each table. I refresh the materialized views and want to be sure the data is \
correct, so I add foreign keys between the materialized views. I also want my queries \
to perform well, so I add indexes to the appropriate columns. However both the key \
constraints and indexes create problems the next time I refresh, since I'm doing a \
complete refresh of all three tables.
How are other people managing this scenario?
Question 2:
What kind of overhead does adding materialized view logs add to the source \
system? Do they really work as well as publicized to only move changed rows across to \
the materialized views? What are the main obstacles to using materialized view logs?
Thanks for your response,
Tom
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2668" name=GENERATOR><!--[if !mso]>
<STYLE>v\:* {
BEHAVIOR: url(#default#VML)
}
o\:* {
BEHAVIOR: url(#default#VML)
}
w\:* {
BEHAVIOR: url(#default#VML)
}
shape {
BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]--><o:SmartTagType name="place"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><!--[if \
!mso]> <STYLE>
st1\:*{behavior:url(#default#ieooui) }
</STYLE>
<![endif]-->
<STYLE>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:Arial;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</STYLE>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=EN-US vLink=purple link=blue bgColor=white>
<DIV><FONT face=Arial size=2>You "refresh daily across a database link". Data
Guard will do the same thing forever and also be available to do other indexes
for improved searches if you want. Setup once and then almost forget about it is
better than doing the same thing manually (OK via cron jobs etc)</FONT></DIV>
<DIV><BR>Regards<BR>Amin Adatia (<A
href="mailto:amin@knowtech.ca">amin@knowtech.ca</A>) <BR>KnowTech Solutions Inc.
(<A href="http://www.knowtech.ca">www.knowtech.ca</A>)<BR>(613)
226-8378 Mobile (613) 864-8378 </DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 \
2px solid; MARGIN-RIGHT: 0px"> <DIV style="FONT: 10pt arial">----- Original Message \
----- </DIV> <DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=tberthoff@thomcomp.com href="mailto:tberthoff@thomcomp.com">Berthoff,
Tom</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=ODTUG-WAREHOUSE-L@fatcity.com
href="mailto:ODTUG-WAREHOUSE-L@fatcity.com">Multiple recipients of list
ODTUG-WAREHOUSE-L</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, June 29, 2005 3:23
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: General questions about
replication</DIV>
<DIV><BR></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: \
Arial">Amin,<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial \
color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: \
Arial"><o:p> </o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial \
color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: \
Arial">What we have been told by several independent consultants (Oracle \
Warehousing, 3<SUP>rd</SUP> party consultant) is that using more heavy-duty Oracle \
tools such as database replication or DataGuard is overkill for what we need. They \
say that what we need is something relatively simple and quick to implement, and \
that materialized views fit our needs.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: \
Arial"><o:p> </o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial \
color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: \
Arial">Tom<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial \
color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: \
Arial"><o:p> </o:p></SPAN></FONT></P> <DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=3>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: \
Tahoma">From:</SPAN></FONT></B><FONT face=Tahoma size=2><SPAN style="FONT-SIZE: \
10pt; FONT-FAMILY: Tahoma"> ml-errors@fatcity.com [mailto:ml-errors@fatcity.com] \
<B><SPAN style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Amin Adatia<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, June 29, 2005 2:18
PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> Multiple recipients of
list ODTUG-WAREHOUSE-L<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: General questions about
replication</SPAN></FONT><o:p></o:p></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">If you have two servers why not
use Data Guard?</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR>Regards<BR>Amin Adatia (<A
href="mailto:amin@knowtech.ca">amin@knowtech.ca</A>) <BR>KnowTech Solutions
Inc. (<A href="http://www.knowtech.ca">www.knowtech.ca</A>)<BR>(613)
226-8378 <st1:place w:st="on">Mobile</st1:place> (613) 864-8378
<o:p></o:p></SPAN></FONT></P></DIV>
<BLOCKQUOTE
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: medium none; \
PADDING-LEFT: 3pt; PADDING-BOTTOM: 0in; MARGIN: 5pt 0in 5pt 3pt; BORDER-LEFT: black \
1.5pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: medium none"> <DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">----- Original Message -----
<o:p></o:p></SPAN></FONT></P></DIV>
<DIV style="font-color: black">
<P class=MsoNormal style="BACKGROUND: #e4e4e4"><B><FONT face=Arial
size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: \
Arial">From:</SPAN></FONT></B><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; \
FONT-FAMILY: Arial"> <A title=tberthoff@thomcomp.com \
href="mailto:tberthoff@thomcomp.com">Berthoff, Tom</A> \
<o:p></o:p></SPAN></FONT></P></DIV> <DIV>
<P class=MsoNormal><B><FONT face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: \
Arial">To:</SPAN></FONT></B><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; \
FONT-FAMILY: Arial"> <A title=ODTUG-WAREHOUSE-L@fatcity.com
href="mailto:ODTUG-WAREHOUSE-L@fatcity.com">Multiple recipients of list
ODTUG-WAREHOUSE-L</A> <o:p></o:p></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><B><FONT face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: \
Arial">Sent:</SPAN></FONT></B><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; \
FONT-FAMILY: Arial"> Wednesday, June 29, 2005 1:33 \
PM<o:p></o:p></SPAN></FONT></P></DIV> <DIV>
<P class=MsoNormal><B><FONT face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: \
Arial">Subject:</SPAN></FONT></B><FONT face=Arial size=2><SPAN style="FONT-SIZE: \
10pt; FONT-FAMILY: Arial"> General questions about \
replication<o:p></o:p></SPAN></FONT></P></DIV> <DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hi,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I have a general question about
replication.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Here’s the
situation:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">We have a medium-sized base
application database. Only a small subset of the entire database gets
changed on a daily basis (our principle table is “accounts” with around 600K
accounts in it; only 100-500 accounts and their related tables are updated
daily).<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">We need to publish data to
outside clients. The current setup has the published information on a
separate database, which gets refreshed daily across a database link.
<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">We’re looking at different
versions of replication to solve some of our problems, and have been advised
that using materialized views is probably the best way to go, since our
database size is small, the number of updates is small, and the views are
the most easily customized solution. We have been told that database
replication is not a good solution.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Here’s question
1:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">If you’re using materialized
views to simply create replicas of one application’s tables in another
application’s database, how do you replicate the source table key
constraints and indexes without interfering with the
replication?<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Example:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Source application has three
tables, DEPT, EMP, and DEPT_EMP. Table DEPT_EMPS has two foreign keys, one
to DEPT and one to EMP. I create three materialized views, one for each
table. I refresh the materialized views and want to be sure the data is
correct, so I add foreign keys between the materialized views. I also want
my queries to perform well, so I add indexes to the appropriate columns.
However both the key constraints and indexes create problems the next time I
refresh, since I’m doing a complete refresh of all three
tables.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">How are other people managing
this scenario?<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Question
2:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">What kind of overhead does
adding materialized view logs add to the source system? Do they really work
as well as publicized to only move changed rows across to the materialized
views? What are the main obstacles to using materialized view
logs?<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks for your
response,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Tom<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: \
Arial"><o:p> </o:p></SPAN></FONT></P></BLOCKQUOTE></DIV></BLOCKQUOTE></BODY></HTML>
Hope you were able to visit ODTUG Now! 2005 in New Orleans! Stay tuned for
the location of the 2006 conference. ODTUG will also be at Oracle OpenWorld
in San Francisco, September 17-22, 2005. http://www.odtug.com
--
Author: Amin Adatia
INET: amin_adatia@hotmail.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-WAREHOUSE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic