Ajanthan Eliyathamby's Tech Thoughts: Integrating WSO2 ESB 5.0.0 with Oracle Advanced Queue ( AQ ) ( PART1 )

This Article explains how we can configure WSO2 ESB 5.0.0 with Oracle Advanced Queue. The Flow we are going to integrate here is:

Contents Included:

1) What is Oracle AQ ( Advanced Queue )?

2) Setting up AQ in Oracle Database
3) Configuring LDAP Server
4) Configuring WSO2 ESB 5.0.0

1) What is Oracle AQ ( Advanced Queue )?

Oracle Advanced Queue is a message-oriented middleware, which provides database-integrated message queuing functionality. As this integrated with the database, the functional benefits of database can be obtained while using this.

Messages can be queried using standard SQL. This means that you can use SQL to access the message properties, the message history, and the payload. With SQL access you can also audit and track messages. All available SQL technology, such as indexes, can be used to optimize access to messages.

Above information extracted from [1] and for more information can refer [1] and [2].

[1] https://docs.oracle.com/database/122/ADQUE/aq-introduction.htm#ADQUE2419

[2] http://www.akadia.com/services/ora_advanced_queueing.html#The%20Publish-Subscribe%20Model

2) Setting up Advanced Queue in Oracle Database

Queue Creation

1) First we need to create role, here we are going to create two roles, one is for administrator and other is for the users with the below privileges.

Execute the below commands to create the two roles:

sqlplus / as sysdba;

CREATE ROLE aqesb_adm_role;
GRANT CONNECT, RESOURCE, aq_administrator_role TO aqesb_adm_role;

CREATE USER esbaqadmin IDENTIFIED BY esbaqadmin;

GRANT aqesb_adm_role TO esbaqadmin; 

ALTER user esbaqadmin quota 50m on system;

Now log in as our AQ Administrator and execute the below commands.

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (Queue_table => 'ESBAQADMIN.MESSAGE_QUEUE_TABLE', Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE');

EXECUTE DBMS_AQADM.CREATE_QUEUE (Queue_name => 'ESBAQADMIN.MESSAGE_QUEUE', Queue_table => 'ESBAQADMIN.MESSAGE_QUEUE_TABLE');

EXECUTE DBMS_AQADM.START_QUEUE (Queue_name => 'ESBAQADMIN.MESSAGE_QUEUE');

Below are some useful commands to drop the queue and queue table:


EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'ESB_QUEUE_MESSAGE_TABLE');

EXECUTE DBMS_AQADM.DROP_QUEUE (Queue_name => 'MESSAGE_QUEUE');

Now our oracle queue is ready for use.

When you connect through Oracle SQL Developer using esbaqadmin account, you will get the below screen.

3) Configuring LDAP Server

Here I'm using ApacheDS [1] ldap server and the Apache Directory studio [2].

[1] http://directory.apache.org/apacheds/download/download-linux-bin.html

[2] http://directory.apache.org/studio/downloads.html

Installation details can be found at [3].

[3] https://technology.amis.nl/2014/10/22/getting-started-with-apacheds-ldap-server-and-directory-studio/

Follow the below screens to establish the connection between LDAP Server and Apache Directory Studio.

The default connection parameters ( username / password / port ) of ldap servers can be found at this link [4].

[4] https://directory.apache.org/studio/users-guide/ldap_browser/gettingstarted_create_connection.html

Here the default bind password is: secret

Creating own Partitions

In ApacheDS, partitions are used to organize directories into domains. More details on creating partitions can be found at http://directory.apache.org/apacheds/basic-ug/1.4.3-adding-partition.html

Before we start to create the partitions, first we need to load the needed object classes and schema of oracle.

To implement any open solution for Oracle LDAP, the Schema for Oracle Net Services must be imported into it.

Copy the below content to oracle_schema.ldif file.

# SCHEMA "ORACLE"
dn: cn=oracle, ou=schema
objectclass: metaSchema
objectclass: top
cn: oracle
m-dependencies: system
m-dependencies: core
m-dependencies: java

dn: ou=attributetypes, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: attributetypes

dn: m-oid=2.16.840.1.113894.2.1.100.1, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.1.100.1
m-name: orcldbaqgeneric
m-description: orcldbaqgeneric
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15

dn: m-oid=2.16.840.1.113894.2.1.100.2, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.1.100.2
m-name: orcldbaqobjname
m-description: orcldbaqobjname
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.2.1.100.3, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.1.100.3
m-name: orcldbaqobjowner
m-description: orcldbaqobjowner
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.2.1.100.4, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.1.100.4
m-name: orcldbaqobjtype
m-description: orcldbaqobjtype
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.2.1.100.5, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.1.100.5
m-name: orcldbaqpointerattr
m-description: orcldbaqpointerattr
m-equality: DistinguishedNameMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.12
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.7.1.1, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.7.1.1
m-name: orclVersion
m-description: orclVersion
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.7.1.4, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.7.1.4
m-name: orclServiceType
m-description: orclServiceType
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.1, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.1
m-name: orclNetSourceRoute
m-description: orclNetSourceRoute
m-syntax: 1.3.6.1.4.1.1466.115.121.1.5
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.2, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.2
m-name: orclNetLoadBalance
m-description: orclNetLoadBalance
m-syntax: 1.3.6.1.4.1.1466.115.121.1.5
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.3, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.3
m-name: orclNetFailover
m-description: orclNetFailover
m-syntax: 1.3.6.1.4.1.1466.115.121.1.5
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.12, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.12
m-name: orclNetDescName
m-description: orclNetDescName
m-equality: DistinguishedNameMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.12
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.6, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.6
m-name: orclNetServiceName
m-description: orclNetServiceName
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.13, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.13
m-name: orclNetDescString
m-description: orclNetDescString
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.14, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.14
m-name: orclNetAddressString
m-description: orclNetAddressString
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.15, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.15
m-name: orclNetProtocol
m-description: orclNetProtocol
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.16, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.16
m-name: orclNetShared
m-description: orclNetShared
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15
m-singleValue: TRUE

dn: m-oid=2.16.840.1.113894.3.1.17, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.17
m-name: orclNetAddrList
m-description: orclNetAddrList
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15

dn: m-oid=2.16.840.1.113894.3.1.22, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.22
m-name: orclNetSendBufSize
m-description: orclNetSendBufSize
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15

dn: m-oid=2.16.840.1.113894.3.1.23, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.23
m-name: orclNetReceiveBufSize
m-description: orclNetReceiveBufSize
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15

dn: m-oid=2.16.840.1.113894.3.1.24, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.24
m-name: orclNetFailoverModeString
m-description: orclNetFailoverModeString
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15

dn: m-oid=2.16.840.1.113894.3.1.25, ou=attributetypes, cn=oracle, ou=schema
objectclass: metaAttributeType
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.1.25
m-name: orclNetInstanceRole
m-description: orclNetInstanceRole
m-equality: caseIgnoreMatch
m-syntax: 1.3.6.1.4.1.1466.115.121.1.15

dn: ou=comparators, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: comparators

dn: ou=ditcontentrules, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: ditcontentrules

dn: ou=ditstructurerules, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: ditstructurerules

dn: ou=matchingrules, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: matchingrules

dn: ou=matchingruleuse, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: matchingruleuse

dn: ou=nameforms, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: nameforms

dn: ou=normalizers, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: normalizers

dn: ou=objectclasses, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: objectClasses

dn: m-oid=2.16.840.1.113894.2.2.100.2, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.2.100.2
m-name: orclDBAQConnection
m-supObjectClass: javaContainer
m-must: cn
m-may: orcldbaqgeneric
m-may: orclVersion

dn: m-oid=2.16.840.1.113894.2.2.100.3, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.2.2.100.3
m-name: orclDBAQObject
m-supObjectClass: javaContainer
m-must: cn
m-must: orcldbaqobjtype
m-may: orcldbaqobjowner
m-may: orcldbaqobjname
m-may: orcldbaqpointerattr
m-may: orcldbaqgeneric
m-may: orclVersion

dn: m-oid=2.16.840.1.113894.3.2.1, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.2.1
m-name: orclNetAddress
m-supObjectClass: top
m-must: cn
m-may: orclNetAddressString
m-may: orclNetProtocol
m-may: orclNetShared
m-may: orclVersion
m-may: description

dn: m-oid=2.16.840.1.113894.3.2.2, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.2.2
m-name: orclNetAddressList
m-supObjectClass: top
m-must: cn
m-may: orclNetAddrList
m-may: orclNetSourceRoute
m-may: orclNetLoadBalance
m-may: orclNetFailover
m-may: orclNetShared
m-may: orclVersion
m-may: description

dn: m-oid=2.16.840.1.113894.3.2.3, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.2.3
m-name: orclNetDescription
m-supObjectClass: top
m-must: cn
m-may: orclNetServiceName
m-may: orclVersion
m-may: orclNetFailover
m-may: orclNetAddrList
m-may: orclNetShared
m-may: orclNetLoadBalance

dn: m-oid=2.16.840.1.113894.3.2.5, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.2.5
m-name: orclNetService
m-supObjectClass: top
m-must: cn
m-may: orclNetDescName
m-may: orclNetDescString
m-may: orclVersion

dn: m-oid=2.16.840.1.113894.3.2.6, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.2.6
m-name: orclNetServiceAlias
m-supObjectClass: alias
m-must: cn

dn: m-oid=2.16.840.1.113894.3.2.7, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.3.2.7
m-name: orclNetDescriptionAux1
m-supObjectClass: top
m-typeObjectClass: AUXILIARY
m-may: orclNetSendBufSize
m-may: orclNetReceiveBufSize
m-may: orclNetFailoverModeString
m-may: orclNetInstanceRole

dn: m-oid=2.16.840.1.113894.7.2.2, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.7.2.2
m-name: orclContainer
m-supObjectClass: top
m-must: cn
m-may: orclServiceType
m-may: orclVersion

dn: m-oid=2.16.840.1.113894.7.2.3, ou=objectclasses, cn=oracle, ou=schema
objectclass: metaObjectClass
objectclass: metaTop
objectclass: top
m-oid: 2.16.840.1.113894.7.2.3
m-name: orclContext
m-supObjectClass: top
m-must: cn

dn: ou=syntaxcheckers, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: syntaxcheckers

dn: ou=syntaxes, cn=oracle, ou=schema
objectclass: organizationalUnit
objectclass: top
ou: syntaxes

Now we need to load this to the ou=schema. To do that right click on it and follow the screens.

Now we have loaded the required classes. Now we can go ahead and create the partition.

Follow the below screens to create partition.

Go to partitions tab and create the new partition.

Save the configuration now, and restart the server.

oracle@ajanthan-ThinkPad-T440p:~/Pictures$ sudo /etc/init.d/apacheds-2.0.0-M23-default restart
Stopping ApacheDS - default...
Waiting for ApacheDS - default to exit...
Stopped ApacheDS - default.
Starting ApacheDS - default...
oracle@ajanthan-ThinkPad-T440p:~/Pictures$