RE: Dataguard monitering

From: Juan Miranda <jcmiranda_at_oesia.com>
Date: Tue, 11 Dec 2012 10:03:15 +0100
Message-ID: <005b01cdd77e$5b09c330$111d4990$_at_oesia.com>

Hello

Maybe not the thing you are looking for but I use this script to monitor my standbys.

It´s configured to arch_lag_target <30 min.

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT SQL.SQLCODE
set pagesize 10 feedback off verify off heading on echo off linesize 200 serveroutput on trimspool on

variable sql_exit_sts number
spool &1 append
exec

dbms_output.put_line('======================================================
===========================');

column RECOVERY_ON format A11
column DB_NAME format A10
column CUR_SCN format A10
select TO_CHAR(SYSDATE, 'DD/MM/YYYY-HH24:MI:SS') fecha,name DB_NAME, open_mode, decode ((select count(1) from v$managed_standby where process like 'MRP%'),0,'NO','YES') RECOVERY_ON, database_role, to_char(current_scn) CUR_SCN from v$database;
set heading off pagesize 0
select (' ') from dual;
-- received last 2 hours

select 'Received 2ulthoras: ' || count(*) RECEIVED_2ULTHORAS from v$dataguard_status where SEVERITY='Warning' and FACILITY='Remote File Server' and MESSAGE like '%Successfully opened standby log%' and ERROR_CODE=0 and (sysdate-timestamp < ((1/24)*2));

• applied last 2 hours select 'Applied 2ulthoras: ' || count(*) APPLIED_2ULTHORAS from v$dataguard_status where SEVERITY='Informational' and FACILITY='Log Apply Services' and MESSAGE like 'Media Recovery Log%' and ERROR_CODE=0 and (sysdate-timestamp < ((1/24)*2));
• last received select 'last received: ' || max(SEQUENCE#) ULT_RECIBIDO from v$archived_log;
• last applied select 'last applied: ' || max(SEQUENCE#) ULT_APLICADO from v$archived_log where applied='YES';
• arch to apply select 'ARCH to apply: ' || ((select max(SEQUENCE#) from v$archived_log)-(select max(SEQUENCE#) from v$archived_log where applied='YES')) ARCH_FALTA_APLICAR from dual;
• MB to apply select 'MB to apply: ' || round(sum((blocks*block_size))/1024/1024,2) MB_FALTA_APLICAR from v$archived_log where applied<>'YES' and SEQUENCE# > (select max(SEQUENCE#) ULT_APLICADO from v$archived_log where applied='YES');
• ERROR or FATAL messages in the last 24 hours select 'ATENCION: ' || message ATENCION, severity, to_char(timestamp, 'DD/MM/YYYY-HH24:MI:SS') from v$dataguard_status where severity in ('Error','Fatal') and (sysdate-timestamp < ((1/24)*24));

declare
arch_applied number;
arch_received number;
error_fatal number;

begin
:sql_exit_sts := 0;
select count(*) into arch_received from v$dataguard_status where SEVERITY='Warning' and FACILITY='Remote File Server' and MESSAGE like '%Successfully opened standby log%' and ERROR_CODE=0 and (sysdate-timestamp < ((1/24)*2));
--select count(*) into arch_received from v$dataguard_status where SEVERITY='Warning' and FACILITY='Remote File Server' and MESSAGE like '%No standby redo logfiles created%' and ERROR_CODE=0 and (sysdate-timestamp < ((1/24)*2));
select count(*) into arch_applied from v$dataguard_status where SEVERITY='Informational' and FACILITY='Log Apply Services' and MESSAGE like 'Media Recovery Log%' and ERROR_CODE=0 and (sysdate-timestamp < ((1/24)*2)); select count(*) into error_fatal from v$dataguard_status where severity in ('Error','Fatal') and (sysdate-timestamp < ((1/24)*24));

dbms_output.put_line('Received 2 ultimas horas: ' || arch_received );
dbms_output.put_line('Applied 2 ultimas horas: ' || arch_applied );
dbms_output.put_line('Errores stby ultimas 24hr: ' || error_fatal );

if arch_received < 1 then

   dbms_output.put_line('ERROR en recepcion archivelogs');    :sql_exit_sts := 111;
end if;

if arch_applied < 1 then
   dbms_output.put_line('ERROR en aplicacion archivelogs');
   :sql_exit_sts := :sql_exit_sts + 222;

end if;

if error_fatal > 1 then

   dbms_output.put_line('ERROR en standby');    :sql_exit_sts := :sql_exit_sts + 333; end if;

end;
/
spool off
exit :sql_exit_sts

-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Howard Latham
Enviado el: jueves, 29 de noviembre de 2012 11:28 Para: ORACLE-L
Asunto: Dataguard monitering

Anyone know of a decent tool for monitoring Dataguard Maybe like spotlight where you can watch the log files being transmitted , spot bottlenecks etc. Oracle 11 Redhat Linux

BTW please stop send test/ do not reply to this group its very annoying,

--
Howard A. Latham


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


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

Received on Tue Dec 11 2012 - 10:03:15 CET