RE: Poor man's standby monitoring

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 23 Dec 2010 10:22:57 -0800 (PST)
Message-ID: <169642.79298.qm_at_web80603.mail.mud.yahoo.com>

On a database we use ARCH to ship redo, a query on v$managed_standby can't detect the gap at all:

SQL> select process, status, client_process, thread#, sequence# from v$managed_standby;

PROCESS STATUS CLIENT_P THREAD# SEQUENCE# --------- ------------ -------- ---------- ----------

ARCH      CLOSING      ARCH              1      34409
ARCH      CLOSING      ARCH              1      34410
RFS       IDLE         UNKNOWN           0          0
RFS       IDLE         UNKNOWN           0          0
RFS       IDLE         UNKNOWN           0          0
RFS       IDLE         UNKNOWN           0          0
RFS       IDLE         UNKNOWN           0          0
RFS       IDLE         UNKNOWN           0          0
RFS       IDLE         UNKNOWN           0          0
MRP0      WAIT_FOR_LOG N/A               2      28378
RFS       IDLE         UNKNOWN           0          0

because it doesn't tell us which sequence for thread 2 has arrived. A query of v$archied_log (as Don hinted earlier) works:

SQL> select x.thread#, x.cur_log, y.apl_log, x.cur_log - y.apl_log gap   2 from (select thread#, max(sequence#) cur_log from v$log group by thread#) x,   3 (select thread#, max(sequence#) apl_log from v$archived_log where applied = 'YES' group by thread#) y   4 where x.thread# = y.thread#
  5 order by 1;

   THREAD# CUR_LOG APL_LOG GAP ---------- ---------- ---------- ----------

         1      34411      34372         39
         2      28418      28377         41

...

We have 1 day's delay of redo apply so the gaps are expected.

Yong Huang

• On Thu, 12/23/10, Yong Huang <yong321_at_yahoo.com> wrote:

> > This works for an exclusive primary, but doesn't quite work for
> > a RAC primary (since there are multiple redo threads):
> > ...
>
> The SQL below works for RAC when LGWR is shipping redo:
>
> SQL> select x.sequence# - y.sequence# gap
> 2 from (select thread#, sequence# from
> v$managed_standby where process = 'MRP0') x,
> 3 (select thread#,
> sequence# from v$managed_standby where process = 'RFS' and
> client_process = 'LGWR') y
> 4 where x.thread# = y.thread#;
>
> GAP
> ----------
> 0
>
> It queries the view twice. There may be a way to query it
> only once.
>
> Yong Huang
      

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

Received on Thu Dec 23 2010 - 12:22:57 CST