In Dataguard, we have a monitor agent which runs:
select count(*) from v$archived_log where standby_dest=’YES’ and applied = ‘NO’
This gives us a count of logs not yet applied to DR which we can trigger an alert on.
Is there a similar query in the Dbvisit standby data dictionary that we could run that would provide a similar result?
The goal is to run one query in the database to count the number of logs not yet applied to DR. This would allow us to tie directly in to our existing monitor framework.
The main reason we can not use
v$archived_log (but rather
v$log_history) is outlined here:
In Dbvisit Standby there is a
dictionary table that can be queried and this is
The key with this, however, is that it needs to either have the Log Gap report run periodically - or else
RUN_INSPECT enabled (which it is by default) - in order to record the details on the archive log sequences on the primary and DR sides at particular points in time. With
RUN_INSPECT enabled by default it is more than likely that this information is already being captured in your environment:
So a query such as the following, can get you a view of the log sequences over time... though you will want to amend this for your purposes:
SELECT (PRIMARY_LOG_SEQUENCE - STANDBY_SEQUENCE) as GAP from dbvisit7.dbv_sequence_log where ORACLE_SID='orcl1db' and TIMESTAMP = (select MAX(TIMESTAMP) from dbvisit7.dbv_sequence_log);
One other thing worth mentioning is that if you schedule the Log Gap report independently then there is actually a setting by which you can elect to be notified if the log gap between primary and standby exceeds a certain threshold:
Additional Comment from the Development Team
In Oracle Standard Edition(SE) it is not so simple as in Enterprise Edition(EE).
The standby only knows what is applied, so you have to query the primary and find out what the current archive sequence is, then compare that against the standby sequence that was applied - this is what the Dbvisit Standby log gap report does.
Querying our internal tables is not an optimal solution because, if for some reason, Dbvisit Standby does not run, then you will not know the standby database is out by an hour or two until the heart-beat email (again assuming that is running).
Our recommendation on monitoring is that it should be outside the product or use the log gap report, but not to rely on our internal tables. You can also be sure that these internal tables (and this mechanism) will also change in v8 of Dbvisit Standby, so perhaps not best to build on it. Look at
v$log_history on standby and compare against
v$archived_log on primary.
See at bottom of http://blog.dbvisit.com/a-few-notes-on-dbvisit-standby-monitoring/
Another option could be to run the log gap report and parse the output in a bash script or perl script (or something).
Parsing the log gap report should not be too difficult.
Donna Zehl June 18, 2014 00:35