Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:howtos:logminer_mini-howto

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:howtos:logminer_mini-howto [2023/01/31 08:26] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:howtos:logminer_mini-howto [2023/01/31 08:26] (current) – ↷ Page moved from dba:oracle:logminer_mini-howto to dba:oracle:howtos:logminer_mini-howto dodger
Line 1: Line 1:
 +====== [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]]