Skip to main content

Create an Oracle source connector

Stream every row change in your Oracle tables to Kafka topics, where sink connectors can deliver it to other systems. The connector reads the redo logs with Oracle LogMiner.

Prerequisites​

  • A CDC Service whose status is Healthy. See Create a CDC Service.
  • The host, port, and database names of your Oracle database. The Oracle connector connects with Manual configuration only.
  • The database prepared for CDC as described in Prepare the Oracle database.

Prepare the Oracle database​

Run these statements as a user with the SYSDBA privilege. Run steps 1 to 5 in the root container (CDB$ROOT), and steps 6 to 9 in the pluggable database (PDB) that the connector connects to.

  1. Check the log mode. The result must be ARCHIVELOG.

    SELECT LOG_MODE FROM V$DATABASE;
  2. Turn on archive log mode if the result is NOARCHIVELOG.

    warning

    These commands shut down the database, and applications cannot use it until ALTER DATABASE OPEN completes. Run them in a maintenance window.

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;
    SELECT LOG_MODE FROM V$DATABASE;
  3. Turn on supplemental logging for the database, so that the redo logs hold the full change information.

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  4. Check supplemental logging. SUPPLEMENTAL_LOG_DATA_MIN must be YES.

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,
    SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK,
    SUPPLEMENTAL_LOG_DATA_ALL
    FROM V$DATABASE;
  5. Find the archive log destination that the connector reads archived redo logs from. Note the DEST_NAME of a destination whose TYPE is LOCAL. You select it in Name under Archive log when you create the connector.

    SELECT DEST_NAME, STATUS, TYPE FROM V$ARCHIVE_DEST_STATUS WHERE STATUS = 'VALID';
  6. Switch to the pluggable database that holds the tables to capture.

    ALTER SESSION SET CONTAINER = YOUR_PDB;
  7. Create a local user for the connector. A local user exists only in this pluggable database.

    CREATE USER YOUR_USER IDENTIFIED BY YOUR_PASSWORD;
  8. Grant the user the privileges that the connector needs.

    GRANT CREATE SESSION TO YOUR_USER;
    GRANT SELECT ANY TABLE TO YOUR_USER;
    GRANT FLASHBACK ANY TABLE TO YOUR_USER;
    GRANT SELECT_CATALOG_ROLE TO YOUR_USER;
    GRANT EXECUTE_CATALOG_ROLE TO YOUR_USER;
    GRANT LOGMINING TO YOUR_USER;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO YOUR_USER;

    Test connection checks the system privileges and roles in this block, and accepts SELECT ANY DICTIONARY in place of SELECT_CATALOG_ROLE. Grants on single tables cannot replace SELECT ANY TABLE or FLASHBACK ANY TABLE.

  9. Turn on supplemental logging for each table to capture, still in the pluggable database.

    ALTER TABLE YOUR_SCHEMA.YOUR_TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Create the connector​

  1. Select Data platform → Workspace management, then select your workspace.

  2. On the Overview tab, under Services, select the CDC card.

  3. Select the Connectors tab, then select Create Connector.

    Connectors tab of a Healthy CDC Service with the No connector yet message and the Create Connector button

  4. On the Connector Information screen, enter a Name, select source in Type, and select Oracle in Database.

    Connector Information screen of the Create Connector form with the Name, Type, and Database fields

  5. Select Next Step. On the Properties screen, enter the connection details and the Topic prefix, select Test connection, then select the archive log destination in Name. Next Step becomes available after the test succeeds.

    If a field is empty, Test connection marks it with a message such as "Username is required" and does not test the connection.

    Database Information section with Manual configuration selected, From FPT Database Engine greyed out, and the Host name, Port, Container database name, Pluggable database name, Username, and Password fields

    Archive log section with the Name list

    Kafka topic section with the Topic prefix field

  6. Select Next Step. On the Additional properties screen, select the snapshot Mode and add the tables to capture.

    Additional properties screen with initial selected in Mode and three tables of the DBSFWUSER schema in the Include list

  7. Select Next Step and check the settings on the Review screen.

    Review screen of an Oracle source connector with the Basic Information, Kafka topic, Database Information, Archive log, Snapshot, and Include sections

    To change a setting, select Back to return to the previous screen.

  8. Select Create.

    The portal shows "Create Successfully" and opens the Essential Information tab of the CDC Service. On the Connectors tab, the new connector shows Running, with every Tasks counter at 0 until its task starts, for up to about 2.5 minutes.

Connector settings​

Connector information​

FieldRequiredDescription
NameYesThe connector name, in lowercase letters, digits, and hyphens (-). Uppercase letters, _, ., and spaces give the error "Name has invalid characters".
TypeYesSelect source.
DatabaseYesSelect Oracle.

Properties​

FieldRequiredDescription
Manual configurationYesEnter the connection details yourself. From FPT Database Engine is greyed out for Oracle. See Connection mode.
Host nameYesHost name or IP address of the Oracle database.
PortYesThe Oracle server port, for example 1521. The field has no default.
Container database nameYesThe name of the container database (CDB) that the connector connects to. Applies to Oracle 12c and later with the multitenant architecture.
Pluggable database nameYesThe pluggable database (PDB) that holds the data to capture.
UsernameYesThe local user you created in step 7.
PasswordYesThe user's password.
Name (Archive log)YesThe archive log destination that the connector reads archived redo logs from, such as LOG_ARCHIVE_DEST_1. Select the destination you found in step 5. The list shows no options until Test connection succeeds.
Topic prefixYesPrefix of the Kafka topics the connector writes to. Each table gets the topic TOPIC_PREFIX.SCHEMA.TABLE. See Topic prefix.

The Oracle form has no Enable incremental snapshot checkbox.

Additional properties​

FieldRequiredDescription
ModeYesWhat the connector does with existing rows. The field has no default, so select one. initial snapshots them, then captures changes through LogMiner. initial_only snapshots them, then stops without capturing changes. no_data captures only the changes made after the connector starts. See Snapshot mode.
IncludeNoThe tables to capture. Select +, then choose the Schema, the Table, and the Column. You can select several schemas and tables. Column is .*, all columns, by default. Use Filter to search the list and the delete icon to remove a table. If you add no table, the connector captures all tables.

Next steps​