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