RE: Dataguard Monitoring
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "'Richard.Goulet@xxxxxxxxxxx'" <Richard.Goulet@xxxxxxxxxxx>, "hdeng@xxxxxxxxxxxxxxxx" <hdeng@xxxxxxxxxxxxxxxx>, "mhyder@xxxxxxxx" <mhyder@xxxxxxxx>, "howard.latham@xxxxxxxxx" <howard.latham@xxxxxxxxx>
- Date: Wed, 6 Oct 2010 09:09:53 -0700
Here's one that I use.
$ORACLE_HOME/bin/sqlplus -SL > $OUTF / << END
set lines 100 pages 1000 feed off echo off
col message for a30 trunc
col error for a30 trunc
col archive_gap for a11
col database_mode for a15
select name, sysdate from v\$database;
select message, severity, timestamp
from v\$dataguard_status
where severity in ('Error','Fatal')
and timestamp > sysdate-15/1440
;
SELECT dest_id,status,
(CASE
WHEN database_mode <> 'MOUNTED-STANDBY'
THEN 'ERROR'
ELSE database_mode
END
) database_mode,
recovery_mode, archived_seq#, applied_seq#,
(CASE
WHEN archived_seq# - applied_seq# > 10
THEN 'ERROR-' || TO_CHAR (archived_seq# - applied_seq#)
ELSE TO_CHAR (archived_seq# - applied_seq#)
END
) archive_gap
FROM v\$archive_dest_status
WHERE dest_id = 2
;
exit
END
Michael Dinh
NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
individual or entity to which it is addressed, and may contain information that
is privileged, confidential and exempt from disclosure under applicable laws.
BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION
(PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS
FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this
email (and attachments) is not the intended recipient, you are hereby notified
that any dissemination, distribution or copying of this communication is
strictly prohibited. Please notify the sender of the error and delete the
e-mail you received. Thank you.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Goulet, Richard
Sent: Wednesday, October 06, 2010 7:58 AM
To: hdeng@xxxxxxxxxxxxxxxx; mhyder@xxxxxxxx; howard.latham@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Dataguard Monitoring
My favorite is:
select process, client_process, sequence#, status from v$managed_standby
where sequence# is not null
and sequence# > 0
order by 3;
It produces the following:
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
RFS ARCH 0 IDLE
RFS ARCH 0 IDLE
ARCH ARCH 35482 CLOSING
ARCH ARCH 35494 CLOSING
RFS LGWR 35495 IDLE
MRP0 N/A 35495 APPLYING_LOG
And since RFS and MRP0 are both on the same log, your just fine. If the
status is "Waiting on Log" then you've got a problem.
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Flora Deng
Sent: Wednesday, October 06, 2010 10:25 AM
To: mhyder@xxxxxxxx; howard.latham@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Dataguard Monitoring
If you're using real time apply and just want to check if DG is out of
sync,
here is a quick SQL:
Suppose 'dest_id=2' is your standby and any rows returned will mean
standby
is out of sync.
select thread#,max(sequence#) from v\$archived_log
where dest_id=2 and applied='YES'
group by thread#
minus
select thread#,max(sequence#) from v\$archived_log
where dest_id=2 and archived='YES'
group by thread#;
Flora
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mir M. Mirhashimali
Sent: Tuesday, October 05, 2010 11:46 AM
To: howard.latham@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Dataguard Monitoring
we use ZABBIX to monitor our database with custom scripts. when the lag
is 15 mins or more i get a SMS and email.
data-guard.sh
-------------------
#!/bin/sh
. /home/oracle/.profile_db01
cd /home/oracle/zabbix
sqlplus -s /nolog @check_log.sql
check_log.sql
------------------
connect / as sysdba
set pagesize 0;
set heading off;
set feedback off
set trimspool on;
select to_char(max(next_time), 'rrrr-mm-dd-hh24:mi:ss') next_time
from v$archived_log
where upper(applied) = 'YES'
and registrar = 'RFS';
exit;
--
Hope this Helps
Mir
On 10/5/2010 8:09 AM, Howard Latham wrote:
> Anyone point me in the right direction for an 11g Linux Script to
> monitor Dataguard lag?
>
> I have googled and looked on oracle Support.
>
> --
> Howard A. Latham
>
> Sent from my Nokia N97
>
> !DSPAM:2052,4cab240948768356213163!
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
