dba:oracle:howtos:logminer_mini-howto
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
dba:oracle:howtos:logminer_mini-howto [2023/01/31 08:26] – removed - external edit (Unknown date) 127.0.0.1 | dba: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 | ||
+ | < | ||
+ | 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:// |