Oracle LogMiner Configurations

Oracle LogMiner is a backend utility that allows Spectrum to query and access the logs created for an Oracle database.

The utility allows Spectrumâ„¢ to read the Oracle data source's logs to track the changes made to columns of its tables, as part of the DB Change Data Reader stage.

Unsupported Datatypes and Table Storage Attributes

Oracle LogMiner does not support the following datatypes and table storage attributes:
  • BFILE datatype
  • Simple and nested abstract datatypes (ADTs)
  • Collections (nested tables and VARRAYs)
  • Object references
  • Tables on which compression has been enabled
  • SecureFiles

Supported Databases and Redo Log File Versions

LogMiner runs on Oracle databases version 8.1 or later.

You may use LogMiner to analyze redo log files from Oracle 8.0 databases as well. However, the information retrieved depends on the version of the log and not the version of the database in use.

For example, to use LogMiner optimally, the redo log files for Oracle9i can be augmented to capture additional information when supplemental logging is enabled. Redo log files created with older versions of Oracle do not have the additional data and may therefore have limitations on the operations and datatypes supported by LogMiner.

SQL* Loader Restrictions

Spectrum CDC can capture data that was loaded into Oracle tables by the SQL*Loader utility. However, the following restrictions apply:
  1. The data load must be through conventional path. Spectrum CDC cannot capture data that is loaded by a direct path load as Oracle LogMiner does not support direct path loads.
  2. The load method should be INSERT, APPEND, or REPLACE.

    TRUNCATE is not supported as the TRUNCATE command causes the SQL*Loader to issue the TRUNCATE TABLE DDL command. Since the Spectrum CDC feature does not capture the mentioned DDL, row deletions that result from using the TRUNCATE TABLE DDL command are not captured.

Required User Privileges

The following table identifies the minimum system privileges that Oracle CDC users must have:

System Privilege Oracle Version
ALTER ANY TABLE ALL
CONNECT ALL
LOCK ANY TABLE ALL
SELECT ANY TRANSACTION 10g or later

The following table identifies the minimum object privileges that Oracle CDC users must have:

Object Name Privilege
Source Tables LOCK ANY TABLE OR SELECT
PUBLIC.V$DATABASE SELECT
PUBLIC.V$LOGMNR_CONTENTS SELECT
SYS.DBMS_LOGMNR EXECUTE
SYS.DBMS_LOGMNR_D EXECUTE
For more information on Oracle LogMiner, see here.