Re: Replication options with ROWID updates

Rich,

You must be able to uniquely identify a row to be able to logically replicate it, whether that be via a primary key, unique key, or a combination of columns which can be guaranteed to be unique (up to 38 columns in the case of Goldengate).

If a table has no identifiable PK/UK, I usually recommend that a default surrogate key is added using a sequence and a default (in 12, I like the DEFAULT sequence-name.NEXTVAL syntax ). Do not add an identity column - they are not support for them in replication scenarios at present.

If you are doing updates by rowid, you need to set the appropriate level of supplemental logging to ensure that the relevant data (pk/uk/all columns) is captured in the redo regardless of the modification statement to ensure the row on the destination can be identified and manipulated. This will increase the amount of redo written and therefore have an impact on redo performance. PK supplemental logging will add minimal overhead, supplementally logging all columns may be devastating depending upon the size of each row.

regards

Neil Chandler

On 2018/03/27 11:21, vit.spinka wrote:

Note that all logical replication products see the data as they are in the redo - they don't see the original SQL. Thus if the table has a primary key, they will use it to apply the changes - and it does not matter at all whether the user sql was using rowid or whatever else.

Right, but a primary key is required in this case, correct? Not sure how else this would be accomplished...

Thanks!
Rich

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