Configuring CDC for Oracle Databases

An Oracle Database deployment comes with various integration features, and one particularly interesting one is Change Data Capture (CDC) used for mining Oracle Redo/archive log.This feature allows designated users to mine Oracle redo logs, providing a detailed stream of all data modifications.

When setting up CDC, certain information is crucial for proper configuration. Before diving into the setup, let's review the fundamental requirements.

CDC Requirements Checklist

Before you begin, ensure your Oracle Database meets these prerequisites:

• Privileges: The Oracle user designated for CDC must have the necessary privileges to access system views and perform log mining operations - only granted users can access the runtime logminer.
• Supplemental Logging: Supplemental logging is enabled for both minimal and all column data. This ensures that the redo logs contain enough information to reconstruct changes accurately.
• ARCHIVELOG Mode: Your database must be running in ARCHIVELOG mode. This is a requirement for retaining redo logs, which CDC relies on for historical data capture.
• Archived Logs: Verify the availability of archived redo logs. You'll need to know the range of available logs to ensure you can capture changes from the desired timeframe.

Step-by-Step Configuration

Now, let's get into the practical steps for setting up CDC.

1. Creating a User for LogMiner Session Data

First, you'll need a dedicated Oracle user with specific privileges to read the LogMiner session data.

SQL

create user cdc_user identified by *******************;
grant connect, resource, create session to cdc_user;
GRANT SELECT ANY TRANSACTION TO cdc_user;
GRANT SELECT_CATALOG_ROLE TO cdc_user;
GRANT EXECUTE_CATALOG_ROLE TO cdc_user;

Note: Replace ******************* with a strong, secure password.

2. Setting Supplemental Logging

Supplemental logging adds additional information to the redo logs, which is essential for CDC to accurately capture changes.

First, check the current supplemental logging settings:

SQL

SQL> select SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI, SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG from v$database;

MIN      PK  UI  ALL

-------- --- --- ---
NO       NO  NO  NO

If the ALL_LOG column shows NO, you'll need to enable it:

SQL

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Database altered.

Verify the change:

SQL

SQL> select SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI, SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG from v$database;

MIN      PK  UI  ALL

-------- --- --- ---

IMPLICIT NO  NO  YES

Now, ALL_LOG should be YES or IMPLICIT.

3. Configuring LogMiner Options and Adding Redo Logs

To enable LogMiner to read your redo logs, you need to add the redo log files to its session.

First, generate a script to add all log files to be target of LogMiner:

SQL

set linesize 200 trimspool on pagesize 0
select 'exec dbms_logmnr.add_logfile('''||member||''')'
from v$logfile;
spool /tmp/add_logfiles.sql
/
spool off

Next, execute DBMS_LOGMNR_D.BUILD to build the LogMiner dictionary:

SQL

execute dbms_logmnr_d.build(options=> dbms_logmnr_d.store_in_redo_logs);

Then, explicitly add the redo log files you want to mine. The first ADD_LOGFILE call uses DBMS_LOGMNR.NEW to start a new LogMiner session, and subsequent calls use DBMS_LOGMNR.ADDFILE.

SQL

execute DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME =>'+RECO/ORCL/ONLINELOG/group_1.349.1234567',OPTIONS => DBMS_LOGMNR.NEW);
execute DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '+RECO/ORCL/ONLINELOG/group_2.351.1234567',OPTIONS => DBMS_LOGMNR.ADDFILE);
execute DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '+DATA/ORCL/ONLINELOG/group_3.405.1234567',OPTIONS => DBMS_LOGMNR.ADDFILE);

Finally, start the LogMiner session and grant the LOGMINING privilege to your CDC user:

SQL

exec dbms_logmnr.start_logmnr(options =>  DBMS_LOGMNR.COMMITTED_DATA_ONLY);
GRANT LOGMINING TO cdc_user;

Verifying LogMiner Contents

Once configured, you should be able to query V$LOGMNR_CONTENTS to see the mined log data:

SQL

SQL> select count(*) from  V$LOGMNR_CONTENTS;
   2667669

This count will vary depending on the activity in your database and the logs you've added.

You can also confirm successful LogMiner activity by checking your Oracle Alert Log for messages similar to these:

2025-01-30T12:43:55.647719-05:00
LOGMINER: Summary for session 2150095873, OS id 1548315
LOGMINER: StartScn: 6039716003450 (0x000005799308b67a)
LOGMINER: EndScn: 0 (0x0000000000000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: PSR flags: 0x0
LOGMINER: Session Flags: 0x7000449
LOGMINER: Session Flags2: 0x0
LOGMINER: Read buffers: 4
LOGMINER: Region Queue size: 256
LOGMINER: Redo Queue size: 4096
LOGMINER: Memory Size = 15M, HWM 14M, LWM 13M, 90%
LOGMINER: Memory Release Limit: 0M
LOGMINER: Max Decomp Region Memory: 1M
LOGMINER: Transaction Queue Size: 1024
2025-01-30T12:45:18.714520-05:00
LOGMINER: Begin mining logfile for session -2144871423 thread 1 sequence 260624, +RECO/ORCL/ONLINELOG/group_5.5430.
... (and so on for other log files)

Terminating LogMiner

If you need to stop the LogMiner session (for testing or cleanup), you can simply execute:

SQL

-- just for test
-- EXECUTE DBMS_LOGMNR.END_LOGMNR;

Configuring Oracle CDC through LogMiner provides a robust mechanism for understanding and reacting to changes within your database and retrieving data with tools that are compatible with Oracle CDC. With these steps, you're well on your way to leveraging this powerful feature for your data integration and analysis needs.

Licensing:

If you're using basic, built-in CDC mechanisms within Oracle Database Enterprise Edition (EE), such as:

• LogMiner (for non-real-time analysis or with some third-party tools): LogMiner is generally included with Oracle Database EE. However, its continuous mining option for real-time changes was deprecated in Oracle 19c. Many third-party CDC tools still leverage LogMiner.

Do you have any specific use cases in mind for Oracle CDC, or are you exploring its general capabilities and need Help? Reach out to the Pythian Team.