Create a Mirroring Replication from Oracle

Overview

This aricle provides essential information for setting up replications using Oracle as a Source database for one-way mirroring and synchronization that uses the Change Data Capture (CDC) capability. To create a refresh replication, read this article.

This guide describes the setup process using either the Log Reader or Log Server Agent options for one-way mirroring and synchronization when replicating data from an Oracle database. For mirroring and synchronization replications using Oracle as a source, Syniti Replicate offers several approaches:

• Log Reader (default): Queries the Oracle Log Miner directly

• Log Server Agent (optional): Uses a Windows service and a Log Server component to query the Oracle Log Miner

• Triggers: Uses Syniti Replicate triggers installed on the Oracle database to log changes

Versions Supported for Mirroring

Oracle is supported as source systems in Refresh, Mirroring, and Synchronization with the following version combinations:

• Oracle version 12c

• Oracle version 18c

• Oracle version 19c

• Oracle version 21c

Editions Supported

The following versions are supported as source systems in Mirroring mode:

• Oracle Enterprise Edition

• Standard Edition

• Express Edition (Triggers only)

• Oracle Autonomous (Triggers only)

• Oracle Autonomous Warehouse (Triggers only)

• AWS RDS for Oracle

• Oracle Cloud Instance

Enable a Mirroring replication using Log Reader

To enable a Mirroring or CDC replication using Log reader option, you have to follow those steps:

• Check the requirements to use Oracle as a source connection

• Understand how Syniti Replicate utilize the Log Miner and the Redo Logs

• Check User Permissions for Oracle

• Enable the Transactional mode in Syniti Replicate

• Create a transactional replication with Oracle

User Permissions

When setting up the connection for transactional replications (where Oracle is serving as the source of data), you may need additional privileges that are not required for replication operations. You need to alter the database to enable SUPPLEMENTAL LOG.

You need an ALTER DATABASE privilege to set up supplemental logging and an ALTER TABLE privilege on each table to be replicated when setting up a replication. If the table already has a supplemental log set, then Syniti Replicate does not need this privilege. You can control whether supplemental logging is enabled at the table or database level using the Oracle Setup Info dialog available from the Connection Properties dialog (under Transaction Log Type.) To set up supplemental logging independent of Syniti Replicate (i.e. not through the Oracle Setup Info dialog), use the following commands:

• For database-level supplemental logging:

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS

• For minimal/table-level supplemental logging:
  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

• In this last case, also an ALTER TABLE statement will be used on each replicated table:
  ALTER TABLE MYSCHEMA.MYTABLE ADD SUPPLEMENTAL LOG GROUP <groupname> ( <pkList> ) ALWAYS

  Where pkList is a comma-separated list of the primary keys of the table, while groupname is an internally generated name of the supplemental log group.

When setting up the connection for transactional replications (where Oracle is serving as the source of data, either for mirroring or for synchronization), the following permissions are also required: SELECT_TABLE permissions on the source table (see above); execute permission to run the logminer; and access all system tables to retrieve log information and the last transaction ID.

Connection Permissions
grant create session to replicate_user; or grant connect to replicate_user;
grant select on SYS.ALL_USERS to replicate_user;
grant select on SYS.ALL_TABLES to replicate_user;
grant select on SYS.ALL_TAB_COMMENTS to replicate_user;
grant select on SYS.ALL_OBJECTS to replicate_user;
grant select on SYS.ALL_VIEWS to replicate_user;
grant select on SYS.ALL_TAB_COLUMNS to replicate_user;
grant select on SYS.ALL_COL_COMMENTS to replicate_user;
grant select on SYS.ALL_CONSTRAINTS to replicate_user;
grant select on SYS.ALL_CONS_COLUMNS to replicate_user;
grant select on SYS.ALL_IND_COLUMNS to replicate_user;
grant select on SYS.ALL_INDEXES
grant select on SYS.USER_CONSTRAINTS to replicate_user;
grant select on SYS.USER_CONS_COLUMNS to replicate_user;
grant select on <tables_name> to replicate_user;

Specific Mirroring Permissions
grant execute on sys.dbms_logmnr to replicate_user; 
grant select on sys.v_$parameter to replicate_user; 
grant select on sys.v_$log to replicate_user; 
grant select on sys.v_$logfile to replicate_user; 
grant select on sys.V_$logmnr_contents to replicate_user; 
grant select on sys.V_$thread to replicate_user; 
grant select on sys.V_$archive_dest to replicate_user;
grant select on SYS.AUD$ to replicate_user;
grant select on sys.v_$archived_log to replicate_user;
grant select on cdb_pdbs to replicate_user;
grant logmining to replicate_user;
grant select on sys.v_$database to replicate_user; 
grant select on sys.v_$containers to replicate_user; 
grant select on DBA_LOG_GROUPS to replicate_user;
grant select on DBA_LOG_GROUP_COLUMNS to replicate_user;
grant select on V_$NLS_PARAMETERS
grant select on V_$TIMEZONE_NAMES
grant select on GV_$TRANSACTION
grant EXECUTE_CATALOG_ROLE to replicate_user;

For Oracle versions 12c and above using Pluggable Database (PDB) all the GRANT statements should be run on the container:

grant execute on sys.dbms_logmnr_d to container=ALL;
grant select on sys.v_$parameter to container=ALL;
grant select on sys.v_$log to container=ALL;
grant select on sys.v_$logfile to container=ALL;
grant select on sys.V_$logmnr_contents to container=ALL;
grant select on sys.V_$thread to container=ALL;
grant select on sys.V_$archive_dest to container=ALL;
grant select on sys.v_$archived_log to container=ALL;
grant select any transaction to container=ALL;
grant select on cdb_pdbs to container=ALL;
grant logmining to container=ALL;
grant select on sys.v_$database to container=ALL;
grant select on sys.v_$containers to container=ALL;
grant select on DBA_LOG_GROUPS to container=ALL;
grant select on DBA_LOG_GROUP_COLUMNS to container=ALL;
grant EXECUTE_CATALOG_ROLE to container=ALL;