Re: Oracle Streams and Uniqueness

Comments in-line

On Mon, Nov 10, 2008 at 4:57 PM, Dominic Delmolino <ddelmoli_at_cox.net> wrote:

> Assuming the application doesn't "care" about uniqueness, you can probably
> assess the impact on various DML statements. For INSERTS, I'm guessing
> you're somehow making sure you don't get duplicates, or you don't care about
> them. In those cases, Streams should faithfully replicate the duplicates.
>

yep that's happening.

>
> For UPDATES and DELETES it becomes problematic, as Streams tries to
> generate row-by-row change statements, so it needs to uniquely identify the
> rows. This, of course, isn't how you probably specified the original
> command -- you want to work on a SET, not on the individual ROWS, so you
> don't care which rows get handled as long as the SET you've identified gets
> handled correctly. I'm not sure if Streams cares about how many rows get
> DELETED / UPDATED -- although I think it cares if the rows exist or not to
> begin with. Curious -- are you getting errors in the apply logs or are you
> still at the conceptual phase in working with Support?
>

I'm at the errors in the apply logs stage (actually not right now since I've implemented a workaround solution for the customer - they wanted to offload problematic reporting processing from production to a copy, but not include all database schemas in the copy. Right now we're cloning the db nightly and the streams config exists only as sql scripts to recreate at an appropriate time - this may be never).

Streams does care about how many rows get at least updated since it generates ORA-1422 in the apply logs, The deletes generate -ORA-1403 no data found which would be OK to ignore.

Right now I don't know enough about the application logic to comment on whether the app really cares about the duplicates or not, and I don't really want to ask the question :(.

> Back in the days of Advanced Replication (pre-Streams), we supported the
> idea of Procedure Replication -- a way for you to replicate a procedure call
> instead of relying on the row-by-row triggers. In the procedure call, you'd
> signal the triggers NOT replicate the row-by-row changes and the Procedure
> Call would be sent -- that simulated the SET command. I'm not sure if
> Streams has this concept.
>

I don't believe so, but thanks for the pointer.

>
> All of this gets really hairy if you're allowing multiple sites to do DML,
> since it screams data corruption and conflict.
>

Well I never really liked the idea of multi-master replication (or whatever it's called now) anyway - haven't used it, but never want to use it either for exactly the conflict/corruption reasons you describe!

Niall

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l