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 and cannot be used when the
LogMiner dictionary being used is one that was extracted to a flat file prior to Oracle9i.
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.
Status column option flags
Name
Data Type
Value
ASSEMBLY_REQUIRED_SQL
BINARY_INTEGER
5
CORRUPTED_BLK_IN_REDO
BINARY_INTEGER
4
HOLE_IN_LOGSTREAM
BINARY_INTEGER
1291
INVALID_SQL
BINARY_INTEGER
2
UNGUARANTEED_SQL
BINARY_INTEGER
3
VALID_SQL
BINARY_INTEGER
0
Workarounds for the lack of constrained subtypes
LogFileNameTemplate VARCHAR2(256);
SUBTYPE LogFileName IS LogFileNameTemplate%TYPE;
LogFileDescTemplate VARCHAR2(256);
SUBTYPE LogFileDescription IS LogFileDescTemplate%TYPE;
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
A logfile containing the dictionary dump to redo logs is missing
Pragma
pragma TIMESTAMP('1998-05-05:11:25:00');
Security Model
Execute granted to execute_catalog_role
ADD_LOGFILE
Register logfiles to be analyzed
dbms_logmnr.add_logfile(
logfilename IN VARCHAR2,
options IN BINARY_INTEGER DEFAULT ADDFILE);
See MINE_VALUE Demo
COLUMN_PRESENT
Designed to be used in conjunction with the MINE_VALUE
function. If the MINE_VALUE function returns a NULL value, it can mean either
the specified column is not present in the redo or undo portion of the data or t
he specified column is present and is NULL.
dbms_logmnr.column_present(
sql_redo_undo IN NUMBER DEFAULT 0,
column_name IN VARCHAR2 DEFAULT '') RETURN BINARY_INTEGER;
See MINE_VALUE Demo
END_LOGMNR
Completes a log miner session
dbms_logmnr.end_logmnr;
See MINE_VALUE Demo
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 exclusive;`
alter database archivelog;
alter database open;
grant select on gv$logmnr_contents to uwclass;
desc gv$log
SELECT group#, thread#, sequence#, members, status
FROM gv$log;
alter system switch logfile;
SELECT group#, thread#, sequence#, members, status
FROM gv$log;
desc gv$logmnr_contents
SELECT COUNT(*)
FROM gv$logmnr_contents;
GRANT select ON gv_$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
SELECT v.scn, v.commit_timestamp, v.table_name, o.object_name, v.operation
FROM sys.v_$logmnr_contents v, dba_objects 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);
Free