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’;
Hi,
Its a nice write up on supplemental logging if using DMS. Can you please suggest what actual supplemental logging needs to be run for Migrate Existing and Ongoing replication:
Minimal supplemental logging:
alter database add supplemental log data;
And
Alter database add supplemental log data (primary key) columns;
or
on all tables for PK columns, or Unique or both PK and unique or all columns.
AWS doc says, if tables have PK, then it must be enabled on PK columns.
My source env shows:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_PL from v$database;
SUPPLEME SUP SUP SUP SUP SUP
——– — — — — —
YES YES NO NO NO NO
Assuming all the tables have PK, which supplemental logging we should enable to ensure replication capture and propagate all the changes from source to target post full load.
LikeLike
As per the docs, you should enable supplemental logging – “Enable supplemental logging by running the following query: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;” and enable supplemental logging for each table -ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS (When you enable identification key logging at the database level, minimal supplemental logging is enabled implicitly.
Supplemental), and for unique keys etc…. You will want to confirm your use case. If you have blobs “Binary Reader supports CDC for LOBS in Oracle version 12c. LogMiner does not.”. Reference https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Configuration; https://docs.oracle.com/database/121/SUTIL/GUID-D2DDD67C-E1CC-45A6-A2A7-198E4C142FA3.htm#SUTIL1583
LikeLike
Thanks Alinafe for clarification. We’ll have to use Oracle Binary reader option, however I am not clear as what user its referring to when it says you need password for Oracle User and ASM user? Is it Oracle OS and ASM Os user (grid) or DB user with privilege on dbm2_file_transfer package to be able to read and write Archive logs from ASM DISKS. Source is 2 node 11gr2 RAC using ASM Storage.
LikeLike
I think, I figured out the ASM User requirement – ASMSNMP with sysasm. I think, Golden Gate Classic capture use the same. But, I am still not clear as in what DMS doc mean when they say Oracle USer Password along with ASM User password. Is it the DMS DB user or Oracle OS User or a separate db user with SYSDBA/SYSOPER privs. Please can you clarify if you know about this?
LikeLike
Example: oracle_user_password,asm_user_password; seems to only reference database user not os.
LikeLike
Thanks Alinafe again. If it seems to only refer database user, does that mean the DMS database user we have on source to connect and migrate data? We already have a user for DMS on source db with privileges required.
LikeLike