RE: Poor man's standby monitoring
Date: Thu, 23 Dec 2010 09:45:57 +0200
Message-ID: <OF374D45B5.FFDC8006-ONC2257802.0029E4F1-C2257802.002AA92C_at_seb.lt>
did anyone mentioned V$MANAGED_STANDBY - it has everything(almost) needed for real time apply DG monitoring (and not only real time) ?
A very important thing to monitor: write a script to compare parameters of
standby and primary db. Some parameters will differ (filter those out) but
most must not.
You will avoid a LOT OF surprises after switch/failover.
Some usefull queries (run on standby db):
prompt STB APPLY STATUS (Recovery is on if process MRP is present):
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS, blocks, block# FROM V
$MANAGED_STANDBY;
SELECT min(SEQUENCE#)-1 STB_MAX_APPLIED_SEQ# FROM V$MANAGED_STANDBY where
status in ('APPLYING_LOG', 'WAIT_FOR_LOG') and
(process like 'MRP%' OR process like 'MR(fg)' ) ;
COLUMN current_stb_scn NEW_VALUE v_current_stb_scn FORMAT A24
select to_char(current_scn) current_stb_scn,
decode((select count(1) from v$managed_standby where process like 'MRP%'),
0, 'NO', 'YES') RECOVERY_ON
,name db_name, open_mode, database_role, db_unique_name
from v$database d;
prompt
prompt STB ACTIVE REDO LOG STATUS (it shows nothing for Maximum Perf.
mode):,
COLUMN last_stb_change# FORMAT 99999999999999999COLUMN last_time format A20
COLUMN db_time format A20
select last_change# last_stb_change#, sequence#, status , to_char(last_time, 'YYYY.MM.DD HH24:MI:SS') last_time , to_char(sysdate, 'YYYY.MM.DD HH24:MI:SS') db_time from v$standby_log where status='ACTIVE';
The following ones are for primary DB:
select FS_FAILOVER_STATUS, FS_FAILOVER_OBSERVER_PRESENT, FS_FAILOVER_OBSERVER_HOST, FS_FAILOVER_CURRENT_TARGET from V$DATABASE;
COLUMN prim_scn NEW_VALUE v_current_prim_scn FORMAT A20,
COLUMN STANDBY_SCN FORMAT A20
COLUMN prim_current_arch_seq# NEW_VALUE v_current_prim_seq
COLUMN ARCH_LAG NEW_VALUE v_arch_lag
SELECT to_char(current_scn) prim_scn, '&v_current_stb_scn' STANDBY_SCN,
(current_scn - '&v_current_stb_scn') SCN_LAG
,(select max(sequence#) from v$log) prim_arch_seq#, &v_stb_applied_seq
STB_MAX_APPLIED_SEQ
, ( (select max(sequence#) from v$log) - &v_stb_applied_seq ) ARCH_LAG
, (select min(log_sequence) from v$archive_dest where target='STANDBY' and
status='VALID') shiped_stb_log_seq
FROM V$DATABASE;
select &v_arch_lag arch_lag from dual;
I also recommend to write a script to check archive log houskeeping - if you backup archive logs on one of databases (I prefer primary) then somehow you need to delete logs on stanndby (10g can do it "automagically" if logs are thrown into recovery area - at least it will try )
Please consider the environment before printing this e-mail
Don Granaman
<DonGranaman_at_solu
tionary.com> To
Sent by: "Richard.Goulet_at_parexel.com"
oracle-l-bounce_at_f <Richard.Goulet_at_parexel.com>,
reelists.org "deshpande.subodh_at_gmail.com"
<deshpande.subodh_at_gmail.com>,
"William.Blanchard_at_fisglobal.com"
2010.12.23 00:29 <William.Blanchard_at_fisglobal.com>
cc
ORACLE-L <oracle-l_at_freelists.org>
Please respond to Subject
DonGranaman_at_solut RE: Poor man's standby monitoring
ionary.com
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 - 01:45:57 CST