Rights to grant in order to capture the Oracle source

The LogMiner must be activated to be able to read the journals of transactions.

For this, you need to run the Oracle instance in ArchiveLog mode. Open a CMD window and enter the following commands:

sqlplus sys/<password>@orcl as sysdba;
SELECT LOG_MODE FROM V$DATABASE;    
alter database close;
alter database archivelog;
shut immediate;
sqlplus as sysdba;
startup;
SELECT LOG_MODE FROM V$DATABASE;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (all) COLUMNS;

On the source Oracle instance, a DD_USRDATA schema is created with a default user profile. It contains the Talend Change Data Capture repository for this machine. To grant the needed rights to DD_USRDATA repository, open a CMD window and enter the following commands:

sqplus sys/<password>@orcl as sysdba;
GRANT ALTER SESSION TO "DD_USRDATA";
GRANT EXECUTE ON DBMS_LOGMNR TO "DD_USRDATA";
GRANT EXECUTE ON DBMS_LOGMNR_D TO "DD_USRDATA";
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO "DD_USRDATA";
GRANT EXECUTE ON SYS.DBMS_LOGMNR_INTERNAL TO "DD_USRDATA";
GRANT EXECUTE ON SYS.DBMS_LOGMNR_LOGREP_DICT TO "DD_USRDATA";
GRANT EXECUTE ON SYS.DBMS_LOGMNR_SESSION TO "DD_USRDATA";
GRANT EXECUTE ON DBMS_FLASHBACK TO "DD_USRDATA";
GRANT FLASHBACK ANY TABLE TO "DD_USRDATA";
GRANT FLASHBACK ARCHIVE ADMINISTER TO "DD_USRDATA";
GRANT LOGMINING TO "DD_USRDATA";
GRANT SELECT ANY DICTIONARY TO "DD_USRDATA";
GRANT SELECT ON V_$LOGMNR_CONTENTS TO "DD_USRDATA";
GRANT SELECT ON V_$LOGMNR_DICTIONARY TO "DD_USRDATA";
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO "DD_USRDATA";
GRANT SELECT ON V_$LOGMNR_LOGS TO "DD_USRDATA";
GRANT SELECT ON V_$LOG TO "DD_USRDATA";
GRANT SELECT ON V_$LOGFILE TO "DD_USRDATA";
GRANT SELECT ON V_$ARCHIVED_LOG TO "DD_USRDATA";
GRANT SELECT ON V_$PARAMETER TO "DD_USRDATA";
GRANT SELECT ON V_$DATABASE TO "DD_USRDATA";
GRANT SELECT ON v_$instance TO "DD_USRDATA";
GRANT SELECT ON SYS.CDEF$ TO "DD_USRDATA";
GRANT SELECT ON SYS.USER$ TO "DD_USRDATA";
GRANT SELECT ON SYS.OBJ$ TO "DD_USRDATA";
GRANT SELECT ON SYS.COL$ TO "DD_USRDATA";
GRANT SELECT ON SYS.ALL_CONSTRAINTS TO "DD_USRDATA";
GRANT SELECT ON SYS.all_catalog TO "DD_USRDATA";
GRANT SELECT ON SYS.dba_objects TO "DD_USRDATA";
GRANT SELECT ON SYS.dba_cons_columns TO "DD_USRDATA";
GRANT SELECT ON SYS.V_$DATABASE TO "DD_USRDATA";
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO "DD_USRDATA";
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO "DD_USRDATA";
GRANT SELECT ON product_component_version TO "DD_USRDATA";

Make sure you have the following rights on the source database:

Read right on the tables in SYS:
- dbms_flashback.get_system_change_number,
- partobj$.
Execution right:
- DBMS_LOGMNR.START_LOGMNR,
- DBMS_LOGMNR.ADD_LOGFILE,
- DBMS_LOGMNR.ADDFILE,
- DBMS_LOGMNR.END_LOGMNR,
- DBMS_LOGMNR.NEW,
- DBMS_LOGMNR.REMOVEFILE.
On each schema that needs to be captured: GRANT SELECT right.
For each table that needs to be captured:
- ALTER TABLE schema.table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS,
- ALTER TABLE schema.table DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS.
On the target schema: make sure you have a user profile with read/write permissions.

Did this page help you?