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 99999999999999999 COLUMN 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@solu tionary.com> To Sent by: "Richard.Goulet@xxxxxxxxxxx" oracle-l-bounce@f <Richard.Goulet@xxxxxxxxxxx>, reelists.org "deshpande.subodh@xxxxxxxxx" <deshpande.subodh@xxxxxxxxx>, "William.Blanchard@xxxxxxxxxxxxx" 2010.12.23 00:29 <William.Blanchard@xxxxxxxxxxxxx> cc ORACLE-L <oracle-l@xxxxxxxxxxxxx> Please respond to Subject DonGranaman@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Richard Sent:, Wednesday, December 22, 2010 10:02 AM To: deshpande.subodh@xxxxxxxxx; William.Blanchard@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Subodh Deshpande Sent: Tuesday, December 21, 2010 8:33 AM To: William.Blanchard@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx> 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 ============================== -- //www.freelists.org/webpage/oracle-l