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

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