Special Handling for Off-Row Large Objects

Learn how Replication Agent handles off-row stored LOBs.

LOB types that are stored within the Oracle database (BLOB, CLOB, and NCLOB) may 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 separately 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 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 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, these 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

Note: For purposes of replication, LOB objects populated with the empty_clob or empty_lob function are replicated as NULL values. Replication definitions for LOB columns should therefore include the “null” keyword as part of the column definition.

These 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 applies only 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 this query against your Oracle database:

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