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

Oracle's multitenant architecture allows multiple Pluggable Databases (PDBs) to exist within a Container Database (CDB).
For Change Data Capture (CDC) using LogMiner, Rivery requires a Common User (c##user) at the CDB level to access redo/archive logs across all PDBs.

Additionally, a regular Local User (pdb_user) at the PDB level can be created for application use within a specific PDB, but this user does not require CDC privileges.

User Types & Purpose

Creating & Configuring the CDB CDC User (c##user)

This Common User operates at the CDB level, allowing CDC across all PDBs by reading redo/archive logs.

Step 1: Create c##user at the CDB Level

-- Switch to the CDB root container
ALTER SESSION SET CONTAINER=CDB$ROOT;

-- Create a Common User for CDC across all PDBs
CREATE USER c##user IDENTIFIED BY <PASSWORD> CONTAINER=ALL;

-- Grant session-related privileges
GRANT CREATE SESSION, ALTER SESSION, SET CONTAINER TO c##user CONTAINER=ALL;

-- Allow access to all PDBs
ALTER USER c##user SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

Step 2: Grant Required Privileges for CDC & LogMiner

-- Grant privileges to access LogMiner and system views
GRANT SELECT ON V$CONTAINERS TO c##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_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 execution privileges for LogMiner
GRANT EXECUTE ON DBMS_LOGMNR TO c##user;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##user;

-- Additional required privileges for CDC
GRANT SELECT ANY TRANSACTION TO c##user;
GRANT EXECUTE_CATALOG_ROLE TO c##user;
GRANT LOGMINING TO c##user;
GRANT SELECT ON V_$MYSTAT TO c##user CONTAINER=ALL;
GRANT SELECT ON V_$STATNAME TO c##user CONTAINER=ALL;

**Please Note: **

• c##user is the required user in Rivery’s connection to enable CDC.
• This user has access to redo/archive logs across multiple PDBs.

Creating a Regular PDB User (pdb_user) (No CDC Privileges

A Local User (pdb_user) is only needed for application-level access inside a single PDB.
This user does not require access to redo/archive logs and is not used for CDC.

Step 1: Switch to the Target PDB

-- Switch to the desired PDB
ALTER SESSION SET CONTAINER=<PDB_NAME>;

Step 2: Create a Regular PDB User

-- Create a Local User inside the PDB
CREATE USER pdb_user IDENTIFIED BY <PASSWORD>;

-- Grant basic session privileges
GRANT CREATE SESSION TO pdb_user;

-- Grant standard object permissions (Modify as needed for your application)
GRANT CONNECT, RESOURCE TO pdb_user;
GRANT SELECT ON ALL_TABLES TO pdb_user;
GRANT SELECT ON ALL_VIEWS TO pdb_user;
GRANT SELECT ON ALL_TAB_COLUMNS TO pdb_user;

Please Note:

• This user is only for application use within the PDB and is not involved in CDC.
• No LogMiner or redo log permissions are granted.

Connection Settings for CDC

To enable CDC in Rivery, connect using the Common User (c##user), as it has access to the archive logs required for LogMiner.

Please Note:

• Use a Service Name (<CDB_Service_Name>) instead of an SID for connections.
• Avoid using SID-based connections, as they are not suitable for CDC.

Limitations Related to Oracle CDB-PDB

• 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