Re: Replication : database triggers vs log sniffing

Thomas Kyte wrote:
>
> On Tue, 06 May 1997 12:47:08 +0100, James Powrie <jp_at_manip.demon.co.uk> wrote:
> >I would say that Oracle's "Advanced" Replication was an easy way for
> >Oracle to get into the replication market without a major rewrite.
> >Because it was implemented out of existing user-level features such as
> >triggers, packages and job queues, minimal server mods were necessary.
> >Compared to a pukka server based solution (which Oracle are working on)
> ^^^^^^^ not AFAIK ^^^^^^^^^
I was told this by an Oracle developer at an Oracle conference in Paris. He was candidly undefensive of the current implementation and said that Oracle were working on a server level rewrite in Version 8.

> >it has the following problems (at least):
> >1. Sequences are not replicated
>
> It doesn't make sense to replicate sequences and you would find it to be not
> desirable if we did. The most common use of sequences is as surrogate primary
> keys. Primary keys must be unique. If we simply replicated a sequence (which
> would be trivial, a whole lot easier then replicating table definitions,
> indexes, triggers, stored procedures, packages, etc -- all of which it does)

I disagree that its trivial. I can't see any cheap and cheerful way to replicate a sequence using
triggers, packages and job queues. If applications are riddled with statements along the lines of: select xxxxxx.nextval into zzzzz from yyyyyy, how are going to replicate the sequences without impacting existing applications? Surely the only way to replicate sequences transparently, is in the server.

> you
> would end up with the same set of primary keys being generated all over the
> place. All N nodes in your system would generate the same exact sequence of
> primary keys, leading to conflicts which are not really conflicts.
>

This is true if you are using replication to create a distributed database where both sites are active at the same time. If, on the other hand, you are creating a 2nd site instance then it seems perfectly reasonable to desire that your sequences be uptodate at the 2nd site. After all, you want the 2nd site instance to be a REPLICA of the primary site. Without this feature the user programmer is constrained not to take advantage of the monotonic and contiguous properties of sequences.

I realise that Oracle are no longer recommending replication as a standby site solution because 2nd site capability has recently become available with the standby database feature of 7.3. However, a replicated solution would have the advantage of non-destructive tesing of the 2nd site instance.

>
> One of the advantages of using what is built into the database, is that we can
> do more then just replicate data. We can replicate your schema as well.
> Replicating the schema and mods to the schema is just as important as
> replicating the data itself. The log sniffing implementations count on YOU to
> know that you can't alter tables, add indexes, etc without syncing every system
> up; shutting them down and adding the new objects simultaneously. Consider what
> would happen if one system alters a table and adds a new check constraint. If
> you didn't do it everywhere, it would soon result in a mess. One system would
> be able to input data that would be rejected by another system. You need to
> replicate you schema as much as the data contained within.
>
> >2. Once replication is up and running, it adds a extra dimension of
> >complexity to maintaining the database.
>
> This, I believe I can safely say, is true of every and all replication schemes.
> They all impact the maintenence of the database.
>
> I can say however that you are just maintaining the database, there are no
> separate servers to monitor, no separate processes to maintain. You are still
> just administering the database. How many products do you want to maintain?

We may be at cross purposes here. I am not advocating a 3rd party log sniffing solution. I am just saying that as a customer of a premium priced, high end product such as an ORACLE RDBMS, I would expect a more elegant, less instrusive implementation of replication.

> >3. Applications must be replication aware. For instance if you define a
> >trigger which would fire at the same time as a replication trigger, the
> >order of events is undefined. i.e. the result is undefined.
> >
>
> No, the results are not undefined. All replication triggers are AFTER, FOR EACH
> ROW triggers. These triggers are not allowed to modify the values of the :new
> and :old bind variables (as before triggers are). So, if the replication
> trigger fires BEFORE yours or AFTER yours, it won't matter.

You got me on this one. I admit it, my example was wrong. However, the fact remains that row based replication does have pitfalls that the programmer must be aware of. One pitfall is related to triggers that create new rows in other tables. Workarounds (involving package variables) have been provided and are detailed, in the manaul in a section on "advanced" techniques.
Another pitfall is related to rollback segments. If you explicitly request a large rollback segment in a transaction, the associated remote replicating transaction can fail because the remote rollback segment may not be large enough.

I would have hoped that replication would be implemented in a way that does not impact existing applications. NFS and disc mirroring are examples of technologies that work in this useful, well accepted paradigm. The user of such a file can be unaware of how the file i/o is implemented and does not need to know or care if the file is a local file, a nfs file, or a mirrored file.

>
> >For the UNIX users out there I would say Oracle replication is analagous
> >to a disk replication solution built out of user-level shell scripts.
> >
>
> No, not really. Its a little more robust than that. Since it is built on the
> same base foundation that you build on, it can take advantage of things like 2pc
> (make sure a transaction is delivered to another site at least once, at most
> once), data integrity (make sure there exists no conflicts before pushing this
> transaction out, if there are work to resolve it). Why on earth would you want
> Oracle to re-implement in a separate process many things it had.

First, because I believe Oracle are doing just that, implementing replication in the server.

Second, because, the current solution which relies on autogeneration of a one trigger and two packages for EACH table to be replicated is inelegant and inefficient. Inelegant because of the repeated logic in the packages and triggers, and inefficient because the shared pool gets clogged up with replication baggage and because user level operations are almost certainly slower than server level operations. Surely a chunk of generic code in the server
would be a lot neater.

Third, a declarative way of specifying replication would be very convenient for the users.

James Powrie Received on Tue May 13 1997 - 00:00:00 CDT