dba:oracle:howtos:logminer_mini-howto
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revision | Last revisionBoth sides next revision | ||
| dba:oracle:logminer_mini-howto [2022/02/11 11:12] – ↷ Page moved from oracle:logminer_mini-howto to dba:oracle:logminer_mini-howto dodger | dba:oracle:howtos:logminer_mini-howto [2023/01/31 08:26] – removed - external edit (Unknown date) 127.0.0.1 | ||
|---|---|---|---|
| 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 | ||
| - | < | ||
| - | col MEMBER format a60; | ||
| - | col TYPE FORMAT A8 | ||
| - | COL STATUS FORMAT A8 | ||
| - | SELECT * FROM v$logfile ; | ||
| - | </ | ||
| - | Or archived redo' | ||
| - | < | ||
| - | SELECT NAME FROM GV$ARCHIVED_LOG | ||
| - | WHERE NAME IS NOT NULL | ||
| - | AND NAME LIKE ' | ||
| - | </ | ||
| - | <code sql> | ||
| - | COL NAME FORMAT A150 ; | ||
| - | SELECT RECID, | ||
| - | STAMP, | ||
| - | NAME, | ||
| - | TO_CHAR(FIRST_TIME, | ||
| - | TO_CHAR(COMPLETION_TIME, | ||
| - | FROM V$ARCHIVED_LOG WHERE NAME LIKE ' | ||
| - | </ | ||
| - | |||
| - | The date is an example :-P | ||
| - | |||
| - | |||
| - | For example: | ||
| - | < | ||
| - | 1* SELECT * FROM v$logfile | ||
| - | |||
| - | GROUP# STATUS | ||
| - | ------------ -------- -------- ------------------------------------------------------------ --------- | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | | ||
| - | </ | ||
| - | |||
| - | This will help you: | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | ' | ||
| - | from v$logfile; | ||
| - | </ | ||
| - | and: | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | ' | ||
| - | from GV$ARCHIVED_LOG WHERE NAME LIKE ' | ||
| - | </ | ||
| - | |||
| - | 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' | ||
| - | < | ||
| - | EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - | ||
| - | | ||
| - | | ||
| - | </ | ||
| - | |||
| - | And the rest with the ADDFILE procedure: | ||
| - | < | ||
| - | EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - | ||
| - | LogFileName => ' | ||
| - | 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, | ||
| - | FROM V$LOGMNR_CONTENTS | ||
| - | where | ||
| - | SEG_NAME LIKE ' | ||
| - | AND to_char(TIMESTAMP,' | ||
| - | AND ROWNUM <50 | ||
| - | ; | ||
| - | </ | ||
| - | Add the " | ||
| - | |||
| - | |||
| - | |||
| - | Finish the LogMiner process when done: | ||
| - | < | ||
| - | EXECUTE DBMS_LOGMNR.END_LOGMNR(); | ||
| - | </ | ||
| - | ====== Links ====== | ||
| - | * [[http:// | ||
| - | * [[http:// | ||
| - | * [[http:// | ||
dba/oracle/howtos/logminer_mini-howto.txt · Last modified: 2023/01/31 08:26 by dodger