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

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"
}

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