In this article, i will explain how we can check our database alert.log file for any error messageAlert.log is record everything about what is happening our database. With this script we can check alert.log for any error message and we can take precautions for any problem.

First of all; we will create custom env file for my script. I like to create env file because i can use same env(after edit) for other clients.

Here is my env file:

CUSTOMER=ABC
ORA_OWNER_DB=oraprod
SID=PROD
HOSTNAME=prod
ORACLE_HOME=/xx/PROD/proddb/10.2.0
SID_HOST=PROD_prod

DBA=”erp@xxx.com”

export CUSTOMER USER SID ORACLE_HOME SID_HOST ALR_DATE ALR_LOG_FILE DBA

As you can notice, this env uses for our ABC company, my dbtier owner is oraprod, my SID is PROD,my linux machine hostname is prod.You can change those information wiht yours.

Here is my alert<SID>.log check script:

ALR_DATE=`date +%Y%m%d`
export ALR_DATE

ALR_LOG_FILE=/u01/alert_errors_”$ALR_DATE”.log
export ALR_LOG_FILE

cat $ORACLE_HOME/admin/$SID_HOST/bdump/alert*log | grep rror  > $ALR_LOG_FILE
cat $ORACLE_HOME/admin/$SID_HOST/bdump/alert*log | grep ORA- >> $ALR_LOG_FILE
su – $ORA_OWNER_DB -c “cp $ORACLE_HOME/admin/$SID_HOST/bdump/alert_$SID.log $ORACLE_HOME/admin/$SID_HOST/bdump/alert_$SID.log_$ALR_DATE”
tail -10  $ORACLE_HOME/admin/$SID_HOST/bdump/alert_$SID.log > $ORACLE_HOME/admin/$SID_HOST/bdump/alert_$SID.log

if [ `cat $ALR_LOG_FILE | wc -l` -gt 1 ]; then
mailx -s “ERROR: $CUST $SID ALERT ERRORS” erp@xxx.com < $ALR_LOG_FILE
fi

find $ORACLE_HOME/admin/$SID_HOST/bdump/ -mtime +15 -exec rm -f {} \;
find $ORACLE_HOME/admin/$SID_HOST/udump/ -mtime +15 -exec rm -f {} \;
tail -10  $ORACLE_HOME/network/admin/prod.log >  $ORACLE_HOME/network/admin/prod.log

Advertisements