Skip to content


Oracle Log and Trace File Cleanup

UPDATE: Several script bugs brought to my attention by a comment posted below have been fixed. The script should now be compatible with Linux and Solaris. Please let me know if any additional bugs are identified.

Every running Oracle installation has several directories and files that need to be rotated and/or purged. Surprisingly, or not, Oracle has not included this basic maintenance in their software. I have come across the oraclean utility in the past, but the script does not do everything I need.

To achieve what I required, I recently hacked together a single script that does the following things:

  • Cleans audit_dump_dest.
  • Cleans background_dump_dest.
  • Cleans core_dump_dest.
  • Cleans user_dump_dest.
  • Cleans Oracle Clusterware log files.
  • Rotates and purges alert log files.
  • Rotates and purges listener log files.

The script has been tested on Solaris 9 and 10 with Oracle database versions 9i and 10g. It has also been tested with Oracle Clusterware and ASM 11g. The script can be scheduled on each server having one or more Oracle homes installed, and it will clean all of them up using the retention policy specified. The limitation is that log file retention is specified per server, not per instance. However, I find that placing a single crontab entry on each database server is easier than setting up separate log purge processes for each one.

The script finds all unique Oracle Homes listed in the oratab file and retrieves the list of running Oracle instances and listeners. Once the script knows that information, it rotates and cleans the trace, dump, and log files.

Download: cleanhouse.sh

Usage: cleanhouse.sh -d DAYS [-a DAYS] [-b DAYS] [-c DAYS] [-n DAYS] [-r DAYS] [-u DAYS] [-t] [-h]
   -d = Mandatory default number of days to keep log files that are not explicitly passed as parameters.
   -a = Optional number of days to keep audit logs.
   -b = Optional number of days to keep background dumps.
   -c = Optional number of days to keep core dumps.
   -n = Optional number of days to keep network log files.
   -r = Optional number of days to keep clusterware log files.
   -u = Optional number of days to keep user dumps.
   -h = Optional help mode.
   -t = Optional test mode. Does not delete any files.

Posted in Database, Oracle.

Tagged with , , , , , , , , , .


23 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Chris says

    Hi Shad,

    thanks a lot for this little gem, this is exactly what I was looking for. How can Oracle ship a product without something equivalent is beyond me. I heard as a joke that the overall goal was to keep the DBAs busy and clear of risks of unemployment, but was it really a joke ?

    I would suggest a few modifications on the current version of the script though. Some of the errors I got are probably due to discrepancies between Solaris and Linux bash.

    1 – the command find $DIR/*.trc might fail in case there are a large number of files to clean, because the *.trc is processed by the shell and can end up in a ‘argument list too long’ error. The more correct syntax is

    find $DIR -name ‘*.trc’

    2 – The ‘ps -ef -o args’ bombs out on my Redhat distrib with a ‘bad syntax’ message (the -f “full” format clause conflicts with the -o args), I had to replace it by :

    ps h -C oracle -o args | grep pmon
    ps h -C tnslsnr -o args

    which looks more correct and avoid to ‘grep -v ‘ etc.

    3 – Also I had to add double quotes on many lines as such
    if [ -z "$1" ]
    to avoid some ‘too many arguments’ errors when $1 contains spaces.

    4 – I also to add spaces ro the expression members :
    x = `expr $x + 1`
    which could also be written
    x = $(( x + 1))

    5 – However you might want to entirely replace the f_getuniq function by something more concise like

    echo “$1″ | awk -F’ ‘ ‘{ for (i=1; i<=NF; i++) print $i | “sort|uniq” }’

    Chris

  2. ury says

    Hi Shad,
    Thanks a lot for this little gem. I runs this script in HP UX.
    1) bash-3.2$ ps -e -o args
    ps: illegal option — o
    The program /usr/ucb/ps in my operation system is not present.
    I quickly modified program to
    # Get the list of running databases.
    SIDS=`ps -e -f args | grep pmon | grep -v perl | perl -ne’{print qq[$1\n] if /ora_pmon_(\S+)/}’ | sort`
    and
    # Get the list of running listeners.
    ps -ef | grep tnslsnr | grep -v grep | perl -ne’split; print “@_[8..@_]“‘ | while read LSNR; do

    2) I have SID oracle database with symbol “_”. List name running database after “awk -F_ ‘{print $3}’” was make incorrect.

  3. Stream says

    Hi Shad,
    Thanks
    I have Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
    Linux on Itanium –> RHEL 5.2 IA64
    1)audit_dump_dest — such parameter to initialization no in Oracle
    ADUMPDEST=`f_getparameter audit_file_dest`
    2)Get the listener version on IA64
    has changed {print $5} on {print $6}

  4. Naveed says

    Hi Chris,

    Can you please modify the script with your recommendations and send it to me via email. I am using Oracle 10g on Solaris 5.10. I am not at all good in shell scripting but thats a lot to Shad for his great effort. It is great help if you can send me the script via email.

    naveed27c@yahoo.com

    Thanks
    -Naveed

  5. Samar says

    Hi Shad,

    Script is working fine for ORACLE_HOME, ASM_HOME but is not working for CRS_ORACLE_HOME. Instead it is locating the CRS logs from RDBMS ORACLE_HOME. Please advise if I need to set the CRS_ORACLE_HOME any where.

    Thanks
    Samar

  6. Miz Lola says

    LUV IT!!! THANK YOU! THANK YOU! THANK YOU!

  7. Anand says

    Hi,
    Please send me the oracle files purging script as well to atatikay@gmail.com
    Thanks,
    Anand

  8. Bhaskar Reddy says

    The script is awsome. It would be very good if we add logs of AGENT as well. We need to one more location i.e., AGENT_HOME/sysman/log.

    Reagrds
    Bhaskar Reddy

  9. Ed says

    Hi,

    Any chance of having a Perl version of this script?

  10. Jay says

    very nice script.
    what unix user privileges are required for the cleanup os user. Due to default oracle umask of 022 we get permission denied errors. then we also get the following error:
    TNS-01190: The user is not authorized to execute the requested listener command

    • Shad says

      Hi Jay,

      I believe the script available for download was written for systems running all Oracle instances under a single user. I do have a version that accounts for different “oracle” users, and I’ll try to upload it sometime this week.

      Thanks,
      Shad

      • Mariela says

        Shad, do yo still have cleanhouse.sh version for an installation with different oracle users? Can you upload it? Thanks!!

  11. Sarju Patel says

    In Oracle11g Rel1 onward, oracle interally manges this using ADRCI..Infact there is no parameter called background/user/core_dump_dest..Instead it has combined all these together into a new parameter called diagnostic_dest..which is set to ORACLE_BASE by default.

  12. Ad Smeulders says

    Hi Shad,

    Downloaded your script some days ago and it runs like a charm on HP-UX 11.31 with oracle 11gR2.. Did need to make some changes however to get it running. Had to replace the part ” ps -e -o args ” into ” ps -ef | awk ‘{print substr($0,49,length($0))}’ “.
    Here are the changes in your code:

    230 # Get the list of running databases.
    231 #SIDS=`ps -e -o args | grep pmon | grep -v grep | awk -F_ ‘{print $3}’ | sort`
    232 #SIDS=`ps -ef | grep pmon | grep -v grep | awk -F_ ‘{print $3}’ | sort`
    233 SIDS=`ps -ef | awk ‘{print substr($0,49,length($0))}’ | grep pmon | grep -v grep | awk -F_ ‘{print $3}’ | sort`

    378 # Clean Listener Log Files.
    379 # Get the list of running listeners. It is assumed that if the listener is not running, the log file does not need to be cut.
    380 #ps -e -o args | grep tnslsnr | grep -v grep | while read LSNR; do
    381 #ps -ef | grep tnslsnr | grep -v grep | while read LSNR; do
    382 ps -ef | awk ‘{print substr($0,49,length($0))}’ | grep tnslsnr | grep -v grep | while read LSNR; do

    Thanks for your excellent work on the base script.

  13. Roland says

    Very nice script. Should take into account different os users for different databases. Please add truncating the dataguard log (default: drc.log in Background Dump Destination )

  14. sai says

    its nice script how to run for particular some Databases only on a UNIX box, i mean not all DB’s

  15. Mary Shea says

    Thank you – beautiful script

  16. Sachin says

    Worked like charm on our test installation of oracle 11gR2 on Red hat 5.
    Thanks

  17. Gavin says

    Thanks, you made my day. Great to have you blokes around that share your knowledge and expertise with others

  18. Roland says

    Hi,
    Any chance to update this script to clean the diag destination of oracle 11 DBs ? Currently the script only cleans the logs of V11-listeners … :-(

  19. sathya says

    Hi,

    First, thanks a lot for this little gem. It works like a charm!

    However, there is a small nitpick with the way you are grepping the oracle sid from the pmon process.

    # Get the list of running databases.
    SIDS=`ps -e -o args | grep pmon | grep -v grep | awk -F_ ‘{print $3}’ | sort`

    If there is a underscore part of the oracle sid as below;

    $ ps -e -o args | grep pmon | grep -v grep
    ora_pmon_emrep_dr

    Then your script will pick only the part of the oracle sid not the full one, which will eventually invalidate all the subsequent steps for that database.

    $ ps -e -o args | grep pmon | grep -v grep | awk -F_ ‘{print $3}’ | sort
    emrep

    So, i replace that line with the below one, which will get the full SID;

    $ ps -e -o args | grep pmon | grep -v grep | cut -c 10- | sort
    emrep_dr

    Thanks,
    sathya

  20. Ryan Wendel says

    This is badass. Thank you very much for putting it together. I ran through the code making sure everything was up to my standards and was surprised to see it meet and surpass them. WELL DONE!!!!

  21. Varun says

    Hi Shad,

    We encountered one major issue on prod due to outgrown logs. Can you please provide this script for AIX . You can exclude RAC part of cleanup as we use single instances only.
    Thanks ..
    Varun



Some HTML is OK

or, reply to this post via trackback.