Installing WSO2 Enterprise Service Bus (ESB) with Oracle

Applies To

1. WSO2 Enterprise Service Bus (ESB) v1.7.1
2. Oracle Database 10g (Enterprise/Standard Edition)

Prerequisite

* Working installation of oracle 10g.

Configuring ESB to support oracle DB requires only few steps. ESB create it's all the underlying DB tables automatically by itself with given database options.

Step 1: Download and extract WSO2 ESB 1.7.1 binary distribution.

Step 2: Create ESB database as wso2_esbdb. You can do this by either using oracle db configuration assistant (dbca) or manually.

Step 3: Do necessary changes to oracle tnsnames.ora file in order to define databases addresses for establishing connections to wso2_esbdb.

After creating the database. Your tnsnames.ora file should have a entry like below:

WSO2_ESBDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = wso2_esbdb)
    )
  )

And your listner.ora should contain

(SID_DESC =
      (GLOBAL_DBNAME = your global DB name)
      (ORACLE_HOME = your/oracle/home)
      (SID_NAME = wso2_esbdb)
    )

Note:Above setting may differ based on your oracle environment configuration.

Step 4:Connect to oracle as System DBA using SQL*Plus. Start SQL*Plus session by executing the "sqlplus" command-line utility. Please note that one must prepare the environment before starting sqlplus. It is recommended to set ORACLE_SID as wso2_esbdb before starting the sqlplus session.

$ sqlplus sys/sys-password as sysdba
Connected to: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
With the Partitioning, OLAP and Data Mining options

Now startup the oracle instance.

SQL> startup 

ORACLE instance started.
Total System Global Area  444596224 bytes 
Fixed Size                  1219880 bytes 
Variable Size             142607064 bytes 
Database Buffers          293601280 bytes 
Redo Buffers                7168000 bytes Database mounted. 
Database opened.

You can check the started oracle DB by issuing.

SQL> select sys_context('USERENV','DB_NAME') from dual;  SYS_CONTEXT('USERENV','DB_NAME') 
--------------------------------------------------------------------------------

wso2_esbdb

Step 5:

As SYS DBA Create a ESB DB user,and grant privileges to the user as below:

Create user esb_usr identified by esb_user_pw account unlock;  
/  
grant connect to esb_user;  
/  
grant create session, dba to esb_user;  
/  
commit;  
/

Check the connectivity to wso2_esbdb with new user login credentials.

$ sqlplus esb_user/esb_user_pw@wso2_esbdb
Connected to: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

Step 6:

Start oralcle listner

$ lsnrctl start

Step 7: Configure oracle database details in /webapp/WEB-INF/classes/conf/registry.xml.

Edit the file with following database configuration:

<dbconfig name="oracle-db">         
	<url>jdbc:oracle:thin:@hostname:1521:wso2_esbdb</url>         
	<username>esb_user</username>
	<password>esb_user_pw</password>
	<drivername>oracle.jdbc.driver.OracleDriver</drivername>     
</dbconfig> 

Edit the current database configuration in registry.xml, and set currnetConfig as oracle-db.

oracle-db

Step 8: Edit hibernate properties.Add the following properties to /webapp/WEB-INF/classes/conf/wso2esb.hibernate.cfg.xml, and comment the exiting derby properties.

	<property name="connection.url">jdbc:oracle:thin:@hostname:1521:esbdb</property>
	<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
	<property name="connection.username">esb_user</property>
	<property name="connection.password">esb_user_pw</property>
	<property name="dialect">org.hibernate.dialect.OracleDialect</property>
	<property name="hibernate.hbm2ddl.auto">update</property>

Step 9: Remove wso2registry-core-1.1.jar files from /webapp/WEB-INF/lib.

Step 10: Copy all oracle jdbc libraries to /webapp/WEB-INF/lib. Oracle jdbc libraries are available in $ORACLE_HOME/jdbc/lib

Step 11: Add following jar files in to /webapp/WEB-INF/lib.

wso2registry-core-SNAPSHOT.jar
compass-2.0.1.jar
lucene-core-2.3.2.jar

Download and extract wso2registry-SNAPSHOT-bin.zip from here. Go to the lib directory of registry binary distribution and get the above jar files.
Note: wso2registry-core-1.1.jar doesn't support for oracle.So you have to manually replace the jar files. In future releases you will be able to use ESB out of the box without copying wso2registry jar files.

Step 12: Finally,start ESB

sh wso2-esb.sh or wso2-esb.bat

Author:

Krishantha Samaraweera, Senior Software Engineer- Quality Assurance, krishantha AT wso2 dot com