RE: Physical Standby Montoring

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Tue, 20 Sep 2011 19:18:51 +0000
Message-ID: <7642C3D639A75E4284658CAA38BF60A403C87F_at_US-BOS-VEX001.eu.pxl.int>

I rely on a cron'd script the is as follows (partial)

sqlplus "/ as sysdba" _at_/home/oracle/src/standby_check.sql export STB_RSLT=$?
 if [ $STB_RSLT -gt 0 ] ; then

         export SBJ=`hostname`' - standby_check '"$ORACLE_SID"' - Gap is '"$STB_RSLT"
       /bin/mailx -s "$SBJ" richard.goulet_at_parexel.com </home/oracle/standby_check.log

 fi

Standby_check.sql consiste of:

spool $HOME/standby_check.log
select name, sysdate from v$database;
column process format A10 trunc
column client_process format A8 trunc
column sequence# format 9999999999
column status format A13 trunc
column current_log_gap new_value _clg
break on current_log_gap
SELECT nvl(abs(max(decode(PROCESS, 'MRP0', sequence#)) -max(decode(process,'RFS',decode(CLIENT_PROCESS,'LGWR', SEQUENCE#),NULL))),1) current_log_gap FROM V$MANAGED_STANDBY;
clear breaks
select process, client_process, sequence#, status from v$managed_standby where sequence# is not null
  and sequence# > 0
order by 3;
set numwidth 20
prompt Unrecoverable File Check
select file#, unrecoverable_change#, to_char(unrecoverable_time,'dd/mm/yyyy hh24:mi:ss') from v$datafile
where unrecoverable_change# > 0;
spool off
exit _clg;

Works like a charm.

Richard Goulet
Senior Oracle DBA/Na Team Leader

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Uzzell, Stephan Sent: Tuesday, September 20, 2011 3:13 PM To: 'Bill Zakrzewski'
Cc: Oracle L
Subject: RE: Physical Standby Montoring

Bill,

We are not currently using GC for this. I will look into it. If anyone knows offhand of a good by-example setup, that would be nice to have - otherwise I'll trial and error my way through it ;-)

We don't have 100% faith in GC so we tend to use a secondary monitoring system where possible. So any other suggestions would also be welcomed.

Stephan Uzzell

-----Original Message-----
From: Bill Zakrzewski [mailto:bill_at_intactus.com] Sent: Tuesday, 20 September, 2011 14:01
To: Uzzell, Stephan
Cc: Oracle L
Subject: Re: Physical Standby Montoring

Oracle Grid Control????

On Sep 20, 2011, at 1:51 PM, Uzzell, Stephan wrote:

> Hey Listers,
> Any cool tricks for monitoring a bunch of physical standby environments? We have 20 or so physical standby databases, and are having trouble figuring the best strategy to monitor them. We do not currently have enough DBAs to sit around watching them.
>
> We are currently using two scheduled tasks to grab the current_scn from v$database, alternating every 45 minutes or so; we then parse the two output files using perl, do some quick math, and as long as there is a change in the SCN, we feel things are probably OK.
>
> The problem is that the SCN does not advance at a regular interval. Some database are busier than others. Some database therefore do not have the SCN advance between a pair of tests; other times one of the scheduled tasks hangs. So we get periodic "BAD" reports that are not really bad.
>
> Definitely not the best system. Any recommendations for the best way to monitor - to only get alerts if / when there is actually something wrong?
>
> Thanks!
>
> Stephan Uzzell
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 20 2011 - 14:18:51 CDT