RE: Poor man's standby monitoring

  • From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
  • To: "Don Granaman" <DonGranaman@xxxxxxxxxxxxxxx>, <deshpande.subodh@xxxxxxxxx>, <William.Blanchard@xxxxxxxxxxxxx>
  • Date: Thu, 23 Dec 2010 08:53:09 -0500

Well, OK.  All we have is single primary db's here, but you could add
the thread# column so that you get a reading for each thread.  We
abandoned RAC over the year in favor of standby databases.  Reasons were
that the applications didn't understand RAC and the RAC install was
badly done in the first place.  They were our number one maintenance
problem and would crash multiple times each week.  The entire problem
was how it was installed by a contractor without proper documentation
and other stuff.  I'm told, because it was done before I got here, that
the contractor admitted it was his first time installing it.  OH
BOTHER!!!!
 

Dick Goulet 
Senior Oracle DBA 

 

________________________________

From: Don Granaman [mailto:DonGranaman@xxxxxxxxxxxxxxx] 
Sent: Wednesday, December 22, 2010 5:28 PM
To: Goulet, Richard; deshpande.subodh@xxxxxxxxx;
William.Blanchard@xxxxxxxxxxxxx
Cc: ORACLE-L
Subject: RE: Poor man's standby monitoring



This works for an exclusive primary, but doesn't quite work for a RAC
primary (since there are multiple redo threads):

 

idle:SQL> SELECT max(decode(PROCESS, 'MRP0',
sequence#))-max(decode(process,'RFS',decode(CLIENT_PROCESS,'LGWR',
SEQUENCE#),NULL))current_log_gap

FROM V$MANAGED_STANDBY;

 

CURRENT_LOG_GAP

---------------

        -274811

 

Even though the standby is really only a couple of minutes behind. 

 

idle:SQL>  select to_char(sysdate,'YYYY-MM-DD HH24:MI') WHEN,

  2         ': standby is ' || trim(to_char(1440 * (sysdate -
max(next_time)),99999.99) ||

  3*        ' minutes behind') LAG from v$archived_log where applied =
'YES';

 

WHEN             LAG

---------------- -------------------------------------

2010-12-22 16:26 : standby is 2.17 minutes behind

 

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Richard
Sent: Wednesday, December 22, 2010 10:02 AM
To: deshpande.subodh@xxxxxxxxx; William.Blanchard@xxxxxxxxxxxxx
Cc: ORACLE-L
Subject: RE: Poor man's standby monitoring

 

I've used

 

SELECT max(decode(PROCESS, 'MRP0',
sequence#))-max(decode(process,'RFS',decode(CLIENT_PROCESS,'LGWR',
SEQUENCE#),NULL))current_log_gap
FROM V$MANAGED_STANDBY;

 

for a long time to find my gaps.  Works like a dream.  Expected value is
0, anything else is trouble.

 

Dick Goulet 
Senior Oracle DBA 

 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Subodh Deshpande
Sent: Tuesday, December 21, 2010 8:33 AM
To: William.Blanchard@xxxxxxxxxxxxx
Cc: ORACLE-L
Subject: Re: Poor man's standby monitoring

you want to find out the gap between priamry and standby database
archives shipped

 

use the view v$archive_gap on primary

 

Thanks!

Subodh

On 21 December 2010 03:21, Blanchard, William G
<William.Blanchard@xxxxxxxxxxxxx> wrote:

Does anyone have a script or know a way to monitor a poor man's standby
(log shipping) to see how far behind it is and send an alert if it falls
x minutes behind?

 

DB: 10g & 11g

OS: Windows, Solaris, Linux, AIX

 

 

Thank you,

 

WGB

_____________

The information contained in this message is proprietary and/or
confidential. If you are not the intended recipient, please: (i) delete
the message and all copies; (ii) do not disclose, distribute or use the
message in any manner; and (iii) notify the sender immediately. In
addition, please be aware that any message addressed to our domain is
subject to archiving and review by persons other than the intended
recipient. Thank you.
_____________




-- 
==============================
DO NOT FORGET TO SMILE TODAY
==============================

Other related posts: