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.
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}