Oracle Goldengate: Can I configure a specific column always to be captured in Extract even though not a Key Column

user_7272518 0
Viewed 451

I have a Goldengate configuration where INSERTALLRECORDS is used on the target database to replicate INSERT, UPDATE and DELETE operations on the source database to new records.

On the Target DB, the Source DML Operation and Commit Time are captured/recorded to the tables, via the use of @GETENV('GGHEADER') calls

This target DB is then read by an ETL process which applies records to Hadoop in the order they were committed to the target DB.

For example:

CREATE TABLE my_test (id         NUMBER PRIMARY KEY,
                      my_text    VARCHAR2(100) NOT NULL,
                      date_added DATE          NOT NULL);

If I run the following SQL on the Source DB:

INSERT INTO my_test VALUES (1,'Inserting',SYSDATE);
UPDATE my_test SET my_text = 'Updating' WHERE id = 1;
DELETE FROM my_text WHERE id = 1;

this results in 0 records for ID=1 on the Source DB and 3 records on the Target DB, i.e.

ID    MY_TEXT     DATE_ADDED            GG_DML_TYPE     GG_COMMIT_TIMESTAMP
--    -------     ----------            -----------     -------------------
1     Inserting   12-12-2021 16:00:00   INSERT          12-12-2021 16:00:00
1     Updating    12-12-2021 16:00:00   SQL COMPUPDATE  12-12-2021 16:00:01
1                                       DELETE          12-12-2021 16:00:02

The corresponding tables in Hadoop are partitioned by DAY_ADDED, calulated from the Oracle DATE_ADDED column.

Currently, when applying a DELETE operation from Oracle, the ETL is having to scan all Hadoop partitions to find the matching ID record.

Consequently, to improve performance, I would like the DATE_ADDED column to always have its value captured in the Oracle GG Extract, so this will be present in the GG Trail file for all Source DML operations, including DELETEs

The only way I have found to do this is via the use of LOGALLSUPCOLS - however, this logs all columns in the Extract, which I don't want to do for some of our tables which have lots of columns and high volumes.

If anyone knows a way to always capture explicit columns, i.e. DATE_ADDED in this example, in the GG Extract, this would be much appreciated.

oracle-golden-gate extract oracle
1 Answers

There are 2 things you need to take care of:

  1. Make sure that the data for this column is added to supplemental log. You can make sure this happens - with running:

    add trandata my_test cols(date_added)

or by running SQL in the database:

   alter table my_test add supplemental log group grp1 (date_added) always;

  1. You need to make sure that this column gets captured by extract process. To make sure this happens you should use in extract parameter:

    table my_test, cols(date_added);

This should be enough to include this columns in the trail. You can verify trail file if it actually contains date_added column;

user_82177020
Related