Prerequisites

The following section describes the prerequisites for working with the Qlik Replicate Oracle endpoint.

Install the required driver

Replicate On Windows

Install Oracle Instant Client for Microsoft Windows (x64) Version 19.0 or later.

Replicate On Linux

1. Stop the Replicate service and optionally confirm that it has stopped as described in Replicate server procedures.

2. Install Oracle Instant Client for Linux (x86-64) Version 19.0 or later.

3. In addition, if not already included in your system, create a symbolic link in the $Oracle_Home\lib directory. Name the link libclntsh.so, and point it to a specific version of this file.

   Example:

   lrwxrwxrwx 1 oracle oracle 63 Oct 2 14:16 libclntsh.so -> /u01/app/oracle/home/lib/libclntsh.so.19.1

4. Change the working directory to <Replicate-Install-Dir>/bin.and append the LD_LIBRARY_PATH environment variable to the Oracle instant client directory and to the Replicate lib directory. Do this by copying the driver location to the site_arep_login.sh file:

   echo "export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/opt/oracle/instantclient_<version>:/opt/attunity/replicate/lib/" > site_arep_login.sh

5. Optionally, confirm that the driver location was copied:

   cat site_arep_login.sh

6. Start the Replicate service and optionally confirm that it has started as described in Replicate server procedures.

Provide Oracle account access

You must provide Oracle account access to the Qlik Replicate user. This user must have read/write privileges on the Oracle database. For information on setting up access to the Oracle account, see Required permissions.

Ensure that ARCHIVELOG mode is on

Oracle can be run in two different modes: the ARCHIVELOG mode and the NOARCHIVELOG mode. To use the Oracle logs with Qlik Replicate, run the database in ARCHIVELOG mode. If the log is not set to ARCHIVELOG mode, then execute the following query:

ALTER database ARCHIVELOG

Note that if your Oracle database instance is on Amazon RDS, a different command needs to be executed. For more information, see Enabling automatic backups and Setting up archiving in Working with Amazon RDS for Oracle.

Set up supplemental logging

Supplemental logging must be enabled for the Oracle database.

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 Working with 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

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).

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.