Hevo ingests data from your Oracle database in real-time using three replication modes
- Table mode : This mode refers to replicating data from oracle tables either incrementally using an offset or using full load.
- Custom query: This mode involves executing the user specified custom query to ingest the data from Oracle. Use this mode, when you need to ingest the data in a different format than how it is structured.
- Redo log: This involves using Oracle Logminer to incrementally ingest the data from oracle redo logs. This is the recommended way to do data replication in Oracle. You can read more about Logminer here
Setting up Redo log based replication
- Enable ARCHIVE LOG mode
Logminer reads the archived redo log files. Hence its necessary to enable archiving to do redo based replication. To know the current log mode, run this command
SELECT LOG_MODE FROM V$DATABASE;If it returns NOARCHIVELOG, enable archive mode using the steps mentioned here in case of self hosted oracle servers.
In case of AWS RDS servers, run the following command
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',72);
- Enable Supplemental logging
Supplemental logging is required to make sure that the server logs all columns of every change event to the redo logs. Enabled supplemental logging at the database level.
or at the table level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ALL COLUMNS
In case of AWS RDS servers, run the following command to enable supplemental logging.
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA ALL COLUMNS
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');Verify if supplemental logging is enabled.
This should return YES or IMPLICIT.
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE