Home » Server Options » Streams & AQ » Dare to Solve my Problem!(Propagation propagate but apply process does not get it)
Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257285] Wed, 08 August 2007 00:32 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

My propagation propagates but apply does not get messages. Where is wrong. If any kind hearted people there to solve my problem?

SELECT p.PROPAGATION_NAME, s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES
  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.SOURSQL> SQL> CE_QUEUE_NAME;SQL> SQL> SQL>   2    3    4    5

                     Total Time
Propagation           Executing Total Messages    Total Bytes
Name                 in Seconds     Propagated     Propagated
-------------------- ---------- -------------- --------------
PROP_SFDB_TO_LADB           159             83          69235
PROP_SFDB_TO_LADB             0              0              0



SQL> SELECT APPLY_NAME,
      TOTAL_RECEIVED,
      TOTAL_APPLIED,
      TOTAL_ERRORS,
      (TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED)) BEING_APPLIED,
      TOTAL_IGNORED
      FROM V$STREAMS_APPLY_COORDINATOR;  2    3    4    5    6    7

Apply          Total     Total  Total       Total     Total
Process        Trans     Trans  Apply Trans Being     Trans
Name        Received   Applied Errors     Applied   Ignored
---------- --------- --------- ------ ----------- ---------
APPLY_SRC_         0         0      0           0         0
SFDB




Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257295 is a reply to message #257285] Wed, 08 August 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you apply process started?

Check the result of:
COLUMN apply_name              FORMAT A20             HEADING 'Apply'
COLUMN process_name            FORMAT A07             HEADING 'Process'
COLUMN sid                     FORMAT A08             HEADING 'Sid'
COLUMN state                   FORMAT A17             HEADING 'State'
COLUMN total_messages_dequeued FORMAT 99G999G999G999  HEADING 'Nb Msg dequeued'
COLUMN total_applied           FORMAT 999G999G999G999 HEADING 'Total TX Applied' 
SELECT r.apply_name, substr(s.program, instr(s.program,'(')+1,4) process_name, 
       r.sid||','||r.serial# sid, r.state, 
       r.total_messages_dequeued, c.total_applied 
FROM v$streams_apply_reader r,  v$streams_apply_coordinator c,  
     v$session s, dba_apply ap 
WHERE r.sid = s.sid 
  AND r.serial# = s.serial# 
  AND r.apply_name = ap.apply_name 
  AND r.apply_name = c.apply_name 
ORDER BY 1
/

Regards
Michel
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257320 is a reply to message #257285] Wed, 08 August 2007 01:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> COLUMN apply_name              FORMAT A20             HEADING 'Apply'
COLUMN process_name            FORMAT A07             HEADING 'Process'
COLUMN sid                     FORMAT A08             HEADING 'Sid'
COLUMN state                   FORMAT A17             HEADING 'State'
COLUMN total_messages_dequeued FORMAT 99G999G999G999  HEADING 'Nb Msg dequeued'
COLUMN total_applied           FORMAT 999G999G999G999 HEADING 'Total TX Applied'
SELECT r.apply_name, substr(s.program, instr(s.program,'(')+1,4) process_name,
       r.sid||','||r.serial# sid, r.state,
       r.total_messages_dequeued, c.total_applied
FROM v$streams_apply_reader r,  v$streams_apply_coordinator c,
     v$session s, dba_apply ap
WHERE r.sid = s.sid
  AND r.serial# = s.serial#
  AND r.apply_name = ap.apply_name
  AND r.apply_name = c.apply_name
ORDER BY 1
/SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11

Apply                Process Sid      State             Nb Msg dequeued
-------------------- ------- -------- ----------------- ---------------
Total TX Applied
----------------
APPLY_SRC_SFDB       P000    128,424  DEQUEUE MESSAGES                0
               0


Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257322 is a reply to message #257320] Wed, 08 August 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$lock and v$session_wait to see if session 128 is locked or waiting on something.

Regards
Michel
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257323 is a reply to message #257285] Wed, 08 August 2007 01:44 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select state from v$session_wait where sid=128;

State
-----------------
WAITING
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257337 is a reply to message #257323] Wed, 08 August 2007 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Event column and p* ones are important to know on what it is waiting.

Regards
Michel
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257339 is a reply to message #257285] Wed, 08 August 2007 02:21 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select p1,p2,p3,wait_class,state,SECONDS_IN_WAIT,event,event# from v$session where sid=128;

        P1         P2         P3
---------- ---------- ----------
WAIT_CLASS
----------------------------------------------------------------
State             SECONDS_IN_WAIT
----------------- ---------------
EVENT                                                                EVENT#
---------------------------------------------------------------- ----------
     51405 1.5253E+10          3
Idle
WAITING                     40764
Streams AQ: waiting for messages in the queue                           266
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257344 is a reply to message #257285] Wed, 08 August 2007 02:27 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

But please see ,In this example source as treated as destination and dest is treated as source

SQL> select * from global_name
  2  ;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEST.WORLD

SELECT p.PROPAGATION_NAME,
       p.SOURCE_QUEUE_OWNER,
       p.SOURCE_QUEUE_NAME ||'@'||
       g.GLOBAL_NAME "Source Queue",
       p.DESTINATION_QUEUE_OWNER,
       p.DESTINATION_QUEUE_SQL> SQL> SQL> NAME ||'@'||
       p.DESTINATION_DBLINK "Destination Queue"
  FROM DBA_PROPAGATION p, GLOBAL_NAME g;SQL> SQL> SQL>   2    3    4    5    6    7    8

                     Source                     Dest
Propagation          Queue      Source          Queue      Destination
Name                 Owner      Queue           Owner      Queue
-------------------- ---------- --------------- ---------- ---------------
PROP_SFDB_TO_LADB    STRMADMIN  CAPTURE_SFQ@DES STRMADMIN  APPLY_LAQ@SOURC
                                T.WORLD                    E.WORLD





SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SOURCE.WORLD

SQL> select queue_name, queue_owner, apply_user from dba_apply;

Queue           Queue
Name            Owner APPLY_USER
--------------- ----- ------------------------------
APPLY_LAQ       STRMA STRMADMIN
                DMIN


[Updated on: Wed, 08 August 2007 02:31]

Report message to a moderator

Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257372 is a reply to message #257285] Wed, 08 August 2007 03:54 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel I am looking for you everywhere. Don't leave me alone putting on distress. Help me.
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257386 is a reply to message #257372] Wed, 08 August 2007 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have also my work.
I don't if Streams can work in a single database, I never test.
should not global_name be set to true?
This means that you can't have SOURCE.WORLD and DEST.WORLD db links for the same database.

Regards
Michel
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257391 is a reply to message #257285] Wed, 08 August 2007 04:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I am also in my office.

They are of course in two databases. Through two console I use to configure. The two databases is situated in two hosts.

In one console,
SQL> !hostname
terminus

SQL> show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE
SQL> select instance from v$thread;

INSTANCE
--------------------------------------------------------------------------------
source


In another console,

SQL>  !hostname
saturn

SQL> show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE
SQL> select instance from v$thread;

INSTANCE
--------------------------------------------------------------------------------
dest


Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257665 is a reply to message #257285] Wed, 08 August 2007 23:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel can we proceed more please. I don't have any client. Through two shell/console (connecting to two servers) I use to configure.
Re: Dare to Solve my Problem!(Propagation propagate but apply process does not get it) [message #257683 is a reply to message #257665] Thu, 09 August 2007 00:28 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I already told you, it is almost impossible to debug Streams environment when you are not on site and don't know the configuration.
I'm out of idea for the moment.

Regards
Michel
Previous Topic: Which SQL statement is captured/ propagated / applied.
Next Topic: Capture Process to Java JMS problem
Goto Forum:
  


Current Time: Thu Mar 28 08:35:47 CDT 2024