Monitor Oracle Streaming Setup

Health Checkup Report

download from MetaLink-
streams_hc_11_2_0_2.sql

sqlplus /nolog

SQL> spool /tmp/mysite.html
SQL> @streams_hc_11_2_0_2.sql
SQL> exit

Monitor Tables Count

Node-1 chk_cnt_pdb.sql

connect myschema/*********@pdb
SET SERVEROUTPUT ON;
!rm /home/oracle/monstreaming/pdb_count.log
spool '/home/oracle/monstreaming/pdb_count.log'
DECLARE
    V_CNT INT:=0;
    STMT VARCHAR(4000);
BEGIN

    FOR CNT IN (SELECT TNAME FROM TAB ORDER BY TNAME)
    LOOP

     DBMS_OUTPUT.PUT_LINE('SELECT COUNT(1) FROM  '|| CNT.TNAME);

     STMT := 'SELECT COUNT(1) FROM '|| CNT.TNAME;

     EXECUTE IMMEDIATE STMT INTO V_CNT;

     DBMS_OUTPUT.PUT_LINE('COUNT:==>  '|| V_CNT);

    END LOOP;

END;
/
@chk_cnt_sdb.sql
spool off;
exit;

Node-2 chk_cnt_sdb.sql

connect myschema/********@sdb
SET SERVEROUTPUT ON;
spool '/home/oracle/monstreaming/sdb_count.log'
DECLARE
    V_CNT INT:=0;
    STMT VARCHAR(4000);
BEGIN

    FOR CNT IN (SELECT TNAME FROM TAB ORDER BY TNAME)
    LOOP

     DBMS_OUTPUT.PUT_LINE('SELECT COUNT(1) FROM  '|| CNT.TNAME);

     STMT := 'SELECT COUNT(1) FROM '|| CNT.TNAME;

     EXECUTE IMMEDIATE STMT INTO V_CNT;

     DBMS_OUTPUT.PUT_LINE('COUNT:==>  '|| V_CNT);

    END LOOP;

END;
/
spool off;
exit;

SQL Script- check_status_streaming.sql

--Capture Process Status Query
SELECT CAPTURE_NAME,QUEUE_NAME,STATUS,ERROR_NUMBER,ERROR_MESSAGE FROM DBA_CAPTURE;

SELECT CAPTURE_NAME,STATE FROM V$STREAMS_CAPTURE;

--Propagation Process Status Query
SELECT PROPAGATION_NAME,STATUS,ERROR_MESSAGE,ERROR_DATE FROM DBA_PROPAGATION;

--Apply Process Status Query
SELECT APPLY_NAME,STATUS,ERROR_NUMBER,ERROR_MESSAGE FROM DBA_APPLY;

--DBA_APPLY_ERROR
SELECT APPLY_NAME,QUEUE_NAME,ERROR_NUMBER,ERROR_MESSAGE,ERROR_CREATION_TIME  FROM DBA_APPLY_ERROR;

SELECT COUNT(1)  FROM DBA_APPLY_ERROR;

Shell Script- rpt_streaming.sh

#!/bin/sh
. /home/oracle/.bash_profile
sqlplus /nolog << EOF
CONNECT strmadmin/********@tlkmdb1
set serveroutput on
set markup HTML ON entmap off
set lines 180
set pages 9999
spool on
spool `date '+rpt_pdb_streaming_%d%m%y%H%M%S'`.html
@check_status_streaming.sql
SPOOL OFF
EXIT;
EOF
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s