Streams Replication| Monitoring Oracle Streams Apply Processes at Destination Server

Following shell script will be handy when you are working with Streams Replication.  There are 2 versions, one for 10g:monitor_streams_applyprocess_10g and another for 11g: monitor_streams_applyprocess_11g

#!/bin/bash
## Monitoring Oracle Streams Apply Processes at Destination Server
export STREAMS_ADMIN_SCHEMA=STRMADMIN
export STREAMS_ADMIN_SCHEMA_PWD=STRMADMIN
export DEST_DB=streams1

echo "-------------------Menu---------------------------"
echo " **** This script was tested against RHEL/Oracle 10gR2, Please update script variables with right database values ***"
echo "To run option 16 you will need some plsql procedures in place, detailed at http://www.oracledbasupport.co.uk/streams-displaying-detailed-information-about-apply-errors/"
echo ""
flag=0
while [ $flag -ne 17 ]
do
echo "1.  Determining the Queue, Rule Sets, and Status for Each Apply Process"
echo "2.  Displaying General Information About Each Apply Process"
echo "3.  Listing the Parameter Settings for Each Apply Process"
echo "4.  Displaying Information About Apply Handlers"
echo "5.  Displaying Information About the Reader Server for Each Apply Process"
echo "6.  Monitoring Transactions and Messages Spilled by Each Apply Process"
echo "7.  Determining Capture to Dequeue Latency for a Message"
echo "8.  Displaying General Information About Each Coordinator Process"
echo "9.  Displaying Information About Transactions Received and Applied"
echo "10. Determining the Capture to Apply Latency for a Message for Each Apply Process"
echo "11. Displaying Information About the Apply Servers for Each Apply Process"
echo "12. Displaying Effective Apply Parallelism for an Apply Process"
echo "13. Viewing Rules that Specify a Destination Queue on Apply"
echo "14. Viewing Rules that Specify No Execution on Apply"
echo "15. Checking for Apply Errors"
echo "16. Displaying Detailed Information About Apply Errors"
echo "17. Exit"

echo "Enter the option:"

read flag

echo "*******************************************************"
if [ $flag -eq 1 ]
then
#### Display this general information about each apply process in a database, run the following query:
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15

SELECT APPLY_NAME,
       QUEUE_NAME,
       RULE_SET_NAME,
       NEGATIVE_RULE_SET_NAME,
       STATUS
  FROM DBA_APPLY;
exit
EOF
fi

if [ $flag -eq 2 ]
then
#### To display this general information about each apply process in a database, run the following query:
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN APPLY_CAPTURED HEADING 'Type of Messages Applied' FORMAT A25
COLUMN APPLY_USER HEADING 'Apply User' FORMAT A30

SELECT APPLY_NAME,
       DECODE(APPLY_CAPTURED,
              'YES', 'Captured',
              'NO',  'User-Enqueued') APPLY_CAPTURED,
       APPLY_USER
  FROM DBA_APPLY;
exit
EOF
fi

if [ $flag -eq 3 ]
then
####   The following query displays the current setting for each apply process parameter for each apply process in a database:
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN PARAMETER HEADING 'Parameter' FORMAT A25
COLUMN VALUE HEADING 'Value' FORMAT A20
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A15

SELECT APPLY_NAME,
       PARAMETER,
       VALUE,
       SET_BY_USER
  FROM DBA_APPLY_PARAMETERS;
exit
EOF
fi

if [ $flag -eq 4 ]
then
#### To display the error handler for each apply process that applies changes locally in a database, run the following query:
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A10
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A30
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15

SELECT OBJECT_OWNER,
       OBJECT_NAME,
       OPERATION_NAME,
       USER_PROCEDURE,
       APPLY_NAME
  FROM DBA_APPLY_DML_HANDLERS
  WHERE ERROR_HANDLER = 'Y'
  ORDER BY OBJECT_OWNER, OBJECT_NAME;

-- To display each message handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20;
COLUMN MESSAGE_HANDLER HEADING 'Message Handler' FORMAT A20;

SELECT APPLY_NAME, MESSAGE_HANDLER FROM DBA_APPLY
  WHERE MESSAGE_HANDLER IS NOT NULL;

-- To display each precommit handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN PRECOMMIT_HANDLER HEADING 'Precommit Handler' FORMAT A30
COLUMN APPLY_CAPTURED HEADING 'Type of|Messages|Applied' FORMAT A15

SELECT APPLY_NAME,
       PRECOMMIT_HANDLER,
       DECODE(APPLY_CAPTURED,
              'YES', 'Captured',
              'NO',  'User-Enqueued') APPLY_CAPTURED
  FROM DBA_APPLY
  WHERE PRECOMMIT_HANDLER IS NOT NULL;

exit
EOF
fi

if [ $flag -eq 5 ]
then
#### The information displayed by this query is valid only for an enabled apply process.
#### Run the following query to display this information for each apply process:
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN APPLY_CAPTURED HEADING 'Dequeues Captured|Messages?' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Messages|Dequeued' FORMAT 99999999

SELECT r.APPLY_NAME,
       ap.APPLY_CAPTURED,
       SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
       r.STATE,
       r.TOTAL_MESSAGES_DEQUEUED
       FROM V\$STREAMS_APPLY_READER r, V\$SESSION s, DBA_APPLY ap
       WHERE r.SID = s.SID AND
             r.SERIAL# = s.SERIAL# AND
             r.APPLY_NAME = ap.APPLY_NAME;

exit
EOF
fi

if [ $flag -eq 6 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
-- select "Display information for each apply process in a database" from dual;
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A20
COLUMN 'Transaction ID' HEADING 'Transaction ID' FORMAT A15
COLUMN FIRST_SCN HEADING 'First SCN'   FORMAT 99999999
COLUMN MESSAGE_COUNT HEADING 'Message Count' FORMAT 99999999

SELECT APPLY_NAME,
       XIDUSN ||'.'||
       XIDSLT ||'.'||
       XIDSQN "Transaction ID",
       FIRST_SCN,
       MESSAGE_COUNT
  FROM DBA_APPLY_SPILL_TXN;

COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A15
COLUMN TOTAL_MESSAGES_SPILLED HEADING 'Total|Spilled Messages' FORMAT 99999999
COLUMN ELAPSED_SPILL_TIME HEADING 'Elapsed Time|Spilling Messages' FORMAT 99999999.99

SELECT APPLY_NAME,
       TOTAL_MESSAGES_SPILLED,
       (ELAPSED_SPILL_TIME/100) ELAPSED_SPILL_TIME
  FROM V\$STREAMS_APPLY_READER;
exit
EOF
fi

if [ $flag -eq 7 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATION HEADING 'Message Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 9999999999

SELECT APPLY_NAME,
     (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
     TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,
     TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
     DEQUEUED_MESSAGE_NUMBER
  FROM V\$STREAMS_APPLY_READER;
exit
EOF
fi

if [ $flag -eq 8 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Coordinator|Process|Name' FORMAT A11
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A21

SELECT c.APPLY_NAME,
       SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
       c.SID,
       c.SERIAL#,
       c.STATE
       FROM V\$STREAMS_APPLY_COORDINATOR c, V\$SESSION s
       WHERE c.SID = s.SID AND
             c.SERIAL# = s.SERIAL#;
exit
EOF
fi

if [ $flag -eq 9 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN TOTAL_RECEIVED HEADING 'Total|Trans|Received' FORMAT 99999999
COLUMN TOTAL_APPLIED HEADING 'Total|Trans|Applied' FORMAT 99999999
COLUMN TOTAL_ERRORS HEADING 'Total|Apply|Errors' FORMAT 9999
COLUMN BEING_APPLIED HEADING 'Total|Trans Being|Applied' FORMAT 99999999
COLUMN UNASSIGNED_COMPLETE_TXNS HEADING 'Total|Unnasigned|Trans' FORMAT 99999999
COLUMN TOTAL_IGNORED HEADING 'Total|Trans|Ignored' FORMAT 99999999

SELECT APPLY_NAME,
       TOTAL_RECEIVED,
       TOTAL_APPLIED,
       TOTAL_ERRORS,
       (TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED)) BEING_APPLIED,
       TOTAL_IGNORED
       FROM V\$STREAMS_APPLY_COORDINATOR;
exit
EOF
fi

if [ $flag -eq 10 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 9999999999

SELECT APPLY_NAME,
     (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
     TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
        "Message Creation",
     TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
     HWM_MESSAGE_NUMBER
  FROM V\$STREAMS_APPLY_COORDINATOR;

-- select "Following query will display capture to apply latency using DBA_APPLY_PROGRESS view for a captured LCR for each apply process" from dual;
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 9999999999

SELECT APPLY_NAME,
     (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
     TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
        "Message Creation",
     TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
     APPLIED_MESSAGE_NUMBER
  FROM DBA_APPLY_PROGRESS;

exit
EOF
fi

if [ $flag -eq 11 ]
then
#### Run the following query to display information about the apply servers for each apply process:
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A22
COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_ASSIGNED HEADING 'Total|Transactions|Assigned' FORMAT 99999999
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total|Messages|Applied' FORMAT 99999999

SELECT r.APPLY_NAME,
       SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
       r.STATE,
       r.TOTAL_ASSIGNED,
       r.TOTAL_MESSAGES_APPLIED
  FROM V\$STREAMS_APPLY_SERVER R, V\$SESSION S
  WHERE r.SID = s.SID AND
        r.SERIAL# = s.SERIAL#
  ORDER BY r.APPLY_NAME, r.SERVER_ID;
exit
EOF
fi

if [ $flag -eq 12 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
-- select "The following query displays the effective apply parallelism for an apply process named apply:" from dual;

SELECT COUNT(SERVER_ID) "Effective Parallelism"
  FROM V\$STREAMS_APPLY_SERVER
  WHERE APPLY_NAME = 'APPLY' AND
        TOTAL_MESSAGES_APPLIED > 0;

-- select "You can display the total number of messages applied by each apply server by running the following query" from dual;
COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total Messages Applied' FORMAT 999999
SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED
  FROM V\$STREAMS_APPLY_SERVER
  WHERE APPLY_NAME = 'APPLY'
  ORDER BY SERVER_ID;
exit
EOF
fi

if [ $flag -eq 13 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
-- select "To view destination queue settings for rules, run the following query:" from dual;
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN DESTINATION_QUEUE_NAME HEADING 'Destination Queue' FORMAT A30
SELECT RULE_OWNER, RULE_NAME, DESTINATION_QUEUE_NAME
  FROM DBA_APPLY_ENQUEUE;
exit
EOF
fi

if [ $flag -eq 14 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
-- select "To view each rule with NO for its execution directive, run the following query:" from dual;
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
SELECT RULE_OWNER, RULE_NAME
  FROM DBA_APPLY_EXECUTE
  WHERE EXECUTE_EVENT = 'NO';
exit
EOF
fi

## Checking for Apply Errors
if [ $flag -eq 15 ]
then
sqlplus -s ${STREAMS_ADMIN_SCHEMA}/${STREAMS_ADMIN_SCHEMA_PWD}@${DEST_DB} <<EOF
-- select "To check for apply errors, run the following query:" from dual;
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A10
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20
COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999

SELECT APPLY_NAME,
       SOURCE_DATABASE,
       LOCAL_TRANSACTION_ID,
       ERROR_NUMBER,
       ERROR_MESSAGE,
       MESSAGE_COUNT
  FROM DBA_APPLY_ERROR;

exit
EOF
fi
done
echo "exit"