By Default, Captured LOB Update Contains Only Key And Changed Columns In IE, Even With Supplemental Log On All The Non-LOB Columns (Doc ID 2106771.1)

Last updated on JANUARY 05, 2017

Applies to:

Oracle GoldenGate - Version 12.1.2.1.2 and later
Information in this document applies to any platform.

Goal

When using DBMS_LOB.LOADFROMFILE to update a LOB column only and supplemental log is on all the columns, IE (integrated extract) by default will capture the key column(s) and LOB, for better performance. For CE (classic extract), it will capture the all the columns.

===========================
example:

create table x1 (a number primary key, b varchar2(100), c clob, d date);

ggsci> add trandata fzhang.x1 ALLCOLS

create directory fan_dir as '/tmp';
INSERT INTO x1 values (1,1,1,sysdate);
commit;

DECLARE
l_src_file bfile := BFileName( 'FAN_DIR', 'tt' ); --- /tmp/tt is a text file > 4k
l_dest_lob clob;
l_dest_offset integer := 1;
l_src_offset integer := 1;
l_lang_context number := dbms_lob.default_lang_ctx;
l_warning number;
BEGIN
select c into l_dest_lob from x1 where a=1 for update;

dbms_lob.open( l_src_file, dbms_lob.lob_readonly );
dbms_lob.LoadCLOBFromFile(
dest_lob => l_dest_lob,
src_bfile => l_src_file,
amount => dbms_lob.getLength( l_src_file ),
dest_offset => l_dest_offset,
src_offset => l_src_offset,
bfile_csid => dbms_lob.default_csid,
lang_context => l_lang_context,
warning => l_warning );

dbms_lob.close( l_src_file );
commit;
END;
/

Logdump 123 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 43 (x002b) IO Time : 2016/02/04 23:29:54.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 520 AuditPos : 7437548
Continued : N (x00) RecCount : 1 (x01)

2016/02/04 23:29:54.000.000 FieldComp Len 43 RBA 1479
Name: XXX.X1
Before Image: Partition 4 G b
0000 0005 0000 0001 3100 0100 0500 0000 0131 0003 | ........1........1..
0015 0000 3230 3136 2d30 322d 3034 3a32 333a 3033 | ....2016-02-04:23:03
3a30 30 | :00
Column 0 (x0000), Len 5 (x0005)
0000 0001 31 | ....1
Column 1 (x0001), Len 5 (x0005)
0000 0001 31 | ....1
Column 3 (x0003), Len 21 (x0015)
0000 3230 3136 2d30 322d 3034 3a32 333a 3033 3a30 | ..2016-02-04:23:03:0
30 | 0

Logdump 124 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 9 (x0009) IO Time : 2016/02/04 23:29:54.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x01) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 520 AuditPos : 7437548
Continued : Y (x01) RecCount : 1 (x01)

2016/02/04 23:29:54.000.000 FieldComp Len 9 RBA 1645
Name: XXX.X1
After Image: Partition 4 G m
0000 0005 0000 0001 31 | ........1
Column 0 (x0000), Len 5 (x0005)
0000 0001 31 | ....1

Logdump 125 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 4360 (x1108) IO Time : 2016/02/04 23:29:54.000.000
IOType : 116 (x74) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 520 AuditPos : 7437548
Continued : N (x00) RecCount : 2 (x02)

2016/02/04 23:29:54.000.000 LargeObject Len 4360 RBA 1750
Name: XXX.X1
After Image: Partition 4 e
0000 0002 0000 0001 0000 0001 0000 0000 0000 10f4 | ....................
0000 0000 0000 0001 726f 6f74 2020 2020 2020 3334 | ........root 34
3733 2020 2020 2031 2020 3020 4a61 6e31 3920 3f20 | 73 1 0 Jan19 ?
2020 2020 2020 2030 303a 3030 3a30 3020 2e2f 6870 | 00:00:00 ./hp
696f 640a 726f 6f74 2020 2020 2020 3334 3738 2020 | iod.root 3478
2020 2031 2020 3020 4a61 6e31 3920 3f20 2020 2020 | 1 0 Jan19 ?
2020 2030 303a 3030 3a31 3720 2f75 7372 2f62 696e | 00:00:17 /usr/bin
Column 2, LOB fragment 1 of 1
Size 4340 (x10f4)
Offset 0 (x0000)

=========================================

This will make no difference for like to like replication. But for
replication to data warehouse, if you want all the columns for update,
this default behavior difference needs to be considered.


 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms