Oracle 26ai LogMiner: Investigate Database Changes Through Redo

Oracle LogMiner gives administrators a SQL interface to supported online and archived redo logs. It helps answer a focused question: what recorded changes affected this data during the period under investigation? A mistaken update, an unexpected delete, or a disputed application transaction can leave evidence in redo that explains how the current state arose.

LogMiner complements auditing and recovery. It does not replace an audit policy, restore a damaged physical block, or automatically reverse a business mistake. Its value is the evidence it exposes: transaction identifiers, change and commit information, affected objects, available session context, and reconstructed SQL. The administrator must establish that the relevant redo is available and interpret the results within their limits.

Define a bounded investigation

Start with the affected database and PDB, object owner and name, approximate time, and the business symptom. Translate the period into a suitable SCN or time range and retain the reasoning used to choose it. Specify the question before collecting a large result set: identifying one erroneous payroll transaction requires different evidence from measuring a month's update activity.

Suppose an employee's salary unexpectedly doubled. Record the employee identifier, expected salary, observed salary, and when the discrepancy was discovered. Then investigate changes to the relevant table around the suspected event. Once a candidate transaction appears, expand the investigation to all relevant changes in that transaction and any dependent later work. A filter that finds the first symptom should not define the entire recovery scope.

Retain original logs and query output separately from working notes. Record database identity, incarnation, redo threads, log sequences, dictionary choice, mining options, and filters. An exported report without that context can look complete even when a missing archive log or an overly narrow interval excluded decisive evidence.

Agree on the stopping condition as well. An investigation may finish when the responsible transaction and affected records are identified, even though recovery still requires a separate decision. Another investigation may need to explain a gap in the available evidence. Treat those as different outcomes. A useful report can conclude that the retained material is insufficient, provided it states exactly what is missing and why that prevents a stronger conclusion.

For time-based reports, record the time zone used by the incident report and by the database session. Avoid assuming that an application timestamp and a database timestamp express the same clock convention. SCNs provide a database change boundary, while business timestamps help explain the event to people. Keep both when available, along with the method used to relate them.

Prepare redo, logging, and a compatible dictionary

The examples below assume a self-managed source CDB that is open, uses ARCHIVELOG mode for retained history, and has suitable supplemental logging already configured. Online redo is also mineable, but it is reused; plan retention rather than assuming yesterday's changes remain available. Preserve the required archived logs before routine retention processes remove them.

Supplemental logging adds information useful for interpreting changes. Minimal supplemental logging is not a guarantee of complete before-and-after row images. Key and column logging choices have different purposes and costs. Configure the required information before the events of interest; enabling it after an incident does not fill gaps in historical redo.

The following is an example of a separately approved preparation change at the CDB root, not a diagnostic query to run during every investigation. Its scope is broad and must be assessed before use:

-- Future preparation in CDB$ROOT, after configuration review.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

In 26ai, local undo permits supplemental logging to be configured independently for a PDB; shared undo has different root requirements. Review the actual undo mode and logging configuration instead of applying one rule to every CDB. The DBA_SUPPLEMENTAL_LOGGING view helps inspect PDB logging settings. Oracle's LogMiner Utilities guide explains the 26ai distinction.

The dictionary gives meaning to object identifiers and recorded values. This lesson uses the source database's online catalog. That is convenient when the current definitions match the objects at the time of the changes. Historical DDL can invalidate that assumption. For other investigations, evaluate a supported dictionary-in-redo workflow and its required logs. Do not combine online-catalog mining with DDL_DICT_TRACKING, or copy an obsolete flat-file-dictionary recipe into a 26ai runbook.

Example: investigate one PDB from the CDB root

Use an authorized common account in CDB$ROOT, with LOGMINING, the documented EXECUTE_CATALOG_ROLE requirement, and access to the diagnostic views used here. These are powerful privileges to provision through normal administration, not permissions to grant casually. Confirm the session container before starting:

SHOW CON_NAME

The following server-side file paths, SCNs, and PDB name are illustrative. They have not been executed against a database. Substitute verified values from the same source database and incarnation. The two files illustrate registration syntax, not a promise that two logs always cover an incident. Include all relevant active RAC threads and enough history to cover the transactions and their commits.

1. Register the required files

BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u02/logminer/incident_redo_01.arc',
    OPTIONS => DBMS_LOGMNR.NEW);

  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u02/logminer/incident_redo_02.arc',
    OPTIONS => DBMS_LOGMNR.ADDFILE);
END;
/

NEW replaces the session's previous file list and ends an existing mining session. Use it intentionally for the first file, then add the rest. Inspect the registered list rather than assuming that successful calls establish complete coverage:

SELECT filename, low_scn, next_scn, status, info
FROM v$logmnr_logs
ORDER BY low_scn, filename;

Review file ranges and diagnostic information using the V$LOGMNR_LOGS reference. Resolve missing or unsuitable inputs before interpreting the investigation as complete. A file whose range overlaps the requested period is only one part of the coverage check.

2. Start the bounded session

BEGIN
  DBMS_LOGMNR.START_LOGMNR(
    STARTSCN => 5000000,
    ENDSCN => 5010000,
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
             + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;
/

COMMITTED_DATA_ONLY returns committed transaction changes together in commit order and filters rolled-back or unfinished work. This helps examine changes that took effect, but the necessary transaction and commit evidence must be within the available mining coverage. An incomplete window can mislead an investigator. Choose a wider justified interval when transaction boundaries require it.

3. Find candidate changes

SELECT scn, commit_scn, timestamp,
       RAWTOHEX(xid) AS transaction_id,
       username, src_con_name, operation,
       seg_owner, seg_name, status,
       rs_id, ssn, csf, sql_redo, sql_undo
FROM v$logmnr_contents
WHERE src_con_name = 'APP_PDB'
  AND seg_owner = 'HR'
  AND seg_name = 'EMPLOYEES'
  AND operation IN ('INSERT', 'UPDATE', 'DELETE');

This query finds candidate DML for one table in one source PDB. It deliberately does not claim to be a complete transaction export or an executable rollback script. Examine diagnostic and unsupported-operation records separately; the DML filter excludes them. Once a candidate transaction is identified, retrieve the broader transaction evidence rather than restricting every subsequent query to the original table.

4. End the session

Register files, start mining, query, and end mining in the same SQL connection. Save the required results before cleanup. When the mining session has started successfully, release it after the investigation, including when later analysis encounters an error:

BEGIN
  DBMS_LOGMNR.END_LOGMNR;
END;
/

An automation should preserve the original error and attempt appropriate cleanup; cleanup errors should not hide the incident that interrupted mining. A new connection should not be assumed to inherit another connection's LogMiner state.

Use the findings to plan recovery

The previous lesson addressed physical corruption and block recovery. A valid but mistaken salary update is a different problem: the database blocks may be healthy while the business values are wrong. LogMiner can help identify the responsible transaction and a useful recovery boundary. It supplies evidence for choosing an application correction, a compensating transaction, or a broader recovery procedure.

Do not execute every returned SQL_UNDO value automatically. Consider the entire original transaction, later legitimate updates, related tables, constraints, triggers, and external effects. Reversing an employee row without considering payroll records may create a new inconsistency. A statement that matched a row at the incident time may also be unsuitable for the row's current state.

Rehearse the proposed correction in a test copy representing the relevant state. Specify exactly which records should change and which later work must remain intact. Compare row identifiers, values, relationships, and business totals before and after the rehearsal. Have the application owner review the result and obtain authorization for the actual production correction. None of the examples in this lesson executes mined SQL.

For the doubled-salary example, distinguish two cases. If the erroneous update was the last relevant operation, a carefully verified correction may be straightforward. If a later authorized adjustment changed the same employee, blindly restoring the earlier salary could erase valid work. The investigator needs the sequence of events and intended business outcome, not simply the first reverse statement found in redo.

Control cost and preserve useful results

Redo already exists for recovery, but mining it is not free. Reading logs, reconstructing transactions, and querying the contents consume resources; supplemental logging can also increase redo volume. Start with a justified interval and monitor the investigation's effect on the system. A selective SQL filter makes the report easier to review, but does not guarantee that little redo must be read.

If several analysts need the same evidence, consider an approved staging dataset instead of repeatedly mining the same interval. Retain transaction and continuation metadata alongside the text, document extraction filters, and limit access to sensitive row values. Keep any sanitized report distinct from the protected source evidence so that later reviewers understand what was omitted.

Finish with a concise incident record: the question investigated, source database and PDB, available logs, dictionary and options, observed transactions, limitations, and the proposed response. LogMiner can also support change-volume analysis and application debugging, but those findings remain bounded by the selected logs and supported operations. A count of mined changes is not a complete measure of reads, workload cost, or user activity.

Check your understanding

Before continuing, explain why supplemental logging must precede the events of interest, why committed-only output needs adequate log coverage, and why reconstructed undo SQL requires review.

Use the log_archive_dest - Quiz to review backup and recovery improvements. The next lesson explains how to create reports and lists about backups.