Logical Standby Monitoring

Logical standbys add value through the offloading of reporting and other activities that can have performance impact on the production database. However, they are a special beast that require additional management. They easily can fall out of step with the primary production database. A simple create user can cause this situation to occur or the creation of an object that is not supported. I like data guard, but I would rather not manage standbys simply because they require extra babysitting.

Here are some scripts that I run periodically in an effort to proactively manage the logical standbys. I currently don’t have the data guard broker running so EM12c doesn’t provide me the alerts that would super beneficial in these situations. I prefer one stop shop to review all the databases status and health as opposed to scripts running through cron.

If nothing else one should have an alert setup to notify the right individuals when there is a lag in the apply at the logical database. EM12c provides for this, provided you are on bundle 2 and have your alerts set up correctly (Side bar I’m currently working on re-mediating this situation. Alerts for this condition worked on the old standby but since the cut over to the new standby they have effectively stopped). It would be nice if the patch went smoothly for EM12c, but that has not been the case so I do need to mitigate the situation with a quick alert running from within cron or dbms_scheduler. My preferred method is dbms_scheduler for all jobs related to the database.

The dba_logstdby_progress has the data required for alerting specifically the applied time and newest_time. I did modify my original query to include the comparison with the NEWEST and the APPLIED_TIME this matched what can be found in the Data Guard Menu utility. This utility is a treasure trove of awesome scripts for data guard. Although not a heavy user preferring to utilize SQL from my laptop over logging into the linux host to perform checks. But that’s just me the menu has helped me when I didn’t have a script readily available.

The job executes a query against the dba_logstdby_progress table checking the NEWEST and APPLIED times against each other, as well as the NEWEST against the current date and time (SYSDATE).

select
ltrim(rtrim(trunc((86400*(newest_time-applied_time))/60)-
60*(trunc(((86400*(newest_time-applied_time))/60)/60)))) apply,
ltrim(rtrim(trunc((86400*(sysdate-newest_time))/60)-
60*(trunc(((86400*(sysdate-newest_time))/60)/60)))) newest
into vApply, vNewest
from dba_logstdby_progress;

I select into variables and then perform the simple comparisons. Here’s how the anonymous block with the calls to the UTL_SMTP look. UTL_MAIL could easily substituted. This can be cleaned up it was a quick attempt to put something into place while I continue to work on EM12c. I’ll continue running this in the background until I’m convinced that I can rely on the alerts.

declare
vApply number;
vNewest number;
vLag number;
vLag_minutes number;
v_mailhost varchar2(200);
v_sender varchar2(200);
v_recipient varchar2(200);
v_subject varchar2(200);
v_message1 varchar2(10000);
v_message2 varchar2(10000);
v_message3 varchar2(10000);

mail_conn utl_smtp.connection;
crlf varchar2( 2 ):=CHR( 13 ) || CHR( 10 ) ;
mesg VARCHAR2(32767);

begin
v_mailhost:=’10.0.0.10′;
v_subject:=’Lag Time For Logical Standby Database’;
v_recipient:=’me@somewhere.com’;
v_sender:=’mea@somewhere.com’;
vlag:=15;

select
ltrim(rtrim(trunc((86400*(newest_time-applied_time))/60)-
60*(trunc(((86400*(newest_time-applied_time))/60)/60)))) apply,
ltrim(rtrim(trunc((86400*(sysdate-newest_time))/60)-
60*(trunc(((86400*(sysdate-newest_time))/60)/60)))) newest
into vApply, vNewest
from dba_logstdby_progress;

If vApply > vLag Then
vLag_minutes := vApply – vLag;
dbms_output.put_line (vLag_minutes);
v_message1:=’Apply Lag Time is: ‘|| vLag_Minutes;
elsif vNewest > vLag Then
vLag_minutes := vNewest – vLag;
dbms_output.put_line(vLag_minutes);
v_message1:=’Lag Time is: ‘|| vlag_minutes;
end if;

mesg:= ‘FROM: <”>’ || crlf ||
‘Subject: ‘||v_subject || crlf ||
‘TO: ‘||v_recipient || crlf ||
‘Logical Standby ACCLSTD LAG is:’ ||crlf||
‘ ‘||crlf||
v_message1 ||crlf||
‘ ‘;

mail_conn := UTL_SMTP.OPEN_CONNECTION(v_mailhost,25);
utl_smtp.helo(mail_conn,v_mailhost);
utl_smtp.mail(mail_conn,v_sender);
utl_smtp.rcpt(mail_conn,v_recipient);
utl_smtp.data(mail_conn,mesg);

utl_smtp.quit(mail_conn);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(‘ Invalid Operation in Mail’);
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(‘ Temporary e-mail issue – try again’);
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(‘ Permanent Error Encountered.’);
WHEN OTHERS THEN
raise_application_error(-20002,’unable to send the mail.’||SQLERRM);
END;

There are other checks that should be performed on the logical standby and the Data Guard Menu is loaded with the scripts to perform those checks. If you are interested in the companion book that details the use of the menu as well as other data guard information check out Oracle Data Guard 11g Handbook Undocumented Best Practices and Real-World Techniques. It would have been nice to have had the book before I delved into the world of data guard, sometimes being thrown into the rough waters is the best teacher.

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: