Striim 3.9.7 documentation

Enabling supplemental log data

The following steps check whether supplemental log data is enabled and, if not, enable it.

  1. Enter the following command: 

    select supplemental_log_data_min, supplemental_log_data_pk from v$database;

    If the command returns YES or IMPLICIT, supplemental log data is already enabled. For example, 

    SUPPLEME SUP
    -------- ---
    YES      NO

    indicates that supplemental log data is enabled, but primary key logging is not. If it returns anything else, enter:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  2. To enable primary key logging for all tables in the database enter: 

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Alternatively, to enable primary key logging only for selected tables (do not use this approach if you plan to use wildcards in the OracleReader Tables property to capture change data from new tables):

    ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
  3. If replicating Oracle data to Redshift, or to BigQuery or Snowflake with Optimized Merge disabled, enable supplemental logging on all columns for all tables in the database:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Alternatively, to enable only for selected tables:

    ALTER TABLE <schema>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
  4. To activate your changes, enter:

    alter system switch logfile;

If using Amazon RDS for Oracle, use the following commands instead:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD', p_type => 'PRIMARY KEY');
exec rdsadmin.rdsadmin_util.switch_logfile;
select supplemental_log_data_min, supplemental_log_data_pk from v$database;