| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Replication : database triggers vs log sniffing
On Tue, 06 May 1997 12:47:08 +0100, James Powrie <jp_at_manip.demon.co.uk> wrote:
[snip]
>>
>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 ^^^^^^^^^
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) 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.
You, as the implementor, must devise how you want to deal with sequences in this environment. The most common method being:
1- determine the max number of nodes you will replicate to, say 10 2- multiply that by 10 to be safe 3- at site 1; create sequence my_seq start with 1 increment by 100
at site 2; create sequence my_seq start with 2 increment by 100
....
at site N; create sequence my_seq start with N increment by 100
Now, you have a sequence that generates globally unique primary keys, probably more along the lines of what you wanted.
Actually, if you look at other implementations, I believe you will find that since they sniff the log to do replication that they
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?
>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. Your trigger can't change the data on the affected row and the replication trigger can't change the data on the affected row. If your trigger FAILS the insert/update/delete, it won't matter if it came before or after the replication trigger -- the whole statement (including any work performed by any trigger) is rolled back. If the replication trigger fails the insert/update/delete same is true.
I cannot think of, nor have I heard of, any issues with order dependence of AFTER, FOR EACH ROW triggers. Since these cannot modify values in the affected row AND the replication triggers do not refer to any other database objects except for that row, the ordering of firing is not relevant.
>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. Perhaps the log sniffing databases do it that way because they have to, they don't have the facilities to do it internally.
What other one lets you
- replicate data - replicate schemas - replicate procedures/triggers/etc - detects conflicts, allowing for update anywhere?
>James Powrie
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |