ORA-00942 While running Oracle CDC on 19c Databases

Issue:

Even after providing all the grants on Oracle 19c database as per CDC documentation

Refer https://streamsets.com/documentation/datacollector/latest/help/datacollector/UserGuide/Origins/OracleCDC.html

12c, 18c, or 19c standard databasesFor standard Oracle and Oracle RAC 12c, 18c, and 19c databases, create a user account with the necessary privileges:

1. In a SQL shell, log into the database as a user with DBA privileges.
2. Create the user account.

   Use the following set of commands for 12c and 18c:

   CREATE USER <user name> IDENTIFIED BY <password>;
   GRANT create session, alter session, logmining, execute_catalog_role TO <user name>;
   GRANT select on GV_$DATABASE to <user name>;
   GRANT select on V_$LOGMNR_CONTENTS to <user name>;
   GRANT select on GV_$ARCHIVED_LOG to <user name>;
   GRANT select on <db>.<table> TO <user name>;

   Repeat the final command for each table that you want to use.

   Use the following set of commands for 19c:

   CREATE USER <user name> IDENTIFIED BY <password>;
   GRANT create session, alter session, logmining, execute_catalog_role TO <user name>;
   GRANT select on GV_$DATABASE to <user name>;
   GRANT select on V_$LOGMNR_CONTENTS to <user name>;
   GRANT select on GV_$ARCHIVED_LOG to <user name>;
   GRANT select on V_$LOG to <user name>;
   GRANT select on V_$LOGFILE to <user name>;
   GRANT select on V_$LOGMNR_LOGS to <user name>;
   GRANT select on <db>.<table> TO <user name>;

We may run into the below error

=======

com.streamsets.pipeline.api.StageException: JDBC_52 - Error starting LogMiner
at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.startGeneratorThread(OracleCDCSource.java:455)
at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.produce(OracleCDCSource.java:325)
Caused by: com.streamsets.pipeline.api.StageException: JDBC_603 - Error while retrieving LogMiner metadata: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

======================================

Versions affected:

SDC 3.16.1 and above

Solution:

If you turn on debug mode you will see that CDC executes a query similar to the one below

SELECT
    name,
    thread#,
    sequence#,
    first_time,
    next_time,
    first_change#,
    next_change#,
    dictionary_begin,
    dictionary_end,
    status,
    'NO' AS online_log,
    'YES' AS archived
FROM
    v$archived_log
WHERE
    status = 'A'
    AND ( ( first_time <= TO_DATE('05-11-2020 09:59:21', 'DD-MM-YYYY HH24:MI:SS')
            AND next_time > TO_DATE('05-11-2020 09:59:21', 'DD-MM-YYYY HH24:MI:SS') )
          OR ( first_time < TO_DATE('05-11-2020 09:59:21', 'DD-MM-YYYY HH24:MI:SS')
               AND next_time >= TO_DATE('05-11-2020 09:59:21', 'DD-MM-YYYY HH24:MI:SS') )
          OR ( first_time > TO_DATE('05-11-2020 09:59:21', 'DD-MM-YYYY HH24:MI:SS')
               AND next_time < TO_DATE('05-11-2020 09:59:21', 'DD-MM-YYYY HH24:MI:SS') ) )
UNION
SELECT
    c.member,
    a.thread#,
    a.sequence#,
    a.first_time,
    (
        CASE
            WHEN a.status = 'CURRENT' THEN
                current_timestamp AT TIME ZONE DBTIMEZONE
            ELSE
                a.next_time
        END
    ) next_time,
    a.first_change#,
    (
        CASE
            WHEN a.status = 'CURRENT' THEN
                b.current_scn
            ELSE
                a.next_change#
        END
    ) next_change#,
    'NO' AS dictionary_begin,
    'NO' AS dictionary_end,
    a.status,
    'YES' AS online_log,
    a.archived
FROM
    v$log                                                                                        a,
    v$database                                                                                   b,
    (
        SELECT
            group#,
            member,
            ROW_NUMBER() OVER(
                PARTITION BY group#
                ORDER BY
                    group#
            ) AS rowno
        FROM
            v$logfile
    ) c
WHERE
    a.group# = c.group#
    AND a.members = c.rowno

Running this query on the Oracle database directly using a SQL tool connecting as the same oracle user that pipeline is connecting too will flush out the exact objects that may not be having the privileges

In this case we had to add these additional grants

GRANT select on V_$DATABASE to <user name>;

GRANT select on V_$ARCHIVED_LOG to <user name>;