Skip to main content

Create Oracle User and Grant Required Permissions

Oracle 19c + Debezium LogMiner Setup Guide

1. Enable Required Database Settings (CDB$ROOT)

sqlplus / as sysdba

Switch to CDB root

ALTER SESSION SET CONTAINER = CDB$ROOT;

Confirm:

SHOW CON_NAME;

Should show:

CDB$ROOT

⚠ Now you can run MOUNT / ARCHIVELOG commands

Shut down the entire CDB

SHUTDOWN IMMEDIATE;

You should see:

Database closed. Database dismounted. ORACLE instance shut down.

Start in MOUNT mode

Now run:

STARTUP MOUNT;

This time it will succeed because now you are in the root.

Enable ARCHIVELOG

ALTER DATABASE ARCHIVELOG;

Open the database

ALTER DATABASE OPEN;

Verify

ARCHIVE LOG LIST;

Apply supplemental logging

ALTER SESSION SET CONTAINER=CDB$ROOT;

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

ALTER DATABASE FORCE LOGGING;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

2. Create Common User for Debezium

ALTER SESSION SET CONTAINER=CDB$ROOT;

CREATE USER C##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. Required Debezium Connector Settings

{
  "database.user": "C##DEBEZIUM",
  "database.password": "StrongPassword",
  "database.dbname": "ORCLPDB1",
  "database.pdb.name": "ORCLPDB1"
}

{
  "name": "oracle-connector",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "tasks.max": "1",

    "database.hostname": "192.168.1.225",
    "database.port": "1521",


    "database.dbname": "ORCLPDB1",
    "database.pdb.name": "ORCLPDB1",


   "database.user": "C##DEBEZIUM",
   "database.password": "StrongPassword",
   "oracle.proxy.user.name": "PDBZIUM",

    "topic.prefix": "oraclecdc",

    "database.connection.adapter": "logminer",

    "schema.history.internal.kafka.bootstrap.servers": "192.168.1.226:9092",
    "schema.history.internal.kafka.topic": "oracle.schema-changes",

    "include.schema.changes": "true",
    "tombstones.on.delete": "false",

    "log.mining.strategy": "online_catalog",
    "log.mining.continuous.mine": "true",
    "log.mining.archive.log.only.mode": "false"
  }
}

5. Notes

  • This configuration works for Oracle 19c + Debezium 3.3.
  • It supports LogMiner CDC for Kafka Connect.
  • Ensure redo logs and archive logs are enabled.
Back to top