As an Oracle RAC environment increases in size, complexity and importance the more important it is to achieve high levels of automation and standardization.  Increased levels of automation and standardization creates high reliability and allows production DBAs to focus on improvements in the infrastructure and proactive performance tuning. In this blog post, I am going to share with you how I deal with rotating various log files that have the tendency to grow and grow in the Oracle environment. I do not use “cp /dev/null” commands to the log files.

The following script will generate the scripts necessary to rotate all the database alert logs, ASM alert log, listener log and scan listener log(s) for the RAC node.

The script has a dependence on the SH environment variable script being set. The SH environment variable will simple be the location where you store all your shell scripts. This script will create a sub-directory called logrotate. In the logrotate directory, it will create 2 files for each database alert logs, ASM alert log, listener log and scan listener log(s). The first file is the logrotate state file. The second file is the actual log rotate directives. For the database instance and scan listener, this script will perform a “ps -ef” command and look for actively running occurrence of the scan listener and database instance.

In the very end of the script, we will generate the logrotate script for you to put into your weekly master cleanup script. We promote 2 sets of cleanup scripts: a daily cleanup script to handle things like audit log purges and a weekly cleanup scripts to address all the growing log file required by Oracle. We no longer have to deal with the Oracle cluster services log files as Oracle started to rotate the logs for us automatically starting in Oracle Database 11g Release 2.

function log_rotate {
export SQLP="sqlplus -s / as sysdba"
export SET="set pages 0 trims on lines 2000 echo off ver off head off feed off"


export ASM_RUNNING=$(ps -ef |grep -i asm_pmon |awk {'print $8'} |sed "s/asm_pmon_//g" |egrep -v "sed|grep")
[ "$ASM_RUNNING" != "" ] && ASM_INSTANCE=$(echo $ASM_RUNNING |sed '$s/.$//')

LISTENER_LOG=$ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log

function diag {
export DIAG_DEST=$(
echo "
$SET
select value from v\$diag_info where name='Diag Trace';" |$SQLP )
}

# -- Determine ASM Log
export ORACLE_SID=$ASM_RUNNING
export ORAENV_ASK=NO
. oraenv -s
export GRID_HOME=$ORACLE_HOME
diag;

ASM_LOG=$DIAG_DEST/alert_${ORACLE_SID}.log

ls -l  $ASM_LOG
ls -l  $LISTENER_LOG

function rotate {
export LOGFILE=$1
export CONFIG_FILE=$2
export PATH=$PATH:/usr/sbin
export CONF_DIR=$SH/logrotate
[ ! -d "$CONF_DIR" ] && ( echo $CONF_DIR does not exist .. issuing mkdir; mkdir -p $CONF_DIR )

export CONF=$CONF_DIR/$CONFIG_FILE

cat <<!! >$CONF
$LOGFILE {
weekly
copytruncate
rotate 2
compress
}
!!

echo logrotate -s $CONF_DIR/log_rotate_status.$CONFIG_FILE -f $CONF
}

for DATABASES in $(ps -ef |grep -i pmon |grep -v ASM |awk {'print $8'} |sed "s/ora_pmon_//g" |egrep -v "sed|grep")
do
  export DB=$(echo $DATABASES |sed '$s/.$//')
  export ORACLE_SID=$DATABASES
  export ORAENV_ASK=NO
  . oraenv -s
  diag;
  export DB_LOG=$DIAG_DEST/alert_${ORACLE_SID}.log
  ls -l $DB_LOG

  rotate $DB_LOG $DATABASES
done

for SCAN in $(ps -ef |grep -i tns |grep SCAN |awk {'print $9'})
do
export LOWER_SCAN_LISTENER=$(echo $SCAN |tr '[A-Z]' '[a-z]')
SCAN_LISTENER_LOG=$GRID_HOME/log/diag/tnslsnr/$(hostname -s)/$LOWER_SCAN_LISTENER/trace/$LOWER_SCAN_LISTENER.log
ls -l  $SCAN_LISTENER_LOG
done

rotate $LISTENER_LOG listener
rotate $SCAN_LISTENER_LOG $LOWER_SCAN_LISTENER
rotate $ASM_LOG $ASM_RUNNING
}

Here’s a sample output of the log rotation script:

logrotate -s /u01/app/oracle/general/sh/scripts/logrotate/log_rotate_status.test1 -f /u01/app/oracle/general/sh/scripts/logrotate/test1
logrotate -s /u01/app/oracle/general/sh/scripts/logrotate/log_rotate_status.erpqa1 -f /u01/app/oracle/general/sh/scripts/logrotate/erpqa1
logrotate -s /u01/app/oracle/general/sh/scripts/logrotate/log_rotate_status.listener -f /u01/app/oracle/general/sh/scripts/logrotate/listener
logrotate -s /u01/app/oracle/general/sh/scripts/logrotate/log_rotate_status.listener_scan1 -f /u01/app/oracle/general/sh/scripts/logrotate/listener_scan1
logrotate -s /u01/app/oracle/general/sh/scripts/logrotate/log_rotate_status.+ASM1 -f /u01/app/oracle/general/sh/scripts/logrotate/+ASM1

Note the -s option is to specify an alternate state file. Since we are executing logrotate as the oracle or grid user, we must specify the -s option. The default state file is /var/lib/logrotate/status.

As you can see, it create a logrotate script for 2 of our databases, the local ASM instance, database listener and the scan listener. If you drill down into the actual logrotate script, you will notice that it is designed to rotate on a weekly basis, copy the file, truncate the original file, keep 2 copies and compress the copies. Here’s a sample logrotate script:

cat logrotate/listener_scan1
/u01/app/grid/11203/log/diag/tnslsnr/dallinux01/listener_scan1/trace/listener_scan1.log {
weekly
copytruncate
rotate 2
compress
}

Comments are closed