3

in the last few days our Oracle DB has been generating a large amount of archive log files. How would I find out what DML activity has been causing such a spike in log file generation?

Lloyd
  • 133
  • 1
  • 4

1 Answers1

1

First check the addm reports and see what sql did the most block changes. Most likely these are the ones you are looking for. An other reason could be that your database setup changed to force_logging and that your load processes now started logging as a result of this change. If you have no diagnostics pack license, you could use logminer to read the archives and generate the undo transactions for what is in the log. This also gives lots of info. You might want to check the archive log history first to find where you find a changed pattern.

  • Do you have a good resource for using logminer? –  Apr 11 '11 at 12:20
  • http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_logmnr.htm#i79049 Start a new logminer session, add the files you want and query the V$LOGMNR_CONTENTS view to actually read the contents. Keep an eye on the alertlog, usefull debugging info appears here. –  Apr 11 '11 at 14:14
  • Logminer was the trick. That was non-trivial, but powerful. – Lloyd Apr 13 '11 at 16:31
  • Yes, it is a good tool, not entirely intuitive ..... –  Apr 15 '11 at 06:25