create or replace PROCEDURE STREAMS_MONITORING AS
v_cap_status number;
v_prop_status number;
v_apply_status number;
v_apply_err_amt number;
v_subject VARCHAR2(250);
v_message VARCHAR2(500);
BEGIN

v_apply_err_amt :=0;
-- Check the status of stream capture
SELECT DECODE(STATUS, 'ENABLED', 1, 'DISABLED', 0 ) into v_cap_status
FROM DBA_CAPTURE
where capture_name ='STRM_CAPTURE';

DBMS_OUTPUT.PUT_LINE('Check the status of stream capture' ||v_cap_status );
if v_cap_status <> 1
then
admin.p_send_mail( sender=>'db-...-r3.idc.com',recipient=>'db-monitoring@amazon.com', ccrecipient=>'weifengz@amazon.com', subject=>'[FC1CN] Replication Failure: Replication Process From FC1CN to FCDW1CN fail!',message=>'Replicaton Process failure, pls contact DBA immediate');
-- Send alarm email and then exit the monitoring
DBMS_OUTPUT.PUT_LINE('return');
return;
end if;

-- Check the status of stream propagation
SELECT DECODE(s.SCHEDULE_DISABLED, 'Y', 0, 'N', 1 ) into v_prop_status
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.DESTINATION_DBLINK = s.DESTINATION
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME;

DBMS_OUTPUT.PUT_LINE('Check the status of stream propagation' ||v_prop_status );
if v_prop_status <> 1
then
admin.p_send_mail( sender=>'db-...-r3.idc.com',recipient=>'db-monitoring@amazon.com', ccrecipient=>'weifengz@amazon.com', subject=>'[FC1CN] Replication Failure: Replication Process From FC1CN to FCDW1CN fail!',message=>'Replicaton Process failure, pls contact DBA immediate');
-- Send alarm email and then exit the monitoring
DBMS_OUTPUT.PUT_LINE('return');
return;
end if;

-- Check the status of apply
SELECT decode(status, 'ENABLED',1, 'DISABLED', 0) into v_apply_status
FROM DBA_APPLY@fcdw1cn.amazon
where apply_name ='STRM_APPLY';
DBMS_OUTPUT.PUT_LINE('Check the status of apply ' ||v_apply_status );

if v_apply_status <> 1
then
admin.p_send_mail( sender=>'db-...-r3.idc.com',recipient=>'db-monitoring@amazon.com', ccrecipient=>'weifengz@amazon.com',subject=>'[FC1CN] Replication Failure: Replication Process From FC1CN to FCDW1CN fail!',message=>'Replicaton Process failure, pls contact DBA immediate');
-- Send alarm email and then exit the monitoring
DBMS_OUTPUT.PUT_LINE('return');
return;
end if;

-- Check the apply error in destination database
select count(*) into v_apply_err_amt
from dba_apply_error@fcdw1cn.amazon
where apply_name ='STRM_APPLY';

DBMS_OUTPUT.PUT_LINE('----- BEGIN ERROR #' ||v_apply_err_amt );

if v_apply_err_amt > 0
then
SYS.DBMS_System.ksdwrt(2, 'ORA-00600: Replication Failure');
admin.p_send_mail( sender=>'db-...-r3.idc.com',recipient=>'db-monitoring@amazon.com', ccrecipient=>'weifengz@amazon.com', subject=>'[FC1CN] Replication Failure: Replication Process From FC1CN to FCDW1CN fail!',message=>'Replicaton Process failure, pls contact DBA immediate');
-- Send alarm email and then exit the monitoring
return;
end if;

EXCEPTION
WHEN OTHERS then
null ;

END;