How can we get an email whenever an error occurs in the Oracle database Alert log

How can we get an email whenever an error occurs in the Oracle database Alert log
How can we get an email whenever an error occurs in the Oracle database Alert log

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.

How can we get an email whenever an error occurs in the Oracle database Alert log

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.sql
set 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-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
;


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.sh

How 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:

How can we get an email whenever an error occurs in the Oracle database Alert log
Share it

Leave a reply