Skip to content

Required Oracle database setup for oracdc Source connector

Jump to bottom
averemee-si edited this page Nov 19, 2021 · 2 revisions

The following steps need to be performed in order to prepare the Oracle database so the oracdc Connector can be used.

Enabling Oracle RDBMS ARCHIVELOG mode

Log in to SQL*Plus as SYSDBA and check results of query

select LOG_MODE from V$DATABASE

If the query returns ARCHIVELOG, it is enabled. Skip ahead to Enabling supplemental log data. If the query returns NOARCHIVELOG :

shutdown immediate
startup mount
alter database archivelog;
alter database open;

To verify that ARCHIVELOG has been enabled run again

select LOG_MODE from V$DATABASE

This time it should return ARCHIVELOG

Enabling supplemental logging

Log in to SQL*Plus as SYSDBA, if you like to enable supplemental logging for whole database:

alter database add supplemental log data (ALL) columns;

Alternatively, to enable only for selected tables and minimal supplemental logging, a database-level option (recommended):

alter database add supplemental log data;
alter table <OWNER>.<TABLE_NAME> add supplemental log data (ALL) columns;

If using Amazon RDS for Oracle please see AWS Amazon Relational Database Service User Guide about rdsadmin.rdsadmin_util.alter_supplemental_logging procedure.

To verify supplemental logging settings at database level:

select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
from V$DATABASE;

To verify supplemental logging settings at table level:

select LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS, 'ALWAYS', 'Unconditional', NULL, 'Conditional') ALWAYS
from DBA_LOG_GROUPS;

Creating non-privileged Oracle user for running LogMiner

Instructions below are for CDB, for non-CDB ([depreciated in 12c](Deprecation of Non-CDB Architecture), will be desupported in 20c) you can use role and user names without c## prefix. Log in as sysdba and enter the following commands to create a user with the privileges required for running oracdc with LogMiner as CDC source. For CDB:

create user C##ORACDC identified by ORACDC
  default tablespace SYSAUX
  temporary tablespace TEMP
  quota unlimited on SYSAUX
CONTAINER=ALL;
alter user C##ORACDC SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
grant
  CREATE SESSION,
  SET CONTAINER,
  SELECT ANY TRANSACTION,
  SELECT ANY DICTIONARY,
  EXECUTE_CATALOG_ROLE,
  LOGMINING
to C##ORACDC
CONTAINER=ALL;

For non-CDB or for connection to PDB in RDBMS 19.10+:

create user ORACDC identified by ORACDC
  default tablespace SYSAUX
  temporary tablespace TEMP
  quota unlimited on SYSAUX;
grant
  CREATE SESSION,
  SELECT ANY TRANSACTION,
  SELECT ANY DICTIONARY,
  EXECUTE_CATALOG_ROLE,
  LOGMINING
to ORACDC;

Additional configuration for physical standby database (V$DATABASE.OPEN_MODE = MOUNTED)

Connection to physical standby database when database is opened in MOUNTED mode is possible only for users for SYSDBA privilege. To check for correct user settings log in to SQL*Plus as SYSDBA and connect to physical standby database. To verify that you connected to physical standby database enter

select OPEN_MODE, DATABASE_ROLE, DB_UNIQUE_NAME from V$DATABASE;

it should return MOUNTED PHYSICAL STANDBY Then enter:

select USERNAME from V$PWFILE_USERS where SYSDBA = 'TRUE';

For the user who will be used to connect to physical standby database create a Oracle Wallet. Please refer to section Oracle Wallet above. To run oracdc in this mode parameter a2.standby.activate must set to true.

Clone this wiki locally