Re: Oracle Streams and Uniqueness
From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 10 Nov 2008 09:26:26 -0700
Message-ID: <491860B2.6080106@evdbt.com>
It is not a restriction only of Oracle Streams, but of absolutely any replication solution, anywhere, at any time. How can any UPDATE or DELETE be propagated with a way of uniquely identifying rows, regardless of technology, whether using Oracle or Sybase or DB2 or MySQL, or any mixture of these?
A few years ago, I was on a project where we had Quest and Golden Gate each claiming that their respective products, unlike Oracle Advanced Replication and Oracle Streams, imposed no such restriction. Which is true -- they didn't actually impose the restriction. The Oracle products simply imposed the restriction before it caused logical data corruption.
Supplemental redo logging enables replication by recording the "logical ROWID" data values (i.e. PK/UK/substitute-key) in the generated redo, along with the usual "physical ROWID" values, for each change. For example, prior to the advent of Streams and supplemental redo logging, the Quest SharePlex product had to perform a "lookup" (after the redo was replicated) by "physical ROWID" on the source database in order to obtain the "logical ROWID" data values. The same is almost certainly true of other log-based replication products on Oracle prior to Oracle9i as well.
Still, none of this implies that declarative PK or UK constraints have to be created in the source database. The combination of columns that represent uniqueness can alternatively be recorded into the metadata of Oracle Streams or Oracle Advanced Replication; declarative PK or UK constraints are simply a convenience. Of course, if neither of these are found, then Streams/AdvRep is forced to assume that all columns in combination represent uniqueness, which is not always true either. In which case, should the blame be placed on Streams/AdvRep for assuming that the application was designed according to well-established relational database theory?
Hope this helps...
-Tim
Niall Litchfield wrote:
Date: Mon, 10 Nov 2008 09:26:26 -0700
Message-ID: <491860B2.6080106@evdbt.com>
Niall,
It is not a restriction only of Oracle Streams, but of absolutely any replication solution, anywhere, at any time. How can any UPDATE or DELETE be propagated with a way of uniquely identifying rows, regardless of technology, whether using Oracle or Sybase or DB2 or MySQL, or any mixture of these?
A few years ago, I was on a project where we had Quest and Golden Gate each claiming that their respective products, unlike Oracle Advanced Replication and Oracle Streams, imposed no such restriction. Which is true -- they didn't actually impose the restriction. The Oracle products simply imposed the restriction before it caused logical data corruption.
Supplemental redo logging enables replication by recording the "logical ROWID" data values (i.e. PK/UK/substitute-key) in the generated redo, along with the usual "physical ROWID" values, for each change. For example, prior to the advent of Streams and supplemental redo logging, the Quest SharePlex product had to perform a "lookup" (after the redo was replicated) by "physical ROWID" on the source database in order to obtain the "logical ROWID" data values. The same is almost certainly true of other log-based replication products on Oracle prior to Oracle9i as well.
Still, none of this implies that declarative PK or UK constraints have to be created in the source database. The combination of columns that represent uniqueness can alternatively be recorded into the metadata of Oracle Streams or Oracle Advanced Replication; declarative PK or UK constraints are simply a convenience. Of course, if neither of these are found, then Streams/AdvRep is forced to assume that all columns in combination represent uniqueness, which is not always true either. In which case, should the blame be placed on Streams/AdvRep for assuming that the application was designed according to well-established relational database theory?
Hope this helps...
-Tim
Niall Litchfield wrote:
-- http://www.freelists.org/webpage/oracle-l Received on Mon Nov 10 2008 - 10:26:26 CSTAll,I am being told by a support analyst at Oracle re Streams thatOracle must be able to identify uniquely and match corresponding rows at different
databases.
So each table in a Streams environment should have a primary key, unique key constraints or a substitute key.This in addition to supplemental logging. Is this correct? If so, surely this implies that streams is only useful in environments where tables are guaranteed (by Oracle or the application(s) running against it) to be unique. This seems a somewhat significant real-world disadvantage to me, based on the applications that I see. I guess I sort of hoped that Oracle might have used the ROWID to generated the LCR (in a way that I fully admit I have spend no time at all thinking about).--Niall LitchfieldOracle DBA