Prepare Oracle and Oracle RAC for RDI

Prepare Oracle and Oracle RAC databases to work with RDI

The Oracle Debezium connector uses Oracle LogMiner to get data from the commitlog to a view inside the database. Follow the steps below to configure LogMiner and prepare your database for use with RDI.

1. Configure Oracle LogMiner

The following example shows the configuration for Oracle LogMiner.

ORACLE_SID=ORACLCDB dbz_oracle sqlplus /nolog

CONNECT sys/top_secret AS SYSDBA
alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
-- You should now see "Database log mode: Archive Mode"
archive log list

exit;

Configure Amazon RDS for Oracle

AWS provides its own set of commands to configure LogMiner.

Check that Oracle has backups enabled with the following command:

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

The LOG_MODE should be set to ARCHIVELOG. If it isn't then you should reboot your Oracle AWS RDS instance.

Once LOG_MODE is correctly set to ARCHIVELOG, execute the following commands to complete the LogMiner configuration. The first command enables archive logging and the second adds supplemental logging.

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

2. Enable supplemental logging

You must enable supplemental logging for the tables you want to capture or for the entire database. This lets Debezium capture the state of database rows before and after changes occur.

The following example shows how to configure supplemental logging for all columns in a single table called inventory.customers:

ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

You must also enable minimal supplemental logging at the database level with the following command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3. Check the redo log sizing

Before you use the Debezium connector, you should check with your database administrator that there are enough redo logs with enough capacity to store the data dictionary for your database. In general, the size of the data dictionary increases with the number of tables and columns in the database. If you don't have enough capacity in the logs then you might see performance problems with both the database and the Debezium connector.

4. Set the Archive log destination

You can configure up to 31 different destinations for archive logs (you must have administrator privileges to do this). You can set parameters for each destination to specify its purpose, such as log shipping for physical standbys, or external storage to allow for extended log retention. Oracle reports details about archive log destinations in the V$ARCHIVE_DEST_STATUS view.

The Debezium Oracle connector only uses destinations that have a status of VALID and a type of LOCAL. If you only have one destination with these settings then Debezium will use it automatically. If you have more than one destination with these settings, then you should consult your database administrator about which one to choose for Debezium.

Use the log.mining.archive.destination.name property in the connector configuration to select the archive log destination for Debezium.

For example, suppose you have two archive destinations, LOG_ARCHIVE_DEST_2 and LOG_ARCHIVE_DEST_3, and they both have status set to VALID and type set to LOCAL. Debezium could use either of these destinations, so you must select one of them explicitly in the configuration. To select LOG_ARCHIVE_DEST_3, you would use the following setting:

{
    "log.mining.archive.destination.name": "LOG_ARCHIVE_DEST_3"
}

5. Create a user for the connector

The Debezium Oracle connector must run as an Oracle LogMiner user with specific permissions. The following example shows some SQL that creates an Oracle user account for the connector in a multi-tenant database model:

sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdba
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf'
    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
exit;

sqlplus sys/top_secret@//localhost:1521/ORCLPDB1 as sysdba
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf'
    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
exit;

sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdba

CREATE USER c##dbzuser IDENTIFIED BY dbz
    DEFAULT TABLESPACE logminer_tbs
    QUOTA UNLIMITED ON logminer_tbs
    CONTAINER=ALL;

GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL;
GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to c##dbzuser CONTAINER=ALL;

-- See `Limiting privileges` below if the privileges
-- granted by these two commands raise security concerns.
GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL;
-- 

GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL;
GRANT LOGMINING TO c##dbzuser CONTAINER=ALL;

-- See `Limiting privileges` below if the privileges
-- granted by these two commands raise security concerns.
GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;
-- 

GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL;

GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL;

GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$TRANSACTION TO c##dbzuser CONTAINER=ALL;

GRANT SELECT ON V_$MYSTAT TO c##dbzuser CONTAINER=ALL;
GRANT SELECT ON V_$STATNAME TO c##dbzuser CONTAINER=ALL;

exit;

Limiting privileges

The privileges granted in the example above are convenient, but you may prefer to restrict them further to improve security. In particular, you might want to prevent the Debezium user from creating tables, or selecting or locking any table.

The Debezium user needs the CREATE TABLE privilege to create the LOG_MINING_FLUSH table when it connects for the first time. After this point, it doesn't need to create any more tables, so you can safely revoke this privilege with the following command:

REVOKE CREATE TABLE FROM c##dbzuser container=all;

The example above grants the SELECT ANY TABLE and FLASHBACK ANY TABLE privileges for convenience, but only the tables synced to RDI and the V_$XXX tables strictly need these privileges. You can replace the GRANT SELECT ANY TABLE command with explicit commands for each table. For example, you would use commands like the following for the tables in our sample chinook database. (Note that Oracle 19c requires you to run a separate GRANT command for each table individually.)

GRANT SELECT ON chinook.album TO c##dbzuser;
GRANT SELECT ON chinook.artist TO c##dbzuser;
GRANT SELECT ON chinook.customer TO c##dbzuser;
...

Similarly, instead of GRANT FLASHBACK ANY TABLE, you would use the following commands:

GRANT FLASHBACK ON chinook.album TO c##dbzuser;
GRANT FLASHBACK ON chinook.artist TO c##dbzuser;
GRANT FLASHBACK ON chinook.customer TO c##dbzuser;
...

The LOCK privilege is automatically granted by the SELECT privilege, so you can omit this command if you have granted SELECT on specific tables.

Revoking existing privileges

If you initially set the Debezium user's privileges on all tables, but you now want to restrict them, you can revoke the existing privileges before resetting them as described in the Limiting privileges section.

Use the following commands to revoke and reset the SELECT privileges:

REVOKE SELECT ANY TABLE FROM c##dbzuser container=all;
ALTER SESSION SET container=orclpdb1;

GRANT SELECT ON chinook.album TO c##dbzuser;
-- ...etc

The equivalent commands for FLASHBACK are:

REVOKE FLASHBACK ANY TABLE FROM c##dbzuser container=all;
ALTER SESSION SET container=orclpdb1;
GRANT FLASHBACK ON chinook.album TO c##dbzuser;

The SELECT privilege automatically includes the LOCK privilege, so when you grant SELECT for specific tables you should also revoke LOCK on all tables:

REVOKE LOCK ANY TABLE FROM c##dbzuser container=all;

6. Configuration is complete

Once you have followed the steps above, your Oracle database is ready for Debezium to use.