How can we get email whenever synchronization between databases is lost in Oracle Data Guard

How can we get email whenever synchronization between databases is lost in Oracle Data Guard
How can we get email whenever synchronization between databases is lost in Oracle Data Guard

In this article we will see how we build a mechanism that will notify us by email when some of the standby databases syncing with the primary are left behind in the sequence number of the logs either because the logs stopped being sent or because they stopped being applied.

The footsteps

In previous article we had seen how we can set one up Linux servers (Oracle Linux based on RHEL specifically) so that we receive an email whenever an error occurs in the Alert log of the database instance. Accordingly, we will now see the steps to receive emails when a certain replica is back above a sequence number.

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 email whenever synchronization between databases is lost in Oracle Data Guard

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 do we see if a standby replica has stopped synchronizing and is some sequences behind

We create the following sql query that generates the warning message for the difference in sequence numbers that it has in case there is more than one (either because they were not received, or because the logs have not been applied):

[oracle@dp-gr ~]$ vi check_dg.sql
set heading off;
set feedback off;
set echo off;
set pagesize 0;


SELECT 'Warning: Data Guard Replication not working on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance)||'!!! ~~~~~~~~~~~~~~~~ Received Sequence Difference is: '
 ||cast(LAST_SEQUENCE_RECEIVED-MAX_SEQUENCE as number) || ' and Applied Sequence Difference is: ' || cast(LAST_SEQUENCE_APPLIED-LAST_SEQUENCE_RECEIVED as number)
FROM
(SELECT DEST_ID, max(SEQUENCE#) LAST_SEQUENCE_RECEIVED
FROM V$ARCHIVED_LOG where resetlogs_change#=(select resetlogs_change# from v$database)
GROUP BY DEST_ID) ARCS
LEFT JOIN
(SELECT DEST_ID, max(SEQUENCE#) LAST_SEQUENCE_APPLIED
FROM V$ARCHIVED_LOG
where APPLIED='YES' AND DEST_ID not in (0,1) and resetlogs_change#=(select resetlogs_change# from v$database)
GROUP BY DEST_ID
UNION
SELECT 1 DEST_ID,max(SEQUENCE#) LAST_SEQUENCE_APPLIED
FROM V$LOG_HISTORY
where resetlogs_change#=(select resetlogs_change# from v$database)
) APPLIED ON APPLIED.DEST_ID=ARCS.DEST_ID
,
(SELECT max(SEQUENCE#) MAX_SEQUENCE
FROM V$LOG_HISTORY
where resetlogs_change#=(select resetlogs_change# from v$database))MAX
WHERE 1=1
and LAST_SEQUENCE_RECEIVED-MAX_SEQUENCE < -1
or LAST_SEQUENCE_APPLIED-LAST_SEQUENCE_RECEIVED < -1
;

exit;
/

How do we send email when a standby replica is more than one sequence behind in sync

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 one line, then it will send email with the title “Oracle DB Alert ORCL” in the emails that we have defined, having as text a warning that contains the name of the server and the instance, together with the number of logs that have not been received and the number of logs that have not been applied.

The email is only sent if replication is more than one log behind.

So we create the executable with the following code:

[oracle@dp-gr ~]$ vi check_dg.sh
#!/bin/bash
sqlplus -S "/as sysdba" @/home/oracle/check_tablespaces.sql > /home/oracle/check_dg.log
cnt=$(wc -l &lt;/home/opc/check_dg.log)
echo $cnt
if [ $cnt -ge "1" ]
then
        /usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
                -r info@dataplatform.gr \
                dataplatform.gr@gmail.com \
         < /home/opc/check_dg.log

fi

We can alternatively run it remotely on another machine with the user SYS via ezconnect:

#!/bin/bash
sqlplus -S "sys/Kwdikos@hostname:1521/ORCL as sysdba" @/home/oracle/check_dg.sql > /home/oracle/check_dg.log
cnt=$(wc -l &lt;/home/opc/check_dg.log)
echo $cnt
if [ $cnt -ge "2" ]
then
        /usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
                -r info@dataplatform.gr \
                dataplatform.gr@gmail.com \
         < /home/opc/check_dg.log

fi

We don't forget to have given execute permission before executing it:

[oracle@dp-gr ~]$ chmod +x check_dg.sh
[oracle@dp-gr ~]$ ./check_dg.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
0,5,10,15,20,25,30,35,40,45,50,55 * * * * . ~/.bash_profile && (/home/opc/check_dg_live.sh  >> /dev/null 2>&1)

The result

Now in case a standby replica either has not received some logs (Received Sequence Difference) or has not applied them (Applied Sequence Difference) it will send us an email like the one below:

Warning: Data Guard Replication not working on ORCL@dp-gr!!! ~~~~~~~~~~~~ Received Sequence Difference is: -6 and Applied Sequence Difference is: -1
How can we get email whenever synchronization between databases is lost in Oracle Data Guard
Share it

Leave a reply