There are times when you already know or suspect where a problem may be in the Streams environment. So, having a handful of queries related to the main processes is useful. Here are some of the queries we keep in our toolbox and the reasoning on when to use each. The queries listed below are in Handy.sql
. The scripts are from source side to destination side.
-- CHECK ON THE STATUS AND IF THERE ARE
-- ANY ERRORS WITH CAPTURE PROCESS
SELECT CAPTURE_USER, CAPTURE_NAME, QUEUE_OWNER, QUEUE_NAME, STATUS,STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_CAPTURE;
-- TO CHECK ON THE BUFFER QUEUE PROPAGATION SCHEDULES ON THE
--SENDING (SOURCE) SIDE
SELECT QUEUE_ID, QUEUE_SCHEMA, QUEUE_NAME, STARTUP_TIME, PROPAGATION_NAME, DBLINK, STATE FROM V$PROPAGATION_SENDER;
-- CHECK ON THE PROPAGATION
SELECT PROPAGATION_NAME, SOURCE_QUEUE_OWNER, SOURCE_QUEUE_NAME, DESTINATION_QUEUE_OWNER, DESTINATION_QUEUE_NAME, DESTINATION_DBLINK, STATUS, ERROR_MESSAGE, ERROR_DATE FROM DBA_PROPAGATION;
-- CHECK ON THE BUFFER QUEUE PROPAGATION SCHEDULES ON THE SENDING (DESTINATION) SIDE
SELECT SRC_QUEUE_SCHEMA, SRC_QUEUE_NAME, SRC_DBNAME, DST_QUEUE_SCHEMA, DST_QUEUE_NAME, PROPAGATION_NAME, STATE FROM V$PROPAGATION_RECEIVER;
-- CHECK ON THE STATUS OF APPLY
SELECT APPLY_NAME, STATUS, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_APPLY;
-- CHECKING THE PROGRESS OF THE APPLY PROCESS