cayenne wrote:
Hello all,

Just curious, what do ya'll do (particularly on unix/linux system) to manage your logs? Do you have scripts that periodically move your alert logs somewhere or just delete them periodically? What about trc files generated...other log files (say from opmn, agents...etc).

Just curious on what most people do to manage them...do you copy them via a cron job periodically? Do you have something monitoring the log directories to look for when space might start getting full...something that emails or alerts you?

Do you have any links to sites that might have good shell scripts out there for Oracle database maintenance in general to learn from?

Thanks in advance,

cayenne

I just have a shell script, sheduled via cron, that deletes all trace files over 'x' days old, and rotates the alert logs - alertSID.log.9 is deleted, alertSID.log.8 becomes .9, etc. down to the current alertSID.log becomes alertSID.log.1. There are also some heavily buried logs associated with the dbcontrol that have to be managed. It's not terribly difficult shell scripting.

I like to rename the file every night and use a simple routine with UTL_FILE to load the previous day's contents into an internal heap table with each line numbered in sequence. The nightly backup thus captures the log and I have it for audit purposes as well as reference.

This is easy to automate with DBMS_SCHEDULER and UTL_FILE.

The following command can be used to delete trace files greater than 30 days.

/bin/find /oracle/product/admin -name \"*.trc\" -mtime +30 -exec rm -f {} \;



Also, here is a scipt that I use to rename my alert logs daily. Currently, I keep 1 year worth of alerts logs - which is controlled by the variable LOG_KEEP_DAYS in the script. The script is run from cron everynight at 23:59 using the following cron entry:

###Refresh the alert_logs every day...so that we only have one day
###of alert info to research in case there is a problem...don't need
###to wad through days or months of alert info in just one log file.
59 23 * * * /oracle/product/local/scripts/save_alert_log.sh > /dev/null 2>&1


In 11g, Oracle has made some changes to how the alert log data is stored.

#!/usr/bin/ksh

############################################################################
##  Program :   save_alert_log.sh                                          #
##                                                                         #
##  Purpose :   The alert logs on many Oracle databases can grow to a very #
##              large size over time.  This can often impede the maintenace#
##              of the system - because the DBA will need to sometimes scan#
##              through many days or months of data when researching an    #
##              issue.  This script tries to avoid that by ensuring that   # 
##              the log file can be "refreshed" on a daily basis, meaning  # 
##              that only the current day's data will be kept in the log,  #
##              while the previous day's data will be saved to another file#
##              in a backup area.                                          #
##                                                                         #
##              This script should be run from Oracle's crontab at midnight#
##              every night, so that the database will always have a new   #
##              alert log file each day.  An example crontab entry could be#
##              0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
##                                                                         #
##  Date    :   19 May 2006.                                               #
##  Author  :   Basil S. Mullings                                          #
############################################################################
##  Modified:                                                              #
##                                                                         #
##                                                                         #
#  Modification History:                                                   #
#  DATE       WHO      DESC                                                #
#  --------   -----    ----------------------------------------------------#
#  05/29/06   Basil    Add an extra variable LOG_KEEP_DAYS to hold the     #
#                      number of days that the log files should be kept on #
##                     the server before being deleted.                    #
##                                                                         #
##                                                                         #
############################################################################

  ##Setup some needed variables.
BKUP=bkup   ##The backup directory to store the logs...
ORATAB="/etc/oratab"
LOG_KEEP_DAYS=365   ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog   ##Just a temp scratch work area.
SQLUSER="/ as sysdba"
GEN_ORA_ERROR="ORA\-[0-9][0-9]*"
PATH="$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:."
export PATH

  ## Now, parse the oratab file for all databases on the system.
  ## Then use the ORACLE_SID that is found in the oratab file
  ## to log onto that database, and retrieve the directory where
  ## the alter log file is stored (.ie. retrieve the path to the
  ## bdump directory.
  ##
#for sidEntry in `cat $ORATAB | grep -v "^#"`
for sidEntry in `cat $ORATAB | awk -F: '{print $1}' | grep -v "^#"`
do
       ## Get date and time
    CURR_DATE=`date '+%a_%m%d%H%M'`    ##Example Fri_05191256   for Friday May 19th @1256 PM.

    #ORACLE_SID=`echo  $sidEntry | cut -f 1 -d :`
    ORACLE_SID=$sidEntry
    echo "Oracle Sid is $ORACLE_SID"
                                                                                          
    export ORACLE_SID                                                                     
       ## Set the Oracle environment for this SID.                                        
    ORAENV_ASK=NO                                                                       
    . /usr/local/bin/oraenv                                                               
    rm -f $TMPFILE > /dev/null 2>&1
  
       ##Now, let's log onto the DB, and try to 
       ##retrieve the bdump directory path.
    sqlplus -s /nolog << EOF > $TMPFILE
    connect $SQLUSER
    set heading off;
    set echo off;
    set feedback off;

    select 'BACKGROUND_DUMP_DEST=' ||value
    from   v\$parameter
    where  name='background_dump_dest';
    exit;
EOF

       ##Ok, we had a problem talking to the database.
    if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
    then
         echo "ERROR: Unable to find the path to the alert log for DB $ORACLE_SID"
         rm -f $TMPFILE > /dev/null 2>&1

    else  ##Ok, we can log into the DB, now let's go find our bdump directory.
        
         bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F "=" '{print $2}'`
         #echo "BDUMP is $bdump"
         bkupDir=$bdump/$BKUP

            ##Make sure our backup directory exists.
         if [ ! -d $bkupDir ]
         then
               mkdir $bkupDir  > /dev/null 2>&1
         fi

           ##Now, move the alert log.                                                   
         #echo "now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE"
         mv $bdump/alert_${ORACLE_SID}.log  $bkupDir/alert_${ORACLE_SID}.$CURR_DATE
         
             #Procedure to shrink the log to 365 days
             ##Keep only the last 365 days worth of logs...delete all logs older than 365 days.
         #echo "Now shrinking the logs in dir $bkupDir ..."
         find $bkupDir  -name "*.*" -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
    fi

done