Skip to main content

Setting up supplemental logging

Supplemental logging must be enabled for the Oracle database.

Information note

Set up supplemental logging as described in the steps below.

Step 1: Check that supplemental logging is enabled for the database

  1. Run the following query:

    SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

    The returned result should be from GE to n.n.n where n.n.n is the Oracle database version (e.g. 10.0.0).

    Information note

    For Replicate to work, the parameter value must match the real version of the database.

  2. Run the following query:

    SELECT supplemental_log_data_min FROM v$database;

    The returned result should be YES or IMPLICIT.

    Enable supplemental logging by executing the following query:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

    Information note

    If your Oracle database instance is on Amazon RDS, a different command needs to be executed. For more information, see Preparing Replication from Amazon RDS for Oracle.

Step 2: Make sure that the required supplemental logging is added for all columns on each table

  1. If a Primary Key exists, supplemental logging must be added for the Primary Key either by using the format to add supplemental logging on the Primary Key, or by adding supplemental logging on the Primary Key columns.
  2. If no Primary Key exists and the table has a single Unique Index, then all of the Unique Index’s columns must be added to the supplemental log. Using SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS does not add the Unique Index columns to the log.
  3. If no Primary Key exists and the table has multiple Unique Indexes, Qlik Replicate will select the first Unique Index. Qlik Replicate will use the first index in an alphabetically ordered ascending list. Supplemental logging must be added on the selected index's columns. Using SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS does not add the Unique Index columns to the log.
  4. If there is no Primary Key and no Unique Index, supplemental logging must be added on all columns.

    Information note

    When the target table Primary Key/Unique Index is different than the source table Primary Key/Unique Index, the user needs to add supplemental logging manually on the source table columns that comprise the target table Primary Key/Unique Index.

  5. If you change the target table primary key, the supplemental logging must be added on the selected index's columns instead of the columns of the original primary key/unique index.

Step 3: If a filter or transformation is defined for the table, additional logging might be necessary

Information note

If ALL COLUMNS supplemental logging has been added to the table, there is no need to add any additional logging.

If the table has a Unique Index or a Primary Key, you also need to add supplemental logging on each column that is involved in a filter or transformation (if those columns are different than the Primary Key or Unique Index columns).

Information note

If a transformation uses only one column, this column may not be added to a supplemental logging group. For example, "A+B" needs both columns to be added, whereas substring(A, 10) does not need "A" to be added.

One method of setting up both Primary Key/Unique Index supplemental logging and supplemental logging on specific columns is to add USER_LOG_GROUP supplemental logging only on the Primary Key/Unique Index columns and on the columns that are filtered or transformed.

For example, to replicate a table named EXAMPLE.TABLE with Primary Key ID and filter by column NAME, you can run a command similar to the one below to create the log group supplemental logging:

ALTER TABLE EXAMPLE.TABLE ADD SUPPLEMENTAL LOG GROUP example_log_group (ID,NAME) ALWAYS;

Step 4: When the Insert the missing target record Apply Conflicts option is selected, supplemental logging must be enabled for ALL the source table columns.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!