Requirement:
Configure a shell script to monitor alert log for all the databases on a server once in every 15 min.And in the case of any ORA- error mail to the DBA TEAM.
Below script is prepared using the ADRCI utility of oracle 11g. It will monitor alert log for all the databases having same oracle base.
SCRIPT:(Adrci_alert_log.ksh)
###########################################################################################
###### ALERT LOG CHECKING VIA ADRCI #######################################################
###### Author - DBACLASS ADMIN #######################################################
###########################################################################################
LOG_DIR=/export/home/oracle/Housekeeping/logs/alert_log_check_daily.txt
adrci_homes=( $(adrci exec="show homes" | egrep -e rdbms ))
echo '#########################################################################################################' > $LOG_DIR
echo '###########################################ALERT LOG OUTPUT FOR LAST 15 MINUTES ###########################' >> $LOG_DIR
echo '##########################################################################################################' >> $LOG_DIR
for adrci_home in ${adrci_homes[@]}
do
echo ' '>>$LOG_DIR
echo '##########################################################################################################' >> $LOG_DIR
echo '##########################################################################################################' >> $LOG_DIR
echo ' '>>$LOG_DIR
echo $adrci_home' Alert Log' >> $LOG_DIR
adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/96\\\"" -term >> $LOG_DIR
done
num_errors=`grep -c 'ORA' $LOG_DIR`
if [ $num_errors != 0 ]
then
mailx -s "ORA- error found in alert Log of the server " support@dbaclass.com <$LOG_DIR
fi
Give 755 permission to the script
chmod 755 Adrci_alert_log.ksh
Configure the script in crontab:
0,15,30,45 * * * * /export/home/oracle/Housekeeping/scripts/Adrci_alert_log.ksh > /export/home/oracle/Housekeeping/logs/error_alert.log 2>&1
Hi I am a big fan of your blogs I was trying to setup alertlog notifications and got this errors any suggestions? Thanks in advance
$ more error_alert.log
/export/home/oracle/Housekeeping/logs/Adrci_alert_log.ksh: line 7: adrci: command not found
/export/home/oracle/Housekeeping/logs/Adrci_alert_log.ksh: line 24: [: too many arguments
Hi Brother,
Please provide below information from the database server.
echo $SHELL
uname -a
Also please make sure you set ORACLE_HOME,ORACLE_SID,PATH in the script.
Admin
$ echo $SHELL
/bin/bash
$uname -a
Linux ssh.machinename.com 3.10.0-693.5.2.el7.x86_64 #1 SMP Fri Oct 13 10:46:25 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux
After setting ORACLE_HOME, ORACLE_SID it is failing on
if [ $num_errors!=0 ] —>[: too many arguments
After making necessary corrections I find this error
more error_alert.log
/export/home/oracle/Housekeeping/logs/Adrci_alert_log.ksh: line 10: adrci: command not found
adrci_homes=( $(adrci exec=”show homes” | egrep -e rdbms ))–> this is line 10
When I do show homes in adrci I see
diag/rdbms/primedg/PrimeDG
My alert log is in
diag/rdbms/primedg/PrimeDG/trace
Any suggestions?
Figured out adrci_homes=( $((space)adrci exec=”show homes” | egrep -e rdbms )) and it worked.
Thanks much
Hi Brother,
Pleased to know tha, you fixed the issue 🙂 .
Do connect with us .
Admin
Hi Admin,
Could you suggest me how to write the the oracle alert.log script
I am struggling for writing the script from 2 week.
Did you followed the mentioned steps . Else please post in
Forum.dbaclass.com
Yeah I tired what ever you mentioned.
One question from my side how to run adrci command in Linux environment.
Can you give some different suggest.
Hi Admin,
Can You suggest me how to run adrci command in Linux machine
When I am run the above script on my Linux Machine, It is showing adrci command is error.
Dear,
Set the environment variables for ORACLE_HOME,PATH and then just type Adrci.
$export ORACLE_HOME=/u01/app/oracle/product/12c/dbhome_1
$export PATH=$ORACLE_HOME/bin:$PATH
$adrci
ADRCI> show base
ADRCI> show homes
ADRCI> show control
ADRCI> show alert -tail 50
Can you tell me hot to setup ORACLE_HOME,PATH in linux vm
Hello,
How can i edit the script to give reporting for just one database instead of every database in the server?
In that case, you can remove the for loop and hardcode the homeopath ,
However if wish to modify the above script, then put the home path in below {home path} place holder.
adrci_homes=( $(adrci exec=”show homes” | egrep -e {home path} ))
Hi,
I’ve been struggling to have the script executed by crontab. When I run the script manually it works as expected. However, when It runs by crontab, it doens’t run. If i did debug correctly, the script is not being able to retrieve information from “show homes”. I’ve tried to set manualy the base and the homepath, but no success. Cron is being executed by oracle os user, which is able to run the script manually properly.
Any idea why or what I’m missing ?
Thanks
Do one thing, set ORACLE_HOME,PATH inside the shell script in the beginning part of the script.
export ORACLE_HOME=/u01/app/oracle/product/12.2.0
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=/export/home/oracle/Housekeeping/logs/alert_log_check_daily.txt
adrci_homes=( $(adrci exec=”show homes” | egrep -e rdbms ))