Re: Replication : database triggers vs log sniffing

Thomas Kyte wrote:
>
> On Tue, 13 May 1997 09:34:48 +0100, James Powrie <jp_at_manip.demon.co.uk> wrote:
>
> >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.
>
> No, not true. The triggers, which in 7.x got generated as pl/sql, are
> 'internalized'. If you look at the generated triggers, they were 90%
> boilerplate and only unique with respect to table/column names. These have been
> internalized. They are still triggers, they still behave the same, they are
> just in C now. The architecture is fundementally unchanged, many performance
> enhancements added, but basically the same.
I standby my original statement. Oracle are rewriting replication. First you imply no knowledge of a rewrite, now you concede they replication IS being reimplemented in a different language, the logic IS being moved from "compiled" plsql into native object code which will execute internally in the RDBMS and the system IS being enhanced. Whether this represents an architectural shift is debatable, but what ever way you look at it, the system is being revamped.
> >
> >> >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.
>
> Believe me, it would be trivial, or don't believe me, read the snippet of code
> below. Like I said, if you can replicate TABLE STRUCTURES, TRIGGERS,
> PROCEDURES, it would be *trivial* to replicate a sequence.
>
> consider looking at
>
> - dbms_job_at_sitename
> - dbms_sql
>
> For example, I use the following procedure to alter passwords of a user at a
> remote site:
>
> create or replace procedure cpw( p_user in varchar2,
> p_passwd in varchar2 )
> as
> l_job number;
> l_stmt varchar2(4096) default '
> declare
> exec_cursor integer default dbms_sql.open_cursor;
> rows_processed number default 0;
> begin
> dbms_sql.parse(exec_cursor,
> ''alter user $1 identified by $2'',
> dbms_sql.native );
> rows_processed := dbms_sql.execute(exec_cursor);
> dbms_sql.close_cursor( exec_cursor );
> end;
> ';
> begin
> dbms_job.submit_at_aria
> ( l_job, replace( replace( l_stmt, '$1', p_User ), '$2', p_passwd ) );
> dbms_output.put_line( 'Job ' || l_job || ' scheduled' );
> commit;
> end;
> /
>
> It submits DDL for execution at some other site. Turn the alter user into
> Create Sequence, do it for each database link you have and you are pretty much
> done.
>
> Again, it was a conscious decision to NOT replicate sequences for the reasons
> outlined above. It in general does NOT make sense to just replicate them. You
> must design to use them.
>
> Even if you were just using replication to have a failover, replicating
> sequences would not make sense. If i replicated the sequence at the beginning,
> when it started at 1, then used the primary for a while so its value was 1000
> and then failed over, the failover would have a sequence with a value of 1. Not
> very useful.

The example I have in mind is this:
A table has a unique primary key which is populated automatically via a sequence and trigger
Create a 2nd instance with the same table and the sequence. Start row level replication of the table. Insert 50 rows on primary (next value of sequence should be 51) Attempt to use secondary
Can't insert any rows because sequence is still at at 1 and duplicate keys are rejected.
Not very useful.

I know you have a workaround to this problem. All I am trying to demonstrate is that there are scenarios where sequence replication would be useful.

> >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.
> >

I have no doubt that you can replicate the creation of of sequences. My point is regarding transparent use of sequences. My analagy regarding NFS/mirroring is simply that NFS/mirroring can added to a UNIX machine and ALL applications can instantly make use of NFS/mirrored file access with any modification. Row level replication can be transparent in that a trivial application (that does not use sequences or triggers) could in principle make use of replication without any modifications.

An application developer who understands triggers, database links, 2pc, packages and job queues could knock together a crude implementation of transparent row level replication built entirely out of user level components. My contention is that it is neither possible nor trivial to do the same with sequences. Wrapping all interaction with seqences with replicated procedures does not qualify for the label "transparent" because all non trivial application would need reworking.

> Look, for existing applications, you will be installing the database at the
> other sites right? Install the existing sequences when you create the database.
> Use the routine outlined in the previous post to ensure each sequence at each
> site generates it's own set of number. We don't replicate user accounts or
> priveleges either. You will still have N databases to manage. At least when
> you modify a table, it will push that out. If you create a procedure, it will
> replicate that. if the procedure you replicate depends on a sequence that does
> not exist at a remote site, you will be informed of this.
>
> >> 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
>
> And THAT is exactly what this software was designed to do.
>
> >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.
>
> What is a 2nd site instance? IF you are not going to use the sequence to
> generate new records at the second site (you MUST NOT BE since they would always
> conflict with the same sequence at the initial site), you don't NEED the
> sequence. If the 2nd site is read only, the sequences are NOT needed.
>
> BTW: as for the comment "...take advantage of the monotonic and contiguous
> properties..."
>
> Sequences are NEITHER monotonic nor contiguous, even in a single instance the
> following can (*and will*) happen:
>
> SQL> create sequence foo start with 1 increment by 1;
>
> Sequence created.
>
> SQL> select foo.nextval from dual;
> NEXTVAL
> ----------
> 1
>
> SQL> /
> NEXTVAL
> ----------
> 3
> SQL> /
>
> NEXTVAL
> ----------
> 4
> SQL>
>
> As you can see, neither contiguous NOR monotonic. After I did the create and
> the initial select, i opened another sql*plus session and did a select
> foo.nextval from dual. When the above session when to do it's next select, it
> does not get 2 (i got that in another session already), but 3. Then it gets 4
> by luck since no one else selected from the sequence yet. BTW: I rolled back
> the second session, forever losing the number 2 (definitely not contiguous). By
> playing with cache settings and crashing the server, I can get *really* big
> gaps. Any transaction that selects and rolls back will create gaps.

Firstly I am not sure that you understand what monotonic means. Look it up in a dictionary and reconsider your argument. Second sequences can deliver monotonic and contiguous sequences of numbers. The fact that multi-user environments and rollbacks can introduce complications does not detract from an intrinsic property 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.
> >
>
> What do you mean by a 'non-destructive testing' of the 2nd site instance?
ng the database. How many products do you want to maintain? Destructive testing is when you stretch an elastic band until it breaks. You know how strong it WAS. With standby database, you can activate your database to test that it works, but then you no longer have a standby database. You have to create another one.
> >
> >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.
> >
>
> What could possible be less intrusive for general purpose, update anywhere
> replication then something that is
>
> - built into the product you are already using
> - doesn't require the installation of separate software
> - uses the same constructs/syntax you are already using (sql, pl/sql, sql*plus,
> svrmgrl, enterprise manager)
>
> >> >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.
>
> Elaborate. I think you are referring to mutating tables and the fact that a row
> level trigger cannot read/write a table that is dependent on the table that is
> firing the trigger (eg: a child table related via a declaritive foreign key
> cannot be read/written to by a row trigger fired by the parent table). If so,
> this is *not* related to replication in any way.
I am referring to triggers that create rows in other tables. You have to be wary of row level replication of the other tables.
> >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.
> >
>
> This is true, but......
>
> You would in this case probably want to replicate the process, NOT the data.
> If you are doing a transaction the size and scope of which would require a
> specially allocated, larger then normal rollback statement, you might
> consider replicating the process itself instead of replicating the data.
> It would be much more efficient. IMO, if you took the time to identify the
> problem transaction in the first place, were concerned enough to setup a
> special rollback segment for it, you would take the time to consider what it
> would do to you in a replicated environment as well.
>
> >I would have hoped that replication would be implemented in a way that
> >does not impact existing applications.
>
> This is impossible if you like data integrity AND you want to support update
> anywhere AND support disconnected computing.
>
> >NFS and disc mirroring are examples of
> >technologies that work in this useful, well accepted paradigm.
>
> NFS and disk mirroring are completely besides the point and doesn't map well to
> a database paradigm, especially one that supports, and was designed to support,
> the need to remove the distributed nature of replication from the client
> application.
>
> Not that I would even call NFS replication, NFS is NOT replication. NFS, if you
> must come up with an analogy, is like sql*net or a database link which we do
> quite well. With NFS there is no second copy, no failover to another machine
> when the first one goes down. Nothing. Also, with NFS, all machines are
> constantly in touch (in network touch). If they are not, then it doesn't work.
> Database replication was designed to support network failures as well as machine
>
> failures. Database repliations doesn't need a fully connected network to work.
> It only needs a sometimes connected network.
>
> Same with disk mirroring. disk mirroring is NOT replication. Disk mirroring
> provides some degree of fault resistance. If one disk fails, as they will, you
> can continue processing. If you must draw an analogy, it would be to the Oracle
> Parallel Server. Two instances mount the same set of database files. They
> share the same database. If one machine disappears, or one of the instances
> crashes, then the other survives and your database stays working. Sort of like
> losing a disk in a set of mirrors.
>
> >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.
> >
> True but UFS, NFS, and mirrors are not replication :)
>
> UFS is like a host based connection.
> NFS is like using database links and synonyms to access remote data
> transparently.
> Mirrors are like parallel server for fault tolerence.
>
> >>
> >> >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.
> >
>
> We already did, in version 7.0 with read only and 7.1 with advanced update
> anywhere. Continous enhancements will make this faster over time. That's what
> you'll see in O8. the same calls (pl/sql wise) will be made, the same defered
> queue is used, it's all pretty much the same-- tuned better, more performant,
> more flexible but architecturally, the same.
>
> >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.
>
> The triggers are 'internalized', they are still triggers, they are still
> packages.
>
> >
> >Third, a declarative way of specifying replication would be very convenient for the users.
> >
>
> It is declaritive as far as I can tell. True, you use a procedure call to do
> it, but it's single procedure calls. There are gui tools to generate the calls
> if you don't like typing. Its as close to 'pure' declaritive as you can get.
Given that Oracle distinguish between declaritive constraints that are built into Oracle and user-level logic in triggers. I don't see how you can argue that a system built out of of user level triggers can possibly be declaritive. Obviously you disagree when the thrust of my argument, but refuting every statement I make may not make the most convincing argument.

James Powrie Received on Thu May 15 1997 - 00:00:00 CDT