How to configure Oracle database with WSO2 ESB, BAM and CEP clustered nodes

As depicted in the above diagram you need to create 5 new databases in Oracle (You can give any meaningful name).

For each database you need to run the oracle.sql file found under $ESB_HOME/dbscripts/ directory. This file has all the tables required by the usr_mgt and gov_registry databases (Both gov_registry and config_registry_<product> databases should have the same table structures.).Please note that all three ESB,CEP and BAM  packs has same content in the oracle.sql file found under dbscripts directory.

You can point to same database without having two databases for usr_mgt and gov_registry. But the recommended way is to have two different databases as depicted in the above diagram.


Note: I will follow the names i have mentioned in the diagram for convenience.


usr_mgt DB

1. create the database in Oracle.
# Modify below configurations by adding the new datasource:
    1. ESB
    $ESB_HOME/repository/conf/datasources/master-datasources.xml

    2. CEP
    $CEP_HOME/repository/conf/datasources/master-datasources.xml

    3. BAM
    $BAM_HOME/repository/conf/datasources/master-datasources.xml

# user-mgt.xml  refer this data source. Therefore modify the below configurations accordingly.
    1. EBS
    $ESB_HOME/repository/conf/user-mgt.xml

    2. CEP
    $CEP_HOME/repository/conf/user-mgt.xml

    3. BAM
    $BAM_HOME/repository/conf/user-mgt.xml

E.g:
Say the database name you have given is "usr_mgt" in Oracle.
1Modify each master-datasources.xml file by adding new datasource.

       <datasource>
            <name>WSO2_USR_MGT_DB</name>
            <description>The datasource used for user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2UsrMgtDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:oracle:thin:@localhost:1521:usr_mgt</url>
                    <username>user</username>
                    <password>user123</password>
                    <driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

Modify the user-mgt.xml by pointing to the above created database.

<Property name="dataSource">jdbc/WSO2UsrMgtDB</Property>

gov_registry DB

1. create the database in Oracle.
# Modify below configurations by adding the new datasource:
    1. ESB
    $ESB_HOME/repository/conf/datasources/master-datasources.xml

    2. CEP
    $CEP_HOME/repository/conf/datasources/master-datasources.xml

    3. BAM
    $BAM_HOME/repository/conf/datasources/master-datasources.xml

# Registry.xml should refer this datasource. Therefore modify the below configurations accordingly.
Modify the mount path aswell.
    1. EBS
    $ESB_HOME/repository/conf/registry.xml

    2. CEP
    $CEP_HOME/repository/conf/registry.xml

    3. BAM
    $BAM_HOME/repository/conf/registry.xml

E.g:
Say the database name you have given is "gov_registry" in Oracle.
Modify each master-datasources.xml file by adding new datasource.

        <datasource>
            <name>WSO2_GOV_REGISTRY_DB</name>
            <description>The datasource used for governance registry</description>
            <jndiConfig>
                <name>jdbc/WSO2GovRegistryDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:oracle:thin:@localhost:1521:gov_registry</url>
                    <username>user</username>
                    <password>user123</password>
                    <driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

Modify the registry.xml by pointing to the above created database.

<dbConfig name="governanceRegistry">
        <dataSource>jdbc/WSO2GovRegistryDB</dataSource>
</dbConfig>

<remoteInstance url="https://10.20.30.41:9443/registry">
    <id>governanceRegistryInstance</id>
    <dbConfig>governanceRegistry</dbConfig>
    <readOnly>true</readOnly>
    <registryRoot>/</registryRoot>
</remoteInstance>

<mount path="/_system/governance" overwrite="true">
    <instanceId>governanceRegistryInstance</instanceId>
    <targetPath>/_system/governance</targetPath>
</mount>

Config_registry DB

Each 2 ESB,CEP and BAM Instances should have one Config database as depicted in the diagram.
# Modify below configurations by adding the new datasource:
    1. ESB
    $ESB_HOME/repository/conf/datasources/master-datasources.xml

    2. CEP
    $CEP_HOME/repository/conf/datasources/master-datasources.xml

    3. BAM
    $BAM_HOME/repository/conf/datasources/master-datasources.xml

# Registry.xml should refer this data source. Therefore modify the below configurations accordingly.
Modify the mount paths aswell.
    1. EBS
    $ESB_HOME/repository/conf/registry.xml

    2. CEP
    $CEP_HOME/repository/conf/registry.xml

    3. BAM
    $BAM_HOME/repository/conf/registry.xml

E.g:
Consider 2 ESB instances.
Say the database name you have given is "config_registry_esb" in Oracle.
Modify each master-datasources.xml file by adding new datasource.

        <datasource>
            <name>WSO2_CONFIG_REG_ESB</name>
            <description>The datasource used for config registry</description>
            <jndiConfig>
                <name>jdbc/WSO2ConfigRegESB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:oracle:thin:@localhost:1521:config_registry_esb/url>
                    <username>user</username>
                    <password>user123</password>
                    <driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

Modify each registry.xml by pointing to the above created database.

<dbConfig name="configRegistry">
        <dataSource>jdbc/WSO2ConfigRegESB</dataSource>
</dbConfig>

<remoteInstance url="https://10.20.30.41:9443/registry">
    <id>configRegistryInstance</id>
    <dbConfig>configRegistry</dbConfig>
    <readOnly>true</readOnly>
    <registryRoot>/</registryRoot>
</remoteInstance>

<mount path="/_system/config" overwrite="true">
    <instanceId>configRegistryInstance</instanceId>
    <targetPath>/_system/nodes</targetPath>
</mount>

Note: You need to do the same for 2 CEP and 2 BAM instances.