4 Replies Latest reply: May 9, 2012 2:16 AM by user178732 RSS

    Queue to Queue Propagation through DB link

    user178732
      Hi,

      I need to propagate msgs from queue at one DB to another queue at another DB using DB link. What is step-by-step workflow to acheive this. I need to propagate automaticly. Could you help me? I've tried it, but every my atempt failed. THX.

      J.
        • 1. Re: Queue to Queue Propagation through DB link
          damorgan
          No one can possibly help you based on what you've written which is why no one, so far, has responded. Here's what's missing.

          1. Oracle version number.
          2. DB Link definition.
          3. What you mean when you write: "I've tried it, but every my atempt failed"

          We have no idea what you've tried and I assume you do not want us to just guess though we can if you wish. ;-)

          When you run this query ... what comes back?
          SELECT queue_name, consumer_name, address, protocol, delivery_mode, queue_to_queue
          FROM dba_queue_subscribers;
          • 2. Re: Queue to Queue Propagation through DB link
            user178732
            Sorry for uncomplete first post.

            1. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
            2. CREATE PUBLIC DATABASE LINK link.test CONNECT TO user IDENTIFIED BY passwd
                 USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = service_name)))';
            3. I've tried it this way:
            BEGIN
              DBMS_AQADM.CREATE_QUEUE_TABLE( Queue_table => 'schema.testout_T', Queue_payload_type => 'schema.type', COMMENT => 'comment');
            END;
            /
            show errors
            
            BEGIN
              DBMS_AQADM.CREATE_QUEUE( Queue_name => 'schema.testout_Q', Queue_table => 'schema.testout_T', Queue_type => 0, Max_retries => 5, Retry_delay => 0, dependency_tracking => FALSE);
            END;
            /
            show errors
            
            BEGIN
              DBMS_AQADM.start_queue (
                   queue_name         => 'schema.testout_Q',
                   enqueue            => TRUE,
                   dequeue            => TRUE);
            END;
            /
            show errors
            
            BEGIN
            DBMS_AQADM.grant_queue_privilege (
               privilege     =>     'ALL',
               queue_name    =>     'schema.testout_Q',
               grantee       =>     'userpropag',
               grant_option  =>      FALSE);
            END;
            /
            show errors
            
            -- 1st atempt
            BEGIN
                DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
                propagation_name => 'testout_p'
                ,source_queue => 'schema.testout_Q'
                ,destination_queue => 'dest.testIN_Q'
                ,destination_dblink => 'link.test'
                ,queue_to_queue => false);
            END;
            / 
            
            --2nd atempt
            BEGIN
                DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
                propagation_name => 'testout_p'
                ,source_queue => 'schema.testout_Q'
                ,destination_queue => 'dest.testIN_Q'
                ,destination_dblink => 'test.link'
                ,queue_to_queue => TRUE);
            END;
            / 
            
            BEGIN
               DBMS_AQADM.SCHEDULE_PROPAGATION(
                  queue_name    =>    'schema.testout_Q', 
                  destination   =>    'test.link');
            END;
            /
            
            BEGIN
              DBMS_PROPAGATION_ADM.START_PROPAGATION(
                propagation_name => 'testout_p');
            END;
            /
            Return of query:
            SCHEDULER$_JOBQ     SCHEDULER_COORDINATOR          0     PERSISTENT     FALSE
            SCHEDULER$_JOBQ     SCHEDULER_PICKUP          0     PERSISTENT     FALSE
            SRVQUEUE     TAFTSM          0     PERSISTENT     FALSE
            ALERT_QUE     HAE_SUB          0     PERSISTENT     FALSE
            ALERT_QUE     xxx          0     PERSISTENT     FALSE
            SYS$SERVICE_METRICS     SYS$RLB_GEN_SUB          0     PERSISTENT     FALSE
            • 3. Re: Queue to Queue Propagation through DB link
              Mathias Zarick
              Hi Gocoo,

              do not use DBMS_PROPAGATION_ADM.CREATE_PROPAGATION. it is for setting up Streams Replications Propagations. You need to add remote subscribers to your queue. you do this with DBMS_AQADM.ADD_SUBSCRIBER, see Example 8-41 of this AQ Docs : http://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_admin.htm#i1006671
              after this you schedule the propagation with DBMS_AQADM.SCHEDULE_PROPAGATION. see Example 8-50.

              HTH Mathias