How can we get an email whenever an error occurs in the Oracle database Alert log
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In a database based system Oracle, that an error occurs is recorded in a log file called Alert Log. It is very useful to be informed immediately when something happens and the most immediate way is none other than sending an email.
In this article we will see how we can set one up Linux servers (Oracle Linux based on RHEL specifically) to perform this job.
Create an SMTP relay with Google's gmail
If we have our own relay on the exchange server with configured smtp that will send the emails, we don't need this step.
But now we will see what we do in case we want to send the emails through Gmail.
First we need to connect to gmail account security.
There we should activate the two factor authentication and then create an application password.
After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:
[oracle@dp-gr ~]$ sudo vi /etc/mail.rc account gmail {
set smtp-use-starttls
set ssl-verify=ignore
set smtp-auth=login
set smtp=smtp://smtp.gmail.com:587
set from="info@dataplatform.gr"
set smtp-auth-user=dataplatform.gr@gmail.com
set smtp-auth-password="edw_vazoume_to_app_password"
set ssl-verify=ignore
set nss-config-dir=/etc/pki/nssdb
}
We are ready to test that we can send email:
[oracle@dp-gr ~]$ echo "Test" | /usr/bin/mail -A gmail -s "dokimastiko" ToEmailMas@outlook.com
How will we find the Alerts that have happened in the last 5 minutes
We create the following script that will catch us that an error has appeared in the Alert Log or Shutdown process:
[oracle@dp-gr ~]$ vi check_alertlog.sqlset echo off
set pagesize 0
set feedback off
select
'TIMESTAMP: '|| originating_timestamp
|| ' ---- INSTANCE_NAME: ' || (select instance_name from v$instance)
|| ' ---- HOST_NAME: ' || host_id
||' ---- MESSAGE: ' || message_text
from v$diag_alert_ext
where 1=1
and CAST(SUBSTR(originating_timestamp,1,LENGTH(originating_timestamp)-7) AS TIMESTAMP) > CAST (sysdate-interval '5' minute AS TIMESTAMP)
and
(message_text like '%ORA-%' OR message_text like '%Shutting down instance%' or message_text like '%archival error%' or message_text like '%Cannot allocate log, archival required%')
and message_text not like '%ORA-609%' --process abort
and message_text not like 'ORA-00060%' --ignore deadlocks
and message_text not like 'ORA-01555%' --ignore snapshot too old
and message_text not like '%ORA-1013%' --operation canceled by user
and message_text not like '%ORA-3136%' --inbound connection timeout
and message_text not like '%ORA-235 %' --unlocked control file
and message_text not like '%Result = ORA-0%'
and message_text not like '%Result = ORA-31%'
and message_text not like '%Result = ORA-30%'
and message_text not like '%ORA-609%' --process abort
and message_text not like '%ORA-3135%' --aborting process unknown
;
quit;
/
In originating_timestamp we specify that we only want the last 5 minutes.
Of course we have the possibility to limit to specific errors only or to change the time interval by changing the parameters in where of the query.
*At the end of the article I am telling you a system procedure with which we can create fake errors in the Alert Log for testing.
How do we email Alerts if any
Now we will create an executable that will run the above query, exporting the results to a text file. In case the file is more than 3 lines then it will send an email with the title “Oracle DB Alert ORCL” in the emails we have defined with Alerts as text.
The reason that the process only completes if the file contains more than 3 lines is so that it does not send an email if there is no alert and the text file is empty.
So we create the executable with the following code:
[oracle@dp-gr ~]$ vi check_alert_live.sh#!/bin/bash
sqlplus -S "/as sysdba" @/home/oracle/check_alertlog.sql > /home/oracle/alerts_formail.txt
cnt=$(wc -l </home/oracle/alerts_formail.txt)
echo $cnt
if [ $cnt -ge "4" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
-r info@dataplatform.gr \
patatakia@outlook.com, dokimi@outlook.com \
< /home/oracle/alerts_formail.txt
fi
We can alternatively run it remotely on another machine with the SYS user via ezconnect:
#!/bin/bash
sqlplus -S "sys/Kwdikos@hostname:1521/ORCL as sysdba" @/home/oracle/check_alertlog.sql > /home/oracle/alerts_formail.txt
cnt=$(wc -l </home/oracle/alerts_formail.txt)
echo $cnt
if [ $cnt -ge "4" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
-r info@dataplatform.gr \
patatakia@outlook.com, dokimi@outlook.com \
< /home/oracle/alerts_formail.txt
fi
We don't forget to have given execute permission before executing it:
[oracle@dp-gr ~]$ chmod +x check_alert_live.sh
[oracle@dp-gr ~]$ ./check_alert_live.shHow do we schedule it to check every 5 minutes for Alerts
Since our executable is ready, we can add a line to the crontab in which we will set it to run every 5 minutes:
[oracle@dp-gr ~]$ crontab -e#crontab 0,5,10,15,20,25,30,35,40,45,50,55 * * * * . ~/.bash_profile && (/home/oracle/check_alert_live.sh >> /dev/null 2>&1)
The result
Now we will see that whenever an error occurs we will receive an email.
To test it we can connect to the database and run the command which will generate a fake error in the Alert Log:
exec dbms_system.ksdwrt(2,'ORA-00600: This is a test error message for monitoring and can be ignored.');
And after a while the following email came:
