Using Log Miner Utility

Using LogMiner utility, you can query the contents of online redo log files and archived log files. And that’s not all, if you’ve the knack for it, you might as well use it as a data audit tool and also as a tool for sophisticated data analysis(Nice, isn’t it!)

Now lets concentrate on the very basics. Suppose you’ve a primary database which is really troubling you by generating a lot of archives. And you need to find out which are the culprit tables/objects. The safest way to use LogMiner is to use it on the standby(assuming that you have one!)

Follow the simple steps below.

1. Logon to the standby
2. Open the database in read-only mode

   SQL> ALTER DATABASE OPEN; 

[Since it’s a standby, this command would open it in Read-Only]
3. On the sql prompt

   SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/mount1/archive/arch1.arc',OPTIONS => DBMS_LOGMNR.NEW);

This statement tells the Log Miner that ‘I want to know what’s inside arch1.arc’

   SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/mount1/archive/arch2.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);

‘And this is one more that I want to know about’ [Notice the difference in OPTIONS]

4. Start the Log Miner

   SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR

5. The Log Miner dumps all the info gathered into a view called V$LOGMNR_CONTENTS. The most useful columns of this view are

   • OPERATION [VARCHAR2(32)] - User level SQL operation that made the change: 
          o INSERT - Change was caused by an insert statement
          o UPDATE - Change was caused by an update statement
          o DELETE - Change was caused by a delete statement
          o DDL - Change was caused by a DDL statement
          o START - Change was caused by the start of a transaction
          o COMMIT - Change was caused by the commit of a transaction
          o ROLLBACK - Change was caused by a full rollback of a transaction
          o LOB_WRITE - Change was caused by an invocation of DBMS_LOB.WRITE
          o LOB_TRIM - Change was caused by an invocation of DBMS_LOB.TRIM
          o LOB_ERASE - Change was caused by an invocation of DBMS_LOB.ERASE
          o SELECT_FOR_UPDATE - Operation was a SELECT FOR UPDATE statement
          o SEL_LOB_LOCATOR - Operation was a SELECT statement that returned a LOB locator
          o MISSING_SCN - LogMiner encountered a gap in the redo records.
          o INTERNAL - Change was caused by internal operations initiated by the database
          o XML DOC BEGIN - Beginning of a change to an XMLType column or table
          o XML DOC WRITE - Data for an XML document
          o XML DOC END - End of the Data for an XML document
          o UNSUPPORTED - Change was caused by operations not currently supported by LogMiner 

   • OPERATION_CODE [NUMBER] -  Number of the operation code: 
          o 0 - INTERNAL
          o 1 - INSERT
          o 2 - DELETE
          o 3 - UPDATE
          o 5 - DDL
          o 6 - START
          o 7 - COMMIT
          o 9 - SELECT_LOB_LOCATOR
          o 10 - LOB_WRITE
          o 11 - LOB_TRIM
          o 25 - SELECT_FOR_UPDATE
          o 28 - LOB_ERASE
          o 34 - MISSING_SCN
          o 68 - XML DOC BEGIN
          o 70 = XML DOC WRITE
          o 71 = XML DOC END
          o 36 - ROLLBACK
          o 255 - UNSUPPORTED

   • ROLLBACK [NUMBER] - 1 = if the redo record was generated because of a partial or a full rollback of the 
associated transaction, 0 = otherwise
   • TABLE_NAME [VARCHAR2(32)] - Name of the modified table (in case the redo pertains to a table modification)
   • SEG_NAME [VARCHAR2(256)] - Name of the modified data segment
   • SEG_TYPE [NUMBER] - Type of the modified data segment: 
          o 0 - UNKNOWN
          o 1 - INDEX
          o 2 - TABLE
          o 19 - TABLE PARTITION
          o 20 - INDEX PARTITION
          o 34 - TABLE SUBPARTITION
          o All other values – UNSUPPORTED

   • SEG_TYPE_NAME [VARCHAR2(32)] - Segment type name: 
          o UNKNOWN
          o INDEX
          o TABLE
          o TABLE PARTITION
          o INDEX PARTITION
          o TABLE SUBPARTITION
          o UNSUPPORTED

   • ROW_ID [VARCHAR2(18)] - Row ID of the row modified by the change (only meaningful if the change 
pertains to a DML). 
This will be NULL if the redo record is not associated with a DML. 
   • USERNAME [VARCHAR2(30)] - Name of the user who executed the transaction 
   • OS_USERNAME [VARCHAR2(4000)] - Name of the operating system user
   • SEQUENCE# [NUMBER] -  Sequence number of the redo log that contained the redo record corresponding 
to the database change
   • SQL_REDO [VARCHAR2(4000)] - Reconstructed SQL statement that is equivalent to 
the original SQL statement that made the change. LogMiner does not generate SQL redo 
for temporary tables. In such a case, this column will contain the string "/* No 
SQL_REDO for temporary tables */".
   • SQL_UNDO [VARCHAR2(4000)] - Reconstructed SQL statement that can be used to 
undo the effect of the original statement that made the change. DDL statements have 
no corresponding SQL_UNDO. LogMiner does not generate SQL undo for temporary tables. 
In such a case, this column will contain the string "/* No SQL_UNDO for temporary 
tables */".

6. End the Log Miner session after your queries are answered
SQL> EXECUTE DBMS_LOGMNR.STOP_LOGMNR

7. Remember that the view would exist only for the current session and not any subsequent sessions. So you have to start the LogMiner each time you wish to query the view.