Oracle CDC Configuration

Introduction

The Oracle CDC (Change Data Capture) is a powerful tool that allows you to capture and track changes made to your Oracle Database. To use this feature, you must first enable the Archivelog mode in your Oracle Database, which is essential for the CDC mechanism to function effectively. This mode ensures that all changes to the data are preserved and can be retrieved even in the event of a system failure.

This document will guide you through the process of setting up the Oracle CDC Connector by enabling Archivelog mode and supplemental logging, as well as granting necessary permissions to use LogMiner.

Oracle CDC Support: On-Premises and Amazon RDS

The Oracle CDC DB Connector is a powerful tool that offers support for both on-premises Oracle Database installations and Oracle RDS (Relational Database Service) environments on Amazon Web Services (AWS). Whether you are managing your Oracle Database on-premises or leveraging the advantages of Amazon RDS in the cloud, you can seamlessly implement change data capture using the Oracle CDC Connector.

On-Premises Support

If you have an on-premises Oracle Database deployment, the Oracle CDC Connector can be seamlessly integrated into your existing database infrastructure. The setup process remains consistent with the steps mentioned in this documentation, allowing you to enable Archivelog mode, set up supplemental logging, and grant necessary permissions for LogMiner. This ensures that you can efficiently capture and track changes in your on-premises Oracle Database.

Amazon RDS Support

Oracle CDC support extends to Amazon RDS, a fully managed database service provided by AWS. RDS simplifies database administration tasks, making it an attractive choice for organizations looking to leverage cloud-based database solutions. With Oracle CDC support for RDS, you can effectively enable change data capture and extract real-time data changes from your Oracle RDS instance.

When using Oracle CDC on RDS, the configuration process is similar to that of on-premises databases. You can enable Archivelog mode, set up supplemental logging, and grant the necessary permissions for LogMiner using the AWS Management Console or appropriate SQL commands.

Prerequisite

The ability to modify the database necessitates possessing sysdba user permissions.

Enabling Archivelog Mode

Before using the Oracle CDC Connector, you need to ensure that the Archivelog mode is enabled in your Oracle Database. If it is not already enabled, follow the steps below to enable it:

1. Log in as a sysdba user using SQL:

sqlplus / as sysdba

2. Verify if archivelog mode is already enabled:

SELECT log_mode FROM v$database;

If the result is "NOARCHIVELOG," the archivelog mode is disabled.

1. To enable archivelog mode, first shut down the database:

SHUTDOWN IMMEDIATE;

2. Start the database in mount mode:

STARTUP MOUNT;

3. Enable the archivelog mode:

ALTER DATABASE ARCHIVELOG;

4. Open the database:

ALTER DATABASE OPEN;

5. Verify that archivelog mode is now enabled:

SELECT log_mode FROM v$database;

Enabling Supplemental Logging

After enabling the archivelog mode, you need to enable supplemental logging, which provides additional information at the column level for update operations. Follow these steps to enable it:

1. Log in as a sysdba user using SQL:

sqlplus / as sysdba

2. Enable supplemental logging for all columns at the database level:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Required Permissions for LogMiner

Rivery retrieves data from LogMiner using Redologs file format.
To use Oracle LogMiner, users must have specific database privileges granted directly to them (roles are not sufficient). The following privileges are necessary:

1. 'SELECT ANY TRANSACTION': This privilege allows users to query the V$LOGMNR_CONTENTS view.

2. 'SELECT_CATALOG_ROLE': This role provides users with the ability to query data dictionary views.

3. 'EXECUTE_CATALOG_ROLE': This role provides users with the ability to execute packages owned by SYS, including the DBMS_LOGMNR package, which is essential for working with LogMiner.

To grant these privileges to a user, execute the following commands as a user with the necessary privileges, such as the SYS user:

GRANT SELECT ANY TRANSACTION TO username;
GRANT SELECT_CATALOG_ROLE TO username;
GRANT EXECUTE_CATALOG_ROLE TO username;

Replace username with the name of the user you want to grant the permissions to.

Establish a Connection Between Rivery and the Oracle

With everything configured in the Oracle Database, you can now establish a connection between Rivery and Oracle, allowing you to utilize the Oracle CDC feature.

Limitations

• Table and Column Names: LogMiner requires table and column names that are 30 characters or less. Therefore, ensure that your table and column names adhere to this limit to avoid errors when enabling CDC.

• CDBs and PDBs: Rivery does not provide compatibility for the multitenant architecture involving CDBs and PDBs, which allows an Oracle database to operate as a multitenant container database.

• Oracle Data Types: Oracle CDC does not support the following data types:

  • LONG
  • LONG RAW
  • BFILE
  • BLOB
  • CLOB
  • NCLOB
  • XMLTYPE
  • EMOJI types