Below are few elaborations on supplemental logging requirements when replicating changes (CDC) from Oracle end point source:
- For CDC Replication, Replicate uses the information written to the database redo logs. Therefore, to be able to replicate changes from Oracle endpoint, CDC must be enabled on the database level. This is usually done the Oracle DBA, using the command:
"ALTER database ARCHIVELOG" – which enables the mechanism of Oracle archive logs.
- For Change Data Capture(CDC), database-level supplemental logging must be enabled for every table that you wish to replicate.
- There are a few ways to define supplemental logging for tables. You can either:
- Define in Oracle, that it will be automatically add supplemental logging for every table.
or
2. Add it "manually" for the required table/s.
When supplemental logging is set to be added manually, Replicate can define it automatically for tables defined for replication (from Oracle source endpoint). This option is the default setting for Oracle endpoint. i.e., under Oracle endpoint --> Advanced tab the option ‘Automatically add supplemental logging’ is checked. (In this case the Replicate user should be granted with the permissions required to perform this action)
Alternatively, the definition of supplemental logging for every table (or group of tables) defined for replications is performed manually by the DBA.
- Supplemental Logging can be enabled at database level or at the table level in different settings that determines which CDC information will be written to the Oracle redo logs.
- In many organizations, the default setting of supplemental logging is' identification key' 'conditional' setting. With this setting, when a row is updated, Oracle writes to the archive log only the information of the rows’ primary key (or unique key) column/s and the information of the columns that were updated. That is also the setting implemented by Replicate when the option ‘Automatically add supplemental logging’ is checked.
For example:
If the PK of a table is COL1 and the operation was an Update on COL4 only, only COL1 and COL4 will appear in the Oracle redo log (i.e., all other table columns’ values will NOT appear in the redo-log).
However, in case you configured in the task that for each CDC event, a new column - COLX will be added to the table, and you set its value to be COLX=COL2+COL3, then if you run the Update example mentioned above, it would require the values of the COL2 & COL3 (as they are used in the expression that defines the value of COLX). In this case COLX will be populated with the requested value only if COL2 and COL3 will appear in the redo-log. This scenario would require that supplemental logging setting will include all table columns (and not only changed columns).
*For further details on Oracle supplemental logging setting please refer to Oracle documentation.
