In order for the Oracle Capture Agent to be able to extract the changed data for a Oracle table(s) from the recovery log, supplemental logging must be explicitly activated for each table and column that will be captured.
Syntax
$ oracle connect to <database_name>
SQL> ALTERTABLE <schema.tablename> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Keyword and Parameter Descriptions
| Keyword | Description |
|---|---|
| <schema.tablename> |
Fully qualified name of the source table in which changes are to be captured. |
Note:
- While it is possible to specify only the columns that are required for downstream replication, Precisely recommends selecting all columns for capture since the additional overhead is generally not significant and it simplifies maintenance required when transformation and replication requirements change. Oracle 10 and above support and Precisely recommends, selection of all columns using the (ALL) option each source Oracle table.
- Optionally for Oracle 10 but required for Oracle 9, each column must be explicitly activated and there is a limit of 32 columns per statement. If a table has more than 32 columns it is necessary to execute the ALTER statement for each slice of 32 columns:
ALTERTABLE schema.tableADD SUPPLEMENTAL LOG DATA (column [,column][,column]...) COLUMNS; - If All columns are not specified you must ensure that the DDL used to describe the source datastore in the Engine scripts specifies ONLY those columns that are captured.
- Enabling change data capture will increase the amount of data written to the Oracle recovery log for each update to the source data table. Depending on the size of the tables and the volume of updates made to the table, the size of the active Oracle logs may have to be adjusted to accommodate the increase data.