Effect of Supplemental Logging on LogMiner with Example
(Doc ID 750198.1)
Last updated on FEBRUARY 03, 2022
Applies to:Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
Supplemental Logging :
Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging. Also other actions may depend on supplemental logging, e.g. DataPump import (impdp) uses either "Direct_Path" or "External_Table" mode for loading depending on supplemental logging (See <Note 552424.1>).
Please refer to the following documentation for Utilities, which mentions two important points regarding Supplemental Logging. 727633.1
1) You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
If the query returns a value of YES or IMPLICIT, minimal supplemental logging is enabled.
You can turn off Supplemental Logging by following command.
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
2) By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable.
Let us take an example of this, which is using Online Catalog option for logminer dictionary.
Part A : Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned OFF (default option )
Part B : Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned ON.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document