Oracle DBMS_LOGMNR and Log Miner
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Enables online query online of archived redo log files through a SQL interface and provides most of the tools requiredd to start and stop log mining.
AUTHID DEFINER
Constants
Name Data Type Value
Add archive log option flags
NEW BINARY_INTEGER 1
REMOVEFILE BINARY_INTEGER 2
ADDFILE BINARY_INTEGER 3
Status column option flags
VALID_SQL BINARY_INTEGER 0
INVALID_SQL BINARY_INTEGER 2
UNGUARANTEED_SQL BINARY_INTEGER 3
CORRUPTED_BLK_IN_REDO BINARY_INTEGER 4
ASSEMBLY_REQUIRED_SQL BINARY_INTEGER 5
HOLE_IN_LOGSTREAM BINARY_INTEGER 1291

Start LOGMNR option flags
Name Data Type Value Description
COMMITTED_DATA_ONLY BINARY_INTEGER 2 If set, DML statements corresponding to committed transactions are returned. DML statements corresponding to a committed transaction are grouped together. Transactions are returned in their commit order. Transactions that are rolled back or in-progress are filtered out, as are internal redo records (those related to index operations, management, and so on).

If this option is not set, all rows for all transactions (committed, rolled back, and in-progress) are returned in the order in which they are found in the redo logs (in order of SCN values).
CONTINUOUS_MINE BINARY_INTEGER 1024 Directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You only need to specify the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. You are not required to specify any redo log files explicitly. LogMiner automatically adds and mines the (archived and online) redo log files for the data of interest. This option requires that LogMiner is connected to the same database instance that is generating the redo log files. It also requires that the database be mounted and that archiving be enabled.

Beginning with Oracle Database release 10.1, the CONTINUOUS_MINE options is supported for use in an Oracle Real Application Clusters environment.
DDL_DICT_TRACKING BINARY_INTEGER 8 If the LogMiner dictionary in use is a flat file or in the redo log files, LogMiner updates its internal dictionary if a DDL event occurs. This ensures that correct SQL_REDO and SQL_UNDO information is maintained for objects that are modified after the LogMiner internal dictionary is built. The database to which LogMiner is connected must be open.

This option cannot be used in conjunction with the DICT_FROM_ONLINE_CATALOG option.
DICT_FROM_ONLINE_CATALOG BINARY_INTEGER 16 Directs LogMiner to use the current online database dictionary rather than a LogMiner dictionary contained in a flat file or in the redo log files being analyzed.

This option cannot be used in conjunction with the DDL_DICT_TRACKING option. The database to which LogMiner is connected must be the same one that generated the redo log files.

Expect to see a value of 2 in the STATUS column of the GV$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.
DICT_FROM_REDO_LOGS BINARY_INTEGER 32 If set, LogMiner expects to find a LogMiner dictionary in the redo log files that were specified. The redo log files are specified with the DBMS_LOGMNR.ADD_LOGFILE procedure or with the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option.
NO_DICT_RESET_ONSELECT BINARY_INTEGER 1 Will be deprecated soon
NO_ROWID_IN_STMT BINARY_INTEGER 2048 If set, the ROWID clause is not included in the reconstructed SQL statements. The redo log file may already contain logically unique identifiers for modified rows if supplemental logging is enabled.

When using this option, you must be sure that supplemental logging was enabled in the source database at the appropriate level and that no duplicate rows exist in the tables of interest. LogMiner does not make any guarantee regarding the uniqueness of logical row identifiers.
NO_SQL_DELIMITER BINARY_INTEGER 64 If set, the SQL delimiter (a semicolon) is not placed at the end of reconstructed SQL statements. This is helpful for applications that open a cursor and then execute the reconstructed statements.
PRINT_PRETTY_SQL BINARY_INTEGER 512 If set, LogMiner formats the reconstructed SQL statements for ease of reading. These reconstructed SQL statements are not executable.
SKIP_CORRUPTION BINARY_INTEGER 4 Directs a select operation on the GV$LOGMNR_CONTENTS view to skip any corruptions in the redo log file being analyzed and continue processing. This option works only when a block in the redo log file (and not the header of the redo log file) is corrupt. You should check the INFO column in the GV$LOGMNR_CONTENTS view to determine the corrupt blocks skipped by LogMiner. When a corruption in the redo log file is skipped, the OPERATION column contains the value CORRUPTED_BLOCKS, and the STATUS column contains the value 1343.
STRING_LITERALS_IN_STMT BINARY_INTEGER 4096 Undocumented
Data Types SUBTYPE Length IS BINARY_INTEGER;

SUBTYPE ThreadId IS BINARY_INTEGER;

-- workarounds for the lack of constrained subtypes
LogFileNameTemplate VARCHAR2(256);
SUBTYPE LogFileName IS LogFileNameTemplate%TYPE;

LogFileDescTemplate VARCHAR2(256);
SUBTYPE LogFileDescription IS LogFileDescTemplate%TYPE;
Dependencies
DBA_LOGMNR_LOG GV_$LOGMNR_CALLBACK GV_$LOGMNR_PARAMETERS
DBA_LOGMNR_PURGED_LOG GV_$LOGMNR_CONTENTS GV_$LOGMNR_PROCESS
DBA_LOGMNR_SESSION GV_$LOGMNR_DICTIONARY GV_$LOGMNR_REGION
DBMS_APPLY_ADM_INTERNAL GV_$LOGMNR_DICTIONARY_LOAD GV_$LOGMNR_SESSION
DBMS_LOGMNR_INTERNAL GV_$LOGMNR_LATCH GV_$LOGMNR_STATS
DBMS_STREAMS_ADM_IVK GV_$LOGMNR_LOGFILE GV_$LOGMNR_TRANSACTION
DBMS_STREAMS_ADM_UTL GV_$LOGMNR_LOGS  
Documented Yes
Exceptions
Error Code Reason
ORA-00904 Value specified for the column_name parameter is not a fully qualified column name.
ORA-01281 startScn or endSCN parameter specified is not a valid SCN or endScn is greater then startScn
ORA-01282 startTime parameter not between years 1988 and 2110 or endTime parameter is greater than year 2110
ORA-01283 The value specified in the Options parameter is not a NUMBER or is not a known LogMiner Adhoc option
ORA-01284 Specified dictionary file in DictFileName parameter has a length greater then 256 or cannot be opened
ORA-01285 DictFileName parameter is not a valid VARCHAR2
ORA-01286 Options specified require start time or start SCN
ORA-01287 Specified file is from a different database incarnation
ORA-01289 Specified file has already been added to the list. Duplicate redo log files cannot be added.
ORA-01290 Specified file is not in the current list and therefore cannot be removed from the list.
ORA-01291 Redo files needed to satisfy the user's requested SCN/time range are missing. The user can specify ALLOW_MISSING_LOGS option. Missing logs are not allowed when DDL tracking is in use
ORA-01292 No log file has been registered with LogMiner
ORA-01293 Mounted database required for options specified (CONTINUOUS_MINE)
ORA-01294 Error while processing the data dictionary extract
ORA-01295 DB_ID of the data dictionary does not match that of the redo logs
ORA-01296 Character set specified in the data dictionary does not match (is incompatible with) that of the database
ORA-01297 Redo version mismatch between the dictionary and the registered redo logs
ORA-01298 More than one dictionary source was specified or DDL_DICT_TRACKING was requested with DICT_FROM_ONLINE_CATALOG
ORA-01299 Dictionary is from a different database incarnation
ORA-01300 Writable database required for options specified (DDL_DICT_TRACKING, DICT_FROM_REDO_LOGS,
DICT_FROM_ONLINE_CATALOG)
ORA-01323 A LogMiner dictionary is not associated with the LogMiner session
ORA-01324 Specified file cannot be added to the list because there is a DB_ID mismatch.
ORA-01371 A logfile containing the dictionary dump to redo logs is missing
First Available 8.1.5
Pragmas pragma TIMESTAMP('1998-05-05:11:25:00');
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmslm.sql
Subprograms
 
ADD_LOGFILE
Register log files to be analyzed dbms_logmnr.add_logfile(
logfilename IN VARCHAR2,
options     IN BINARY_INTEGER DEFAULT ADDFILE);
See MINE_VALUE Demo Below
 
COLUMN_PRESENT
Call this function for any row returned from the V$LOGMNR_CONTENTS view to determine if undo or redo column values exist for the column specified by the column_name input parameter to this function -- designed to be used in conjunction with the MINE_VALUE function
dbms_logmnr.column_present(
sql_redo_undo IN NUMBER   DEFAULT 0,
column_name   IN VARCHAR2 DEFAULT '') RETURN BINARY_INTEGER;
See MINE_VALUE Demo Below
 
END_LOGMNR
Completes a log miner session dbms_logmnr.end_logmnr;
See MINE_VALUE Demo Below
 
MINE_VALUE
Facilitates queries based on a column's data value dbms_logmnr.mine_value(
sql_redo_undo IN NUMBER DEFAULT 0,
column_name IN VARCHAR2 DEFAULT '')
RETURN VARCHAR2;
conn / as sysdba

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

GRANT select ON v$logmnr_contents TO uwclass;

desc gv$log

SELECT group#, thread#, sequence#, members, status
FROM v$log;

ALTER SYSTEM switch logfile;

SELECT group#, thread#, sequence#, members, status
FROM gv$log;

desc v$logmnr_contents

SELECT COUNT(*)
FROM v$logmnr_contents;

GRANT select ON v_$database TO uwclass;

conn uwclass/uwclass

-- capture starting SCN
SELECT current_scn
FROM v$database;
-- 7466113

UPDATE airplanes
SET customer_id = 'FIND'
WHERE line_number = 13397;

COMMIT;

-- capture ending SCN
SELECT current_scn
FROM v$database;
-- 7466134

ALTER SYSTEM switch logfile;

-- copy control file to c:\temp\demo.arc

exec sys.dbms_logmnr.add_logfile('c:\temp\demo1.arc');
exec sys.dbms_logmnr.add_logfile('c:\temp\demo2.arc');
exec sys.dbms_logmnr.add_logfile('c:\temp\demo3.arc');
exec sys.dbms_logmnr.start_logmnr(7466113,7466134);

exec sys.dbms_logmnr.start_logmnr(7466113, 7466134, options=>2);

col object_name format a30

desc v$logmnr_contents;
-- note abs_file#, rel_file#, data_blk#

SELECT v.scn, v.commit_timestamp, v.table_name, o.object_name, v.operation
FROM sys.v_$logmnr_contents v, dba_objects_ae o
WHERE SUBSTR(v.table_name,6) = o.object_id;

SELECT sql_redo
FROM sys.v_$logmnr_contents;

SELECT sql_undo
FROM sys.v_$logmnr_contents;

exec sys.dbms_logmnr.end_logmnr;

conn / as sysdba

SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE OPEN;

SELECT info
FROM gv$logmnr_contents;

SELECT sql_redo, sql_undo
FROM gv$logmnr_contents
WHERE username = 'UWCLASS';

SELECT utl_raw.cast_to_varchar2(HEXTORAW('53414c')) FROM dual;
NWO  HEXTORAW('4e574f')
USAF HEXTORAW('55534146')
DAL  HEXTORAW('44414c')
SAL  HEXTORAW('53414c')

SELECT sql_redo
FROM sys.v_$logmnr_contents
WHERE seg_name = 'AIRPLANES'
AND seg_owner = 'UWCLASS'
AND operation = 'UPDATE'
AND sys.dbms_logmnr.mine_value(REDO_VALUE, 'CUSTOMER_ID') <>  sys.dbms_logmnr.mine_value(UNDO_VALUE, 'CUSTOMER_ID');

exec sys.dbms_logmnr.end_logmnr;
 
REMOVE_LOGFILE
Removes a redo log file from an existing list of redo log files for LogMiner to process dbms_logmnr.remove_logfile(LogFileName IN VARCHAR2);
TBD
 
START_LOGMNR
Begin a log miner session dbms_logmnr.start_logmnr(
startscn      IN NUMBER         DEFAULT 0,
endscn        IN NUMBER         DEFAULT 0,
starttime     IN DATE           DEFAULT '',
endtime       IN DATE           DEFAULT '',
dictfilename  IN VARCHAR2       DEFAULT '',
options       IN BINARY_INTEGER DEFAULT 0);
See MINE_VALUE Demo Above

Related Topics
DBMS_FLASHBACK
DBMS_LOGMNR_D
Packages
UTL_RAW

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved