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.
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 : EXATREE 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
SHELL script SHELL SCRIPT