Oracle

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

  1. 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);
    
  2. 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.

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ALL COLUMNS
    

    or at the table level

    ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA ALL COLUMNS
    

    In case of AWS RDS servers, run the following command to enable supplemental logging.

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');
    

    Verify if supplemental logging is enabled.

    SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM "V$DATABASE"
    

    This should return YES or IMPLICIT.