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.

Prerequisite

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

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.

Singletenant Configuration

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.

Multitenant Configuration

A Multitenant architecture, is built on the concept of a CDB (Container Database) that can host multiple Pluggable Databases (PDBs) within a shared infrastructure. The CDB contains common resources, while each PDB represents a distinct, isolated database within the shared container.

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.

Enabling CDC in Oracle CDB-PDB

In Oracle's multitenant architecture, especially when dealing with redo logs in a Pluggable Database (PDB) environment, it is necessary to have an administrative user named 'sys' with the 'sysdba' role to create a new user for Rivery applications. This user must have the ability to employ the LogMiner API for performing redo log operations on Pluggable Databases (PDBs), a functionality utilized by Rivery for streaming changes.

The following permissions need to be assigned:

ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER c##redouser IDENTIFIED BY <PASSWORD> CONTAINER=ALL;
GRANT CREATE SESSION, ALTER SESSION, SET CONTAINER TO c##redouser CONTAINER=ALL;
ALTER USER c##redouser SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

GRANT SELECT ON V$CONTAINERS TO c##redouser;
GRANT SELECT ON SYS.V_$DATABASE TO c##redouser;
GRANT SELECT ON SYS.V_$PARAMETER TO c##redouser;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO c##redouser;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO c##redouser;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO c##redouser;
GRANT SELECT ON SYS.V_$LOG TO c##redouser CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOG_HISTORY TO c##redouser CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO c##redouser CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO C##REDOUSER CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO c##redouser CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGFILE TO c##redouser CONTAINER=ALL;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST_STATUS TO c##redouser CONTAINER=ALL;
GRANT SELECT ON SYS.V_$TRANSACTION TO c##redouser CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO c##redouser;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##redouser;
GRANT SELECT ANY TRANSACTION TO c##redouser;
GRANT EXECUTE_CATALOG_ROLE TO c##redouser;
GRANT LOGMINING TO c##redouser;
GRANT SELECT ON V_$MYSTAT TO c##riveryuser CONTAINER=ALL;
GRANT SELECT ON V_$STATNAME TO c##riveryuser CONTAINER=ALL;

RAC CDB-PDB Configuration

Oracle Real Application Clusters (RAC) enables the clustering of Oracle databases across multiple servers, providing high availability and scalability. Within a RAC environment utilizing Container Database (CDB) and Pluggable Database (PDB) architecture, users are categorized into 2 main types: Common Users and Local Users.
This section covers the characteristics, connection settings, permissions, limitations, and operational procedures associated with these user types.

Types of Users

1. Common Users
   Common Users are established at the Container Database (CDB) level and possess uniform identity and privileges across all Pluggable Databases (PDBs) within the same CDB. They are centrally managed at the CDB level, facilitating streamlined administration of privileges and access rights. Common Users are typically employed for tasks necessitating access to data and resources spanning multiple PDBs.

2. Local Users
   Local Users are created at the Pluggable Database (PDB) level, tailored to a specific PDB, and possess privileges confined solely to that database. Managed within individual PDBs, Local Users offer granular control over privileges and access rights specific to the respective database. They are utilized for tasks exclusive to a particular PDB without the need for accessing resources beyond its scope.

Connection Settings

To connect to a RAC CDB-PDB environment, users should select the multitenant Database Architecture in the Connection form. It's important to note that when connecting to the CDB database, a Common User should be utilized to ensure access to archive log files for Change Data Capture (CDC) functionality.

For CDB Database connection, configure connection settings with the RAC CDB host using a Common User.
For PDB DB connection, configure settings with the RAC PDB host utilizing either a Common or Local User, as per requirements.

Permissions by Users

Common User Permissions:

-- Permissions for Common User
GRANT SELECT ON SYS.V_$DATABASE TO c##user;
GRANT SELECT ON SYS.V_$PARAMETER TO c##user;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO c##user;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO c##user;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO c##user;
GRANT SELECT ON SYS.V_$LOG TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOG_HISTORY TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGFILE TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST_STATUS TO c##user CONTAINER=ALL;
GRANT SELECT ON SYS.V_$TRANSACTION TO c##user CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO c##user;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##user;
GRANT SELECT ANY TRANSACTION TO c##user;
GRANT SELECT ANY TRANSACTION TO c##user;
GRANT ALTER ANY TABLE TO c##user;
GRANT SELECT ANY TABLE TO c##user;
GRANT SELECT ANY DICTIONARY TO c##user;
GRANT SELECT_CATALOG_ROLE TO c##user;
GRANT DROP ANY TABLE TO c##user;
GRANT ALTER ANY TABLE TO c##user;

Local User Permissions:

-- Permissions for Local User
GRANT SELECT ON SYS.V_$DATABASE TO USER;
GRANT SELECT ON SYS.V_$PARAMETER TO USER;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO USER;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO USER;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO USER;
ALTER SESSION SET CONTAINER =CONTAINER NAME;
GRANT SELECT ON SYS.V_$LOG TO USER;
GRANT SELECT ON SYS.V_$LOG TO USER
GRANT SELECT ON SYS.V_$LOG_HISTORY TO USER
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO USER
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO USER
GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO USER
GRANT SELECT ON SYS.V_$LOGFILE TO USER
GRANT SELECT ON SYS.V_$ARCHIVE_DEST_STATUS TO USER
GRANT SELECT ON SYS.V_$TRANSACTION TO USER
GRANT EXECUTE ON DBMS_LOGMNR TO USER;
GRANT EXECUTE ON DBMS_LOGMNR_D TO USER;
GRANT SELECT ANY TRANSACTION TO USER;
GRANT ALTER ANY TABLE TO USER;
GRANT SELECT ANY TABLE TO USER;
GRANT SELECT ANY DICTIONARY TO USER;
GRANT SELECT_CATALOG_ROLE TO USER;
GRANT DROP ANY TABLE TO USER;
GRANT ALTER ANY TABLE TO USER;

Limitations

• Table and Column Naming: Rivery does not support table names and column names that combine uppercase and lowercase characters.
• Special Characters: Special characters are not supported during schema, table, or column creation.
• UTF-8 Characters: Failure of a table due to unsupported UTF-8 characters leads to failure of all associated Rivers.
• CDC Recommendation: Utilize the Upsert-Merge loading mode for CDC to prevent duplication.

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

• DDL Operations: Changes from DDL operations (e.g., adding/dropping columns, truncating tables) are not captured.
• Incomplete/Rolled-Back Transactions: The CDC connector captures changes from incomplete or ROLLBACK transactions, leading to potential inconsistencies.
• Primary Key Updates: Updates to primary keys are not supported, causing errors or inconsistencies in the destination.
• Handling More Than 500 Tables: The CDC connector is limited to track changes for up to 500 tables.
• 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.
• Oracle Data Types: Oracle CDC does not support the following data types:
  • LONG
  • LONG RAW
  • BFILE
  • BLOB
  • CLOB
  • NCLOB
  • XMLTYPE
  • EMOJI types