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##.

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.