The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Environment:
Qlik Replicate
Oracle Source
5 Reasons to set ALL COLUMN supplemental logging on Oracle Source DB
All Column logging set on a table will force Oracle to include every field in the redo log, whether it was updated or not.
ALTER TABLE <schema>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
In most Oracle environments the source logging is set to Primary Key logging only, as this will reduce the size of the redo log since only the PK field and any fields that have changed will be logged.
The following are 5 common task configuration scenarios where ALL Column logging must be enabled.
NOTE: Oracle version 12.2 limitation on All Column Supplemental logging
For the all column supplemental logging to work correctly in version 12.2, the table name or column name should not be over the 30 characters which is an oracle limitation.
Oracle increased the length of the names in version 12.2 but not the supplemental logging limitation. This is Oracle limitation with out the entries in redo log replicate cannot get the all columns data.
Please check/google Oracle : Log Miner Shows SQL_REDO As UNSUPPORTED for table names longer than 30 characters (Doc ID 2703406.1)
1) Upsert Merge:
Under Task Settings/Error Handling/Apply Conflicts: No record found for applying an UPDATE:
Upsert mode will convert an update statement to an insert statement. Without ALL column logging set for the source table when this convert to insert is done there may be missing columns in the insert statement that could cause the insert to fail.
In order for the task to capture all the field values you need to set ALL Column logging.
Please see the link listed below for more information on Upsert Merge.
2) Change capture table:
Under Task Settings/Change Processing/Store Changes Settings: Store Changes in Change Tables
When __ct tables are enabled in a task, every transaction from the source is written to the __CT table. Depending on what the users are doing with the records in this table they may need to see all field values - not just the ones that were changed.
In order for the task to capture all the field values you need to set ALL Column logging.
3)Transformations :
Under Table Settings/Transform/Expression:
Transformations often depend on fields in the record having a value, without All Column logging it is possible that the transformation will fail to evaluate correctly, since the field value the transformation depends on may not be logged in the transaction if it did not change.
In order for the task to capture all the field values you need to set ALL Column logging.
4) Filters :
Under Table Settings/Filter/Filter Conditions:
If the filter is using non key values to determine if a row is included, then you will need to ensure that the field is in the redo log by enabling All Column logging for the table.
In order for the task to capture all the field values you need to set ALL Column logging.
5) Table with no PK field:
Under Table Settings/Transform/Output:
In order to update a record in the target that has no PK fields the task will use the before image of the record, which should contain all the fields in the table to find the record.
In order for the task to capture all the field values you need to set ALL Column logging.
