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.