====== [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 ======
* [[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]]