RE: Poor man's standby monitoring
Date: Thu, 23 Dec 2010 08:53:09 -0500
Message-ID: <6B0D50B70F12BD41B5A67F14F5AA887F0CEDD543_at_us-bos-mx022.na.pxl.int>
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_at_solutionary.com]
Sent: Wednesday, December 22, 2010 5:28 PM
To: Goulet, Richard; deshpande.subodh_at_gmail.com;
William.Blanchard_at_fisglobal.com
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Goulet, Richard
Sent: Wednesday, December 22, 2010 10:02 AM
To: deshpande.subodh_at_gmail.com; William.Blanchard_at_fisglobal.com
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Subodh Deshpande
Sent: Tuesday, December 21, 2010 8:33 AM
To: William.Blanchard_at_fisglobal.com
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_at_fisglobal.com> 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 ============================== -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 23 2010 - 07:53:09 CST