This is a test scenario in which a Proxy Service takes a message from one AQ JMS queue and puts it onto another using a Business Service. It’s designed simply as an example of what can be done to use OSB Services with Oracle AQ without the AQ adapter.
Part 1 – create required AQ components
1) Grant AQ privileges to the user we need for OSB.
grant connect, resource, aq_administrator_role to aq_osb identified by aq_osb;
grant select on sys.DBA_PENDING_TRANSACTIONS to aq_osb;
grant execute on sys.dbms_aqadm to aq_osb;
grant execute on sys.dbms_aqin to aq_osb;
grant execute on sys.dbms_aqjms to aq_osb;
exec dbms_aqadm.grant_system_privilege(‘ENQUEUE_ANY’,'aq_osb’)
exec dbms_aqadm.grant_system_privilege(‘DEQUEUE_ANY’,'aq_osb’)
2) Create the AQ JMS queues
As the newly created user, connect and create the JMS queues on AQ.
exec dbms_aqadm.create_queue_table(queue_table=>’inbound_table’,queue_payload_type=>’sys.aq$_jms_text_message’,multiple_consumers=>false);
exec dbms_aqadm.create_queue(queue_name=>’OSB_INBOUND_QUEUE’,queue_table=>’inbound_table’);
exec dbms_aqadm.start_queue(queue_name=>’OSB_INBOUND_QUEUE’);
exec dbms_aqadm.stop_queue(queue_name=>’OSB_INBOUND_QUEUE’);
exec dbms_aqadm.drop_queue(queue_name=>’OSB_INBOUND_QUEUE’);
exec dbms_aqadm.drop_queue_table(queue_table=>’inbound_table’);
exec dbms_aqadm.create_queue_table(queue_table=>’outbound_table’,queue_payload_type=>’sys.aq$_jms_text_message’,multiple_consumers=>false);
exec dbms_aqadm.create_queue(queue_name=>’OSB_OUTBOUND_QUEUE’,queue_table=>’outbound_table’);
exec dbms_aqadm.start_queue(queue_name=>’OSB_OUTBOUND_QUEUE’);
Part 2 – Create WLS Foreign JMS entries
To allow OSB to connect using the JMS proxy and business services, we need to access them via WLS Foreign JMS.
1) Create a Datasource that is pointing the database that is running the AQ tables. You must use an Oracle supplied thin driver for AQ integration to work.
It is import to note, that any proxy service subsequently created will required 16 database connections, as the MDB pool size for the proxy is set to be 16. The default maximum size within the datasource for development mode is 15 Connections. You will need to increase this or you will start to see errors similar
<18-May-2012 13:10:38 o’clock BST> <Warning> <EJB> <BEA-010096> <The Message-Driven EJB: RequestEJB2430846831066006898XX45ddd8c3.1375fa8568e.X7fda is unable to connect to the JMS destination or bind to JCA resource adapter: AQOutboundQueue. Connection failed after 93 attempts. The MDB will attempt to reconnect/rebind every 10 seconds. This log message will repeat every 600 seconds until the condition clears.>
<18-May-2012 13:10:38 o’clock BST> <Warning> <EJB> <BEA-010061> <The Message-Driven EJB: RequestEJB2430846831066006898XX45ddd8c3.1375fa8568e.X7fda is unable to connect to the JMS destination: AQOutboundQueue. The Error was:
oracle.jms.AQjmsException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool AQDataSource to allocate to applications, please increase the size of the pool and retry..
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:591)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:343)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:330)
…)
Nested exception: java.sql.SQLException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool AQDataSource to allocate to applications, please increase the size of the pool and retry..
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:591)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:343)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:330)
…)
and
####<18-May-2012 13:18:37 o’clock BST> <Info> <Common> <Jeeves> <AdminServer> <[ACTIVE] ExecuteThread: ’5′ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<WLS Kernel>> <> <80027211986643af:52465569:1375fbd7f7c:-8000-0000000000000188> <1337343517846> <BEA-000627> <Reached maximum capacity of pool “AQDataSource”, making “0″ new resource instances instead of “1″.>
Next, for transparency, create a new JMS module and target this to the admin server. Within this, create a new Foreign Server within the Module. Within this, supply the connection details. As we are using the data source to connect, supply the name of the data source that you created within the JNDI properties of the foreign server by adding the property datasource=<dataSourceName>.
Now we need to add the connection factories and the destination queue JNDI references to the Foreign JMS Server. Unlike other JMS providers, Oracle AQ uses the same 6 predefined connection factories. For the purposes of this walkthrough, we shall use the XAConnectionFactory as the remote JNDI name for WLS.
For the Foreign Server queues, we need to prefix Queues/ to the queue name that we used within AQ when we created the queues. This gives the remote JNDI names Queues/OSB_INBOUND_QUEUE and Queues/OSB_OUTBOUND_QUEUE.
At this point a restart of the server is necessary to ensure that everything is correctly bound to the JNDI tree.
Part 3 – create OSB business or proxy services
Now create the business service. As we created a queue tables with the payload type sys.aq$_jms_text_message we need to specify text as the message type. The resulting service looks like this.
Next we create a JMS Proxy Service. The resulting service will look like this.
Finally, for the purpose of this setup, create a route node to route the message from the proxy to the business service
Part 4 – How to test
To test this, you need to enter SQLPLUS as aq_osb and run the following command. This puts a message on the OUTBOUND AQ queue
DECLARE
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(16);
my_message sys.aq$_jms_text_message;
BEGIN
my_message := sys.aq$_jms_text_message.construct;
my_message.set_text(‘<test> some message </test>’);
DBMS_AQ.ENQUEUE(
queue_name => ‘OSB_OUTBOUND_QUEUE’,
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
COMMIT;
END;
/
To validate the test, run the following command and the OUTBOUND queue should have no entries, while the INBOUND queue should have one
select count(*) from inbound_table;
select count(*) from outbound_table;
Most of the issues that I encountered in setting this up involved incorrect targeting, incorrect JNDI names or simple human error. I would strongly suggest that if you can’t get this working, check the JNDI entries for the servers; check the targeting and review the naming before anything else.