Re: Replication or Database Link ?

Been there, done this. We just wnt through the identical scenario. FWIW, here's my 2 pennies. You can use synchronous replication for availability - but you've got to go through large hoops to make it work.

1. First, you need a process to keep checking whether a db is down,
2. When a db down is detected, force the one that's up to become the replication master definition site.
3. Break replication.
4. The other site is available within minutes.

This is unsavory for many reasons. Among them:

1. It's extremely complicated to design and code.
2. When you break the replication, you are then running without backup.
3. Getting back in sync is a BIG bear.
4. When we called Oracle support for a synch rep question, the technician said it was his first Synchronus Replication question in four years. Most people are doing asynch.

The biggest thing to understand is that Oracle has two products designed for different things. Oracle Hot Standby DB is designed for availability. Replication is used for distribution. People like to use their distributed environment for availabilty. It works, but it's not what it's designed for, and you've got to go through the aforementioned hoops.

The only problem with Hot Standby is that you can use your online redo log, but that's your best bet for Oracle-based availability. (Better availability is the hardware solution - HA cluster, and hot-swappable raided disksets.)

We convinced our user to go with async replication. The other site is available very quickly, and you can lose a few transactions until the down db comes back. Those transaction will still be queued up (and may cause replication conflicts).

Those last few minutes of availability are the ones that cost the biggest bucks. Most companies don't need to spend that much for their availability needs.

Good luck, let us know what you decide.

Yosi

Pete Sharman wrote:

> Timo
>
> The reason you've been given for using synchronous replication is exactly
> why you shouldn't use it - availability. Synchronous replication only works
> when both databases are up. If one dies, you can't insert into any tables
> that are being synchronously replicated on the database that stayed up. Of
> course, you can disable the replication manually in this case, but it
> requires someone being around to notice it's broken and do that.
>
> As far as the batch jobs go, what do they do? Are they changing data? If
> so, then the volume of network traffic is going to need testing to make sure
> replication will push things through fast enough for you.
>
> I'd still stay with database links unless there's a good reason to do
> otherwise.
>
> HTH.
>
> Pete
>
> tsalzsie_at_my-deja.com wrote:
>
> > Pete,
> >
> > thanks for your reply. There are some semantic as well as security
> > reasons that we need two databases. The databases are in the same
> > network (in other words in the same data center) and afaik are connected
> > via a 100 Mbit network.
> > One of the reason some people in our department tend to use synchronous
> > replication is availability - in other words, WEBDB is still availabe
> > when CALLDB crashes and vice versa. However, I'm not sure how to handle
> > these large batch jobs at night - how replication reacts or if it makes
> > sense to disable replication while these batch jobs are running.
> > So, database links seems to be the most appropriate solution with
> > respect to simplicity, reliability and performance.
> >
> > Any other recommendations or things we should look for ?
> >
> > Thanks again, Timo.
> >
> > In article <37B19E18.715D5C10_at_us.oracle.com>,
> > Pete Sharman <psharman_at_us.oracle.com> wrote:
> > > This is a multi-part message in MIME format.
> > > --------------14B030D85C968D165FF473D8
> > > Content-Type: text/plain; charset=us-ascii
> > > Content-Transfer-Encoding: 7bit
> > >
> > > Timo
> > >
> > > First question I'd have to ask is why do you need two databases?
> > >
> > > If you really need two separate databases, are they in the same
> > network? If
> > > so, what's the network speed and how reliable is it? I wouldn't use
> > > synchronous replication unless the two databases are on the same very
> > > reliable LAN.
> > >
> > > HTH.
> > >
> > > Pete
> > >
> > > tsalzsie_at_hotmail.com wrote:
> > >
> > > > I would like to ask you for your advice regarding the following
> > > > scenario:
> > > > We have two databases where database CALLDB is accessed by call
> > center
> > > > agents during normal office hours. The second database WEBDB is
> > accessed
> > > > by internet users. Both parties basically work on the same table
> > MEMBER
> > > > where they change data like addresses, phone-numbers, etc.
> > > > During night, we run a large batch job and load up to 5 million rows
> > via
> > > > sqlldr into the table MEMBER. In other words, at night the data is
> > > > not accessed by the call center, but internet folks obviously still
> > > > work with the database (performance!!). This whole scenario applies
> > not
> > > > only to the specified MEMBER table, but also to some other tables.
> > > >
> > > > Now, the question. How do we achieve the requirements that
> > > > - the data must be synchronized instantially (call center must
> > access
> > > > basically the same data as the web user, thus real-time
> > synchronization
> > > > between WEBDB and CALLDB)
> > > > - security
> > > > - performance (limit network traffic)
> > > >
> > > > We were thinking about two possible solutions.
> > > > A. Synchronous Replication
> > > > - almost real-time
> > > > - high network traffic, performance might be a bottleneck
> > > > - high effort to implement (setup, conflict resolution, etc.)
> > > > - handling of large batch job imports still a open issue
> > > > B. Database link
> > > > - high availiablity of either one of the solutions is not guaranteed
> > > > - simple to implement
> > > > - handling of large batch job import also an open issue
> > > >
> > > > We would appreciate any comments, experience reports,
> > recommendations.
> > > >
> > > > Thanks in advance, Timo.
> > > >
> > > > Sent via Deja.com http://www.deja.com/
> > > > Share what you know. Learn what you don't.
> > >
> > > --
> > > Regards
> > >
> > > Pete
> > >
> > > --------------14B030D85C968D165FF473D8
> > > Content-Type: text/x-vcard; charset=us-ascii;
> > > name="psharman.vcf"
> > > Content-Transfer-Encoding: 7bit
> > > Content-Description: Card for Pete Sharman
> > > Content-Disposition: attachment;
> > > filename="psharman.vcf"
> > >
> > > begin:vcard
> > > n:Sharman;Peter
> > > tel;cell:+1.650.868.9969
> > > tel;fax:+1.650.633.1669
> > > tel;work:+1.650.607.0109
> > > x-mozilla-html:FALSE
> > > url:http://www.oracle.com
> > > org:Oracle University, Consulting and Vertical Education;Oracle
> > Corporation
> > > adr:;;500 Oracle Parkway M/S OPL-B1024;Redwood
> > Shores;California;94065;USA
> > > version:2.1
> > > email;internet:psharman_at_us.oracle.com
> > > title:Project Manager
> > > note;quoted-printable:=0D=0A=0D=0A **** The statements and opinions
> > expressed here are my **** =0D=0A **** own and do not necessarily
> > represent those of **** =0D=0A **** Oracle Corporation.
> > =20 ****=0D=0A=0D=0A"Controlling application developers is like
> > herding cats." =0D=0AKevin Loney, ORACLE DBA Handbook =0D=0A=0D=0A"Oh
> > no it's not! It's much harder than that!" =0D=0ABruce Pihlamae, long
> > term ORACLE DBA
> > > x-mozilla-cpt:;9200
> > > fn:Pete Sharman
> > > end:vcard
> > >
> > > --------------14B030D85C968D165FF473D8--
> > >
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
> --
> Regards
>
> Pete
Received on Thu Aug 12 1999 - 14:18:27 CDT