Rights to grant in order to capture the Oracle source - 7.3

Talend Change Data Capture Installation Guide

Version
7.3
Language
English
Product
Talend Change Data Capture
Module
Talend Change Data Capture
Content
Data Governance > Third-party systems > Database components (Integration) > Change Data Capture
Data Quality and Preparation > Third-party systems > Database components (Integration) > Change Data Capture
Design and Development > Third-party systems > Database components (Integration) > Change Data Capture
Last publication date
2023-10-05
  • 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.