====== [HOWTO] Use logminer to query REDOLOGS ======

====== Description ======
Little document on how to use LogMiner to query REDOLOGS.

====== Enabling LogMiner ======
To start the LogMiner process, We must tell LogMiner which are the REDO's to be analysed, so the best option is just query the Online REDO's
<code>
col MEMBER format a60;
col TYPE FORMAT A8
COL STATUS FORMAT A8
SELECT * FROM v$logfile ;
</code>
Or archived redo's:
<code>
SELECT NAME FROM GV$ARCHIVED_LOG 
  WHERE NAME IS NOT NULL
  AND NAME LIKE '%2013_01_18%';
</code>
<code sql>
COL NAME FORMAT A150 ;
SELECT RECID,
  STAMP,
  NAME, 
  TO_CHAR(FIRST_TIME, 'MM-DD-YYYY HH24:MI:SS') ,
  TO_CHAR(COMPLETION_TIME, 'MM-DD-YYYY HH24:MI:SS') 
 FROM V$ARCHIVED_LOG WHERE NAME LIKE '%2014%' ORDER BY 1,2 ;
</code>

The date is an example :-P


For example:
<code>
  1* SELECT * FROM v$logfile

      GROUP# STATUS   TYPE     MEMBER                                                       IS_RECOVE
------------ -------- -------- ------------------------------------------------------------ ---------
           8          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_8.313.785158951     NO
           8          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_8.7459.785158955     YES
           7          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_7.312.785158943     NO
           7          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_7.7446.785158947     YES
           6          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_6.311.785158933     NO
           6          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_6.7429.785158937     YES
           5          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_5.310.785158925     NO
           5          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_5.7444.785158929     YES
           4          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_4.309.785158915     NO
           4          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_4.7440.785158919     YES
           3          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_3.308.785158905     NO
           3          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_3.7478.785158911     YES
           2          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_2.307.785158895     NO
           2          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_2.7465.785158903     YES
           1          ONLINE   +DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_1.306.785158857     NO
           1          ONLINE   +DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_1.7474.785158885     YES
</code>

This will help you:
<code sql>
SELECT 
'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LogFileName => ''' || MEMBER || ''', Options => dbms_logmnr.ADDFILE ) ;'
from  v$logfile;
</code>
and:
<code sql>
SELECT 
'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LogFileName => ''' || NAME || ''', Options => dbms_logmnr.ADDFILE ) ;' 
from  GV$ARCHIVED_LOG WHERE NAME LIKE '%2013%';
</code>

You can ignore the FRA one's (or not, LogMiner will show you a nice ORA-01289 dupe log:-P):

So we begin adding REDO's, the 1st must use the NEW procedure from DBMS_LOGMNR:
<code>
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LogFileName => '+DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_8.313.785158951', -
   Options => dbms_logmnr.NEW);
</code>

And the rest with the ADDFILE procedure:
<code>
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
  LogFileName => '+DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_8.7459.785158955', -
  Options => dbms_logmnr.ADDFILE ) ;
</code>
And so on...


Starting the process:
<code>
EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
</code>

Now you can query the V$LOGMNR_CONTENTS table

<code>
SELECT * FROM V$LOGMNR_CONTENTS where rownum <10;
</code>

More complex query:
<code>
COL OPERATION FORMAT A15;
COL USERNAME FORMAT A15;
COL OS_USERNAME FORMAT A20;
COL MACHINE_NAME FORMAT A20;
COL SESSION_INFO FORMAT A15 WORD WRAPPED;
SELECT SCN, OPERATION, USERNAME, OS_USERNAME, MACHINE_NAME, SESSION_INFO, to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') MONGUITIME
FROM V$LOGMNR_CONTENTS 
where 
SEG_NAME LIKE 'IT_TBUFFER_QUEUE'
AND to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') LIKE '18-01-2013 11%'
AND ROWNUM <50
 ;
</code>
Add the "SQL_REDO" field if you want to obtain the query :-)



Finish the LogMiner process when done:
<code>
EXECUTE DBMS_LOGMNR.END_LOGMNR();
</code>
====== Links ======
  * [[http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_logmnr.htm|DBMS_LOGMNR page]]
  * [[http://docs.oracle.com/cd/B28359_01/server.111/b28319/logminer.htm#i1005553|LogMiner Page]]
  * [[http://www.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96612/d_logmn2.htm|best example]]