I tried to setup streams on my machine with 2 databases. everything seems to be as per the troubleshoot doc, but i am not able to see the data propogated at the destination.
I queried DBA_QUEUE_SCHEDULES which shows following rows:
select SCHEMA, QNAME, DESTINATION, LATENCY, SCHEDULE_DISABLED, PROCESS_NAME, SESSION_ID, INSTANCE, NEXT_RUN_TIME, TOTAL_TIME, TOTAL_NUMBER from DBA_QUEUE_SCHEDULES;
SCHEMA QNAME DESTINATION LATENCY SCHEDULE_DISABLED PROCESS_NAME SESSION_ID INSTANCE NEXT_RUN_TIME TOTAL_TIME TOTAL_NUMBER
------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------------- ------------ --------------
STRMADMIN SCOTT_QUEUE TEST.PS3378.COM 3 N J002 117, 632 1 0 0
STRMADMIN SCOTT_QUEUE TEST.PS3378.COM 3 N J002 117, 632 1 3807 15
and output of qurery select TOTAL_BYTES,MAX_NUMBER,MAX_BYTES,AVG_NUMBER,AVG_SIZE,AVG_TIME,FAILURES from DBA_QUEUE_SCHEDULES is :
TOTAL_BYTES MAX_NUMBER MAX_BYTES AVG_NUMBER AVG_SIZE AVG_TIME FAILURES
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
0 0 0 0 0 0 0
3598 15 3598 15 239.866666666666666666666666666666666667 0 0
my doubt is why is DBA_QUEUE_SCHEDULES showing two rows. I was in impression that i should see only one row here.
Also when i observe into OEM i see all statistics of propogation as zero (0). propagation status is 'queue content is currently being propogated to this target' and corresponding values of 'total number of messages propogated', 'total number of bytes propogated' and 'total propogation time in seconds' are 0 each.
i don't see any error in alert log. all processes (capture, propogate and apply) are enabled. capture shows no. of messeges captured and other stats in OEM. I can't see any data in my SOURCE and DESTINATION queue.
here is a brief description of how i set up streams:
At Source:
. Created streamadmin user and tablespaces.
. Granted necessary privileges.
. created db link to destination database.
. created a table and added supplemental log group
. created queue using DBMS_STREAMS_ADM.SET_UP_QUEUE
. added propogation rules using DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES
. created capture using DBMS_STREAMS_ADM.ADD_TABLE_RULES
At Destination
. Created streamadmin user and tablespaces.
. Granted necessary privileges.
. created db link to destination database.
. created a table and added supplemental log group
. created queue using DBMS_STREAMS_ADM.SET_UP_QUEUE
. Set table instantiation SCN using DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN
. create an apply process using DBMS_STREAMS_ADM.ADD_TABLE_RULES
then apply is started at destination and capture is started at source
all stats for apply are 0. but V$STREAMS_APPLY_READER has one row with TOTAL_MESSAGES_DEQUEUED = 10.
Please throw some light over this problem.
thanks in advance
Deep