Day by day, alert log size will grow in Oracle database. So for housekeeping, we need to move the existing alert log to a backup location and compress there. Upon moving the alert log, the database will create a fresh alert log automatically.

1. Below is the shell script.

WE need to define the ORACLE_HOME in the script. and ORACLE_SID will be passed as an argument while running the script.

# $Header: rotatealertlog.sh 
# *====================================================================================+
# | AUTHOR : DBACLASS SUPPORT TEAM
# | |
# +====================================================================================+
# |
#!/bin/bash
echo ========================
echo Set Oracle Database Env
echo ========================
 
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH;export PATH
TO_DATE="20`date +%y%m%d`"; export TO_DATE
 
echo =======
echo Extract Alert log location
echo =======
export VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S /nolog <<EOF
conn /as sysdba
set pages 0 feedback off;
prompt
SELECT value from v\$parameter where NAME='core_dump_dest';
exit;
EOF
)
export LOCATION=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
export ALERTDB=${LOCATION}/alert_$ORACLE_SID.log
export ELOG=$( echo ${ALERTDB} | sed s/cdump/trace/)

echo =======
echo Compress current
echo =======

if [ -e "$ELOG" ] ; then
 mv ${ELOG} ${ELOG}_${TO_DATE};
 gzip ${ELOG}_${TO_DATE};
 > ${ELOG}
else
 echo not found
fi

exit

2. Configure in crontab:

SCHEDULE – Weekly once

Here, we have passed the ORACLE_SID (PRODDB) as argument 

00 22 * * 5 /u01/app/oracle/dbscripts/rotatealertlog.sh PRODDB