How can we get email whenever synchronization between databases is lost in Oracle Data Guard
Certifications:
- How we enable Unified Auditing in Oracle Database - 7 October 2024
- What is PostgreSQL and how do we do a full installation - September 2, 2024
- How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024
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.
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.comHow 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.sqlset 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_dg.sql > /home/oracle/check_dg.log
cnt=$(wc -l </home/opc/check_dg.log)
echo $cnt
if [ $cnt -ge "1" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Data Guard 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 </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 -e0,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
