Re: Replication - how hard is it really? (Updatable snapshots..)

Roger

See my comments inline.

Pete

Roger Hunwicks wrote:

> In article <37652ECF.F7A31711_at_us.oracle.com>, you say...
> > As with so many things in the database, use the product for what it's
> designed
> > for. Don't try and force it into something else. Don't attempt to use
> > something as advanced as replication without getting the training, and
> while
> > you're doing it, talk to the instructor about the type of conflict
> resolution
> > you want to set up. Pick the instructor's brains, they've done it lots
> of times
> > before.
>
> But as others have said in this thread, how do we know what can be done.
> Like the original poster we have a requirement for replication but have
> been given conflicting advice.

I feel like running a class for clients where all they do is come in and say "I want to do this (xyz). How can it be done in Oracle?". I reckon I'd make a mint!

One of the best ways of knowing what can be done is to ask a group of people that's done it before. The newsgroup is excellent for this. Not only do Oracle staff keep an eye on it, like Tom Kyte and I, but you also get viewpoints from people who have supposedly no axe to grind. I've always endeavoured to give a balanced viewpoint of what Oracle features can be used, but I'm obviously not going to tell you to use one of our competitor's products!

> We need to run a database 24*7. We therefore need to replicate all
> updates to a hot standby server.

Not necessarily. You may have already been down this road, but in case you haven't, you should look at whether there is a real business need for 24x7. There are obvious examples of this. For example, if eTrade used an Oracle database for their data (and I must say I'm using them as an example without having the faintest clue if they do or not!), downtime for them can cost lots of dollars, and can in fact mean they go out of business. On the other hand, I've seen sites where they think they need 24x7, then a disk crash that takes out a day's work is shrugged off as something they can live with! However, let's say for the rest of the discussion here that you do need to run 24x7 ...

> Ordinarily the two servers will both be
> 'up', but updates will only be allowed to the 'live' server - the
> 'standby' server just sits there mirroring the 'live' database.

Let's go back to the basics and start with that. There are two things you need to protect against for real 24x7 - machine failure and site failure. Machine failure is pretty simple. You can use Parallel Server on any clustered environment, OS failover (e.g. TruCluster from Compaq) or FailSafe on NT. I've covered each of those in separate threads, so I won't go into them again.

Site failure can be protected against in a variety of ways too. The simplest is off site backups, with associated data loss of anything since the last available backup. Alternatively, you could use standby database or replication. Standby databases can be started in read-only mode with 8i, but not before that. They can be started in update mode but then they become the primary database and you need to create another standby database somewhere else. Replication can be set up with both sites updatable, but you point clients to one only, then to the other if the first fails. That can be done cleverly enough to avoid the need for conflict resolution. Downside here is that the replication is transaction based rather than log based, so for large throughput it's probably not the way to go.

> However, in the event of a failure of either server we would need to run
> the remaining server standalone until the problem was resolved and then
> resync the servers to get back to the original situation.

This could be done using either standby or replication. If you use standby, and the secondary database becomes the primary, then until you rebuild another standby you have a window of time where you're not protected against another site failure. If you use replication, likewise. There are a number of expensive ways of addressing this sort of thing (geomirroring for example) but you need to determine the business need for it and weigh that up against the cost.

Downtime of a couple of minutes on each change in status is acceptable!

Isn't it always?!

> Given that a server may fail in the middle of the night, the ability to
> let IT Operations staff select which configuration to use (Standalone A,
> Standalone B, A->B Replicated) from a menu without needing to wake up the
> DBA would be a big plus!

Again, isn't it always?! From memory, the GUI's for FailSafe and Replication allow you to do this in a slightly more complex way. I haven't seen a way (from Oracle anyway) to allow similar functionality for standby.

> Transaction levels would be low -> medium - less than 40 interactive
> users of a simple (less than 40 modules) telephone call logging
> application.

It's the transactions per second that's the important measure here. You could have hundreds of users doing very little and cope with it using replication, or few users really hammering the system and not cope with it. Also, the version of the database is important in determining whether the system will cope with the load.

>
> It has been suggested to us that using Oracle Replication for this task
> would be 'a sledgehammer to crack a nut' and that we should write a
> package to automatically generate triggers for each table in the
> application and use the triggers to handle the replication. Obviously,
> this advice is from the person who wants to write the trigger-generator
> package!

> Should we be considering Oracle Replication, or some other approach, or

> should we pay for a trigger generator to be written?
>

I prefer to stay away from home grown answers when something in the kernel already does what you want. There's a number of reasons for that, and none of them are because I work for Oracle! Firstly, you have to build, debug, and maintain it. If it's in the kernel, at least you get Support for it. Secondly, what you build probably won't have all the functionality in it that something like Replication has. For example, if both databases are up how do you handle conflict recognition and resolution? How do you handle referential integrity? What a trigger based approach is trying to do is all there in replication anyway.

> Any advice gratefully received.

Not a problem.

>
>
> Roger

HTH. Regards

Pete

Received on Wed Jun 16 1999 - 18:20:34 CDT