Skip to main content

Create Oracle User and Grant Required Permissions

Use

Oracle 19c + Debezium LogMiner Setup Guide

1. Enable Required Database Settings (Recommended for 3.3.x)

CDB$ROOT)

-------PDB

CREATE USER debezium IDENTIFIED BY "StrongPassword";

GRANT CONNECT, RESOURCE TO debezium;

GRANT CREATEALTER SESSION TOSET debezium;CONTAINER=CDB$ROOT;

GRANT SELECT ON V_$DATABASE TO debezium;

GRANT SELECT ON V_$LOG TO debezium;

GRANT SELECT ON V_$LOGFILE TO debezium;

GRANT SELECT ON V_$ARCHIVED_LOG TO debezium;

GRANT SELECT ON V_$INSTANCE TO debezium;

GRANT FLASHBACK ANY TABLE TO debezium;

GRANT SELECT ANY TRANSACTION TO debezium;

GRANT SELECT ANY DICTIONARY TO debezium;

GRANT LOGMINING TO debezium;

---------CDB

ALTER DATABASE FORCE LOGGING;

SHOW PARAMETER db_recovery_file_dest;

SHOW PARAMETER db_recovery_file_dest_size;

Also make sure ARCHIVELOG mode is enabled:


ARCHIVE LOG LIST;

If not enabled:

SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;



Enable Supplemental Logging (MANDATORY)

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER

CheckDATABASE status:

FORCE 
SELECTLOGGING;

supplemental_log_data_min, supplemental_log_data_pk,
       supplemental_log_data_fk, supplemental_log_data_ui,
       supplemental_log_data_all
FROM v$database;

Enable GoldenGate Replication Flag (Needed for Debezium LogMiner)

ALTER SYSTEM SET enable_goldengate_replication=ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

2.

FIX:Create Grant Tablespace Quota to DebeziumCommon User Usingfor PDB

Debezium

ALTER SESSION SET CONTAINER=CDB$ROOT;

CREATE USER debeziumC##DEBEZIUM IDENTIFIED BY "StrongPassword" CONTAINER=ALL;

Grant LogMiner + Root Privileges

GRANT CREATE SESSION TO C##DEBEZIUM CONTAINER=ALL;
GRANT SET CONTAINER TO C##DEBEZIUM CONTAINER=ALL;

GRANT SELECT ANY DICTIONARY TO C##DEBEZIUM CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO C##DEBEZIUM CONTAINER=ALL;
GRANT LOGMINING TO C##DEBEZIUM CONTAINER=ALL;

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##DEBEZIUM CONTAINER=ALL;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO C##DEBEZIUM CONTAINER=ALL;

GRANT SELECT ON V_$LOG TO C##DEBEZIUM CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO C##DEBEZIUM CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##DEBEZIUM CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE TO C##DEBEZIUM CONTAINER=ALL;
GRANT SELECT ON V_$INSTANCE TO C##DEBEZIUM CONTAINER=ALL;

3. Create PDB User (inside ORCLPDB1)

ALTER SESSION SET CONTAINER=ORCLPDB1;

CREATE USER PDBZIUM IDENTIFIED BY "StrongPassword";

GRANT CONNECT, RESOURCE TO PDBZIUM;
GRANT CREATE SESSION TO PDBZIUM;
GRANT SELECT ANY DICTIONARY TO PDBZIUM;
GRANT SELECT ANY TABLE TO PDBZIUM;
GRANT SELECT ANY TRANSACTION TO PDBZIUM;
GRANT FLASHBACK ANY TABLE TO PDBZIUM;

ALTER USER PDBZIUM QUOTA UNLIMITED ON USERS;
ALTER USER C##DEBEZIUM QUOTA UNLIMITED ON USERS;

4.


Now run these inside the PDB:

GRANT EXECUTE_CATALOG_ROLE TO debezium;
GRANT SELECT_CATALOG_ROLE TO debezium;

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

CREATE OR REPLACE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;
CREATE OR REPLACE PUBLIC SYNONYM DBMS_LOGMNR_D FOR SYS.DBMS_LOGMNR_D;



CreateRequired Debezium Oracle Connector Config

Settings

{
  "name": "oracle-connector",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "tasks.max": "1",
    "database.hostname": "192.168.1.225",
    "database.port": "1521",
  "database.user": "debezium"C##DEBEZIUM",
  "database.password": "StrongPassword",
  "database.dbname": "ORCLPDB1",
  "topic.prefix"database.pdb.name": "oracle",
    "database.connection.adapter": "logminer",
    "database.history.kafka.bootstrap.servers": "192.168.1.226:9092",
    "database.history.kafka.topic": "schema-changes.oracle",
    "snapshot.mode": "initial"
  }ORCLPDB1"
}

5.

RegisterNotes

Connector:

  • This

    configuration 
    curlworks -Xfor POSTOracle -H19c "Content-Type:+ application/json"Debezium \3.3.
    • --data
  • It @/opt/kafka/connect/oracle.jsonsupports \ http://localhost:8083/connectors

     

    Verify Connector

    curl http://localhost:8083/connectors
curl http://localhost:8083/connectors/oracle-connector/status

    Kafka Topics Created

    /opt/kafka/bin/kafka-topics.sh --bootstrap-server localhost:9092 --list

    ConsumeLogMiner CDC Message

    for 
    /opt/kafka/bin/kafka-console-consumer.shKafka \Connect.
    • --bootstrap-server
  • Ensure localhost:9092redo \logs --topicand mysqlserver1.mydb.customersarchive \logs --from-beginningare

    enabled.

Delete Connector

curl -X DELETE http://localhost:8083/connectors/oracle-connector

Restart Connector

curl -X POST http://localhost:8083/connectors/oracle-connector/restart

Back to top