Archive Logs Filling Up - Find The Culprit

If you suddenly find that your archive logs directory is filling up with archives being generated every minute or so, you may be able to find the culprit document or user ID or process, depending on what fields are being changed, by scanning the latest archivelog file for the strings that are being included most often in the changes being written to that archive log.

The following unix command line example extracts the strings from an archivelog file, sorts the resulting string lines, counts the unique strings, finds the largest occurrence numbers (there is a blank after the first "0-9"), and sorts those occurrences by the string counts (the "ls" line shows the latest archivelog file at the end of it's listing in the "cd" line's archive log directory).  You can then look through the resulting sorted counts and strings to see if there is any indication as to what is causing the problem that is filling up your logs.  (Substitute your archive log directory and resulting latest archivelog file name that you want to check.)

cd /u01/oradata/PROD/archivelogs
ls -ltr
strings arch_PROD_00072.arc | sort | uniq -c | grep '^[0-9 ][0-9]' | sort -n

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.