Special handling for off row LOBS

LOB types that are stored within the Oracle database (BLOB, CLOB and NCLOB) can be defined with certain storage characteristics. One of those characteristics, “disable storage in row,” indicates that the data for the LOB should always be recorded separate from the rest of the data in the row the LOB belongs to. This off-row storage requires special handling for replication of updates to these LOB values.

When an off-row LOB value is updated, the change recorded in the redo log is for the index that holds the LOB’s data; the row the LOB belongs to is not changed. As a result, information is missing from the redo log to identify which row in the table the LOB belongs to.

For example, when a non-LOB column is updated in a table, the column data that identifies the changed values and lookup columns is recorded. The command updated myTable set col2 = 2 where col1 = 1 records values in the redo log for the values of both “col2” and “col1.”

In contrast, a command that only updates a LOB that has been defined with the disable storage in row clause records only the LOB data’s change to its index, and not the table that holds the LOB. So the command updated myTable set ClobColumn = 'more data' where col1 = 1 only records the value changed, and does not include the value of “col1”.

Because the value of the columns in the where clause are not logged in that update, there is insufficient information to build the correct where clause to be used to apply the data at the replicate site. To resolve this problem, Replication Agent for Oracle requires that an update to a LOB column defined with disable storage in row must be immediately accompanied by an insert or update to the same row in the table the LOB belongs to.

The Replication Agent uses the additional column data from the associated operation to correctly build the where clause required to support replication.

For example, the following transaction sequences support replication of updates to LOB column “ClobColumn” when it has been defined with the disable storage in row clause:

begin
insert into myTable (col1, col2, ClobColumn, updated) values (1,1,empty_clob(), sysdate);
update myTable set ClobColumn = 'more data' where col1 = 1;
commit 

begin
update myTable set updated = sysdate() where col1 = 1;
update myTable set ClobColumn = 'more data' where col1 = 1;
commit

begin
update myTable set ClobColumn = 'more data' where col1 = 1;
update myTable set updated = sysdate() where col1 = 1;
commit

The following transaction sequences are not supported for LOB columns defined with the disable storage in row clause and result in a failure to supply the LOB data to the replicate site:

• Missing accompanying change to the same row:

  begin
  update myTable set ClobColumn = 'more data' where col1 = 1;
  commit
  

• Accompanying change for the same row is not immediately adjacent to the LOB change:

  begin
  update myTable set updated = sysdate where col1 = 1;
  update myTable set col2 = 5 where col1 = 5;
  update myTable set ClobColumn = 'more data' where col1 = 1;
  commit
  

This limitation only applies to LOB columns that have been defined with the disable storage in row clause.

You can identify the LOB columns in your database that have this constraint using the following query against your Oracle database:

select owner, table_name, column_name from dba_lobs where in_row = 'NO';