Oracle LogMiner Troubleshooting

Common Issues

ORA-01291: missing logfile

Symptom: The agent fails with ORA-01291: missing logfile during startup or processing.

Cause: Gluesync is trying to read a transaction from a redo log file that has been deleted from the disk and is no longer available in V$ARCHIVED_LOG.

Solution: 1. Check your RMAN retention policy. Ensure archives are kept long enough for Gluesync to process them. 2. If this is a new setup, ensure you are starting from a recent point in time or that old archives are restored. 3. You may need to restart the Gluesync pipeline with a fresh snapshot if the gap is unrecoverable.

ORA-01371: Complete LogMiner dictionary not found

Symptom: ORA-01371: Complete LogMiner dictionary not found

Cause: LogMiner cannot find a consistent dictionary to parse the redo logs. This often happens if the DICT_FROM_ONLINE_CATALOG option is used but the source database structure has changed significantly without finding a consistent SCN.

Solution: 1. Ensure SUPPLEMENTAL LOG DATA is enabled on the database or tables. 2. Retry the operation; transient dictionary issues sometimes resolve themselves as the online catalog advances.

Missing Updates / Incomplete Data

Symptom: Updates are processing but some columns are NULL or missing in the target.

Cause: Supplemental logging is not enabled for all columns.

Solution: Execute the following on the source table:

ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

And ensure the table is re-snapshotted or the change is picked up (LogMiner picks up supplemental logging changes dynamically).

High Latency

Symptom: Changes are taking a long time to reflect in the target.

Cause: 1. LogMiner performance can be impacted by large numbers of archived logs. 2. The agent might be reading through a large backlog of logs (e.g., after downtime).

Solution: 1. Check the agent logs to see the "Processing Archive Log sequence X" messages. 2. Verify CPU and I/O on the Oracle server. LogMiner consumes PGA memory and CPU.

LogMiner Session Memory Issues

Symptom: LogMiner sessions consuming excessive memory, causing performance degradation or ORA-04030 errors.

Cause: 1. Multiple concurrent LogMiner sessions running simultaneously 2. Large transaction volumes being processed 3. Insufficient PGA memory allocation 4. Long-running LogMiner sessions accumulating memory

Diagnosis: Check current LogMiner sessions and memory usage:

SELECT sid, serial#, username, program, status,
       pga_used_mem, pga_alloc_mem, pga_max_mem
FROM v$session
WHERE program LIKE '%logmnr%';

SELECT * FROM v$logmnr_session;

Solution: 1. Reduce batch size: Lower LOGMINER_BATCH_SIZE to process smaller batches 2. Adjust polling interval: Increase LOGMINER_POLLING_INTERVAL_SECONDS to reduce frequency 3. Monitor and cleanup: Ensure old LogMiner sessions are properly terminated 4. PGA tuning: Increase PGA_AGGREGATE_TARGET if memory pressure is chronic 5. Restart strategy: Use LOGMINER_RESTART_DELAY_SECONDS to allow memory cleanup between restarts

Prevention: - Set appropriate LOGMINER_MAX_ERROR_DELAY_SECONDS to prevent rapid retry cycles - Monitor V$LOGMNR_SESSIONS for orphaned sessions - Consider database maintenance windows for large data syncs

LogMiner High CPU Usage

Symptom: High CPU consumption on Oracle database server during LogMiner operations.

Cause: 1. Processing large volumes of redo logs 2. Complex dictionary lookups 3. Frequent LogMiner start/stop cycles

Solution: 1. Increase LOGMINER_BATCH_SIZE to reduce start/stop frequency 2. Optimize LOGMINER_START_DELAY_SECONDS for better batching 3. Ensure proper indexing on frequently accessed tables 4. Monitor V$LOGMNR_PARAMETERS for optimal configuration

Diagnostic Queries

Use these queries to check the status of LogMiner and Archivelogs:

1. Check Archive Log gaps:

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, NAME, ARCHIVED, DELETED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC;

2. Check LogMiner Session:

SELECT * FROM V$LOGMNR_SESSION;

Target Not Receiving Changes

Symptom: The target database is not receiving any changes from the source Oracle database.

Cause: This can occur when supplemental logging is not properly configured or when LogMiner is not correctly capturing the changes for the specific tables.

Solution:

1.Ensure supplemental logging is enabled for the table:

ALTER TABLE SCHEMA1.MYFIRSTTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

2.Grant proper permissions to the LogMiner user:

GRANT SELECT ON SCHEMA1.MYFIRSTTABLE TO GLUESYNC_LOGMNR;

3.Test LogMiner manually to verify changes are captured:

First, login as the LogMiner user (GLUESYNC_LOGMNR) and identify the current active log file:

SELECT lf.MEMBER
FROM V$LOG l
         JOIN V$LOGFILE lf ON l.GROUP# = lf.GROUP#
WHERE l.STATUS = 'CURRENT';

Add the log file to LogMiner:

BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/path/to/redo/log/file.log',
    OPTIONS => DBMS_LOGMNR.NEW
  );
END;
/

Start LogMiner:

BEGIN
  DBMS_LOGMNR.START_LOGMNR(
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
  );
END;
/

Query the captured changes with schema and table filters to verify your changes are being captured:

SELECT SCN, TIMESTAMP, SEG_OWNER, SEG_NAME, OPERATION, SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SCHEMA1'
  AND SEG_NAME = 'MYFIRSTTABLE'
ORDER BY SCN DESC;

If you can see your changes in the V$LOGMNR_CONTENTS view, then LogMiner is working correctly and the issue may be in the Gluesync agent configuration or network connectivity.

4.Stop LogMiner when done:

BEGIN
  DBMS_LOGMNR.END_LOGMNR;
END;
/