RE: Poor man's standby monitoring
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 29 Dec 2010 10:07:29 +0200
it does not surprise me. There is a big difference at how oracle registers
data into v$-views depending on standby mode. there used to be (still are?)
some bugs too.
beware of one problem I've encountered on databases with moderate amount of
memory for sorting: query on v$archived_log may fail. standby can not use
temp tbs cause it is mounted only. if workarea auto is used with generous
amount of memory then it works.
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
Yong Huang
<yong321@xxxxxxxx
m> To
Sent by: oracle-l@xxxxxxxxxxxxx
oracle-l-bounce@f cc
reelists.orgblem Richard.Goulet@xxxxxxxxxxx,
DonGranaman@xxxxxxxxxxxxxxx
Subject
2010.12.23 20:24 RE: Poor man's standby monitoring
Please respond to
yong321@xxxxxxxxx
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
