This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 12, 2012 11:47 AM by robn77 RSS

SOA Composite dequeue not removing message from the queue.

robn77 Newbie
Currently Being Moderated
We have a simple process that populates a queue that we have a SOA Composite dequeuing.

SOA processes the data just fine, but the message stays in the queue even after the process has done it's work. Then when the SOA Server is bounced, the messages all get reprocessed.

It looks like once they reach the max_retries of 5, they move into the AQ$_XX_RHM_QUEUE_TBL_E error queue.

This is our queue definition.

BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'APPS.XX_RHM_QUEUE'
,QUEUE_TABLE => 'APPS.XX_RHM_QUEUE_TBL'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 5
,RETRY_DELAY => 0
,RETENTION_TIME => 0
);
END;
/

BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'APPS.XX_RHM_QUEUE'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
  • 1. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    Hi,

    If its not dequeueing is because an exception is being throwed somewhere...

    Check your process, the adapters are transactional, if some exception is raised there will be a rollback and the message will be back on the queue.

    Another indication that there is an exception being raised is that max_retries is increasing!

    Cheers,
    Vlad

    It is considered good etiquette to reward answerers with points (as "helpful" - 5 pts - or "correct" - 10pts)
    https://forums.oracle.com/forums/ann.jspa?annID=893
  • 2. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    The SOA process dequeues and processes the data but the record stays on the queue. It does not dequeue again until the SOA server restarts.
  • 3. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    dequeues and processes the data but the record stays on the queue
    It may dequeue and process but it will wait for a transaction commit to remove the message from the queue...

    If you can't see any exceptions, maybe your dequeueing composite is getting stuck and not committing the transaction, when you restart the server it will rollback automatically... and reprocess when server starts again...

    The problem is probably in the SOA composite that is dequeing... Can you give us more details about it?

    It is considered good etiquette to reward answerers with points (as "helpful" - 5 pts - or "correct" - 10pts)
    https://forums.oracle.com/forums/ann.jspa?annID=893
  • 4. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    The SOA process completes successfully so there is no fault generated in SOA.

    This is a simple AQ adapter dequeue event to kick off the SOA process. It was created in 11.1.1.2 and we have upgraded to 11.1.1.5 so I am going to re-generate the adapter in 11.1.1.5 and see if that makes a difference.
  • 5. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    I just re-created the composite in 11.1.1.5 and it still does not remove the records from the queue after it dequeues.
  • 6. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    Do you think my answers are of any help? Please mark them accordingly if that's the case... If I'm not being helpful then it's just time wasted for both of us...

    It is considered good etiquette to reward answerers with points (as "helpful" - 5 pts - or "correct" - 10pts)
    https://forums.oracle.com/forums/ann.jspa?annID=893
  • 7. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    I responded earlier today that the SOA Process completes so it should commit the transaction. But it is not.

    I also created a test AQ composite that all it does is dequeue and then end. In both cases it pulls the message from the queue, but is still available to be picked up when the server is restarted.

    Any clue on how to get SOA to commit the transaction so that the message is removed from the queue?
  • 8. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    Yes, I was pretty sure the transaction wasn't being committed.

    I think that may be due a configuration problem, please check the XA configuration on the data sources involved.

    I'll do some more research on AQAdapter and let you know.

    Cheers,
    Vlad
  • 9. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    From AQ docs I got this...

    The XADataSourceName or DataSourceName property is the name of the underlying data source (which has the connection information). Specify one of the properties XADataSourceName or DataSourceName. The usage depends on if the scenario involves and would require adapter to participate in global transaction or if local transaction semantics are sufficient. In the former case XADataSourceName must be specified while in the latter case DataSourceName must be specified. When specifying XADataSourceName property ensure that the physical data source it refers to is XA enabled while when specifying DataSourceName property the physical data source it refers to might or might not be XA enabled.

    And this...

    dataSourceName
    Either this property or xADataSource name is a mandatory property, or both. Refers to the JNDI name (jdbc/...) of the tx-level="local" data source connecting to. All operations using this pool are locally transacted, independent on the global transaction. If both xADataSourceName and dataSourceName are specified, then the latter is used for READ operations.

    xADataSourceName
    This is a mandatory property. It specifies the JNDI name (jdbc/...) of the tx-level="global" data source connecting to the database. All operations using this pool bind to the global transaction and commit or roll back as a unit.

    References:
    http://docs.oracle.com/cd/E23943_01/integration.1111/e10231/adptr_aq.htm#BGBFJACA
    http://docs.oracle.com/cd/E23943_01/integration.1111/e10231/adptr_propertys.htm#CIHCHGJJ

    How is your adapter configured? I would try both and see which works out better...

    Cheers,
    Vlad

    It is considered good etiquette to reward answerers with points (as "helpful" - 5 pts - or "correct" - 10pts)
    https://forums.oracle.com/forums/ann.jspa?annID=893
  • 10. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    I created a non-XA JNDI and used my test process to use it. It processed the record in the queue and deleted it from the table.

    Now my question is why is this so?

    The process that we use in Production reads the data from the queue and then sends that data to two SQL Server databases to sync the data with two legacy systems.
    We want the transactional functionality of the XA transaction, but it looks like that is not possible in this situation.

    So how do we force the commit in the SOA process?
  • 11. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    We want the transactional functionality of the XA transaction, but it looks like that is not possible in this situation.
    Should be possible, but...
    sends that data to two SQL Server databases
    You should have mentioned that before... SQL Server is known as having XA issues when connecting via JDBC, see these
    http://www.atomikos.com/Documentation/ConfiguringSQLServer
    http://msdn.microsoft.com/en-us/library/aa342335(v=sql.90).aspx
    http://www.senthilb.com/2010/01/how-to-make-xa-datasource-work-in-mssql.html

    So now, option 1 would be get your XA configuration right, this will may require some intervention from your SQL Server DBAs.
    So how do we force the commit in the SOA process?
    There is no way that I'm aware of, you can not just "force" commit, what you have to do is to create two separated transactions, one for dequeueing and other for sending.

    Then, your option 2 is to work out the transactional behavior, what is not easy either, have a look at these links
    http://docs.oracle.com/cd/E23943_01/dev.1111/e10224/soa_transactions.htm#CIHHDDAH
    https://blogs.oracle.com/soabpm/entry/soa_suite_11g_-transactionsb

    Hope this was helpful.

    Cheers,
    Vlad
  • 12. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    Removing SQL Server from the equation.

    I created a test AQ Composite that all it does is dequeue and then exit There are no other adapters in the composite, but we could be using an Oracle DB Adapter to do some work and that would cause the same issue.

    When executing my test AQ composite, whenever I use the XA DataSource, the message stays on the queue. When I use the Non-XA DataSource, the message dequeue's successfully.

    So onto the links at the bottom of your last post.

    I tried the bpel.config.transaction property in the BPEL process, but because this composite entry point is the AQ Adapter service, there is no parent/child relationship to create a transaction for. I did try both cases and it did not dequeue successfully.

    So the behavior that I am looking for is for the AQ Adapter to dequeue, but only remove the message from the queue when the composite completes successfully. If the process falls into any of the catch blocks, then the record sould stay on the queue.
  • 13. Re: SOA Composite dequeue not removing message from the queue.
    vladodias Guru
    Currently Being Moderated
    That's a hard one, dude...

    At this point I believe you already checked this, but I will ask anyway... The JDBC driver you are using to connect to your Oracle AQ is a XA JDBC Driver? It should be...

    More links...
    http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/jdbc/jdbc_datasources/ConfigureTransactionOptionsForADataSource.html
    http://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/pagehelp/JDBCjdbcdatasourcesjdbcdatasourceconfigtransactiontitle.html
  • 14. Re: SOA Composite dequeue not removing message from the queue.
    robn77 Newbie
    Currently Being Moderated
    At this point I believe that the issue is with the data source configuration.

    I checked the Transactions tab for the Data Soure and the Supports Global Transactions box is unchecked. Now that should make all the transactions act like a local transaction shouldn't it?

    I am 99.9% sure that our driver is the XA driver. I am checking with the DBA's to be entirely sure.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points