Logminer Basics - Or How to Pull Your Bacon out of the Fire

Tim Herring
Packaging Corp of America


How to setup and use Logminer

• select * from users where clue > 0;
• users may not know what they did
• logminer can help by reading from archive redo logs
• Undo also available in redo log
• Source Database -- where you got the logs
• Mining Database -- target database (can be same as source)
• Logminer dictionary
• can be flatfile
• dictionary can be extracted to redo
• requirements
• utl_file_dir
• archive log mode
• supplimental logging enabled
• same platofrm, blocksize,character set
• Build log dictionary
• dbms_logmnr_d.build
• refresh dicionary when necessary
• specify redologs
• dbms_logmnr.add_logfile()
• select * from v@logmnr_logs
• start analysis
• dbms_start_logmnr(dictionary,)
• select * from v$logmnr_contents

Searching Strategies

• select * from v$loghist on source
• gather information on logs for estimated time
• where sql_redo -- shows sql executed
• where sql_undo -- shows sql to rollback transaction

Appropriate use of LogMiner (or ways to save your bacon)

• transactions for things that can be 'undone'
• logical corruption occured and moved moved on. Point-In-Time not an option. 
• LogMiner as a sleuthing tool
• who, what, when
• ALL the information is in Redo log
• Mine Logs so you can move forward
• Resources limit used of other tools
• flashback, etc
• Audit Tool