Table of Contents

[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 

col MEMBER format a60;
col TYPE FORMAT A8
COL STATUS FORMAT A8
SELECT * FROM v$logfile ;

Or archived redo's: 

SELECT NAME FROM GV$ARCHIVED_LOG 
  WHERE NAME IS NOT NULL
  AND NAME LIKE '%2013_01_18%';
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 ;

The date is an example :-P

For example: 

  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

This will help you: 

SELECT 
'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LogFileName => ''' || MEMBER || ''', Options => dbms_logmnr.ADDFILE ) ;'
FROM  v$logfile;

and: 

SELECT 
'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LogFileName => ''' || NAME || ''', Options => dbms_logmnr.ADDFILE ) ;' 
FROM  GV$ARCHIVED_LOG WHERE NAME LIKE '%2013%';

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: 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LogFileName => '+DG_VXLPRE_DATA/ciberterminalpre/onlinelog/group_8.313.785158951', -
   Options => dbms_logmnr.NEW);

And the rest with the ADDFILE procedure: 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
  LogFileName => '+DG_VXLPRE_FRA/ciberterminalpre/onlinelog/group_8.7459.785158955', -
  Options => dbms_logmnr.ADDFILE ) ;

And so on…

Starting the process: 

EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Now you can query the V$LOGMNR_CONTENTS table 

SELECT * FROM V$LOGMNR_CONTENTS where rownum <10;

More complex query: 

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
 ;

Add the “SQL_REDO” field if you want to obtain the query :-)

Finish the LogMiner process when done: 

EXECUTE DBMS_LOGMNR.END_LOGMNR();

Links