Oracle standby databases monitoring

Question

I have many databases for which I have configured data guard setup. When it comes to monitoring the standby's, I use OEM to check the lag and I have enabled mail alerts as well as and when gap is more than 50 archives.

But the thing what I recently observed is that in one of my standby databases my recovery got stopped for one or more reasons and unfortunately lag also did not go above 50 so there was no alerts. In such a situation how I can ensure when my standby's recovery MRP itself is not active and I should be alerted in such a situation..

How is it possible? Any soultions?

Answer 1

There's a few ways to determine if MRP is running. One is to query open_mode from v$database. If MRP is running, you will get:

READ ONLY WITH APPLY

If not, you will get:

READ ONLY

You can run the following query which will show what MRP processes are running:

select process,status,sequence# 
from v$managed_standby;

Look for the process MRPn

You could look in V$DATAGUARD_STATUS.MESSAGE for various things, including:

MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery Canceled

Another way is on the primary, to check

select recovery_mode from v$archive_dest_status where dest_id = n;

If it's IDLE, it's not doing anything. With real-time apply enabled, it returns MANAGED REAL TIME APPLY.