What is Oracle GoldenGate Extract writing to the trail file (OGG 11.2)

In one of my previous posts I have made a detailed analysis about supplemental logging in the database that is a result of ADD TRANDATA command from OGG. Apart of what is written to the redo log it is another subject of what is read from the redo log by OGG and written to the trail file.

In a series of articles I am about do analyze what can be done by the Extract process and how different parameters affect the result. I’m starting with OGG 11.2.

Although OGG 11.2 is a quite old version I have started my blog post series from this version as it allows to understand how the Extract process behavior has changed over the versions and how the parameters interact.

Tests

The Extract process in OGG 11.2 has the following parameters that control information written to the trail:

• NOCOMPRESSUPDATES
• COMPRESSUPDATES
• NOCOMPRESSDELETES
• COMPRESSDELETES
• IGNOREUPDATEBEFORES
• GETUPDATEBEFORES

I’m assuming that ADD TRANDATA has configured the database to write enough information to the redo log (using supplemental logging). Now it is the work of the Extract process to read this information and write to the trail file.

To fully understand the behavior of the Extract process I have created an automated test environment which allows me to check every combination (including permutations) of the Extract parameters. I’m assuming that it is enough to use the parameter just once and I am not using contrary parameters (like COMPRESSDELETES and NOCOMPRESSDELETES) together.

The test has been performed using OGG version 11.2.1.0.33 which is connected to a 11.2.0.4.161018 Oracle database. All hosts are running on Linux x86_64.

Source table

To make sure the test checks every possible combination I have created a test with 8 tables to check the following scenarios:

1. The source table has PK (Primary Key) + UI (Unique Index) + FK (Foreign Keys)
2. The source table has PK + UI + FK – KEYCOLS is defined as the columns from UI
3. The source table has PK + UI + FK – KEYCOLS is defined as the columns outside PK and UI
4. The source table has UI + FK – KEYCOLS is defined as the columns from UI
5. The source table has UI + FK – KEYCOLS is defined as the columns outside UI
6. The source table has no UI and no PK – KEYCOLS is defined as 1 column
7. The source table has no UI and no PK – KEYCOLS is defined as 2 column, one of them is modified
8. The source table has no UI and no PK – KEYCOLS is defined as the full list of all columns

To avoid some different strange behavior when ADD TRANDATA was used I have always specified the full list of columns that needed supplemental logging and I have also used the NOKEY clause (in all scenarios but #1).

For every table I run the test:

1. Insert a row
2. Update a row
3. Update the unique identified (PK,
4. Delete a row

I am aware that updating the PK is not very elegant. But still it might happen in some systems. The purpose of the test is to check what happens if something like is made in the source database.

Target table

The Replicat process is using INSERTALLRECORDS to show what is exactly in the trail file. I am also reading from the trail the information about OPTYPE and BEFOREAFTERINDICATOR using the @GETENV token from GGHEADER. This allows me to get additional information of what is exactly in the trail file.

Results

Checking all possible combinations of parameters I came to the following conclusion:

1. The COMPRESSUPDATES | NOCOMPRESSUPDATES parameter has no effect in OGG for Oracle. This is actually exactly what was supposed to happen. This parameter according to the documentation of OGG 11.2 should not be used for Oracle.
2. The NOCOMPRESSDELETES | COMPRESSDELETES parameter affects only DELETE operations
3. The IGNOREUPDATEBEFORES | GETUPDATEBEFORES parameter affects only UPDATE operations
4. COMPRESSDELETES is default
5. IGNOREUPDATEBEFORES is default
6. The Classic Extract works the same way as the Integrated Extract process.

Insert operations

All parameters have no effect on INSERT operations. The INSERT operation will be written to the trail always as a full set of all the columns.

UPDATE operations

There are two possible scenarios:

1. IGNOREUPDATEBEFORES – the default

The UPDATE operations contained only the AFTER image of the operation. It contained the BEFORE image only if part of PK has been modified.

2. GETUPDATEBEFORES

The UPDATE operations contained both the BEFORE and the AFTER image.

 

There is one issue in OGG 11.2 that is concerning situations when the part of the Primary Key (or columns defined by KEYCOLS) is modified.

1. When there is a Primary Key defined and part of PK is modified on the table – it works fine
2. When there is no Primary Key defined and no Unique Index and all columns are used as KEYCOLS – it works fine
3. Things go wrong if part of the columns defined as KEYCOLS is modified (when there is no Primary Key and no Unique Index).

UPDATE completely fails. The Extract process would contain write NULL values for both BEFORE and AFTER images and even tokens like @GETENV(“GGHEADER”,”TABLENAME”) would fail and give a NULL value.

This is probably a known issue to Oracle, since this behavior would not occur any more in OGG 12.2. So it is probably a bug, which has been corrected in later versions, just not backported to OGG 11.2 (and 12.1). In this situations it is safer to just migrate to OGG 12.2.

Delete operations

There are two possible scenarios:

1. COMPRESSDELETES – the default

The DELETE operations contained only the Primary Key (or columns defined by KEYCOLS) of the deleted row. This is the default.

2. NOCOMPRESSDELETES

The DELETE operations contained the full image of the deleted row.

Conclusions

I have tested all possible 79 permutations of all 6 parameters and there are no surprises here. Parameters work as intended. Actually funny stuff will come out in OGG 12.1.

Do not use OGG 11.2 since the Extract process might work incorrectly in situations where the table does not have physical constraints for PK and UI and you define the KEYCOLS as a part of the columns. And those columns might get updated. Use OGG 12.2 instead.

The COMPRESSUPDATES option does not work for Oracle.

The Extract process in OGG 11.2 is not interested in conditional logging of FOREIGN KEY and UNIQUE constraints like Extracts in later OGG versions. Those constraints are ignored.

But on the other hand OGG 11.2 has very simple and meaningful parameters. And they work as intended. One parameter controls the update operations and one – the delete operations. They seem not to interfere. This is not true in latter versions as will be show in the next post about parameters for OGG 12.