# Create Oracle User and Grant Required Permissions

[![image-1770022792927.png](https://wiki.exceltoquiz.com/uploads/images/gallery/2026-02/scaled-1680-/cTbvD793gVXJu5zz-image-1770022792927.png)](https://wiki.exceltoquiz.com/uploads/images/gallery/2026-02/cTbvD793gVXJu5zz-image-1770022792927.png)# Oracle 19c + Debezium LogMiner Setup Guide

## 1. Enable Required Database Settings (CDB\$ROOT)

```sh
sqlplus / as sysdba
```

Switch to CDB root
```sql
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
```sql
SHUTDOWN IMMEDIATE;
```


You should see:

Database closed.
Database dismounted.
ORACLE instance shut down.

## Start in MOUNT mode

Now run:

``` sql 
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

``` sql
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

``` sql
ALTER SESSION SET CONTAINER=CDB$ROOT;

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

### Grant LogMiner + Root Privileges

``` sql
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)

``` sql
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;
```
# PDB Permissions
```
GRANT CREATE SESSION TO C##DEBEZIUM;
GRANT CREATE TABLE TO C##DEBEZIUM;
GRANT UNLIMITED TABLESPACE TO C##DEBEZIUM;
GRANT RESOURCE TO C##DEBEZIUM;
ALTER USER C##DEBEZIUM QUOTA UNLIMITED ON USERS;

```

OPTIONAL BUT IMPORTANT

If your PDB default tablespace is not USERS, verify with:

``` SELECT username, default_tablespace FROM dba_users WHERE username='C##DEBEZIUM'; ```


If it is something else like 
``` SYSAUX or SYSTEM, fix:```

``` ALTER USER C##DEBEZIUM DEFAULT TABLESPACE USERS; ```

 — Switch to CDB root (Extra Permission)
```
ALTER SESSION SET CONTAINER=CDB$ROOT;
```

Step 3 — Run these grants
```
GRANT CREATE TABLE TO C##DEBEZIUM CONTAINER=ALL;
GRANT UNLIMITED TABLESPACE TO C##DEBEZIUM CONTAINER=ALL;
```

MANUALLY TEST (IMPORTANT)

Run this from C##DEBEZIUM session:

``` 
CONNECT C##DEBEZIUM/StrongPassword@ORCLPDB1
CREATE TABLE LOG_MINING_FLUSH (id NUMBER);
DROP TABLE LOG_MINING_FLUSH;

```
If this works → Debezium will NOT fail again.

## 4. Required Debezium Connector Settings

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

CREATE TABLE sensor_logs (
    id            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    temperature   BINARY_DOUBLE,
    humidity      BINARY_DOUBLE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO sensor_logs (temperature, humidity, created_at)
VALUES (32.45, 79.12, SYSTIMESTAMP);


```sh
{
  "name": "oracle-connectorv9",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "tasks.max": "1",
    "schema.include.list": "C##DEBEZIUM",
   "table.include.list": "C##DEBEZIUM.sensor_logs",
    "database.hostname": "192.168.1.225",
    "database.port": "1521",


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


   "database.user": "C##DEBEZIUM",
   "database.password": "StrongPassword",
 

    "topic.prefix": "oraclecdcv9",

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

    "schema.history.internal.kafka.bootstrap.servers": "192.168.1.226:9092",
    "schema.history.internal.kafka.topic": "oracle.schema-changesv9",
    "snapshot.mode": "initial",
    "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"
  }
}

[![](https://wiki.exceltoquiz.com/uploads/images/gallery/2026-02/scaled-1680-/cTbvD793gVXJu5zz-image-1770022792927.png)](https://wiki.exceltoquiz.com/uploads/images/gallery/2026-02/cTbvD793gVXJu5zz-image-1770022792927.png)
```
# Register

```sh
curl -X POST -H "Content-Type: application/json"   --data @f.json   http://localhost:8083/connectors
```
# Verify Connector

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

# Kafka Topics Created

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

# Consume CDC Messages
```
/opt/kafka/bin/kafka-console-consumer.sh \
  --bootstrap-server localhost:9092 \
  --topic mysqlserver1.mydb.customers \
  --from-beginning
```

# Delete Connector

```
curl -X DELETE http://localhost:8083/connectors/mysql-connector
```
## 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.