LOB Replication With GoldenGate and Importance of Primary Keys

Every time I work on database migrations with GoldenGate, I have the same talks with application teams about missing primary keys. Especially when it comes to LOB replication with GoldenGate. In a previous blog, I wrote about how to interpret the DBA_GOLDENGATE_NOT_UNIQUE view, which lists tables with no primary key or non-null unique indexes. There are two main groups of tables listed here:

• Tables where all columns can be used to identify uniqueness (bad_column='N')
• Tables where not all columns can be used (bad_column='Y')

To show what happens when primary keys are missing in GoldenGate replication, let’s see a few examples. In all the examples, I will extract data from APP_SOURCE schema and replicate it into an APP_TARGET schema.

The parameters of the replication do not really matter here, but to be precise, here is the extract parameter file:

EXTRACT EXT1
USERIDALIAS source_cdb DOMAIN OracleGoldenGate
EXTTRAIL pdb1/aa
SOURCECATALOG PDB1
DDL INCLUDE MAPPED
TABLE APP_SOURCE.*;

And here is the replicat parameter file:

REPLICAT REP2
USERIDALIAS target_pdb DOMAIN OracleGoldenGate
DDL INCLUDE MAPPED
MAP PDB1.APP_SOURCE.*, TARGET PDB2.APP_TARGET.*;

First test : simple table with primary key

First, let’s try the most common example, with a table APP_SOURCE.TEST_PK created with a primary key. You can skip this part if you know how GoldenGate works, but I just wanted to include this for comparison.

CREATE TABLE APP_SOURCE.TEST_PK
(
    id   NUMBER PRIMARY KEY,
    data VARCHAR2(100)
);

Let’s insert a few rows in this table and look at the content of the table in both the source and the target.

INSERT INTO APP_SOURCE.TEST_PK VALUES (1,'A');
INSERT INTO APP_SOURCE.TEST_PK VALUES (2,'B');
COMMIT;

-- Verify source/target
SELECT * FROM APP_SOURCE.TEST_PK ORDER BY id;
SELECT * FROM APP_TARGET.TEST_PK ORDER BY id;

You will see the same content in both tables.

SQL> SELECT * FROM APP_SOURCE.TEST_PK ORDER BY id;

        ID DATA
---------- --------------------
         1 A
         2 B

Now, if you update the row with id=1, it will be updated in both tables as well.

UPDATE APP_SOURCE.TEST_PK SET data='A_UPDATED' WHERE id=1;
COMMIT;

-- Verify
SELECT * FROM APP_SOURCE.TEST_PK ORDER BY id;
SELECT * FROM APP_TARGET.TEST_PK ORDER BY id;

Checking the tables will give you the updated data.

SQL> SELECT * FROM APP_TARGET.TEST_PK ORDER BY id;

        ID DATA
---------- --------------------
         1 A_UPDATED
         2 B

Until then, nothing really interesting, but let’s look at the report file of the extract on the source. You will see the following information:

2026-06-06 07:00:52  INFO    OGG-06509  Using the following key columns for source table PDB1.APP_SOURCE.TEST_PK: ID.

Because the table has a primary key, GoldenGate knows that it should only use this column to identify unique rows.

Second test : table with no primary key and no LOB columns

In this second test, I will create a table without a primary key, but using only types that GoldenGate can use to identify uniqueness.

CREATE TABLE APP_SOURCE.TEST_NOPK
(
    id   NUMBER,
    data VARCHAR2(100)
);

We can already query the DBA_GOLDENGATE_NOT_UNIQUE view to find this new table listed with bad_column='N' :

SELECT *
FROM dba_goldengate_not_unique
WHERE owner='APP_SOURCE'
ORDER BY owner, table_name;

OWNER                          TABLE_NAME                     BAD_COLUMN
------------------------------ ------------------------------ ----------
APP_SOURCE                     TEST_NOPK                      N

Let’s do the same steps as before, inserting data and updating the content.

INSERT INTO APP_SOURCE.TEST_NOPK VALUES (1,'A');
INSERT INTO APP_SOURCE.TEST_NOPK VALUES (2,'B');
COMMIT;

SELECT * FROM APP_SOURCE.TEST_NOPK ORDER BY id;
SELECT * FROM APP_TARGET.TEST_NOPK ORDER BY id;

UPDATE APP_SOURCE.TEST_NOPK SET data='A_UPDATED' WHERE id=1;
COMMIT;

The content of the table is the same on source and target, as expected.

SQL> SELECT * FROM APP_TARGET.TEST_NOPK ORDER BY id;

        ID DATA
---------- --------------------
         1 A_UPDATED
         2 B

And looking at the extract report file, we can see that all viable columns were used. In that case, it meant taking ID and DATA.

2026-06-06 07:08:13  INFO    OGG-06508  Wildcard MAP (TABLE) resolved (entry PDB1.APP_SOURCE.*): TABLE "PDB1"."APP_SOURCE"."TEST_NOPK".

2026-06-06 07:08:13  WARNING OGG-06439  No unique key is defined for table TEST_NOPK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. If using KEYCOLS, make sure that you create an INDEX in the target database for those column(s) as well.

2026-06-06 07:08:13  INFO    OGG-06509  Using the following key columns for source table PDB1.APP_SOURCE.TEST_NOPK: ID, DATA.

As mentioned in the other blog about primary keys in GoldenGate, this will work, but you will of course have performance issues when replicating data.

Third test : Table with no PK and a CLOB column

Now, let me show you the bad use case, where you don’t have a primary key and the table contains a non-viable column for uniqueness identification. In this example, I will use a CLOB column, but it could be another unbounded data type.

CREATE TABLE APP_SOURCE.TEST_CLOB
(
    id   NUMBER,
    code VARCHAR2(10),
    txt  CLOB
);

If we check the DBA_GOLDENGATE_NOT_UNIQUE view, we see the new table listed with bad_column='Y'.

SELECT *
FROM dba_goldengate_not_unique
WHERE owner='APP_SOURCE'
ORDER BY owner, table_name;

OWNER                          TABLE_NAME                     BAD_COLUMN
------------------------------ ------------------------------ ----------
APP_SOURCE                     TEST_CLOB                      Y
APP_SOURCE                     TEST_NOPK                      N

Let’s insert some data. But this time, I will add two more rows that are unique, but for which only the TXT content differs.

INSERT INTO app_source.test_clob VALUES (1,'A',TO_CLOB('LOB_1'));
INSERT INTO app_source.test_clob VALUES (2,'B',TO_CLOB('LOB_2'));
INSERT INTO app_source.test_clob VALUES (100,'DUP',TO_CLOB('LOB_1'));
INSERT INTO app_source.test_clob VALUES (100,'DUP',TO_CLOB('LOB_2'));
COMMIT;

The data was correctly inserted, and the content is the same on the source and target.

SQL> SELECT id,code,DBMS_LOB.SUBSTR(txt,40,1) txt FROM app_target.test_clob ORDER BY id;

        ID CODE       TXT
---------- ---------- ----------------------------------------
         1 A	      LOB_1
         2 B	      LOB_2
       100 DUP	      LOB_1
       100 DUP	      LOB_2

In the report file, we do not see any difference between this example and the one before. GoldenGate mentions using two columns for uniqueness identification : ID and CODE. GoldenGate never mentions that a third column existed and was not used. The warning displayed is the same as before.

2026-06-06 07:10:13  INFO    OGG-06508  Wildcard MAP (TABLE) resolved (entry PDB1.APP_SOURCE.*): TABLE "PDB1"."APP_SOURCE"."TEST_CLOB".

2026-06-06 07:10:13  WARNING OGG-06439  No unique key is defined for table TEST_CLOB. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. If using KEYCOLS, make sure that you create an INDEX in the target database for those column(s) as well.

2026-06-06 07:10:13  INFO    OGG-06509  Using the following key columns for source table PDB1.APP_SOURCE.TEST_CLOB: ID, CODE.

And now, the main problem I wanted to discuss regarding LOB replication with GoldenGate. If you try to delete one row by specifying all three columns, it will not always work.

DELETE FROM app_source.test_clob
WHERE id = 100
AND code = 'DUP'
AND DBMS_LOB.COMPARE(txt,TO_CLOB('LOB_1')) = 0;

Sometimes, the line with LOB_1 will be deleted:

        ID CODE       TXT
---------- ---------- ----------------------------------------
         1 A	      LOB_1
         2 B	      LOB_2
       100 DUP	      LOB_2

But sometimes, the line with LOB_2 will be deleted:

        ID CODE       TXT
---------- ---------- ----------------------------------------
         1 A	      LOB_1
         2 B	      LOB_2
       100 DUP	      LOB_1

More precisely, it will just delete the first line that was inserted. You then have two cases. If you insert LOB_1 first, it will be deleted correctly:

DROP TABLE app_source.test_clob PURGE;

CREATE TABLE app_source.test_clob (id NUMBER, code VARCHAR2(10), txt CLOB);

INSERT INTO app_source.test_clob VALUES (100,'DUP',TO_CLOB('LOB_1'));
INSERT INTO app_source.test_clob VALUES (100,'DUP',TO_CLOB('LOB_2'));
COMMIT;

DELETE FROM app_source.test_clob
WHERE id = 100
AND code = 'DUP'
AND DBMS_LOB.COMPARE(txt,TO_CLOB('LOB_1')) = 0;
COMMIT;

SQL> SELECT id,code,DBMS_LOB.SUBSTR(txt,40,1) txt
FROM app_target.test_clob
WHERE code='DUP';

        ID CODE       TXT
---------- ---------- ----------------------------------------
       100 DUP	      LOB_2

And if you insert LOB_2 first, the incorrect line will be deleted:

DROP TABLE app_source.test_clob PURGE;

CREATE TABLE app_source.test_clob (id NUMBER, code VARCHAR2(10), txt CLOB);

INSERT INTO app_source.test_clob VALUES (100,'DUP',TO_CLOB('LOB_2'));
INSERT INTO app_source.test_clob VALUES (100,'DUP',TO_CLOB('LOB_1'));
COMMIT;

DELETE FROM app_source.test_clob
WHERE id = 100
AND code = 'DUP'
AND DBMS_LOB.COMPARE(txt,TO_CLOB('LOB_1')) = 0;
COMMIT;

SQL> SELECT id,code,DBMS_LOB.SUBSTR(txt,40,1) txt
FROM app_target.test_clob
WHERE code='DUP';

        ID CODE       TXT
---------- ---------- ----------------------------------------
       100 DUP	      LOB_1

You now know why you should always pay attention to tables in the DBA_GOLDENGATE_NOT_UNIQUE view with bad_column='Y'. If you have such tables, there are several scenarios that I presented in the last blog on the topic. But ignoring them is not one of them. This way, you will avoid issues regarding LOB replication with GoldenGate.