Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> {9i New Feature: Logminer } This one is long also

{9i New Feature: Logminer } This one is long also

From: JOE TESTA <>
Date: Tue, 30 Oct 2001 10:28:33 -0800
Message-ID: <>


This will only cover the NEW features of logminer, this is NOT an exhaustive study on how logminer works, SO this means you need to already know how logminer works as the beginning aspects are not covered here. NOTE: the majority of this info comes from metalink doc id:  148616.1, dated Oct 11, 2001
 If you want the total ins/outs of logminer, come and see me at
IOUG-A, if all goes well, i'll be doing a 2 hr mini-lesson on the logminer, beginning (just what is a redo log) thru advanced(can i track DDL changes and how does it affect me for looking at old data before the table had a column added), and we'll contrast/compare the command line versus the GUI logminer viewer that comes with OEM.
 and now back to the show:
 Logminer has been enhanced quite a bit in 9i.

Here are the features that will be discussed in this segment:

New Dictionary options  Redo log files as dictionary  Online data dictionaryDDL TrackingSkipping past redo log corruptionSkip uncommited transactionsChained/Migrated rowsClustered TablesDirect Path Insert LoggingSupplemental LoggingMine_value functionColumn_present functionGUI

--------------------------------------------------------------------------------------------------------------------- New 
Dictionary options:  Back in the 8i days, there was only flat file dictionary option.  This has been expanded to


Redo log files as dictionary:  This is where the current dictionary is written to the redo logs, there are
restrictions:        DBMS_LOGMNR_D.BUILD must be run on an Oracle9i
database        The database must be in archivelog mode        The COMPATIBLE
parameter value must be set to
9.0.X        The dictionary must belong to the same database as the redo logs to be analyzed        No DDL is allowed during the dictionary extraction        SQL>
execute DBMS_LOGMNR.START_LOGMNR(options => -> dbms_logmnr.dict_from_redo_logs); 

Online data dictionary: This means you will be using the current data dictionary as it exists right now in the database.

To instruct LogMiner to use the database data dictionary, simply provide this option to the DBMS_LOGMNR.START_LOGMNR

procedure after adding the logs to be analyzed. No dictionary build is done.SQL> execute DBMS_LOGMNR.START_LOGMNR(options =>
-dbms_logmnr.dict_from_online_catalog); ---------------------------------------------------------------------------------------------------------------------DDL 

A.     LogMiner automatically records the SQL statement used for a DDL operation as such, so that operations like a DROP/ALTER/CREATE table can be easily tracked. In Oracle8i, only the internal operations to the data dictionary are recorded and it is difficult to track these operations (A DROP table results in several DML statements against the data dictionary). B.     By specifying the
DBMS_LOGMNR.DDL_DICT_TRACKING option when starting LogMiner, the LogMiner internal dictionary is updated if a DDL event is found in the redo log files. This allows the SQL_REDO in V$LOGMNR_CONTENTS to accurately display information for objects that are modified by user DML statements after LogMiner dictionary is built.     This option is not valid with the DICT_FROM_ONLINE_CATALOG
1.  Build the
SQL> execute DBMS_LOGMNR_D.BUILD ('dictionary.ora', '/database/9i/logminer');                2.  Alter the table to add a column
SQL> alter table test add(c4
3.  Add the log which contains the ALTER statement:              
SQL> execute DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/database/9i/arch/1_683.dbf', options =>;               
4. Start the log analysis:       =>
option:          SQL>
execute DBMS_LOGMNR.START_LOGMNR(dictfilename => '/database/9i/logminer/dictionary.ora');

contains:          insert into
"SCOTT"."TEST"("COL 1","COL 2","COL 3","COL 4") values(HEXTORAW('c102'), HEXTORAW('c103'), HEXTORAW('c104'),
HEXTORAW('c105'));      => With the
option:         SQL> execute
DBMS_LOGMNR.START_LOGMNR(dictfilename => '/database/9i/logminer/dictionary.ora', options =>dbms_logmnr.ddl_dict_tracking);         V$LOGMNR_CONTENTS.SQL_REDO
contains:         insert into
"SCOTT"."TEST"("C1","C2","C3","C4") values ('1','2','3','4');    Note: You must be sure that you have included the log which contains the DDL statement in the logs to be analyzed with
DBMS_LOGMNR.ADD_LOGFILE. ---------------------------------------------------------------------------------------------------------------------Skipping 
past redo log corruption
 There is an option that can be called when executing the
DBMS_LOGMNR.START_LOGMNR procedure that will let you skip over log corruption, so when would this be useful?  Ok you’re in a recovery situation and you’ve hit a corrupted redo log. 

In most cases you’re outta luck, you can’t get past it, oracle won’t let you.  But you need the data that was committed to the database since then(hopefully its not like week worth of redo).  Calling the procedure like this:

SQL> execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.skip_corruption);  This will work with a caveat, as long as the corruption is NOT in the header of the redo log, you will be fine.  If the corruption is in the header you’re still out of luck. Skip uncommited transactions
 You can now tell logminer "I only want to see committed
transactions".  They will return in the v$logmnr_contents view in commit order.  Call it like this:
 SQL> execute DBMS_LOGMNR.START_LOGMNR(options => ->
Chained/Migrated rows
 DML that is performed on chained/migrated rows will now be shown
correctly in the v$logmnr_contents view.  In the past in 8i, it only showed as "internal operation".
Tables Same here as for chained/migrated rows, logminer now supported clustered tables.
Path Insert Logging: Direct path inserts are now logged as insert statements and the operation column is set to "DIRECT INSERT"
Logging: Oracle9i has the ability to log columns in the redo which are not actually changed as part of the DML statements.

This is useful for maintaining copies of tables on other databases.  Prior to 9i, LogMiner only returned the columns which were changed and identified the row with a WHERE clause with a ROWID. But, ROWIDs are not portable to other databases so it was not possible to extract SQL using LogMiner which could be used on other databases.  There are two types of supplemental logging: database and table.  *** Database supplemental logging  ***  Database supplemental logging allows you to specify logging of primary keys, unique indexes or both. With this enabled, whenever a DML is performed, the columns involved in the primary key or unique index are always logged even if they were not involved in the DML.  To turn on database-wide supplemental logging for both primary keys and unique indexes, execute the following:     SQL> ALTER DATABASE ADD
SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;  This only takes effect for statements which have not yet been parsed. It also invalidates all DML cursors in the cursor cache and therefore has an effect on performance until the cache is repopulated.  1. The EMP table has a primary key defined on the EMPID column.    If supplemental logging is turned on for primary key columns, then any update to EMP logs the EMPID
column.      SQL> select * from 


10     100000      SQL>
update emp set sal=150000;     1 row
updated.  Without supplemental logging, V$LOGMNR_CONTENTS.sql_redo contains:   update "SCOTT"."EMP" set "SAL" = '150000' where "SAL" = '100000' and ROWID ='AAABOaAABAAANZ/AAA';  But, with the supplemental logging as done above, V$LOGMNR_CONTENTS.sql_redo
contains:    update "SCOTT"."EMP" set "SAL" = '150000' where "EMPID" = '10' and "SAL" ='100000' and ROWID = 'AAABOaAABAAANZ/AAA'; 
  2. To turn off the supplemental logging, execute the
following:   SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;  *** Table-level supplemental logging ***  Table-level supplemental logging allows users to define log groups and specify which columns are always logged in the redo stream. It is done on a table-by-table basis.  The ALWAYS keyword is used to indicate that if a row is updated, all columns in the group are logged. If ALWAYS is not used, the columns in the log group are logged when at least one of the columns in the group is updated.  You can define a log group using either CREATE TABLE or ALTER TABLE statement.  1. Create a table with a log group :      SQL> CREATE TABLE test_log(c1 number, c2 number, c3 number, SUPPLEMENTAL LOG GROUP test_always(c1,c2) always);      SQL> select * from
C2         C3 ---------- 

---------- ----------         
2          3  2.
Update a column that does not belong to the log group :      SQL> update test_log set
c3=99;     1 row updated.  Without
supplemental logging, v$logmnr_contents.sql_redo contains:   update "SCOTT"."TEST_LOG" set "C3" = '99' where "C3" = '3' and ROWID ='AAABOZAABAAANZ6AAA';  With the log group test_always defined above, V$LOGMNR_CONTENTS.sql_redo contains:   update "SCOTT"."TEST_LOG" set "C3" = '99' where "C1" = '1' and "C2" = '2' and "C3" = '3' and ROWID = 'AAABOZAABAAANZ6AAA';  3. Define another log group:      SQL> alter table test_log add supplemental log group test_sometimes(c1,c2);  4.1 Update a column that belongs to the log group:      SQL> update test_log set c2=10;     1 row updated.      V$LOGMNR_CONTENTS.sql_redo contains:      update "SCOTT"."TEST_LOG" set "C2" = '10' where "C1" = '1' and "C2" = '2' and ROWID = 'AAABLtAABAAANYgAAA';  4.2  Update a column that does not belong to the log group:      => no information is logged.   5. There are 2 sets of views for log groups:     ALL_/USER_/DBA_LOG_GROUPS - log group definitions on tables    ALL_/USER_/DBA_LOG_GROUP_COLUMNS - columns that are specified in a log group  6. To drop a log group, issue an ALTER TABLE :      
test_always;  ---------------------------------------------------------------------------------------------------------------------Mine_value 
function/Column_present function
  A. DBMS_LOGMNR.MINE_VALUE returns the specified value of
the first parameter   (either redo_value or undo_value) for the column name specified in the second parameter.    Example:    To return update statements executed against SCOTT.EMP which changed SAL to more than twice its original value, the following SQL could be used:      SQL> SELECT
sql_redo        FROM
v$logmnr_contents        WHERE
dbms_logmnr.mine_value(redo_value,'SCOTT.EMP.SAL') >

2*dbms_logmnr.mine_value(undo_value,'SCOTT.EMP.SAL')         AND operation='UPDATE';  B. DBMS_LOGMNR.COLUMN_PRESENT returns 1 if the specified column is contained in a redo record and 0 if it is not.     Note: COLUMN_PRESENT returns a 1 not only if the column was involved in a DML, but also if the column is logged as a result of being part of an identification key or a logging group.  C. MINE_VALUE can return a NULL in two scenarios:     1. The redo record contains the column and its value is actually NULL.    2. The redo record does not contain the column.     So, COLUMN_PRESENT can be used to supplement the MINE_VALUE function to identify NULLs which are the result of a DML which changed the column to NULL vs. a NULL indicating a column was not present in the redo log.     Example:      To select redo values for the SAL column and filter out the case where SAL is not present in the redo, the following SQL could be used:      SQL>
dbms_logmnr.mine_value(redo_value,'SCOTT.EMP.SAL')      2  FROM v$logmnr_contents       3 
WHERE dbms_logmnr.mine_value(redo_value,'SCOTT.EMP.SAL') is NOT NULL       4  OR  
(dbms_logmnr.mine_value(redo_value,'SCOTT.EMP.SAL') is NULL       5  AND  
dbms_logmnr.column_present(redo_value,'SCOTT.EMP.SAL')=1);    These functions do not support LONG, LOB, ADT or COLLECTION data
types.  ---------------------------------------------------------------------------------------------------------------------


GUI:  Just briefly as this has been a long one to write(and read for you all):
   The logminer viewer is part of Oracle Enterprise
Manager(OEM),  it give you GUI type people(you know who you are), the capability to do the pretty boxes, etc.
  You will need to login as sysdba for logminer viewer to work.
  The box pops up and shows 3 tabs:

Criteria:  (graphical and textual modes) this is where you choose what columns you want to query on and the value you’re searching for, the date range of when you want to mine and options to save the query to reuse at a later time, feel free to leave them blank to see everything in the logs.
            Redo Log

Files:  This is where you choose which logs you want to mine(archived and redo logs), this is also the place where you decide what kind of dictionary to use, the default is the online catalog. 

options: Here you choose which columns you want to display while mining.
 Once you have all the options set, hit the "Find Now" button and
logminer is off and running.

This covers all that I’ll talk about on 9i features for logminer, any further info, feel free to ask me offline at

Received on Tue Oct 30 2001 - 12:28:33 CST

Original text of this message