

Setting up an Oracle Database¶

Follow the steps given below to set up the required Oracle databases for your Micro Integrator.

Tip

WSO2 Micro Integrator requires databases for the following scenarios:

Setting up the database and users¶

The following Oracle scripts are stored in the <MI_HOME>/dbscripts/oracle directory of your Micro Integrator. First, select the scripts that are required for your deployment.

You can run the scripts on one database instance or set up separate instances for each requirement. For convenience, it is recommended to set up separate databases for each use case.

Script	Description
oracle_cluster.sql	This script creates the database tables that are required for cluster coordination (i.e., coordinating the server nodes in your VM deployment). This is only applicable if you have stateful integration artifacts deployed in a clustered setup.
oracle_user.sql	This script creates the database tables that are required for storing users and roles. This is only required if you have configured an RDBMS user store.
oracle_transaction_count.sql	This script creates the database tables that are required for storing the transaction counts. This is only required if you want to monitor transaction counts in your deployment.

Create the databases and then create the DB tables by pointing to the relevant script in the <MI_HOME>/dbscripts/oracle directory.

Follow the steps below to set up an Oracle database.

Create a new database by using the Oracle database configuration assistant (dbca) or manually.
Make the necessary changes in the Oracle tnsnames.ora and listner.ora files in order to define addresses of the databases for establishing connections to the newly created database.
After configuring the .ora files, start the Oracle instance using the following command:
```
sudo /etc/init.d/oracle-xe restart
```

Connect to Oracle using SQL*Plus as SYSDBA as follows:

./$<ORACLE_HOME>/config/scripts/sqlplus.sh sysadm/password as SYSDBA

Connect to the instance with the username and password using the following command:
```
connect
```

As SYSDBA, create a database user and grant privileges to the user as shown below:

Create user <USER_NAME> identified by <PASSWORD> account unlock;
grant connect to <USER_NAME>;
grant create session, create table, create sequence, create trigger to <USER_NAME>;
alter user <USER_NAME> quota <SPACE_QUOTA_SIZE_IN_MEGABYTES> on '<TABLE_SPACE_NAME>';
commit;

Exit from the SQL*Plus session by executing the quit command.

Setting up the JDBC driver¶

Copy the Oracle JDBC libraries (for example, \< ORACLE_HOME/jdbc/lib/ojdbc14.jar) to the MI_HOME/lib/ directory.
Remove the old database driver from the MI_HOME/repository/components/dropins/ directory.

Tip

If you get a "timezone region not found" error when using the ojdbc6.jar file with the Micro Integrator, set the Java property as follows: export JAVA_OPTS="-Duser.timezone='+05:30'" the value of this property should be the GMT difference of the country. If it is necessary to set this property permanently, define it inside the micro-integrator. as a new JAVA_OPT property.

Connecting the database to the server¶

Open the deployment.toml file in the <MI_HOME>/conf directory and add the following sections to create the connection between the Micro Integrator and the relevant database. Note that you need separate configurations corresponding to the separate databases (clusterdb, userdb, and transactiondb).

Cluster DB Connection

[[datasource]]
id = "WSO2_COORDINATION_DB"
url= "jdbc:oracle:thin:@SERVER_NAME:PORT/SID"
username="root"
password="root"
driver="oracle.jdbc.OracleDriver"
pool_options.maxActive=50
pool_options.maxWait = 60000
pool_options.testOnBorrow = true

User DB Connection

[[datasource]]
id = "WSO2CarbonDB"
url= "jdbc:oracle:thin:@SERVER_NAME:PORT/SID"
username="root"
password="root"
driver="oracle.jdbc.OracleDriver"
pool_options.maxActive=50
pool_options.maxWait = 60000
pool_options.testOnBorrow = true

Transaction Counter DB Connection

[[datasource]]
id = "WSO2_TRANSACTION_DB"
url= "jdbc:oracle:thin:@SERVER_NAME:PORT/SID"
username="root"
password="root"
driver="oracle.jdbc.OracleDriver"
pool_options.maxActive=50
pool_options.maxWait = 60000
pool_options.testOnBorrow = true
[transaction_counter]
enable = true
data_source = "WSO2_TRANSACTION_DB"
update_interval = 2

About User DB

If you replace 'WSO2CarbonDB' with a different id in the user DB configuration, you also need to list the id as a datasource under the [realm_manager] section in the deployment.toml file as shown below.

[realm_manager]
data_source = "new_id"

Otherwise the user store database id defaults to 'WSO2CarbonDB' in the realm manager configurations..

See the descriptions of database connection parameters.

Top