Oracle Database Prerequisites

Oracle 11g, 12c and 18c Deprecation

Oracle discontinued support for the following Oracle Database versions:

  • Version 11g on December 31, 2022
  • Version 12c on July 31, 2022
  • Version 18c on June 30, 2021

Confluent currently supports Oracle Database versions 19c and later.

This page includes the prerequisite Oracle database configuration steps and post-configuration validation steps. Note that you must complete the following steps before the Oracle CDC Source connector can produce expected results:

Configure database user privileges

The connector requires a database user with role privileges to use LogMiner and to select from all tables captured by the connector. For this reason, you may need to create a new database user for the connector. Note the following:

  • Create a local user for a non-container database. The username must not start with c## or C##.
  • Create a common user for a multitenant database. The username must start with c## or C##.

The following steps are for Oracle Database 19c.

To set up a new user with the correct database privileges, log in as SYSDBA and enter the following commands to create a role for the user with the required privileges. You can use any preferred name for the role. Note that CDC_PRIVS is the role name used in the following steps:

  1. As SYSDBA, enter the following SQL commands to create the role and grant privileges to the role.

    CREATE ROLE CDC_PRIVS;
GRANT CREATE SESSION TO CDC_PRIVS;
GRANT LOGMINING TO CDC_PRIVS;

GRANT SELECT ON V_$DATABASE TO CDC_PRIVS;
GRANT SELECT ON V_$INSTANCE to CDC_PRIVS;
GRANT SELECT ON V_$THREAD TO CDC_PRIVS;
GRANT SELECT ON V_$PARAMETER TO CDC_PRIVS;
GRANT SELECT ON V_$NLS_PARAMETERS TO CDC_PRIVS;
GRANT SELECT ON V_$TIMEZONE_NAMES TO CDC_PRIVS;
GRANT SELECT ON <schema>.<table> TO CDC_PRIVS;

GRANT SELECT ON V_$LOG TO CDC_PRIVS;
GRANT SELECT ON V_$LOGFILE TO CDC_PRIVS;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO CDC_PRIVS;
GRANT SELECT ON V_$ARCHIVED_LOG TO CDC_PRIVS;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO CDC_PRIVS;

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO CDC_PRIVS;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO CDC_PRIVS;

  2. Create a username and password, and grant privileges for the user (you may use any username and password):

    CREATE USER MYUSER IDENTIFIED BY password DEFAULT TABLESPACE USERS;
ALTER USER MYUSER QUOTA UNLIMITED ON USERS;
GRANT SELECT ON <schema>.<table> TO CDC_PRIVS;

GRANT CDC_PRIVS to MYUSER;

Turn on ARCHIVELOG mode

To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode. Note that you shut down the database when completing the following steps:

Note that you can skip the following steps if ARCHIVELOG mode is already enabled for the database. ARCHIVELOG mode is enabled by default for AWS RDS Oracle instances.

  1. Connect as a user with SYSDBA privileges. Check the database ARCHIVELOG mode. If the LOG_MODE shows ARCHIVELOG, you can skip the Turn on ARCHIVELOG mode section.

    SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

  2. Shut down the database instance using the command SHUTDOWN IMMEDIATE.

    SQL> SHUTDOWN IMMEDIATE;

  3. Create a full database backup including all data and control files. You can use operating system commands or RMAN to perform this operation. This backup can be used in the future for recovery with archived redo log files created once the database is in ARCHIVELOG mode.

  4. Start the instance and mount the database using the command STARTUP MOUNT.

    SQL> STARTUP MOUNT;

  5. Place the database in ARCHIVELOG mode using the command ALTER DATABASE ARCHIVELOG and open the database using the command ALTER DATABASE OPEN.

    SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

  6. Check the database ARCHIVELOG mode.

    SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

If you use Oracle Real Application Clusters (RAC), please follow the following steps to enable ARCHIVELOG.

   SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
   ------------
NOARCHIVELOG

  1. Stop the database service.

    srvctl stop database -d <Service Name>

  2. Start the database in mount state.

    srvctl start database -d <Service Name> -o mount

  3. Enable ARCHIVELOG mode.

    SQL> ALTER DATABASE ARCHIVELOG;
Database altered.

  4. Restart the database service (using SRVCTL)

    srvctl stop database -d <Service Name>

srvctl start database -d <Service Name>

  5. Check the database ARCHIVELOG mode.

    SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

Enable supplemental logging

This section includes the commands to enable supplemental logging.

Non-container and multitenant databases

Enter the following commands to specify that when a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file ( you must have correct privileges to execute the command):

  1. Set the session container:

    ALTER SESSION SET CONTAINER=cdb$root;

  2. To enable full supplemental logging for all tables, enter the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Or, to enable full supplemental logging for specific tables, run the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=<pdb-name>; -- ONLY FOR PDB
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  3. To enable minimal supplemental logging (PRIMARY KEY) for specific tables, run the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=<pdb-name>; -- ONLY FOR PDB
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Or, to enable minimal supplemental logging (UNIQUE INDEX) for specific tables, run the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=<pdb-name>; -- ONLY FOR PDB
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG GROUP <LogGroupName> (UniqueIndexColumn1[, UniqueIndexColumn2] ...) ALWAYS;

    For more help with enabling minimal supplemental logging, see Minimal Supplemental Logging for Oracle CDC Source Connector for Confluent Platform.

Note

For a pluggable databases:

  • Up to version 21c, enable minimal supplemental logging for the root container (CDB$ROOT). In version 23ai, enable minimal supplemental logging at either the CDB$ROOT or the specific PDB level.
  • Finally, enable full supplemental logging either for the entire database or specifically for the tables of interest within the PDB (recommended).

Amazon RDS Oracle 19c instance

Important

The Oracle CDC Source connector does not work with an Oracle read-only replica for Amazon RDS. The connector uses the Oracle-recommended Online Catalog, which requires the database to be open for write access. For related details, see Working with an Oracle read-only replica for Amazon RDS.

  1. Run the following exec command:

    exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

  2. To enable full supplemental logging for all tables, run this command:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');

    Or, to enable full supplemental logging for individual tables, run these commands:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  3. To enable minimal supplemental logging (PRIMARY KEY) for specific tables, run the following commands:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Or, to enable minimal supplemental logging (UNIQUE INDEX) for specific tables, run the following commands:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG GROUP <LogGroupName> (UniqueIndexColumn1[, UniqueIndexColumn2] ...) ALWAYS;

Grant the User Flashback Query Privileges

To perform snapshots on captured tables, the connector requires the user to have privileges to perform Flashback queries (that is, SELECT AS OF) on the captured tables. Enter the following command to grant the user privileges to perform Flashback queries on the database:

GRANT FLASHBACK ANY TABLE TO myuser;

To enable FLASHBACK query for specific tables:

GRANT FLASHBACK ON <schema>.<table> TO myuser;

Note that this command is not required if you want to capture the redo log (without generating change events) or generate change events starting from a known System Change Number (SCN) or timestamp using start.from.

For example, you enter the following commands to grant the example role created previously FLASHBACK privileges for a non-container database:

GRANT FLASHBACK ON <schema>.<table> TO CDC_PRIVS

For example, you enter the following commands to grant the example role created above FLASHBACK privileges for a container database or multitenant database:

ALTER SESSION SET CONTAINER=<pdb-name>;
GRANT FLASHBACK ON <schema>.<table> TO C##CDC_PRIVS

Amazon RDS for Oracle 19c instance

GRANT FLASHBACK ON <schema>.<table> TO DB_USER;

Validate start-up configuration and prerequisite completion

Use this section to ensure all prerequisites are met. You can perform an automate readiness check, or choose to validate readiness manually.

Automated readiness check

Download the oracle-readiness.sql script to ensure your database is ready to use the Oracle CDC connector. Be sure to run the script with the user having the DBA role and a PDB name if you are working with a multitenant database. For example:

SQLPlus > @oracle-readiness.sql C##MYUSER ORCLPDB1

If you’re not using PDB:

SQLPlus > @oracle-readiness.sql C##MYUSER ''

Note that the use of the oracle-readiness.sql script is subject to the same usage rights and terms as the use of the Oracle CDC connector.

Manual readiness check

If you’d like to verify all prerequisites manually, follow the steps in this section. The following sample values are used:

  • Role name: CDB_PRIVS and C##CDC_PRIVS
  • Usernames: MYUSER, C##MYUSER and DB_USER
  • Table name: CUSTOMERS. The CUSTOMERS table is created under one of the following schema:
    • ANOTHERUSER: for Non-container database 19c
    • C##ANOTHERUSER: for Container Database (CDB) and Multitenant Database (PDB) including 19c and 21c
    • ADMIN: for Amazon RDS for Oracle

Note that numeric values used in the following subsections may be different from the values you see when you run through the validation example.

The following steps are for Oracle Database 19c.

  1. Log in as SYSDBA and enter the following commands:

    -- Use the following SQL statement if privileges granted to a role (``CDC_PRIVS``) and the role granted to a user (``MYUSER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT granted_role
                  FROM DBA_ROLE_PRIVS
                  WHERE GRANTEE = 'MYUSER')
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
CDC_PRIVS         SYS            DBMS_LOGMNR

CDC_PRIVS         SYS            V_$LOGMNR_CONTENTS

-- Use the following SQL statement if privileges granted directly to a user (``MYUSER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'MYUSER'
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
MYUSER            SYS            DBMS_LOGMNR

MYUSER            SYS            V_$LOGMNR_CONTENTS

  2. Create check_prerequisites.sql with the following commands.

    SELECT * FROM SESSION_PRIVS;
SELECT LOG_MODE FROM V$DATABASE;
SELECT COUNT(*) FROM V$DATABASE;
SELECT COUNT(*) FROM V$THREAD;
SELECT COUNT(*) FROM V$PARAMETER;
SELECT COUNT(*) FROM V$NLS_PARAMETERS;
SELECT COUNT(*) FROM V$TIMEZONE_NAMES;
SELECT COUNT(*) FROM ALL_INDEXES;
SELECT COUNT(*) FROM ALL_OBJECTS;
SELECT COUNT(*) FROM ALL_USERS;
SELECT COUNT(*) FROM ALL_CATALOG;
SELECT COUNT(*) FROM ALL_CONSTRAINTS;
SELECT COUNT(*) FROM ALL_CONS_COLUMNS;
SELECT COUNT(*) FROM ALL_TAB_COLS;
SELECT COUNT(*) FROM ALL_IND_COLUMNS;
SELECT COUNT(*) FROM ALL_ENCRYPTED_COLUMNS;
SELECT COUNT(*) FROM ALL_LOG_GROUPS;
SELECT COUNT(*) FROM ALL_TAB_PARTITIONS;
SELECT COUNT(*) FROM ANOTHERUSER.CUSTOMERS;
-- Flashback query privilege
SELECT COUNT(*) FROM ANOTHERUSER.CUSTOMERS AS OF TIMESTAMP SYSDATE;

-- Added for 19C
SELECT count(*) FROM V$ARCHIVED_LOG;
SELECT count(*) FROM V$LOG;
SELECT count(*) FROM V$LOGFILE;
SELECT count(*) FROM V$INSTANCE;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = 'CURRENT';

  3. Log in using MYUSER (created previously) and then enter the following:

    SQL> @check_prerequisites.sql

PRIVILEGE
----------------------------------------
CREATE SESSION
LOGMINING

LOG_MODE
------------
ARCHIVELOG

  COUNT(*)
----------
    1

  COUNT(*)
----------
    1

  COUNT(*)
----------
        344

  COUNT(*)
----------
    19

  COUNT(*)
----------
      2164

  COUNT(*)
----------
    88

  COUNT(*)
----------
      7261

  COUNT(*)
----------
    14

  COUNT(*)
----------
      5656

  COUNT(*)
----------
        197

  COUNT(*)
----------
        266

  COUNT(*)
----------
      20926

  COUNT(*)
----------
        142

  COUNT(*)
----------
    0

  COUNT(*)
----------
    2

  COUNT(*)
----------
    0

  COUNT(*)
----------
    10

  COUNT(*)
----------
    10

  COUNT(*)
----------
    2

  COUNT(*)
----------
    3

  COUNT(*)
----------
    3

  COUNT(*)
----------
    1

  SEQUENCE#
----------
    20

  4. Log in using MYUSER (created previously) and then enter the following commands:

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

-- If full supplemental logging is enabled for all tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  YES


-- If full supplemental logging is enabled for specific tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  NO


-- If full supplemental logging is enabled for specific tables, enter the following command.

SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER='ANOTHERUSER' and TABLE_NAME='CUSTOMERS';

OWNER          LOG_GROUP_NAME          TABLE_NAME           LOG_GROUP_TYPE          ALWAYS            GENERATED
-------------- ----------------------- -------------------- ----------------------- ----------------- -------------------------
ANOTHERUSER SYS_C007542             CUSTOMERS            ALL COLUMN LOGGING      ALWAYS            GENERATED NAME