Source Database is Oracle 12.1.0.2, the target is PostgreSQL 9.6.3, CDC via LogMiner. Use the Oracle Binary Reader option if you require support for LOBS in 12c. AWS documentation has a more detailed description on dealing with columns without primary keys.
- When defining the source endpoint in the DMS Service page, navigate to “Advanced” > “Extra connection attributes” ensure to include “addSupplementalLogging=Y” in the box.
- Enable this on your Oracle database.
SELECT supplemental_log_data_min “Minimum”, supplemental_log_data_pk “Primary key”, supplemental_log_data_ui “Unique Key”, supplemental_log_data_fk “Foregin Key”, supplemental_log_data_all “All” FROM v$database;
Minimum Pri Uni For All
————— —- —– —– —–
NO NO NO NO NO
Online Operation
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
Review your alert log
select supplemental_log_data_min “Minimum” FROM v$database;
Minimum
—————
YES
Create a dynamic script to enable supplemental logs on primary keys. Its also possible to log all the columns, ensure to test all the options and keep an eye on your alert log, and performance, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Please refer to Oracle 12c documents for further details.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
set head off
set echo offset linesize 100
select ‘alter table ‘||owner||’.’||object_name||’ add supplemental log data (PRIMARY KEY) columns;’ from dba_objectswhere object_type=’TABLE’ and owner=’SCHEMA’;
