Preparing Oracle as a source

 

When replicating with Oracle as the source database, the following prerequisites must be met:

1)     The version of Oracle must be 11g to 18c. The Oracle logminer is not available in Oracle versions past 18c.

2)     The Oracle Database-Level Supplemental Logging must be at least Minimal Supplemental Logging.  Also, the connection to the logminer must be the same as the Oracle source database, logmining from a remote Oracle Database is not allowed. Supplemental Logging can be activated with the following command:

alter database add supplemental log data;

To specify the location of the log data, enter the following command:

alter system set log_archive_dest_1='location-<path to log file>' scope=both;

To see the current location of the logs, enter the following command:

archive log list;

Once supplemental log data has been enabled, the database must be restarted in archive mode:

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
 

3)    The Oracle User used to connect to the source Oracle database must have permissions to access the logminer and read all the entries in the V$TRANSACTION view.  The following is an example of a user with the required permissions:

a)      create role ep_logmnr;

b)      grant create session, logmining, execute, alter system, execute_catalog_role, select any dictionary, select any transaction, select any table, create tablespace, drop tablespace to ep_logmnr;

c)      grant select on v_$logmnr_contents to ep_logmnr;

d)      grant select on v_$logmnr_dictionary to ep_logmnr;

e)      grant select on v_$logmnr_parameters to ep_logmnr;

f)        grant select on v_$logmnr_logs to ep_logmnr;

g)      grant select on v_$archived_log to ep_logmnr;

h)      grant select on V_$TRANSACTION to ep_logmnr;

i)        create user epminer identified by epminer;

j)        grant ep_logmnr to epminer;

k)      alter user epminer quota unlimited on users;

 

4)      If using a multitenant container database (CDB) a common user must be used to access the logminer.  This is an example of a common user with the required permissions:

a)      create role c##cnx_ep_logmnr CONTAINER=ALL;

b)      grant create session, logmining, alter system, execute_catalog_role, select any dictionary, select any transaction, lock any table, select any table, create tablespace, drop tablespace to c##cnx_ep_logmnr CONTAINER=ALL;

c)      grant select on v_$logmnr_contents to c##cnx_ep_logmnr CONTAINER=ALL;

d)      grant select on v_$logmnr_dictionary to c##cnx_ep_logmnr CONTAINER=ALL;

e)      grant select on v_$logmnr_parameters to c##cnx_ep_logmnr CONTAINER=ALL;

f)        grant select on v_$logmnr_logs to c##cnx_ep_logmnr CONTAINER=ALL;

g)      grant select on v_$archived_log to c##cnx_ep_logmnr CONTAINER=ALL;

h)      grant select on V_$TRANSACTION to c##cnx_ep_logmnr CONTAINER=ALL;

i)        create user c##oracleuser4 identified by oracleuser CONTAINER=ALL;

j)        grant c##cnx_ep_logmnr to c##oracleuser4 CONTAINER=ALL;

k)      alter user c##oracleuser4 quota unlimited on users CONTAINER=ALL;

 

CONTAINER=ALL is syntax for Oracle 12 and above, and is not valid for oracle 11 and below.

Notice that the role and user name all start with c## - this prefix is required by oracle for common user and role names.

 

The following restrictions apply:

  1. BLOB/CLOB data types are not supported.  This includes CLOB, NCLOB, BLOB, BFILE, LONG, LONG RAW, etc.

  2. Computed columns have limited support.  

    • The index used for replication cannot be a computed column.

    • The source table can have computed columns but the target table cannot.

    • If a computed column is part of the replication, the data in the computed column will not be replicated.  In this case, the target column that the computed column is mapped to will receive nulls.